区块链技术博客
www.b2bchain.cn

EasyExcel导出不同表头的Sheet页or分页查询导出

这篇文章主要介绍了EasyExcel导出不同表头的Sheet页or分页查询导出的讲解,通过具体代码实例进行20933 讲解,并且分析了EasyExcel导出不同表头的Sheet页or分页查询导出的详细步骤与相关技巧,需要的朋友可以参考下https://www.b2bchain.cn/?p=20933

本文实例讲述了2、树莓派设置连接WiFi,开启VNC等等的讲解。分享给大家供大家参考文章查询地址https://www.b2bchain.cn/7039.html。具体如下:

目录

  • 前言
  • 代码部分
  • 结果

前言

嗯,昨天小伙伴在导出一百多万数据,用动态分多个sheet页。还是很好用的。但是由于本地开发环境垃圾(小声bb公司给配的电脑性能),因为内存有限。在导出几百万数据时,查询映射实体,内存爆掉了。(再次吐槽。。。)提高本地开发环境的JVM大小已经解决不了问题了。然后就想到了分页查询,分页写入的方法。查询一部分数据,写入。再查一部分,再次写入。于是乎,改了改导出的工具类EasyExcelUtil 。

代码部分

  • EasyExcelUtil
package com.sinosoft.service.util;  import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.support.ExcelTypeEnum; import com.alibaba.excel.write.builder.ExcelWriterBuilder; import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import com.sinosoft.web.rest.util.SplitList; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.http.*;  import javax.servlet.http.HttpServletResponse; import java.io.*; import java.net.URLEncoder; import java.util.Date; import java.util.List;  /**  * @author QY  * @since 2020-08-03  * @description 使用easyExcel来导出xlsx的工具类  */ public class EasyExcelUtil {      private static final Logger log = LoggerFactory.getLogger(EasyExcelUtil.class);      private static final int MAXROWS = 1000000;      /**      * 获取默认表头内容的样式      * @return      */     private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy(){         /** 表头样式 **/         WriteCellStyle headWriteCellStyle = new WriteCellStyle();         // 背景色(浅灰色)         // 可以参考:https://www.cnblogs.com/vofill/p/11230387.html         headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());         // 字体大小         WriteFont headWriteFont = new WriteFont();         headWriteFont.setFontHeightInPoints((short) 10);         headWriteCellStyle.setWriteFont(headWriteFont);         //设置表头居中对齐         headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);         /** 内容样式 **/         WriteCellStyle contentWriteCellStyle = new WriteCellStyle();         // 内容字体样式(名称、大小)         WriteFont contentWriteFont = new WriteFont();         contentWriteFont.setFontName("宋体");         contentWriteFont.setFontHeightInPoints((short) 10);         contentWriteCellStyle.setWriteFont(contentWriteFont);         //设置内容垂直居中对齐         contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);         //设置内容水平居中对齐         contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);         //设置边框样式         contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);         contentWriteCellStyle.setBorderTop(BorderStyle.THIN);         contentWriteCellStyle.setBorderRight(BorderStyle.THIN);         contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);         // 头样式与内容样式合并         return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);     }      /**      * 导出,单个Sheet,最大支持xlsx格式sheet的行数      * @param response      * @param data      * @param fileName      * @param sheetName      * @param clazz      * @throws Exception      */     public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {         long exportStartTime = System.currentTimeMillis();         log.info("报表导出Size: "+data.size()+"条。");         EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(getDefaultHorizontalCellStyleStrategy()).doWrite(data);         System.out.println("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );     }      /**      * 单一类型大批量数据导出,适用于超过一百万的数据,需要分多个sheet页来导出。自动分页      * @author QiuYu      * @createDate 2020-11-16      * @param response      * @param data  查询结果      * @param fileName 导出文件名称      * @param clazz 映射实体class类      * @param <T>  查询结果类型      * @throws Exception      */     public static<T> void writeExcel(HttpServletResponse response, List<T> data, String fileName, Class clazz) throws Exception {         long exportStartTime = System.currentTimeMillis();         log.info("报表导出Size: "+data.size()+"条。");          List<List<T>> lists = SplitList.splitList(data,MAXROWS); // 分割的集合          OutputStream out = getOutputStream(fileName, response);         ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getDefaultHorizontalCellStyleStrategy());         ExcelWriter excelWriter = excelWriterBuilder.build();         ExcelWriterSheetBuilder excelWriterSheetBuilder;         WriteSheet writeSheet;         for (int i =1;i<=lists.size();i++){             excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);             excelWriterSheetBuilder.sheetNo(i);             excelWriterSheetBuilder.sheetName("sheet"+i);             writeSheet = excelWriterSheetBuilder.build();             excelWriter.write(lists.get(i-1),writeSheet);         }         out.flush();         excelWriter.finish();         out.close();         System.out.println("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );     }      public static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {         fileName = URLEncoder.encode(fileName, "UTF-8");         //  response.setContentType("application/vnd.ms-excel"); // .xls         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); // .xlsx         response.setCharacterEncoding("utf8");         response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");         return response.getOutputStream();     }      /**      * @author QiuYu      * @createDate 2020-11-18      * @param out 输出流      * @param flag 是否添加默认打印样式,为 true 添加,为 false 不添加。大批量导出去除样式可以节省更多的资源      * @return      */     public static ExcelWriter buildExcelWriter(OutputStream out,Boolean flag)  {         ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out).excelType(ExcelTypeEnum.XLSX);         if (flag){             excelWriterBuilder.registerWriteHandler(getDefaultHorizontalCellStyleStrategy());         }         return excelWriterBuilder.build();     }      /**      * 默认构建带样式      * @author QiuYu      * @createDate 2020-11-18      * @param out      * @return      * @throws Exception      */     public static ExcelWriter buildExcelWriter(OutputStream out)  {         return buildExcelWriter(out,true);     }      /**      * 单纯写入,适用于手动分页      * @author QiuYu      * @param excelWriter      * @param data      * @param clazz      * @param sheetNo      * @param sheetName      * @param <T>      * @throws Exception      */     public static<T> void writeOnly(ExcelWriter excelWriter,List<T> data,Class clazz, Integer sheetNo,String sheetName) {         long exportStartTime = System.currentTimeMillis();         log.info("报表"+sheetNo+"写入Size: "+data.size()+"条。");         ExcelWriterSheetBuilder excelWriterSheetBuilder;         WriteSheet writeSheet;         excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);         excelWriterSheetBuilder.sheetNo(sheetNo);         excelWriterSheetBuilder.sheetName(sheetName);         writeSheet = excelWriterSheetBuilder.build();         writeSheet.setClazz(clazz);         excelWriter.write(data,writeSheet);         log.info("报表"+sheetNo+"写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );     }       /**      * 导出      * @author QiuYu      * @param out      * @param excelWriter      * @throws IOException      */     public static void finishWriter(OutputStream out,ExcelWriter excelWriter) throws IOException {         out.flush();         excelWriter.finish();         out.close();         System.out.println("报表导出结束时间:"+ new Date());     }    } 
  • 测试的Controller
    @GetMapping("/exportEasyExcel")     public void exportEasyExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {         try{             openApiService.exportEasyExcel(response);         }catch (Exception e){             e.getStackTrace();         }     } 
  • 测试的service
   /**      * 多sheet页打印案例,也可以用作手动查询分页导出      * @param response      */     public void exportEasyExcel( HttpServletResponse response){         List<AGYDetailPrintModel> modelList = new ArrayList<>();         for (int i = 1;i<=1000000;i++){             AGYDetailPrintModel agyDetailPrintModel = new AGYDetailPrintModel();             agyDetailPrintModel.setBranchName("setBranchName"+i);             agyDetailPrintModel.setBranchType("setBranchType"+i);             modelList.add(agyDetailPrintModel);         }         List<PrintTEST> modelList2 = new ArrayList<>();         for (int i = 1;i<=1000000;i++){             PrintTEST printTEST = new PrintTEST();             printTEST.setYearMonth("setYearMonth"+i);             printTEST.setBranchType("setBranchType"+i);             modelList2.add(printTEST);         }         try {             // 1 获取导出的流对象             OutputStream outputStream = EasyExcelUtil.getOutputStream("测试报表",response);             // 2 获取EasyExcel写入类             ExcelWriter excelWriter = EasyExcelUtil.buildExcelWriter(outputStream);             // 3.1 写入模板一             EasyExcelUtil.writeOnly(excelWriter,modelList,AGYDetailPrintModel.class,1,"第一个sheet");             // 3.2 写入模板二             EasyExcelUtil.writeOnly(excelWriter,modelList2,PrintTEST.class,2,"第二个sheet");             // 4 结束写入,导出             EasyExcelUtil.finishWriter(outputStream,excelWriter);           } catch (Exception e) {             e.printStackTrace();         }     } 
  • 两个打印表头模型
  1. AGYDetailPrintModel
package com.sinosoft.domain;  import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import com.alibaba.excel.annotation.write.style.HeadRowHeight; import lombok.*;  import java.io.Serializable;   @NoArgsConstructor @AllArgsConstructor @ContentRowHeight(15) @HeadRowHeight(17) public class AGYDetailPrintModel implements Serializable {      @ColumnWidth(10)     @ExcelProperty(value = "月份", index = 0)     private String yearMonth;      @ColumnWidth(10)     @ExcelProperty(value = "销售渠道", index = 1)     private String branchType;      @ColumnWidth(15)     @ExcelProperty(value = "保单号码", index = 2)     private String mainPolNo;      @ColumnWidth(15)     @ExcelProperty(value = "承保日期", index = 3)     private String signDate;      @ColumnWidth(15)     @ExcelProperty(value = "发生日期", index = 4)     private String getPolDate;      @ColumnWidth(15)     @ExcelProperty(value = "代理人编号", index = 5)     private String date;      @ColumnWidth(10)     @ExcelProperty(value = "代理人姓名", index = 6)     private String doubleData;      @ColumnWidth(15)     @ExcelProperty(value = "保险产品代码", index = 7)     private String riskCode;      @ColumnWidth(10)     @ExcelProperty(value = "金额", index = 8)     private String fyc;      @ColumnWidth(15)     @ExcelProperty(value = "回机日期", index = 9)     private String tMakeDate;      @ColumnWidth(10)     @ExcelProperty(value = "计佣年月", index = 10)     private String statWageNo;      @ColumnWidth(8)     @ExcelProperty(value = "账龄日", index = 11)     private String ageDay;      @ColumnWidth(10)     @ExcelProperty(value = "分公司代码", index = 12)     private String branchName;      public String getYearMonth() {         return yearMonth;     }      public void setYearMonth(String yearMonth) {         this.yearMonth = yearMonth;     }      public String getBranchType() {         return branchType;     }      public void setBranchType(String branchType) {         this.branchType = branchType;     }      public String getMainPolNo() {         return mainPolNo;     }      public void setMainPolNo(String mainPolNo) {         this.mainPolNo = mainPolNo;     }      public String getSignDate() {         return signDate;     }      public void setSignDate(String signDate) {         this.signDate = signDate;     }      public String getGetPolDate() {         return getPolDate;     }      public void setGetPolDate(String getPolDate) {         this.getPolDate = getPolDate;     }      public String getDate() {         return date;     }      public void setDate(String date) {         this.date = date;     }      public String getDoubleData() {         return doubleData;     }      public void setDoubleData(String doubleData) {         this.doubleData = doubleData;     }      public String getRiskCode() {         return riskCode;     }      public void setRiskCode(String riskCode) {         this.riskCode = riskCode;     }      public String getFyc() {         return fyc;     }      public void setFyc(String fyc) {         this.fyc = fyc;     }      public String gettMakeDate() {         return tMakeDate;     }      public void settMakeDate(String tMakeDate) {         this.tMakeDate = tMakeDate;     }      public String getStatWageNo() {         return statWageNo;     }      public void setStatWageNo(String statWageNo) {         this.statWageNo = statWageNo;     }      public String getAgeDay() {         return ageDay;     }      public void setAgeDay(String ageDay) {         this.ageDay = ageDay;     }      public String getBranchName() {         return branchName;     }      public void setBranchName(String branchName) {         this.branchName = branchName;     } } 
  1. PrintTEST(不要太在乎命名,为了测试瞎敲得)
package com.sinosoft.domain;  import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import com.alibaba.excel.annotation.write.style.HeadRowHeight; import lombok.*;  import java.io.Serializable;   @NoArgsConstructor @AllArgsConstructor @ContentRowHeight(15) @HeadRowHeight(17) public class PrintTEST implements Serializable {      @ColumnWidth(10)     @ExcelProperty(value = "C1", index = 0)     private String yearMonth;      @ColumnWidth(10)     @ExcelProperty(value = "C2", index = 1)     private String branchType;      @ColumnWidth(15)     @ExcelProperty(value = "C3", index = 2)     private String mainPolNo;      @ColumnWidth(15)     @ExcelProperty(value = "C4", index = 3)     private String signDate;       public String getYearMonth() {         return yearMonth;     }      public void setYearMonth(String yearMonth) {         this.yearMonth = yearMonth;     }      public String getBranchType() {         return branchType;     }      public void setBranchType(String branchType) {         this.branchType = branchType;     }      public String getMainPolNo() {         return mainPolNo;     }      public void setMainPolNo(String mainPolNo) {         this.mainPolNo = mainPolNo;     }      public String getSignDate() {         return signDate;     }      public void setSignDate(String signDate) {         this.signDate = signDate;     }   } 

实体类不用太在意哈,注解会使用就好。

结果

浏览器请求下接口,打印出报表。如下:
EasyExcel导出不同表头的Sheet页or分页查询导出
EasyExcel导出不同表头的Sheet页or分页查询导出
可以看出来,测试的很OK。

同时,该方法也可用于对查询结果多次分页写入。这样能最大程度减少内存占用,如果上了生产,多个用户同时全查询大批量数据。哪怕服务器性能在高,也可能会宕机。所以从代码上下手才是正确的选择。
小弟不才,如有疏漏,还望各位大佬们多多包含。并且留言指出,谢谢。

本文转自互联网,侵权联系删除EasyExcel导出不同表头的Sheet页or分页查询导出

赞(0) 打赏
部分文章转自网络,侵权联系删除b2bchain区块链学习技术社区 » EasyExcel导出不同表头的Sheet页or分页查询导出
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

b2b链

联系我们联系我们