easypoi导出表格设置序号列出错问题
- easypoi中包含了添加序号列功能，但官方作者并没有在公开文档中描述用法，大概是这个功能当时未完善，当然了，我们直接使用，也会有一点小问题，会报错，debug后能看出原因：
在生成sheet之前，会走一个字段排序的方法（类似如下标注时，根据orderNum升序排序，生成每一列的列名称：
@Excel(name = "姓名", orderNum = "1")
private String name; // 人员姓名）
而如果设置了添加序号列，则它会生成一个orderNum为-2147483648的列属性（计算机能表示的最小整形数），然而排序结束后，序号列跑到了最后，后面需要根据orderNum来插入列名称，生成列的代码如下：
while(its.hasNext()) {
Object t = its.next();
index += this.createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0];
tempList.add(t);
if (index >= MAX_NUM) {
break;
}
}于是就会引发index溢出的问题，不多说了，接下来直接上修改完的代码。
接下拉需要创建两个类，一个MyExportService.java，一个PoiUtils.java。
一、通过继承重写insertDataToSheet()方法，仅修改了2处，已标注出来：
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.*;
/**
* @Author mjack
* @Date 2020/11/9 10:54 上午
*/
public class MyExportService extends ExcelExportService {
private static int MAX_NUM = 60000;
@Override
protected void insertDataToSheet(Workbook workbook, ExportParams entity, List<ExcelExportEntity> 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<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
/*修改一，将原本插入序号列方法注释掉*/
/*
// if (entity.isAddIndex()) {
// excelParams.add(indexExcelEntity(entity));
// }
*/
excelParams.addAll(entityList);
sortAllParams(excelParams);
/*修改二，排序结束后再插入序号列*/
if (entity.isAddIndex()) {
excelParams.add(0, indexExcelEntity(entity));
}
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<Object> tempList = new ArrayList<Object>();
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 < le; i++) {
its.next();
its.remove();
}
if (LOGGER.isDebugEnabled()) {
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());
}
}
}二、自己写个PoiUtil工具类，后面直接调这个方法就行，直接调用exportPerformanceExcel()方法即可完成导出，注意Controller层返回值需为void，否则会报错，可以较高度定制化：
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
public class PoiUtils {
/**
*下载导出Excel方法
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 导出接口 设置了序号列为第一列
*
* @author mjack
*
* @param list 数据集合
* @param title sheet标题
* @param sheetName sheet名称
* @param pojoClass list中对象的类信息
* @param fileName excel文件名称
* @param response 响应头
*/
public static void exportPerformanceExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
HttpServletResponse response, String indexName) {
ExportParams param = new ExportParams(title, sheetName);
// 设置序号列
param.setAddIndex(true);
// 第一列序号列名称，默认"序号"
if(indexName != null && !indexName.equals("")) {
param.setIndexName(indexName);
}
// 设置导出格式为xlsx
// param.setType(ExcelType.XSSF);
performanceExport(list, pojoClass, fileName, response, param);
}
private static Workbook exportPerformanceExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {
Workbook workbook = new HSSFWorkbook();
(new MyExportService()).createSheet(workbook, entity, pojoClass, dataSet);
return workbook;
}
private static void performanceExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
ExportParams exportParams) {
Workbook workbook = exportPerformanceExcel(exportParams, pojoClass, list);
if (workbook != null)
;
downLoadExcel(fileName, response, workbook);
}
}如果想生成多个sheet页，那就把它的参数全改成List循环一下，放到PoiUtil类中：
/**
* @author mjack
* @date 2020/11/04
* 导出多个sheet封装方法
*
* @param list 多个数据的List集合
* @param title sheet标题
* @param sheetNameList sheet名称集合
* @param pojoClass 数据中实体类的类信息集合（类相同也需要对应list传）
* @param fileName excel文件名
* @param response 响应头
*/
public static void exportSignExcel(List<List<?>> list, String title, List<String> sheetNameList, List<Class<?>> pojoClass, String fileName,
HttpServletResponse response) {
List<ExportParams> paramsList = new LinkedList<>();
for (String sheetName : sheetNameList) {
ExportParams params = new ExportParams(title, sheetName);
params.setAddIndex(true);
paramsList.add(params);
}
signExport(list, pojoClass, fileName, response, paramsList);
}
private static void signExport(List<List<?>> list, List<Class<?>> pojoClass, String fileName, HttpServletResponse response,
List<ExportParams> exportParams) {
Workbook workbook = exportExcel(exportParams, pojoClass, list);
if (workbook != null)
;
downLoadExcel(fileName, response, workbook);
}
public static Workbook exportExcel(List<ExportParams> paramList, List<Class<?>> pojoClassList, List<List<?>> dataSet) {
Workbook workbook = new HSSFWorkbook();
for (int i = 0; i < paramList.size(); i++) {
(new MyExportService()).createSheet(workbook, paramList.get(i), pojoClassList.get(i), dataSet.get(i));
}
return workbook;
}这个导出工具挺好用，代码量少，也支持导入、自定义excel的类型（xls、xlsx），如果没有很特殊的业务需求，或者很高的性能要求，这个工具完全够用了，很好用，推荐使用。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
