easypoi导出合并单元格并统计

拼搏现实的明天。 2022-12-19 03:55 573阅读 0赞

### easypoi导出合并单元格并统计 ###

*  pom文件
 *  这里是工具方法
 *  接口层
 *  调用
 *  关于合并都在实体类里面（主要是结构和注解）
 *  结果

# pom文件 #

<dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version> 4.2.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>4.2.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>4.2.0</version>
            </dependency>

# 这里是工具方法 #

import cn.afterturn.easypoi.excel.ExcelExportUtil;
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import org.apache.poi.ss.usermodel.Workbook;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.io.UnsupportedEncodingException;
    import java.util.List;
    
    public class AbstractExcelOut implements IExcelOut { 
    
        private String fileName;
        private HttpServletResponse response;
    
        private AbstractExcelOut() { 
    
        }
    
        public AbstractExcelOut(String fileName, HttpServletResponse response) { 
            this.fileName = fileName;
            this.response = response;
            this.responseInit();
    
        }
    
        private void responseInit() { 
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            try { 
                response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xls");
            } catch (UnsupportedEncodingException e) { 
                e.printStackTrace();
            }
    
        }
    
    // @Override
    // public <T extends BaseRowModel> void excelOut(List<T> dataList, Class<T> tClass) throws IOException { 
    // ServletOutputStream out = null;
    // out = response.getOutputStream();
    // ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLS, true);
    // Sheet sheet = new Sheet(1, 0, tClass);
    // //设置自适应宽度
    // sheet.setAutoWidth(Boolean.TRUE);
    // sheet.setSheetName(fileName);
    // writer.write(dataList, sheet);
    // writer.finish();
    // out.flush();
    // response.getOutputStream().close();
    // out.close();
    // }
    
        @Override
        public void excelOut(ExportParams exportParams, List<?> dataList, Class<?> tClass) throws Exception { 
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, tClass, dataList);
            setExportExcelFormat(workbook);
        }
    
        /** * excel导出时 * 返回头设置下载，并设置文件名 * 注意：以下设置编码格式是为了ie，当前项目是ie下使用的。360或者谷歌可能会文件名会乱码。根据自己需要调整编码。或者不用设置这么多，直接outStream输出得了。 * * @param workbook * @throws Exception */
        private void setExportExcelFormat(Workbook workbook) throws Exception { 
            ServletOutputStream outStream = null;
            try { 
                outStream = response.getOutputStream();
                workbook.write(outStream);
            } finally { 
                outStream.close();
            }
        }
    
    }

# 接口层 #

import cn.afterturn.easypoi.excel.entity.ExportParams;
    import java.util.List;
    
    public interface IExcelOut { 
    
    // void responseInit() throws UnsupportedEncodingException;
    
    // <T extends BaseRowModel> void excelOut(List<T> dataList, Class<T> tClass) throws IOException;
    
    
         void excelOut(ExportParams exportParams, List<?> dataList, Class<?> tClass) throws Exception;
    
    }

# 调用 #

String ORDER_File_NAME = "order-excel";
        String ORDER_SHEET_NAME = "order-excel";
        String ORDER_TITLE = "采购统计表";
    
    AbstractExcelOut abstractExcelOut = new AbstractExcelOut(ORDER_File_NAME, response);
            abstractExcelOut.excelOut(new ExportParams(ORDER_TITLE, ORDER_SHEET_NAME), resultList, YmzcOrderStatisticalVo.class);

# 关于合并都在实体类里面（主要是结构和注解） #

关于合并只有两点：
    1、类的数据结构。
    2、注解
    	@ExcelCollection(name = "") 集合使用
    	@Excel(name = "采购数量合计（支）", width = 30, orderNum = "5",needMerge = true, isStatistics = true)
    		name:列的标题名称，width ：表格的宽度， orderNum ：排序（注意每个类中的排序为单独的  不同类排序不通用） 
    		needMerge ： 是否合并单元格， isStatistics：是否进行统计（若为true，则在表格最后自动生成最终统计）
    
    @Data
    @ApiModel("采购统计以及导出")
    public class YmzcOrderStatisticalVo implements Serializable { 
    
        @ExcelCollection(name = "")
        @ApiModelProperty(value = "")
        private List<ExcelSheetHead> number;
    
        /** * 采购单位名称 */
        @ApiModelProperty(value = "采购单位名称")
        @Excel(name = "采购单位", width = 30, orderNum = "3",needMerge = true)
        private String creator;
    
    
        /** * 采购详情内容 */
        @ApiModelProperty(value = "采购疫苗详情")
        @ExcelCollection(name = "", orderNum = "4")
        private List<YmzcOrderVaccineDetailsVo> detailsList;
        /** * 采购单位ID */
        @ApiModelProperty(value = "采购单位ID")
        private Long creatorId;
        /** * 采购数量合计（支） */
        @ApiModelProperty(value = "采购数量合计（支）")
        @Excel(name = "采购数量合计（支）", width = 30, orderNum = "5",needMerge = true, isStatistics = true)
        private Integer statisticalNumber;
    
        /** * 采购金额合计 */
        @ApiModelProperty(value = "采购数量合计（支）")
        @Excel(name = "采购金额合计(元)", width = 30, orderNum = "6",needMerge = true, isStatistics = true)
        private BigDecimal statisticalPrice;
    
    
    
    }

# 结果 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU2Mzg4MA_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU2Mzg4MA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/6a2d3239191249329f45853cf02c2f34.png