Spring Boot结合easyExcel实现自增序号 |
您所在的位置：网站首页 › 雪球用户数据鱼 › Spring Boot结合easyExcel实现自增序号 |
Spring Boot结合easyExcel实现自增序号
|
有些业务功能要求能导出序号，现在提供两种实现方式。 通过mysql查询实现；实现RowWriteHandler接口，自定义拦截器； 一、通过mysql查询实现通过自定义变量实现每行数据+1，得到序号。当然在对应的实体里加上sequenceNumber，直接导出即可。 SELECT (@rownum := @rownum + 1) AS sequenceNumber, memberEvidencedata.* FROM ( SELECT evidence_name,merchant_name FROM member_evidence ) AS memberEvidencedata, ( SELECT @rownum := 0 ) AS rn 二、实现RowWriteHandler接口，自定义拦截器easyExcel提供的接口有RowWriteHandler、CellWriteHandler、SheetWriteHandler和WorkbookWriteHandler拦截器，我们实现RowWriteHandler接口，自定义拦截器。 2.1 自定义行拦截器 package com.meiyuan.food.research.common.utils.easyexcel; import com.alibaba.excel.write.handler.RowWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import org.apache.poi.ss.usermodel.*; /** * 自定义行拦截器 * @author zj * @date 2021/07/08 19:08 */ public class CustomRowWriteHandler implements RowWriteHandler { /** * 序号的样式，与其他列保持一样的样式 */ private CellStyle firstCellStyle; private static final String FIRST_CELL_NAME = "序号"; /** * 列号 */ private int count = 0; @Override public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) { } @Override public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) { // 每一行首列单元格 Cell cell = row.createCell(0); if (firstCellStyle == null) { Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); firstCellStyle = CellStyleUtil.firstCellStyle(workbook); } //设置列宽 0列 10个字符宽度 writeSheetHolder.getSheet().setColumnWidth(0, 10 * 256); if (row.getRowNum() == 0) { cell.setCellValue(FIRST_CELL_NAME); cell.setCellStyle(firstCellStyle); return; } cell.setCellValue(++count); } @Override public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) { } } 2.2 导出添加拦截器 package com.meiyuan.food.research.common.utils.easyexcel; import com.alibaba.excel.EasyExcel; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.util.List; /** * excel工具类 * * @author Zhang Jie * @version 1.0.0 * @since 2021/8/2 15:08 */ public class ExcelUtils { /** * 导出包含序号的excel数据 * @param response 响应对象 * @param exportData excel数据内容 * @param clazz 导出类 * @param fileName 文件名称 * @throws IOException ex */ public static void exportSerialNumber(HttpServletResponse response, List exportData, Class clazz, String fileName) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 fileName = URLEncoder.encode(fileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), clazz) // 添加拦截器 .registerWriteHandler(new CustomRowWriteHandler()) .registerConverter(new LocalDateTimeConverter()) .sheet(fileName).doWrite(exportData); } /** * 直接导出excel数据 * @param response 响应对象 * @param exportData excel数据内容 * @param clazz 导出类 * @param fileName 文件名称 * @throws IOException ex */ public static void export(HttpServletResponse response, List exportData, Class clazz, String fileName) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 fileName = URLEncoder.encode(fileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), clazz) .registerConverter(new LocalDateTimeConverter()) .sheet(fileName).doWrite(exportData); } } 2.3 添加“序号”单元格样式 package com.meiyuan.food.research.common.utils.easyexcel; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import org.apache.poi.ss.usermodel.*; /** * excel 样式工具类 * @author zj * @date 2021/07/08 19:08 */ public class CellStyleUtil { /** * excel首列序号列样式 * @param workbook * @return */ public static CellStyle firstCellStyle(Workbook workbook) { CellStyle cellStyle = workbook.createCellStyle(); //居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 灰色 cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); //设置边框 cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); //文字 Font font = workbook.createFont(); font.setBold(Boolean.TRUE); cellStyle.setFont(font); return cellStyle; } } 2.4 导出实体添加注解需要导出的列从“orderNumber”开始，index=1，相当于将index=0给到了序号的那列。 @Data @ApiModel("OrderPageVO") public class OrderPageVO { @ApiModelProperty("订单ID") @ExcelIgnore private Long id; @ApiModelProperty("预订单号") @ColumnWidth(18) @ExcelProperty(value = "预订单号",index = 1) private String orderNumber; @ApiModelProperty("预订人姓名") @ColumnWidth(15) @ExcelProperty(value = "预订人姓名",index = 2) private String reserveName; } 2.5 接口导出数据 @PostMapping(value = "/export") @ApiOperation("导出订单列表") public void export(@RequestBody OrderPageDTO param, HttpServletResponse response) throws IOException { // 最大导出数据为5000 param.setPageNo(Objects.isNull(param.getPageNo())? 0L:param.getPageNo()); param.setPageSize(Objects.isNull(param.getPageSize())? 5000L:param.getPageSize()); PageData page = ordersService.page(param); // 直接使用工具导出数据 ExcelUtils.exportSerialNumber(response, page.getContent(), OrderPageVO.class,"订单列表"); } 2.6 结果
|
【本文地址】