使用Spring Boot创建Excel

概述

使用ApachePoi来进行Excel文件的输出。
通常情况下，需要创建一个继承AbstractXlsxView的类来进行Excel输出。
然而，在这种情况下，在返回响应时创建Excel文件，所以无法在Controller上处理输出失败的情况。
因此，我们可以继承AbstractView，并创建一个部分继承AbstractXlsxView的类来解决这个问题。
另外，我们还要支持xls格式，并且如果存在一个模板的Excel文件，则可以使用它，使得这个类具有通用性。

摘要XcelView

public abstract class AbstractXcelView extends AbstractView {

	protected String templateFile;

	private Workbook workbook = null;

	public void createWorkbook(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		if (Objects.isNull(templateFile)) {
			Object fileName = model.get("fileName");
			if (FilenameUtils.getExtension((String) fileName).equals("xlsx")) {
				workbook = new XSSFWorkbook();
			} else {
				workbook = new HSSFWorkbook();
			}
		} else {
			try (InputStream is = new ClassPathResource("excel/" + templateFile).getInputStream()) {
				workbook = WorkbookFactory.create(is);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		buildExcelDocument(model, workbook, request, response);
	}

	@Override
	protected boolean generatesDownloadContent() {
		return true;
	}

	@Override
	protected final void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request,
			HttpServletResponse response) throws Exception {

		String fileName = (String) model.get("fileName");
		if (fileName != null) {
			String encodedFilename = URLEncoder.encode(fileName, "UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFilename);
		}

		response.setContentType(getContentType());
		ServletOutputStream out = response.getOutputStream();
		workbook.write(out);
		workbook.close();
	}

    // AbstractXlsxViewを踏襲
	protected abstract void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception;

}

测试控制器 test

AbstractXcelViewを継承したTestExcelViewを用意する（buildExcelDocumentを実装する）

@Controller
@RequestMapping("/test")
public class TestController {

	@Autowired
	private TestService testService;

	@RequestMapping("/excel")
    public ModelAndView excel(TestForm form, ModelAndView mav
			HttpServletRequest request, HttpServletResponse response) {
        List<Test> dataList = testService.getDataList();
        mav = new ModelAndView(new TestExcelView());
        mav.addObject("dataList", dataList);
		mav.addObject("fileName", "test.xlsx");
		try {
			View view = mav.getView();
			if (view instanceof AbstractXcelView) {
				((AbstractXcelView) view).createWorkbook(mav.getModel(), request, response);
			}
		} catch (Exception e) {
			// エラーの場合
		}
		return mav;
	}
}