Springboot + Apache POI实现Excel文件导出

Apache POI实现Excel文件导出

---

---

一、Apache Poi是什么？

Apache POI 是基于 Office Open XML 标准（OOXML）和 Microsoft 的 OLE 2 复合文档格式（OLE2）处理各种文件格式的开源项目。

二、使用步骤

1.导入核心依赖

<!--POI-->
<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>

2.拦截获取本次响应的HttpServletResponse,并获取输出流对象ServletOutputStream

    @GetMapping("/excelLeadingOut")
    public void excelLeadingOut(HttpServletResponse response) {
        try {
            ServletOutputStream outputStream = response.getOutputStream();/*从response获取输出流对象*/
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("Excel导出表.xlsx", "UTF-8"));//设置导出Excel文件名
            operLogService.excelLeadingOut(outputStream);
            outputStream.flush();/*清空输出流*/
            outputStream.close();/*关闭此OutputStream流并释放输出流资源*/
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

3.使用POI 创建工作表，调整样式。

 @Override
    public void excelLeadingOut(OutputStream outputStream) throws IOException {
        /*创建List对象作为测试数据*/
        List<demo.Company> companyList = Arrays.asList(
                new demo.Company("北京部门", "张三"),
                new demo.Company("上海部门", "李四"),
                new demo.Company("北京部门", "王五"));
        /*逻辑处理*/
        if (ObjectUtils.isNotEmpty(companyList)) {
//            System.out.println(companyList);
            if (companyList.size() > 0) {
                XSSFWorkbook wb = new XSSFWorkbook();//创建工作表
                XSSFCellStyle xssfCellStyle = wb.createCellStyle();//创建格式对象
                xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
                xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
                xssfCellStyle.setBorderTop(BorderStyle.THIN);//上边框
                xssfCellStyle.setBorderBottom(BorderStyle.THIN);//下边框
                xssfCellStyle.setBorderLeft(BorderStyle.THIN);//左边框
                xssfCellStyle.setBorderRight(BorderStyle.THIN);//有边框

                XSSFFont xssfFont = wb.createFont();  //生成一个字体对象
                xssfFont.setFontHeightInPoints((short) 12);//以点为单位设置字体高度
                xssfFont.setFontName("宋体");//设置字体样式
                xssfCellStyle.setFont(xssfFont);//把字体应用到当前样式
                xssfCellStyle.setWrapText(true);//设置单元格内容自动换行

                XSSFSheet xssfSheet = wb.createSheet("Sheet名称1");/*工作表名*/
                XSSFRow row = xssfSheet.createRow(0);/*定义序号*/
                List<String> title = new ArrayList<>();
                title.add("序号");
                title.add("部门名称");
                title.add("成员名称");
                for (int j = 0; j < title.size(); j++) {
                    XSSFCell cell = row.createCell(j);
                    if (j == 0) {
                        xssfSheet.setColumnWidth(j, 50 * 40);/*序号列宽*/
                    } else {
                        xssfSheet.setColumnWidth(j, 256 * 40);/*其余列宽*/
                    }
                    cell.setCellValue(title.get(j));
                    cell.setCellStyle(xssfCellStyle);
                }
                int rowLength = 1;
                int rowIndex = 1;
                for (demo.Company a : companyList) {
                    row = xssfSheet.createRow(rowLength);
                    XSSFCell cell = row.createCell(0);
                    cell.setCellValue(rowIndex);/*序号值*/
                    cell.setCellStyle(xssfCellStyle);/*应用样式*/
                    cell = row.createCell(1);
                    cell.setCellValue(a.getDepartment());/*部门名称值*/
                    cell.setCellStyle(xssfCellStyle);/*应用样式*/
                    cell = row.createCell(2);
                    cell.setCellValue(a.getPersonName());/*成员名称值*/
                    cell.setCellStyle(xssfCellStyle);/*应用样式*/
                    rowLength = rowLength + 1;
                    rowIndex = rowIndex + 1;
                }
                wb.write(outputStream);
            }
        }
    }

4.使用浏览器测试