Apache POI导出Excel经典代码

"Apache POI "

Posted by 王晓东 on November 14, 2019

引入pom依赖

<!--导出Excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <!--导出Excel end-->

代码

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;
import sun.tools.jar.resources.jar;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Controller
@RequestMapping("/export")
public class ExportToExcelController {
 @Autowired
 private ReportFormImpl reportFormImpl;

 /**
  * 批量导出 白名单信息 到Excel
  *
  * @param excelName
  * @param response
  * @return
  */
 @ResponseBody
 @RequestMapping(value = "/exportStatisticsNum/{excelName}")
 public jar exportWhiteList(@PathVariable("excelName") String excelName,
                            HttpServletResponse response) {
     HSSFWorkbook wb = new HSSFWorkbook();
     //创建工作表
     HSSFSheet sheet = wb.createSheet();
     //创建行列
     HSSFRow nRow = sheet.createRow(0);
     HSSFCell nCell = nRow.createCell(0);
     //控制行号列号
     int rowNo = 0;
     int colNo = 0;
     //列标题
     String[] title;
     title = new String[]{"业务名称", "项目版本", "重要程度", "风险需求数", "风险需求修复数", "安全漏洞数", "安全漏洞修复数", "修复率"};
     //设置标题到第一行的列中
     nRow = sheet.createRow(rowNo++);
     for (int i = 0; i < title.length; i++) {
         nCell = nRow.createCell(i);
         nCell.setCellValue(title[i]);
     }
     try {
         /*
          * 调用逻辑层函数查询
          */

         List<ReportFormQuerySecurityProject> statisticsNumList = reportFormImpl.getStatisticsNum(new QuerySecurityProject());
         //遍历并且创建行列
         for (ReportFormQuerySecurityProject dto : statisticsNumList) {
             //控制列号
             colNo = 0;
             //每遍历一次创建一行
             nRow = sheet.createRow(rowNo++);
             //业务名称
             nCell = nRow.createCell(colNo++);
             nCell.setCellValue(dto.getJobName());
             //项目版本
             nCell = nRow.createCell(colNo++);
             nCell.setCellValue(dto.getProjectVersion());
             //重要程度
             nCell = nRow.createCell(colNo++);
             Byte degree = dto.getImportanceDegree();
             String importanceDegree = "";
             if (degree == 1) {
                 importanceDegree = "严重";
             } else if (degree == 2) {
                 importanceDegree = "高危";
             } else if (degree == 3) {
                 importanceDegree = "中危";
             } else if (degree == 4) {
                 importanceDegree = "低危";
             }
             nCell.setCellValue(importanceDegree);
             //风险需求数
             nCell = nRow.createCell(colNo++);
             nCell.setCellValue(dto.getRiskDemandNum());
             //风险需求修复数
             nCell = nRow.createCell(colNo++);
             nCell.setCellValue(dto.getRiskDemandRepairNum());
             //安全漏洞数
             nCell = nRow.createCell(colNo++);
             nCell.setCellValue(dto.getBugNum());
             //安全漏洞修复数
             nCell = nRow.createCell(colNo++);
             nCell.setCellValue(dto.getBugRepairNum());
             //修复率
             nCell = nRow.createCell(colNo++);
             nCell.setCellValue(String.format("%.2f", dto.getRepairRate()) + "%");
         }
         loadResponse(excelName, response, wb);
     } catch (Exception e) {
     }
     return null;
 }

 /**
  * 设置Excel相关参数
  *
  * @param excelName
  * @param response
  * @param wb
  * @throws IOException
  */
 private void loadResponse(String excelName, HttpServletResponse response, HSSFWorkbook wb) throws IOException {
     //到这里,excel就已经生成了,然后就需要通过流来写出去
     ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
     //将excel写入流
     wb.write(byteArrayOutputStream);
     //设置文件标题
     String dateTime = DateFormatUtils.format(new Date(), "yyyyMMddHHmm");
     String outFile = excelName + dateTime + ".xls";
     //设置返回的文件类型
     response.setContentType("application/vnd.ms-excel;charset=utf-8");
     //对文件编码
     outFile = response.encodeURL(new String(outFile.getBytes("gb2312"), "iso8859-1"));
     //使用Servlet实现文件下载的时候,避免浏览器自动打开文件
     response.addHeader("Content-Disposition", "attachment;filename=" + outFile);
     //设置文件大小
     response.setContentLength(byteArrayOutputStream.size());
     //创建Cookie并添加到response中
     Cookie cookie = new Cookie("fileDownload", "true");
     cookie.setPath("/");
     response.addCookie(cookie);
     //将流写进response输出流中
     ServletOutputStream outputstream = response.getOutputStream();
     byteArrayOutputStream.writeTo(outputstream);

     byteArrayOutputStream.close();
     outputstream.flush();
 }
}