使用EasyExcel导出数据
easyexcel
快速、简洁、解决大文件内存溢出的java处理Excel工具
项目地址：https://gitcode.com/gh_mirrors/ea/easyexcel
免费下载资源
·
目录
The maximum number of cell styles was exceeded. You can define up to 64000 styles
一、引入EasyExcel依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>二、代码框架实现
1-1、无需复杂处理，直接使用工具类
定义具体对象，并添加@ExcelProperty，来指定列信息
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ExcelIgnoreUnannotated
public static class DemoDataRespVO {
@ExcelProperty(value = "数据1", index = 0)
private String data1;
@ExcelProperty(value = "数据2",index = 1)
private String data2;
@ExcelProperty("数据3")
private String data3;
@ExcelProperty("数据4")
private String data4;
@ExcelProperty("数据5")
private String data5;
@ExcelProperty("数据6")
private String data6;
@ExcelProperty("数据7")
private String data7;
@ExcelProperty("数据8")
private String data8;
@ExcelProperty("数据9")
private String data9;
@ExcelProperty("数据10")
private String data10;
}1-2、创建一批假数据
private List<DemoDataRespVO> getData() {
List<DemoDataRespVO> data = new ArrayList<>();
DemoDataRespVO data1 = DemoDataRespVO.builder()
.data1("数据1-1").data2("数据2-1").data3("数据3-1").data4("数据4-1").data5("数据5-1")
.data6("数据6-1").data7("-数据7-1").data8("数据8-1").data9("数据9-1").data10("数据10-1").build();
data.add(data1);
DemoDataRespVO data2 = DemoDataRespVO.builder()
.data1("数据1-2").data2("数据2-2").data3("数据3-2").data4("数据4-2").data5("数据5-2")
.data6("数据6-2").data7("数据7-2").data8("数据8-2").data9("-数据9-2").data10("数据10-2").build();
data.add(data2);
return data;
}1-3、使用流的方式导出数据
@RestController
@RequestMapping("/test")
public class TestController {
@GetMapping("/exportExcel1")
public void getExportExcel1(HttpServletResponse response) throws IOException {
String sheetName = "sheet名";
String fileName = "文件名.xlsx";
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
List<DemoDataRespVO> dataList = getData();
EasyExcel.write(response.getOutputStream(), DemoDataRespVO.class)
.autoCloseStream(false)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(sheetName).doWrite(dataList);
}
}1-4、使用生成文件的方式导出数据
@GetMapping("/exportExcel1")
public void getExportExcel1(HttpServletResponse response) throws IOException {
File file = new File("H:/test.xls");
OutputStream outputStream = Files.newOutputStream(file.toPath());
List<DemoDataRespVO> dataList = getData();
EasyExcel.write(outputStream, DemoDataRespVO.class)
.autoCloseStream(false)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(sheetName).doWrite(dataList);
}PS：可先上传到文件服务器，再通过返回的下载地址链接返回前端 。
1-5、验证结果
localhost:8622/api/v1/test/exportExcel1
2-1、自定义表头格式，并对数据做处理
@GetMapping("/exportExcel2")
public void getExportExcel2(HttpServletResponse response) throws IOException {
exportExcel(response);
}
/**
* 导出Excel流
*
* @param response
* @throws IOException
*/
public void exportExcel(HttpServletResponse response) throws IOException {
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=testName.xlsx");
// 获取输出流
response.flushBuffer();
//创建自定义的表头（可自行判断增减 列）
ArrayList<List<String>> head = new ArrayList<>();
head.add(CollectionUtil.newArrayList("标题1", "标题1")); //0
head.add(CollectionUtil.newArrayList("标题2", "标题2")); //1
head.add(CollectionUtil.newArrayList("标题3", "标题3")); //2
head.add(CollectionUtil.newArrayList("标题4", "标题4")); //3
head.add(CollectionUtil.newArrayList("标题5", "标题5")); //4
//！！！！ 如果是合并表头，数组头一个设置名称一样即可
head.add(CollectionUtil.newArrayList("合并标题", "子标题1")); //5
head.add(CollectionUtil.newArrayList("合并标题", "子标题2")); //6
head.add(CollectionUtil.newArrayList("合并标题", "子标题3")); //7
head.add(CollectionUtil.newArrayList("合并标题", "子标题4")); //8
head.add(CollectionUtil.newArrayList("合并标题", "子标题5")); //9
//输出流
//表头：原本是 write(response.getOutputStream(), XXX.class) ，XXX是默认表头格式
//表头：现在设置为null, 使用 head 入参 自定义的表头，视情况自行选择
//registerWriteHandler 自定义表格样式
// sheet 自定以 sheet名称
// doWrite(getData()) 行数据，-----！！！ 数据列 需要跟表头一样长度
EasyExcel.write(response.getOutputStream(), null).head(head)
.autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
.registerWriteHandler(new RegisterStyle())
.sheet("sheetName").doWrite(getData());
}
/**
* 自定义样式
*/
static class RegisterStyle extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell,
Head head,
Integer relativeRowIndex, Boolean isHead) {
//这里有10个字段，所以只是设置10个列的宽度即可
if (cell.getColumnIndex() < 10) {
//setColumnWidth （i,j），i是列的下标，从0开始，j是列的宽度，单位是1/256个字符宽度，所以代码中要乘以256
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), 256 * 12);
}
//只是自定义的，判断那些列需要做处理
boolean flag = cell.getColumnIndex() == 6 || cell.getColumnIndex() == 8;
// isHead 表头， “!isHead”：跳过表头行
if (!isHead && flag) {
// 获取当前单元格的数值（存入String格式用 getStringValue 获取）
// 获取当前单元格的数值（存入Number格式用 getNumberValue 获取）
String stringValue = cellDataList.get(0).getStringValue();
//创建单元格
CellStyle cellStyle = cell.getSheet().getWorkbook().createCellStyle();
cellStyle.cloneStyleFrom(cell.getCellStyle());
if (stringValue.startsWith("-")) {
// - 开头表示负数
//单元格数值小于0的 ， 字体展示为 红色
Font font = cell.getSheet().getWorkbook().createFont();
font.setColor(IndexedColors.RED.getIndex());
cellStyle.setFont(font);
}
cell.setCellStyle(cellStyle);
}
}
}2-2、验证结果
localhost:8622/api/v1/test/exportExcel2
效果是跟我们想要的一样，这个时候开始皆大欢喜了。。。。。。错！
三、问题暴露及修复思路
1、问题原由
本次自定义样式是继承 AbstractColumnWidthStyleStrategy 重写 setColumnWidth 方法实现，但这个实现类底层有自己的限制性条件，如果导出的行数据过多大几千起步且需要修改单元格样式（不做修改操作是没事的），是会直接报错：
The maximum number of cell styles was exceeded. You can define up to 64000 styles
知其原由，找到限制的位置在：createCellStyle()
Workbook：
public interface Workbook extends Closeable, Iterable<Sheet> {
//...................
CellStyle createCellStyle();
//...................
}XSSFWorkbook：
public class XSSFWorkbook extends POIXMLDocument implements Workbook, Date1904Support {
//...................
public XSSFCellStyle createCellStyle() {
return this.stylesSource.createCellStyle();
}
//...................
}StylesTable：
public class StylesTable extends POIXMLDocumentPart implements Styles {
//...................
public XSSFCellStyle createCellStyle() {
if (this.getNumCellStyles() > MAXIMUM_STYLE_ID) {
throw new IllegalStateException("The maximum number of Cell Styles was exceeded. You can define up to " + MAXIMUM_STYLE_ID + " style in a .xlsx Workbook");
} else {
int xfSize = this.styleXfs.size();
CTXf xf = org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf.Factory.newInstance();
xf.setNumFmtId(0L);
xf.setFontId(0L);
xf.setFillId(0L);
xf.setBorderId(0L);
xf.setXfId(0L);
int indexXf = this.putCellXf(xf);
return new XSSFCellStyle(indexXf - 1, xfSize - 1, this, this.theme);
}
}
//...................
//...................
static {
MAXIMUM_STYLE_ID = SpreadsheetVersion.EXCEL2007.getMaxCellStyles();
}
}SpreadsheetVersion：
public enum SpreadsheetVersion {
EXCEL97(65536, 256, 30, 3, 4000, 32767),
EXCEL2007(1048576, 16384, 255, 2147483647, 64000, 32767);
//...................
private SpreadsheetVersion(int maxRows, int maxColumns, int maxFunctionArgs, int maxCondFormats, int maxCellStyles, int maxText) {
this._maxRows = maxRows;
this._maxColumns = maxColumns;
this._maxFunctionArgs = maxFunctionArgs;
this._maxCondFormats = maxCondFormats;
this._maxCellStyles = maxCellStyles;
this._maxTextLength = maxText;
}
//...................
public int getMaxCellStyles() {
return this._maxCellStyles;
}
//...................
}定位到方法后，就要想办法处理了。
2、将底层实现或是枚举方法Copy重写
比较费时费力，而且需要熟悉接口层相关的关联依赖
3、重新定义实现类
将RegisterStyle 替换为 重写 CellWriteHandler 的另一个自定义实现类 CustomCellStyleHandler
/**
* excel 自定义样式
*/
public class CustomCellStyleHandler implements CellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
//此处提醒下 beforeCellCreate 的row.getCell(i) 已经获取不到值了， 只会返回NULL
Cell cell = row.getCell(columnIndex);
}
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
if (BooleanUtils.isNotTrue(context.getHead())) {
// 获取当前行的行号
Cell cell = context.getCell();
if (ObjectUtil.isNotNull(cell)) {
String stringValue = cell.getStringCellValue();
if (CharSequenceUtil.isNotEmpty(stringValue) && stringValue.startsWith("-")) {
WriteCellData<?> cellData = context.getFirstCellData();
WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
WriteFont writeFont = new WriteFont();
writeFont.setColor(IndexedColors.RED.getIndex());
writeCellStyle.setWriteFont(writeFont);
}
}
} else {
// 表头自适应列宽
Cell cell = context.getCell();
int length = cell.getStringCellValue().getBytes().length;
WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), length * 300);
}
}
}测试1W数据，成功！！！！！（更大的数据量待各位自行验证）
遗留问题：2-1 步骤中的文件名 fileName=testName 如何设置为中文名称
四、百万级别数据导出
参考文章：百万级数据excel导出功能如何实现？
Controller层
添加 @Idempotent 注解，防止多次点击，处理幂等
@GetMapping("/export-excel")
@Operation(summary = "导出测试 Excel")
@OperateLog(type = EXPORT)
@Idempotent
public void exportTestExcel(@Valid ReqVO reqVO) {
//type: 0-待执行，1-执行中，2-成功，3-失败
downloadReportService.createDownloadResport(DownloadReportDO.builder().type(0).build());
testService.exportExcel(reqVO);
}Service层
@Async
@Override
public void exportExcel(ReqVO reqVO) {
//查看是否有待执行下载的文件
DownloadReportRespVO downLoadInfo = validAndGetInfoByType(reqVO.getType());
downLoadInfo.setStatus(1);
//设置文件名
String filename = LocalDateTimeUtil.format(LocalDateTimeUtil.now(), "yyyyMMdd") + "测试表.xlsx";
String filePath = 'H://' + filename;
try {
//创建文件
File file = new File(filePath);
//目录不存在则创建目录
if (!file.getParentFile().exists()) {
Files.createDirectories(file.getParentFile().toPath());
}
//文件不存在则创建文件
if (!file.exists()) {
Files.createFile(file.toPath());
}
OutputStream outputStream = Files.newOutputStream(file.toPath());
//此处可往上参考：2-1、自定义表头格式，并对数据做处理
//获取表头和列展示情况
ArrayList<List<String>> headTitleList = getHeadTitle(colMap);
ExcelWriter excelWriter = EasyExcel.write(outputStream, null).head(headTitleList)
.autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
.registerWriteHandler(new CustomCellStyleHandler()).build();
//查询数据总数
Long totalCount = testMapper.selectCount(reqVO);
log.info("本次 信息导出，查询到数据有 {} 条", totalCount);
//此处开始做sheet切分用，每50W切换一个sheet
long MAX_SINGLE_PAGE = 500000;
if (ObjectUtil.isNotNull(totalCount) && totalCount != 0) {
//固定分页1，单页查询10W条
reqVO.setPageNo(1);
reqVO.setPageSize(100000);
//计算出总共要几个sheet
long sheetPageNumber = (totalCount % MAX_SINGLE_PAGE == 0) ? totalCount / MAX_SINGLE_PAGE : (totalCount / MAX_SINGLE_PAGE) + 1;
List<DemoDataRespVO> dataList = CollUtil.newArrayList();
WriteSheet writeSheet = null;
for (int i = 0; i < sheetPageNumber; i++) {
writeSheet = EasyExcel.writerSheet("sheet" + i).build();
long currentDataTotal = (sheetPageNumber - 1) == i ? totalCount - (MAX_SINGLE_PAGE * i) : MAX_SINGLE_PAGE;
for (long tempTotal = 0L; tempTotal < currentDataTotal; ) {
//查询分页接口，此处不例句展示
PageResult<DemoDataRespVO> pageReuslt = selecPageList(reqVO);
dataList = pageReuslt.getList();
if (CollUtil.isEmpty(dataList)) {
break;
}
//获取本次列表中最小的 id 游标，（！！列表数据是 id 倒序排序）
long minId = dataList.stream().mapToLong(CollectUserRevenueRespVO::getId).min().orElse(-1);
reqVO.setMinId(minId);
tempTotal += dataList.size();
//此处的getDisposeDataList是做数据的二次封装处理，无需处理的可跳过，这里不详解
excelWriter.write(getDisposeDataList(dataList), writeSheet);
}
}
//处理最后一行合计
DemoDataRespVO respVO = selectTotal(reqVO);
respVO.setData0("合计");
dataList.add(respVO);
excelWriter.write(getDisposeDataList(dataList, colMap), writeSheet);
// 结束写入
excelWriter.finish();
//Excel处理成功，将表格上次到OSS存储桶位置，然后返回下载链接
byte[] fileBytes = FileUtil.readBytes(file);
FileCreateReqDTO fileCreateReqDTO = new FileCreateReqDTO();
fileCreateReqDTO.setName(filename);
fileCreateReqDTO.setPath(filePath);
fileCreateReqDTO.setContent(fileBytes);
Response<String> fileResult = fileTool.createFile(fileCreateReqDTO);
if (fileResult.success()) {
String downUrl = fileResult.getData();
downLoadInfo.setDownUrl(downUrl);
downLoadInfo.setStatus(2);
} else {
downLoadInfo.setStatus(3);
}
}
} catch (Exception e) {
downLoadInfo.setStatus(3);
log.error("[exportExcel] [测试表 - 导出失败]] - {}", e);
throw RunException("导出失败");
} finally {
//更新本次导出下载表格的结果
downloadReportService.updateDownloadResport(downLoadInfo);
}
}处理思路
对于百万级起步的导出方法exportExcel使用的处理点：
1、service层使用异步，可让Controller层及时响应前端，进度通过中间表（download_report）查询
2、数据切割：按每50W一个sheet操作（上述方法中的数据计算是按50W为一个sheet，查询按每页10W数据处理，可自行优化计算方式，这里偷懒）
3、数据搜索：大数据量直接分页查询越往后越会超时，上述方法分页是ID倒序，每页以最后一个ID作为游标，从此ID定位再开始继续往下搜索，可提升查询效率
4、SQL查询使用ID嵌套定位查询（利用自身的主键索引理念），如下：
select t1.*
from test_data t1,
( select id from data_collect_user_revenue where id < #{minId} order by create_date desc, id desc limit #{(pageNo-1)*pageSize},#{pageSize}
) t2 where t1.id = t2.id这里测试数据 1155371 条
上传成功后返回下载链接，点击后结果数据如下：
GitHub 加速计划 / ea / easyexcel
31.64 K
7.47 K
下载
快速、简洁、解决大文件内存溢出的java处理Excel工具
最近提交(Master分支：3 个月前 )
c42183df
Bugfix 3 个月前
efa7dff6 * 重新加回 `commons-io`
3 个月前
旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。
更多推荐
25
0- 0
已为社区贡献5条内容
所有评论(0)