super-csvを使用したcsv出力処理

1.目的

scheduleの内容をCSV出力したい

Top画面で表示しているscheduleテーブルの内容を結果DLボタンを押下してCSV出力

2.事前準備

build.gradleにsuper-csvを追加

build.gradle

plugins {
    id 'org.springframework.boot' version '2.3.3.RELEASE'
    id 'io.spring.dependency-management' version '1.0.10.RELEASE'
    id 'java'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.3'
    compileOnly 'org.projectlombok:lombok'
    developmentOnly 'org.springframework.boot:spring-boot-devtools'
    runtimeOnly 'mysql:mysql-connector-java'
    annotationProcessor 'org.springframework.boot:spring-boot-configuration-processor'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
    implementation 'javax.validation:validation-api:2.0.1.Final'
    // https://mvnrepository.com/artifact/javax.validation/validation-api
    implementation 'javax.validation:validation-api:2.0.1.Final'
    // https://mvnrepository.com/artifact/org.hibernate.validator/hibernate-validator
    runtimeOnly 'org.hibernate.validator:hibernate-validator:6.0.17.Final'
    // https://mvnrepository.com/artifact/org.glassfish/javax.el
    runtimeOnly 'org.glassfish:javax.el:3.0.1-b11'
    // https://mvnrepository.com/artifact/org.webjars/fullcalendar
    compile group: 'org.webjars.bower', name: 'fullcalendar', version: '3.5.1'
    // https://mvnrepository.com/artifact/org.webjars.bower/moment
    compile group: 'org.webjars.bower', name: 'moment', version: '2.19.1'
    // https://mvnrepository.com/artifact/org.webjars/jquery
    compile group: 'org.webjars', name: 'jquery', version: '2.0.3'
    // https://mvnrepository.com/artifact/com.github.mygreen/super-csv-annotation
    compile group: 'com.github.mygreen', name: 'super-csv-annotation', version: '2.2'
}

test {
    useJUnitPlatform()
}

3.Controller

HttpServletResponseにヘッダー情報を設定し、Controller内でCSV出力処理メソッドを呼び出す
service処理内でファイル書き込み関連の例外がraiseされる可能性があるため、IOExceptionをthrow宣言しておく

TopController.java

...中略

//CSV出力リクエストの受付
    @RequestMapping(value = "/top/csv", method = RequestMethod.GET)
    public String csvDownload(HttpServletResponse response) throws IOException {
        String header = String.format("attachment; filename=\"%s\";", UriUtils.encode("result.csv", StandardCharsets.UTF_8.name()));
        response.setHeader(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_OCTET_STREAM_VALUE);
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, header);
        topService.csvDownload(response);
        return "/top";
    }

4.Service

org.supercsv.io.CsvMapWriterクラスのインスタンスをOutputStreamWriter及びエクセル形式可のコンストラクタで生成する
DBからスケジュールの情報を取得したBeanをフォーマット化しつつwriteHeader / writeCommentメソッドで書き込む

TopService.java

package com.example.alhproject.service;

import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.Charset;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.supercsv.io.CsvMapWriter;
import org.supercsv.prefs.CsvPreference;

import com.example.alhproject.entity.Schedule;
import com.example.alhproject.mapper.ScheduleMapper;

@Service
public class TopService {
    @Autowired
    private ScheduleMapper scheduleMapper;

    private static final String OUTPUT_SCHEDULE_FORMAT = "%s,%s,%s,%s,%s,%s";
    private static final String SJIS = "SJIS";
    private static final String TITLE = "title";
    private static final String CONTEXT = "context";
    private static final String USER_ID= "user_id";
    private static final String CREATED_DATE = "created_date";
    private static final String SCHEDULE_START_TIME = "schedule_start_time";
    private static final String SCHEDULE_END_TIME = "schedule_end_time";

    //scheduleテーブル内容取得
    public List<Schedule> getAllSchedule() {
        return scheduleMapper.selectAll();
    }

    //CSV出力処理
    public void csvDownload(HttpServletResponse response) throws IOException {
        try (OutputStreamWriter osw = new OutputStreamWriter(response.getOutputStream(), Charset.forName(SJIS));
                CsvMapWriter wr = new CsvMapWriter(osw, CsvPreference.EXCEL_NORTH_EUROPE_PREFERENCE)) {
            wr.writeHeader(String.format(OUTPUT_SCHEDULE_FORMAT,
                    TITLE,
                    CONTEXT,
                    USER_ID,
                    CREATED_DATE,
                    SCHEDULE_START_TIME,
                    SCHEDULE_END_TIME
            ));

            getAllSchedule().forEach(dbsc -> {
                String scheduleResult = String.format(OUTPUT_SCHEDULE_FORMAT,
                        dbsc.getTitle(),
                        dbsc.getContext(),
                        dbsc.getUserId().toString(),
                        dbsc.getCreatedDate().toString(),
                        dbsc.getScheduleStartTime().toString(),
                        dbsc.getScheduleEndTime().toString());
                try {
                    wr.writeComment(scheduleResult);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            });
        }
    }
}

(参)レコード名と完全一致する場合は,LazyCsvAnnotationBeanWriterが使いやすそう
https://mygreen.github.io/super-csv-annotation/sphinx/labelledcolumn.html

Sample.java

    // 全レコードを一度に書き込む場合
    public void sampleWriteAll() {
...
        LazyCsvAnnotationBeanWriter<UserCsv> csvWriter = new LazyCsvAnnotationBeanWriter<>(
                SampleCsv.class,
                Files.newBufferedWriter(new File("sample.csv").toPath(), Charset.forName("Windows-31j")),
                CsvPreference.STANDARD_PREFERENCE);
...

5.結果