java生成excel并下载

# 依赖使用的是poi

开发中经常会设计到excel的处理，如导出Excel，导入Excel到数据库中，操作Excel目前有两个框架，一个是apache 的poi，另一个是 Java Excel

Apache POI 简介是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office（Excel、WORD、PowerPoint、Visio等）格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“可怜的模糊实现”。

官方主页： http://poi.apache.org/index.html (opens new window)
API文档： http://poi.apache.org/apidocs/index.html (opens new window)

<!--Excel解析-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        </dependencies>

1
2
3
4
5
6
7
8
9
10
11
12

# controller层代码

请求地址接口

   @RequestMapping("excelExport")
   @ApiOperation(value = "导出下载资金信息接口", httpMethod = "POST")
   public void excelExport(HttpServletRequest request, HttpServletResponse response, String ids) {
       townsFundService.excelExport(request,response,ids);
   }

1
2
3
4
5

# service层

这里处理代码逻辑和数据汇总mapper层请求数据大同小异看自己需求这里不做过多描述

@Override
   public void excelExport(HttpServletRequest request, HttpServletResponse response, String ids) {
       //根据id查询数据
       List list = Arrays.asList(ids.split(","));
       List<Map<String, Object>> townsInfobyIds = townsFundMapper.getTownsInfobyIds(list);

       //文件名称
       String fileName = "text.xlsx";
       //Excel文件
       XSSFWorkbook workBook = new XSSFWorkbook();
       //Excel页脚
       XSSFSheet sheet = workBook.createSheet("数据导出");
       //设置列的宽度
       sheet.setDefaultColumnWidth(16);
       //创建标题行
       XSSFRow titleRow = sheet.createRow(0);
       String[] title = new String[]{"id", "name", "sex"};
       //设置标题字体样式
       XSSFCellStyle cellStyle = workBook.createCellStyle();
       XSSFFont font = workBook.createFont();
       font.setBold(true);//加粗
       font.setFontHeightInPoints((short) 14);//设置字体大小
       cellStyle.setFont(font);
       //设置标题列
       for (int i = 0; i < title.length; i++) {
           //创建标题的单元格
           XSSFCell titleCell = titleRow.createCell(i);
           //填充标题数值
           titleCell.setCellValue(title[i]);
           //设置样式
           titleCell.setCellStyle(cellStyle);
       }
       //填充数据
       //第一行是标题所以要从第二行开始
       for (int i = 0; i < townsInfobyIds.size(); i++) {
           Map<String, Object> item = townsInfobyIds.get(i);
           XSSFRow row = sheet.createRow(i + 1);
           for (int j = 0; j < title.length; j++) {
               XSSFCell titleCell = row.createCell(j);
               String exportKey = title[j];
               switch (exportKey) {
                   case "id":
                       titleCell.setCellValue(item.get("id")==null?"":item.get("id").toString());
                       break;
                   case "name":
                       titleCell.setCellValue(item.get("name")==null?"":item.get("name").toString());
                       break;
                   case "sex":
                       titleCell.setCellValue(item.get("sex")==null?"":item.get("sex").toString());
                       break;
               }
           }
       }
       response.reset();
       try {
           setResponseHeader(request,response,fileName);
           //创建页面输出流对象
           ServletOutputStream outputStream = response.getOutputStream();
           workBook.write(outputStream);
       } catch (Exception e) {
           log.info(e.getMessage());
       }
   }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

# setResponseHeader方法

实现页面下载文件的方法

public void setResponseHeader(HttpServletRequest request,HttpServletResponse response, String fileName)  {
//fileName 文件名称
       try {
           String agent = request.getHeader("USER-AGENT").toLowerCase();
           if(StringUtils.contains(agent, "Mozilla")){
               fileName = new String(fileName.getBytes(), "ISO8859-1");
           }else {
               fileName = URLEncoder.encode(fileName, "utf8");
           }
           response.setCharacterEncoding("UTF-8");
           response.setContentType("application/vnd.ms-excel;charset=utf-8");// 设置contentType为excel格式
           response.setHeader("Content-Disposition", "Attachment;Filename="+ fileName);
       } catch (Exception e) {
           e.printStackTrace();
       }
   }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

上次更新: 2022/11/01, 11:29:52