我们在数据导出时如果数据数量较大展示在一个sheet时,用户看起来会非常麻烦,效果不直观,非常的影响用户体验,这时候就需要我们程序猿们搞一个分sheet的功能,这样大家用起来就比较方便,废话不多说,直接上代码(具体解释都在代码中)
/**
* 创建写文件item(根据数据量大小分sheet页进行导出)
*
* @param fileName
* @return
*/
public static <T> FileItem createWriteFileItemWithSheetByListSize(List<T> list, String fileName, Class<T> targetClass) throws IOException {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置背景颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//设置头字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 13);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//设置头居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//背景颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
//内容策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
FileItemFactory factory = new DiskFileItemFactory(16, null);
FileItem item = factory.createItem(fileName, "application/vnd.ms-excel;charset=utf-8", true, fileName);
ExcelWriter excelWriter = EasyExcel.write(item.getOutputStream(), targetClass).registerWriteHandler(horizontalCellStyleStrategy).build();
// 计算需要创建的sheet页数量
int sheetCount = (list.size() + 100 - 1) / 100;
//根据数据量分次写入不同sheet页
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
// 每次迭代创建一个sheet
WriteSheet sheet = EasyExcel.writerSheet(sheetIndex, "Sheet" + (sheetIndex + 1)).build();
// 计算本次迭代的数据范围
int fromIndex = sheetIndex * 100;
// 计算每一页需要截取的数据量
//(这里的意思是如果有320条数据,第三页就要取第200-300条数据,如果是第四页,就要取第300-320条数据)
int toIndex = Math.min((sheetIndex + 1) * 100, list.size());
List<T> subList = list.subList(fromIndex, toIndex);
// 将数据写入对应的sheet页
excelWriter.write(subList, sheet);
}
// 完成写入操作
excelWriter.finish();
return item;
}