[JAVA] Download excel using spring mvc

image.png

//印刷ボタン @RequestMapping(value = "", params = "print", method = RequestMethod.POST) public String print(HttpServletResponse response,WorkmonthForm workmonthForm, Model model,ModelMap map) throws IOException {

	response.reset();
	response.setContentType("application/vnd.ms-excel;charset=utf-8");

String sheetNm = "Cooperating company employee leave table"; String fileNm = "2019-04 roster"; String fileName = URLEncoder.encode ("2019-04 roster", "UTF-8");

	Workbook workbook = null;
	BufferedInputStream bufferedInputStream = null;
	BufferedOutputStream bufferedOutputStream = null;
	ByteArrayOutputStream os = new ByteArrayOutputStream();

	try {

		String filePath = getClass().getResource("/abc.xls").getPath();
		filePath = filePath.substring(1);
		FileInputStream fileInputStream = new FileInputStream(new File(filePath));

		workbook = WorkbookFactory.create(fileInputStream);
		Sheet sheet = workbook.getSheet(sheetNm);





		List<WorkmonthDto> workmonths = (List<WorkmonthDto>) map.get("workmonths");
		if (workmonths !=null && workmonths.size()>0) {
			for (int i = 0; i < workmonths.size(); i++) {
				WorkmonthDto workmonth = workmonths.get(i);

				Cell cell_start = sheet.getRow(4+i).getCell(2);
				Cell cell_end = sheet.getRow(4+i).getCell(3);


				String start = workmonth.getStart_h()+":"+workmonth.getStart_m();
				String end = workmonth.getEnd_h()+":"+workmonth.getEnd_m();

				if (!":".equals(start)) {
					double time = DateUtil.convertTime(start);
					cell_start.setCellValue(time);
				}

				if (!":".equals(end)) {
					double time = DateUtil.convertTime(end);
					cell_end.setCellValue(time);
				}

				Cell cell_biko1 = sheet.getRow(4+i).getCell(7);
				cell_biko1.setCellValue(workmonth.getBiko1());

				Cell cell_biko2 = sheet.getRow(4+i).getCell(8);
				cell_biko1.setCellValue(workmonth.getBiko2());


			}
		}



		workbook.write(os);
		byte[] content = os.toByteArray();
		InputStream inputStream = new ByteArrayInputStream(content);

		response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileNm + ".xls").getBytes(), "UTF-8"));
		//response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileNm, "UTF-8"));

		ServletOutputStream out = response.getOutputStream();

		bufferedInputStream = new BufferedInputStream(inputStream);
		bufferedOutputStream = new BufferedOutputStream(out);
		byte[] buff = new byte[2048];
		int bytesRead;

		while (-1 != (bytesRead = bufferedInputStream.read(buff, 0, buff.length))) {
			bufferedOutputStream.write(buff, 0, bytesRead);
		}

	} catch (FileNotFoundException e) {
		logger.info(e.getMessage());
	} catch (EncryptedDocumentException e) {
		logger.info(e.getMessage());
	} catch (InvalidFormatException e) {
		logger.info(e.getMessage());
	} catch (IOException e) {
		logger.info(e.getMessage());
	} finally {
		if (bufferedInputStream != null)
			bufferedInputStream.close();
		if (bufferedOutputStream != null)
			bufferedOutputStream.close();
	}

	return null;
}

Recommended Posts

Download excel using spring mvc
Download images using requests
Download images from "Irasutoya" using Scrapy
Excel graph creation using python xlwings