Spring Boot结合easyExcel 三种方式实现自增序号

有些业务功能要求能导出序号，现在提供两种实现方式。

1. 通过mysql查询实现；
2. 实现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
    </select>

二、实现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<OrderPageVO> page = ordersService.page(param);
		// 直接使用工具导出数据
		ExcelUtils.exportSerialNumber(response, page.getContent(), OrderPageVO.class,"订单列表");
	}

2.6 结果

三、查询数据后遍历，写入序号

对查询出的数据通过AtomicInteger 递增

List<ExcelVO> list = this.baseMapper.list(dto);
AtomicInteger index = new AtomicInteger(1);
list.forEach(vo-> {
    // 序号递增
    vo.setIndex(index.getAndIncrement());
});