[JavaExcel] Apache POI 實現下載Excel檔案 (SpringMVC)

這邊沿用上一個SpringMVC 範例

https://dotblogs.com.tw/raylee/2019/04/15/143704


 

pom.xml (記得版本要一致,像我之前沒有一致,runserver 的時候就報錯了)

	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.14</version>
	</dependency> 
	
	<dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
    

index.jsp 多加一個 downloadExcel 連結 

	<center>
		<h2>Hello World</h2>
		<h3>
			<a href="hello?name=Eric">Click Here</a>
			
		</h3>
		<h4>
			<a href="downloadExcel">Click Here Download Excel</a>
		</h4>
	</center>

controller

	@RequestMapping(value = "downloadExcel", method = RequestMethod.GET) 
	@ResponseBody 
	public void downloadExcel(HttpServletRequest request, 
	HttpServletResponse response) throws Exception { 
		
		System.out.println("in downloadExcel");
		
		XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
        Object[][] datatypes = {
                {"Datatype", "Type", "Size(in bytes)"},
                {"int", "Primitive", 2},
                {"float", "Primitive", 4},
                {"double", "Primitive", 8},
                {"char", "Primitive", 1},
                {"String", "Non-Primitive", "No fixed size"}
        };

        int rowNum = 0;
        System.out.println("Creating excel");

        for (Object[] datatype : datatypes) {
            Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            for (Object field : datatype) {
                Cell cell = row.createCell(colNum++);
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Integer) {
                    cell.setCellValue((Integer) field);
                }
            }
        }
		
        String filename = "會員訂單報表.xls";
		String headerFileName =  new String(filename.getBytes(), "ISO8859-1");
        response.setHeader("Content-Disposition", "attachment; filename="+headerFileName);
        
        OutputStream out = null;
        try{
        	out = new BufferedOutputStream(response.getOutputStream());
            workbook.write(out);
        }catch (IOException e){
        	System.out.println("excel匯出有誤");
        }finally {
			try {
				out.close();
				workbook.close();
			} catch (IOException e) {
				System.out.println("讀取內容有誤");
			}
        }
	}

run server 測試

按下click here download excel  

彈跳下載視窗

成功

當然還有很多種實作方法,這只是使用apache poi 

像spring framework 也有,繼承 AbstractExcelView 使用 ModelAndView的方式