EasyPoi如何使用注解导出，并且添加自增序号？ |
您所在的位置：网站首页 › excel表格列增加序号 › EasyPoi如何使用注解导出，并且添加自增序号？ |
|
EasyPoi如何使用注解导出，添加自增序号？
1.先看一下导出的实体和接口写法 import cn.afterturn.easypoi.excel.annotation.Excel; import com.fasterxml.jackson.annotation.JsonFormat; import com.github.wxiaoqi.security.common.dto.BaseDTO; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import java.math.BigDecimal; import java.util.Date; /** ** 销售订单跟踪 * * */ @Data @ApiModel public class SalesOrderTrackDTO extends BaseDTO { private static final long serialVersionUID = 1L; @Excel(name = "单据编号",width = 20) @ApiModelProperty("单据编号") private String billNo; // @Excel(name = "客户id") @ApiModelProperty("客户id") private String customerId; @Excel(name = "客户编号") @ApiModelProperty("客户编号") private String customerCode; @Excel(name = "客户名称",width = 30) @ApiModelProperty("客户名称") private String customerName; @ApiModelProperty("订单类型(1商城订单、2普通订单、3赊欠订单、4项目订单、5返利订单)") private String billType; @Excel(name = "销售员编号") @ApiModelProperty("销售员编号") private String salerCode; @Excel(name = "销售员") @ApiModelProperty("销售员") private String salerName; @Excel(name = "销售员部门",width = 30) @ApiModelProperty("销售员部门") private String departName; @Excel(name = "订单数量") @ApiModelProperty("订单数量") private Integer allBillQuantity; @Excel(name = "已锁定数量") @ApiModelProperty("已锁定数量") private Integer allLockedQty; @Excel(name = "订单金额",width = 12) @ApiModelProperty("订单金额") private BigDecimal allBillAmount; @Excel(name = "采购申请数量",width = 12) @ApiModelProperty("采购申请数量") private Integer allApplyQty; // @Excel(name = "在途数量") @ApiModelProperty("在途数量") private Integer allOrderQty; @Excel(name = "出库数量") @ApiModelProperty("出库数量") private Integer allStockOutQuantity; @Excel(name = "出库金额",width = 12) @ApiModelProperty("出库金额") private BigDecimal allStockOutAmount; // @Excel(name = "出库状态") @ApiModelProperty("出库状态") private String storageStatus; @Excel(name = "退货数量") @ApiModelProperty("退货数量") private Integer allStockReturnQty; @Excel(name = "退货金额",width = 12) @ApiModelProperty("退货金额") private BigDecimal allStockReturnAmount; @Excel(name = "收款金额",width = 12) @ApiModelProperty("收款金额=实际出库数量*出库单价") private BigDecimal shouldReceiveAmount; @Excel(name = "已开票数量") @ApiModelProperty("已开票数量") private Integer shouldPurchaseInvoiceQty; @Excel(name = "已开票金额",width = 12) @ApiModelProperty("已开票金额") private BigDecimal invoiceBillAmount; // @Excel(name = "已收款金额") @ApiModelProperty("已收款金额") private BigDecimal receivableAmount; // @Excel(name = "应收余额") @ApiModelProperty("应收余额") private BigDecimal notReceivableAmount; // @Excel(name = "已退款金额") @ApiModelProperty("已退款金额") private BigDecimal allReturnAmount; // @Excel(name = "已退票数量") @ApiModelProperty("已退票数量") private BigDecimal allReturnInvoiceQty; // @Excel(name = "已退票金额") @ApiModelProperty("已退票金额") private BigDecimal allReturnInvoiceAmount; // @Excel(name = "开票状态") @ApiModelProperty("开票状态") private String invoiceStatus; @Excel(name = "单据日期",exportFormat="yyyy-MM-dd",width = 15) @ApiModelProperty("单据日期") @JsonFormat(pattern = "yyyy-MM-dd") private Date billDate; /** * 接收查询条件用 */ private String startBillDate; private String endBillDate; @ApiModelProperty("客户省份") private String customerProvince; @ApiModelProperty("客户城市") private String customerCity; @ApiModelProperty("客户等级") private String customerGradeName; private String receiveBillNo; /** * 金额折扣小数位 */ private Integer value; }接口： @ApiOperation("销售订单跟踪导出") @GetMapping("orderTrackExport") @IgnoreUserToken @IgnoreClientToken public void orderTrackExport(HttpServletResponse response, @RequestParam Map params) { try { ExportParams exportParams = new ExportParams(); exportParams.setTitle("销售订单跟踪报表"); exportParams.setSheetName("销售订单跟踪报表"); exportParams.setStyle(ExcelExportStylerImpl.class); exportParams.setAddIndex(true); Workbook workbook = ExcelExportUtil.exportExcel(exportParams, SalesOrderTrackDTO.class,salesOrderService.orderTrackExport(params)); // 设置响应输出的头类型 response.setCharacterEncoding("UTF-8"); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); // 下载文件的默认名称 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("销售订单跟踪报表.xls", "UTF-8")); workbook.write(response.getOutputStream()); } catch (IOException e) { e.printStackTrace(); } }这里使用ExportParams exportParams = new ExportParams();这个对象，通过设置他来添加序号，点来里面的方法，可以看到 private String[] exclusions; /** * 是否添加需要需要 */ private boolean addIndex; /** * 是否添加需要需要 */ private String indexName = "序号";ExportParams的这个setAddIndex();方法，可以自增序号， 2.但是添加后，控制台报NullPointer Exception空指针异常， 点开ExcelExportUtil工具类，发现该类使用final修饰，不能被继承 解决方法： 1.重写ExcelExportUtil类： package com.meishi.manager.util; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.TemplateExportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity; import cn.afterturn.easypoi.excel.export.ExcelBatchExportService; import cn.afterturn.easypoi.excel.export.ExcelExportService; import cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil; import cn.afterturn.easypoi.handler.inter.IExcelExportServer; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.util.Collection; import java.util.List; import java.util.Map; /** * @ClassName ExcelExportUtil * @description: * @author: qsong * @create: 2021-04-13 09:37 * @Version 1.0 **/ public class ExcelExportUtil { public static int USE_SXSSF_LIMIT = 100000; public static final String SHEET_NAME = "sheetName"; private ExcelExportUtil() { } /** * 大数据量导出 * * @param entity 表格标题属性 * @param pojoClass Excel对象Class * @param server 查询数据的接口 * @param queryParams 查询数据的参数 */ public static Workbook exportBigExcel(ExportParams entity, Class pojoClass, IExcelExportServer server, Object queryParams) { ExcelBatchExportService batchServer = new ExcelBatchExportService(); batchServer.init(entity, pojoClass); return batchServer.exportBigExcel(server, queryParams); } /** * 大数据量导出 * * @param entity * @param excelParams * @param server 查询数据的接口 * @param queryParams 查询数据的参数 * @return */ public static Workbook exportBigExcel(ExportParams entity, List excelParams, IExcelExportServer server, Object queryParams) { ExcelBatchExportService batchServer = new ExcelBatchExportService(); batchServer.init(entity, excelParams); return batchServer.exportBigExcel(server, queryParams); } /** * @param entity 表格标题属性 * @param pojoClass Excel对象Class * @param dataSet Excel对象数据List */ public static Workbook exportExcel(ExportParams entity, Class pojoClass, Collection dataSet) { Workbook workbook = getWorkbook(entity.getType(), dataSet.size()); new SubExcelExportService().createSheet(workbook, entity, pojoClass, dataSet); return workbook; } private static Workbook getWorkbook(ExcelType type, int size) { if (ExcelType.HSSF.equals(type)) { return new HSSFWorkbook(); } else if (size return new SXSSFWorkbook(); } } /** * 根据Map创建对应的Excel * * @param entity 表格标题属性 * @param entityList Map对象列表 * @param dataSet Excel对象数据List */ public static Workbook exportExcel(ExportParams entity, List entityList, Collection dataSet) { Workbook workbook = getWorkbook(entity.getType(), dataSet.size()); ; new SubExcelExportService().createSheetForMap(workbook, entity, entityList, dataSet); return workbook; } /** * 根据Map创建对应的Excel(一个excel 创建多个sheet) * * @param list 多个Map key title 对应表格Title key entity 对应表格对应实体 key data * Collection 数据 * @return */ public static Workbook exportExcel(List list, ExcelType type) { Workbook workbook = getWorkbook(type, 0); for (Map map : list) { ExcelExportService service = new ExcelExportService(); service.createSheet(workbook, (ExportParams) map.get("title"), (Class) map.get("entity"), (Collection) map.get("data")); } return workbook; } /** * 导出文件通过模板解析,不推荐这个了,推荐全部通过模板来执行处理 * * @param params 导出参数类 * @param pojoClass 对应实体 * @param dataSet 实体集合 * @param map 模板集合 * @return */ @Deprecated public static Workbook exportExcel(TemplateExportParams params, Class pojoClass, Collection dataSet, Map map) { return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, pojoClass, dataSet, map); } /** * 导出文件通过模板解析只有模板,没有集合 * * @param params 导出参数类 * @param map 模板集合 * @return */ public static Workbook exportExcel(TemplateExportParams params, Map map) { return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, null, null, map); } /** * 导出文件通过模板解析只有模板,没有集合 * 每个sheet对应一个map,导出到处,key是sheet的NUM * * @param params 导出参数类 * @param map 模板集合 * @return */ public static Workbook exportExcel(Map map, TemplateExportParams params) { return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, map); } /** * 导出文件通过模板解析只有模板,没有集合 * 每个sheet对应一个list,按照数量进行导出排序,key是sheet的NUM * * @param params 导出参数类 * @param map 模板集合 * @return */ public static Workbook exportExcelClone(Map map, TemplateExportParams params) { return new ExcelExportOfTemplateUtil().createExcelCloneByTemplate(params, map); } }注意：重写ExcelExportUtil这个类，去掉final修饰符，同时ExcelExportService需要改成自己自定义的SubExcelExportService，也就是下面这个类 2.发现cn.afterturn.easypoi.excel.export.ExcelExportService没有被final修饰，所以我们定义一个类来继承它，然后重写insertDataToSheet方法，改变的地方就是注释掉排序的代码，具体代码如下： package com.meishi.manager.util; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity; import cn.afterturn.easypoi.excel.export.ExcelExportService; import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler; import cn.afterturn.easypoi.exception.excel.ExcelExportException; import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum; import cn.afterturn.easypoi.util.PoiExcelGraphDataUtil; import org.apache.poi.ss.usermodel.Drawing; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import java.util.*; /** * @ClassName SubExcelExportServiceImpl * @description: * @author: qsong * @create: 2021-04-13 09:39 * @Version 1.0 **/ public class SubExcelExportService extends ExcelExportService { /** * 最大行数,超过自动多Sheet */ private static int MAX_NUM = 60000; @Override protected void insertDataToSheet(Workbook workbook, ExportParams entity, List entityList, Collection dataSet, Sheet sheet) { try { dataHandler = entity.getDataHandler(); if (dataHandler != null && dataHandler.getNeedHandlerFields() != null) { needHandlerList = Arrays.asList(dataHandler.getNeedHandlerFields()); } dictHandler = entity.getDictHandler(); i18nHandler = entity.getI18nHandler(); // 创建表格样式 setExcelExportStyler( (IExcelExportStyler) entity.getStyle().getConstructor(Workbook.class).newInstance(workbook)); Drawing patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet); List excelParams = new ArrayList(); if (entity.isAddIndex()) { excelParams.add(indexExcelEntity(entity)); } excelParams.addAll(entityList); // sortAllParams(excelParams); int index = entity.isCreateHeadRows() ? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0; int titleHeight = index; setCellWith(excelParams, sheet); setColumnHidden(excelParams, sheet); short rowHeight = entity.getHeight() != 0 ? entity.getHeight() : getRowHeight(excelParams); setCurrentIndex(1); Iterator its = dataSet.iterator(); List tempList = new ArrayList(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0]; tempList.add(t); if (index >= MAX_NUM) { break; } } if (entity.getFreezeCol() != 0) { sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0); } mergeCells(sheet, excelParams, titleHeight); its = dataSet.iterator(); for (int i = 0, le = tempList.size(); i LOGGER.debug("List data more than max ,data size is {}", dataSet.size()); } // 发现还有剩余list 继续循环创建Sheet if (dataSet.size() > 0) { createSheetForMap(workbook, entity, entityList, dataSet); } else { // 创建合计信息 addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet); } } catch (Exception e) { LOGGER.error(e.getMessage(), e); throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause()); } } }注意：只需要注释掉这个方法即可// sortAllParams(excelParams); 3.然后，改变接口中com.meishi.manager.util.ExcelExportUtil的导入 @ApiOperation("销售订单跟踪导出") @GetMapping("orderTrackExport") @IgnoreUserToken @IgnoreClientToken public void orderTrackExport(HttpServletResponse response, @RequestParam Map params) { try { ExportParams exportParams = new ExportParams(); exportParams.setTitle("销售订单跟踪报表"); exportParams.setSheetName("销售订单跟踪报表"); exportParams.setStyle(ExcelExportStylerImpl.class); exportParams.setAddIndex(true); Workbook workbook = com.meishi.manager.util.ExcelExportUtil.exportExcel(exportParams, SalesOrderTrackDTO.class,salesOrderService.orderTrackExport(params)); // 设置响应输出的头类型 response.setCharacterEncoding("UTF-8"); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); // 下载文件的默认名称 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("销售订单跟踪报表.xls", "UTF-8")); workbook.write(response.getOutputStream()); } catch (IOException e) { e.printStackTrace(); } }4.也可以自定义单元格样式 package com.meishi.crm.util; import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler; import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler; import org.apache.poi.ss.usermodel.*; /** * @ClassName ExcelExportStylerImpl * @description: * @author: qsong * @create: 2021-04-12 17:44 * @Version 1.0 **/ public class ExcelExportStylerImpl extends AbstractExcelExportStyler implements IExcelExportStyler { public ExcelExportStylerImpl(Workbook workbook) { super.createStyles(workbook); } @Override public CellStyle getTitleStyle(short color) { CellStyle titleStyle = workbook.createCellStyle(); titleStyle.setAlignment(HorizontalAlignment.CENTER); titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); titleStyle.setWrapText(true); //设置字体 Font font = this.workbook.createFont(); font.setColor(IndexedColors.RED.index); titleStyle.setFont(font); return titleStyle; } @Override public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) { CellStyle style = workbook.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setDataFormat(STRING_FORMAT); if (isWarp) { style.setWrapText(true); } return style; } @Override public CellStyle getHeaderStyle(short color) { CellStyle titleStyle = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontHeightInPoints((short) 12); font.setColor(IndexedColors.RED.index); titleStyle.setFont(font); titleStyle.setAlignment(HorizontalAlignment.CENTER); titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); return titleStyle; } @Override public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) { CellStyle style = workbook.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setDataFormat(STRING_FORMAT); if (isWarp) { style.setWrapText(true); } return style; } } |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |