1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

Spring BootでExcel作成

Last updated at Posted at 2023-11-02

概要

ApachePoiを利用してExcelファイル出力を行う。
通常はAbstractXlsxViewを継承したクラスを作成して、Excel出力を行う。
ただこの場合、レスポンスを返すときにExcelを作成しているので、出力に失敗した場合にController上でハンドリングできない。
以下では、AbstractViewを継承して、一部AbstractXlsxViewを踏襲したクラスを作成する。
またxlsにも対応させ、テンプレートのExcelファイルがある場合はこれも利用できるようにして、汎用的なクラスにしている。

AbstractXcelView

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;

}

TestController

  • 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;
	}
}
1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?