导出excel实现（POI）

柔情只为你懂 2022-01-16 02:15 314阅读 0赞

## 1.引入pom依赖 ##

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

## 2.Controller代码 ##

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();
     }
    }

## 然后直接在浏览器访问就行 ##