POI方式实现Excel表格数据导出

1         <!-- office 操作工具 -->
2         <dependency>
3             <groupId>org.apache.poi</groupId>
4             <artifactId>poi</artifactId>
5             <version>3.17</version>
6         </dependency>
7         <dependency>
8             <groupId>org.apache.poi</groupId>
9             <artifactId>poi-ooxml</artifactId>
10             <version>3.17</version>
11         </dependency>
12 
13         <dependency>
14             <groupId>org.apache.poi</groupId>
15             <artifactId>poi-scratchpad</artifactId>
16             <version>3.17</version>
17         </dependency>

1 public classMyExcelUtils {
2     /**
3 * @paramtitles  表头
4 * @paramout
5 * @paramlistMap 数据
6 * @paramkeys    数据库字段（与表头相对应）
7      */
8     public static void export(String[] titles, ServletOutputStream out, List<Map<String, Object>>listMap, String[] keys) {
9         try{
10             //1.创建一个workbook，对应一个Excel文件
11             HSSFWorkbook workbook = newHSSFWorkbook();
12             //2.在workbook中添加一个sheet，对应Excel文件中的sheet
13             HSSFSheet hssfSheet = workbook.createSheet("列表1");
14             //3.在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
15             HSSFRow row = hssfSheet.createRow(0);
16             //4.创建单元格 设置表单  居中
17             HSSFCellStyle hssfCellStyle =workbook.createCellStyle();
18             HSSFCell hssfCell = null;
19             for (int i = 0; i < titles.length; i++) {
20                 hssfCell = row.createCell(i);//列索引从0开始
21                 hssfCell.setCellValue(titles[i]);//列名1
22                 hssfCell.setCellStyle(hssfCellStyle);//列居中显示
23 }
24             //5.填充数据
25             for (int i = 0; i < listMap.size(); i++) { //行数
26                 row = hssfSheet.createRow(i + 1); //表数据的起始行数为 1
27                 //数据库数据
28                 Map<String, Object> map =listMap.get(i);
29                 for (int j = 0; j < titles.length; j++) { //列数
30 row.createCell(j).setCellValue((String) map.get(keys[j]));
31 }
32 }
33             //6.将文件输出到客户端浏览器
34             try{
35 workbook.write(out);
36 out.flush();
37 out.close();
38             } catch(Exception e) {
39 e.printStackTrace();
40 }
41         } catch(Exception e) {
42 e.printStackTrace();
43 }
44 }
45 }

1         //Excel表头、文件名（titles的长度和xml中sql查询的字段个数（keys长度）一致）
2         String[] titles = {"编号", "姓名", "年龄"};
3         //Excel文件名
4         String name = "Excel表格下载";
5         //数据库对应字段
6         String[] keys = {"id", "name", "age"};
7
8         response.setContentType("application/binary;charset=UTF-8");
9         try{
10             ServletOutputStream out =response.getOutputStream();
11             try{
12                 //设置文件头：一个参数是设置下载文件名
13                 response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(name + ".xls", "UTF-8"));
14             } catch(UnsupportedEncodingException e1) {
15 e1.printStackTrace();
16 }
17             //数据库对应的需要导出的Excel数据
18             List<Map<String, Object>> listMap =exportExcelDAO.exportExcelData();
19             //MyExcelUtils下载Excel
20 MyExcelUtils.export(titles, out, listMap, keys);
21         } catch(Exception e) {
22 e.printStackTrace();
23         }