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.結果