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中修改数据，图表可以自动变换