POI複制Excel模闆并填充資料

我們最近需要對系統加一個報表導出的功能，可以通過POI直接導出，導出後的excel檔案需要支援在office裡面修改資料後圖表也會自動變換。方法一可以使用jfreechart+poi，但是這種方法生成的圖表是一張圖檔，不能在office中自動修改；第二種方法是poi調用 office的宏，它需要調用自定義的.dll 檔案，也需要在windows環境中，是以不适用。

我這邊采用的是自定義 excel 模版，然後在模版定義圖表 。 然後通過POI改變圖表資料區域的資料值。進而達到改變圖表的目的。

1、先準備一個excel 模版,裡面把需要的資料寫好，然後先自定義圖表，例如圖表樣式如下：

2、把資料區域的内容情況，資料清空之後如下：

3、在javaee工程中引入poi的相關jar包，并把excel模闆放入相應工程的WEB-INF目錄下，名稱換為temple.xlsx

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

4、讀取模闆檔案,這裡讀取的是xlsx方式的，

//excel模闆路徑
			File f = new File(this.getClass().getResource("/").getPath());
		    String filePath = f+File.separator+"WEB-INF"+File.separator+"temple.xlsx";
			File file = new File(filePath);
			FileInputStream in =new FileInputStream(file);
			//讀取excel模闆
			XSSFWorkbook wb = new XSSFWorkbook(in);
			//讀取了模闆内所有sheet内容
			XSSFSheet sheet = wb.getSheetAt(0);
			//如果這行沒有了，整個公式都不會有自動計算的效果的
			sheet.setForceFormulaRecalculation(true);           

如果是xls格式的，就改為：

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
            //讀取excel模闆
            HSSFWorkbook wb = new HSSFWorkbook(fs);

           //讀取了模闆内所有sheet内容
            HSSFSheet sheet = wb.getSheetAt(0);           

5、找到相應的資料行，進行資料填充，要從0開始計數，excel中的第1行，讀取的時候是從0開始的，例如我這邊添加表頭，填充曆史客流、年齡等資訊進去

Calendar cal = Calendar.getInstance();
			String publishTime = DateUtil.format(cal.getTime(), "yyyy-MM-dd HH:mm:ss");
			if(null==name){
				sheet.getRow(1).getCell(0).setCellValue("景區:全部");
			}else{
				sheet.getRow(1).getCell(0).setCellValue("景區:"+name);
			}
			if(null==beginTime){
				sheet.getRow(1).getCell(2).setCellValue("時間:全部");
			}else{
				sheet.getRow(1).getCell(2).setCellValue("時間:"+beginTime+"～"+endTime);
			}
			sheet.getRow(1).getCell(6).setCellValue("導出時間:"+publishTime);
			
			//曆史客流
			int t1=3;
			Map<String, String> param = initParam(null,beginTime, endTime);
			List<Map<String, Object>> daycountlist = dayCountService.selectHistoryDayCountList(null,null,null,name,param.get("beginTime"), param.get("endTime"),12);
			for (Map<String, Object> map2 : daycountlist) {
                for (Map.Entry<String, Object> m : map2.entrySet()) {
                    sheet.getRow(t1).getCell(1).setCellValue(m.getKey());
				    sheet.getRow(t1).getCell(2).setCellValue(Integer.parseInt(m.getValue().toString()));
				    t1++;
                }
            }
//性别
			Map<String, Object> sexInfo = sexCountService.selectContrastsInfo(name, beginTime, endTime);
			int s1=30;
			for (Object v : sexInfo.values()) {
				   sheet.getRow(s1).getCell(2).setCellValue(Double.parseDouble(v.toString())/100.0);
				   s1++;
			}
			//年齡
			Map<String, Object> ageInfo = ageCountService.selectContrastsInfo(name, beginTime, endTime);
			int s2=33;
			for (Object v : ageInfo.values()) {
				   sheet.getRow(s2).getCell(2).setCellValue(Double.parseDouble(v.toString())/100.0);
				   s2++;
			}           

6、儲存檔案

// 儲存檔案的路徑
			String realPath = "/mnt/app/tomcat/webapps/uploadfile/";
			if(null==name){
				name="全部";
			}
			String newFileName = "report-" +name+"-"+ DateUtil.getAllTime()+ ".xlsx";
			// 判斷路徑是否存在
			File dir = new File(realPath);
			if (!dir.exists()) {
				dir.mkdirs();
			}
			//修改模闆内容導出新模闆
			FileOutputStream out = new FileOutputStream(realPath+newFileName);
			wb.write(out);
			out.close();           

7、将檔案路徑傳回給前端，直接給前端一個檔案的url連結，讓他自己location.href跳轉就可以拿到檔案了

map.put("url", "/uploadfile/"+newFileName);

或者也可以使用response傳回

//傳回檔案給前端

FileUtil.downloadFiles(response, realPath+newFileName);

public static void downloadFiles(HttpServletResponse response,
            String filePath) {
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("UTF-8");
        FileInputStream fs = null;
        BufferedInputStream buff = null;
        OutputStream myout = null;
 
        try {
            File file = new File(filePath.trim());
            if (file.exists()) {
                String fileName = file.getName();
                fs = new FileInputStream(file);
                response.addHeader(
                        "Content-Disposition",
                        "attachment;filename="
                                + URLEncoder.encode(fileName, "UTF-8"));
                buff = new BufferedInputStream(fs);
                byte[] b = new byte[1024];
                long k = 0;
                myout = response.getOutputStream();
                while (k < file.length()) {
                    int j = buff.read(b, 0, 1024);
                    k += j;
                    myout.write(b, 0, j);
                }
                buff.close();
            } else {
                PrintWriter os = response.getWriter();
                os.write("檔案不存在");
                os.close();
            }
            if (myout != null) {
                myout.flush();
                myout.close();
            }
            if (fs != null) {
                fs.close();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (myout != null) {
                try {
                    myout.flush();
                    myout.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
 
    }           

8、導出後效果如下，而且手動在excel中修改資料，圖表可以自動變換