Easy Excel生成压缩包文件，自定义表头样式

最新推荐文章于 2024-07-03 17:19:08 发布

竹林幽深

最新推荐文章于 2024-07-03 17:19:08 发布

阅读量1.5k

点赞数

文章标签： java 开发语言后端

原文链接：https://blog.csdn.net/zyj0070/article/details/116270341

版权

大力夯 2021-04-29 15:50:11 84 收藏
分类专栏： Excel 文章标签： java excel
版权

Excel
专栏收录该内容
1 篇文章0 订阅
订阅专栏
Excel 文件内容形如下图!

需求场景
导出超10w左右的数据，涉及

源数据查。
组装业务数据
解析数据库中的json数据内容，并组装相应的信息
生成Excel文件内容到磁盘
将文件压缩，并写回到浏览器。触发自动下载
上线之后，功能运行正常。

问题（频繁Out of memory异常）
调查之后发现是，程序中的内存无法被GC回收。

导致内存被占用程序呈现出 ‘艰难维持’。导致导出多一些的内容，频繁触发OOM错误。

优化1：数据库查询，只查询出需要的字段信息；
优化2：将POI的模式切换为异步磁盘刷新模式 new SXSSFWorkbook(1024);
优化3：控制只能有一个下载任务在执行
经过上述优化之后，程序运行稳定。但仍然存在当数据越来越多时，仍然存在OOM的错误

终极优化，使用 Easy Excel
Easy Excel官方文档地址

建议阅读之后，再进行下文阅读。

优点：节约内存
避免将全部全部数据一次加载到内存

采用sax模式一行一行解析，并将一行的解析结果以观察者的模式通知处理。

缺点，用法削微晦涩一点
使用方式不创建对象的写
由于Excel内容中存在动态标题列，以及大量的数据解析组装.没法定义一个较完整的实体类进行数据编写

part0 maven引用

       <dependency>
           <groupId>com.alibaba</groupId>
           <artifactId>easyexcel</artifactId>
           <version>2.2.8</version>
       </dependency>
1
2
3
4
5
6
part1 查询数据
略

part2 生成excel文件
public class EasyExcelExportSptReportUtil {

/**
* 生成excel 文件
*
* @param taskSptReportList 数据
* @param file_base_path 文件存放基础路径
*/
public static void handelExportData(List<TaskSptReport> taskSptReportList, String file_base_path) {
if (CollectionUtils.isEmpty(taskSptReportList)) {
return;
}

String serviceTypeName = taskSptReportList.get(0).getServiceTypeName();
String serviceTypeId = taskSptReportList.get(0).getServiceTypeId();
String timeStr = DateUtils.format(new Date(System.currentTimeMillis()), DateUtils.DATE_HHMMSS);
String fileName = serviceTypeId + "_" + serviceTypeName.replaceAll("/", "_") + "-" + timeStr + ".xlsx";
String file_path = file_base_path + "/" + fileName;
createNewFile(file_path);
ExcelWriter excelWriter = EasyExcel.write(file_path).build();
HorizontalCellStyleStrategy defaultStyles = defaultStyles();
//指定单元格样式
CellColorSheetWriteHandler writeHandler = new CellColorSheetWriteHandler();

List<TaskSptReport> groupList = groupTaskSptMap.get(keyName);
String sptVersionId = keyName.split("_")[1];
String sheetName = "(" + serviceTypeId + ")" + sptVersionId;
TaskSptReport one_row = groupList.get(0);
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName)
.registerWriteHandler(defaultStyles)
.registerWriteHandler(writeHandler).build();
writeSheet.setHead(head(one_row));
List<List<String>> rowDataList = convert2RowData(groupList);
excelWriter.write(rowDataList, writeSheet);

excelWriter.finish();
//3. 将多个excel 文件合并压缩成一个zip文件
//生成zip压缩文件
return;
}
/**
* 默认样式
*
* @return
*/
public static HorizontalCellStyleStrategy defaultStyles() {
//TODO 默认样式
//表头样式策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//表头前景设置淡蓝色
headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setBold(true);
headWriteFont.setFontName("Calibri");
headWriteFont.setFontHeightInPoints((short) 11);
headWriteCellStyle.setWriteFont(headWriteFont);

//内容样式策略策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置背景颜色白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// 设置垂直居中为居中对齐
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置左右对齐为靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
// 设置单元格上下左右边框为细边框
contentWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM);
contentWriteCellStyle.setBorderLeft(BorderStyle.MEDIUM);
contentWriteCellStyle.setBorderRight(BorderStyle.MEDIUM);
contentWriteCellStyle.setBorderTop(BorderStyle.MEDIUM);
//创建字体对象
WriteFont contentWriteFont = new WriteFont();
//内容字体大小
contentWriteFont.setFontName("Calibri");
contentWriteFont.setFontHeightInPoints((short) 11);
contentWriteFont.setBold(false);//不加粗
contentWriteCellStyle.setWriteFont(contentWriteFont);

// 初始化表格样式
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}

public static File createNewFile(String filePath) {
File file = new File(filePath);
if (file.exists()) {
file.delete();
} else {
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
}
return file;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
CellColorSheetWriteHandler 代码

public class CellColorSheetWriteHandler implements CellWriteHandler {
private final static Logger logger = LoggerFactory.getLogger(CellColorSheetWriteHandler.class);

//最近一次的task No 信息
private String lastedTaskNo;
//是否需要着色
private boolean needSetColor = false;
//用于全局存放的样式实体信息
private CellStyle cellStyle;

/**
* 有参构造
*/
public CellColorSheetWriteHandler(HashMap<Integer, List<Integer>> map, Short colorIndex) {
this.map = map;
this.colorIndex = colorIndex;
}

/**
* 无参构造
*/
public CellColorSheetWriteHandler() {

}

/**
* 在创建单元格之前调用
*/
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}

/**
* 在单元格创建后调用
*/
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}

@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

}

/**
* 在单元上的所有操作完成后调用
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//当前cell 所在的列号
int columnIndex = cell.getColumnIndex();

if (0 == cell.getRowIndex()) {
//若是第一行，即标题列
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle style_head = workbook.createCellStyle();
if (columnIndex >= 15) {
style_head.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style_head.setFillPattern(FillPatternType.SOLID_FOREGROUND);
} else {
style_head.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
style_head.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
//设置当前行第i列的样式
cell.getRow().getCell(columnIndex).setCellStyle(style_head);
}

if (cell.getRowIndex() == 1 && columnIndex == 0) {
String taskNo = cell.getStringCellValue();
this.lastedTaskNo = taskNo;
}

if (cell.getRowIndex() > 1) {
//从第2行开始需要针对性的着色
if (columnIndex == 0) {
String taskNo = cell.getStringCellValue();
if (taskNo.equals(this.lastedTaskNo)) {
} else {
this.lastedTaskNo = taskNo;
needSetColor = !needSetColor;
}
}
}

if (cell.getRowIndex() > 1 && needSetColor) {//若不是第一行，且需要着色，则执行

// 根据单元格获取workbook
Workbook workbook = cell.getSheet().getWorkbook();
// 单元格策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

// 设置垂直居中为居中对齐
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置左右对齐为靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);

// 设置单元格上下左右边框为细边框
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 创建字体实例
WriteFont cellWriteFont = new WriteFont();
// 设置字体大小
cellWriteFont.setFontName("Calibri");
cellWriteFont.setFontHeightInPoints((short) 11);
cellWriteFont.setBold(false);//不加粗
//设置字体颜色
cellWriteFont.setColor(IndexedColors.BLACK1.getIndex());
//单元格颜色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
contentWriteCellStyle.setWriteFont(cellWriteFont)
//特别提醒见下文解释
if(ObjectUtils.isNull(this.cellStyle)) {
//StyleUtil.buildHeadCellStyle方法的本质是和workbook.createCellStyle()是一样的，调用POI的方法创建样式信息
CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);
cellStyle.setWrapText(false);//关闭自动换行
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置当前行第i列的样式
cell.getRow().getCell(columnIndex).setCellStyle(cellStyle);
this.cellStyle = cellStyle;
}else {
cell.getRow().getCell(columnIndex).setCellStyle(this.cellStyle);
}

}
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
* 在调试代码时，由于数据较多触发了The maximum number of Cell Styles was exceeded. You can define up to 64000 s异常
出问题的代码

CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);

究其原因为：当excel数据较多时，若每次都创建一个全新的cellStyle则有可能超过 Excel文件规定的最大个数。故需要将已经创建了但是需要重复利用的样式给存起来。不要不断的new，因为StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);的本质就是调用POI的方法创建样式。

最简单的方法，就是判断需要使用的样式是否已经创建了。若已经创建了，则直接使用；否则新建该样式；

* easy excel设置的表头样式不生效,需设置 HorizontalCellStyleStrategy
在调试过程中，发现在没有指定默认样式的情况下。对于自定义设置的表头颜色不生效。
原因是：没有查到相关具体说明。但是解决了方法就是设置一个默认样式之后就能生效了

WriteSheet writeSheet = EasyExcel.writerSheet(sheetName)
.registerWriteHandler(defaultStyles)//设置默认样式，对象类为HorizontalCellStyleStrategy
.registerWriteHandler(writeHandler).build();
1
2
3
part3 生产压缩文件
将存在磁盘上的文件，读取出来压缩成zip并返回给前端。
核心代码

OutputStream os = null;
ZipArchiveOutputStream zous = null;
try {
logger.info("开始 - 生成报表压缩文件信息");
String response_fileName = "taskSptReport_" + System.currentTimeMillis() + ".zip";
String downloadFilename = RequestUtil.encodeDownloadFilename(response_fileName, RequestUtil.isIERequest(request));
response.setContentType("application/zip;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + downloadFilename);
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

os = response.getOutputStream();
zous = new ZipArchiveOutputStream(os);
zous.setUseZip64(Zip64Mode.AsNeeded);
String filePath = fileBasePath;
//遍历文件list
File baseFile = new File(filePath);
if (baseFile.isFile() || !baseFile.exists()) {
logger.info("存放的文件路径找不到......");
}
File[] files = baseFile.listFiles();
for (File file : files) {
if (file.isDirectory()) {
//若是文件夹，则不处理
continue;
} else {
String fileName = file.getName();
ArchiveEntry entry = new ZipArchiveEntry(fileName);
zous.putArchiveEntry(entry);
FileInputStream in = new FileInputStream(file);
BufferedInputStream bis = new BufferedInputStream(in);
byte[] buf = new byte[BUFFER_SIZE];
int len;
while ((len = bis.read(buf)) != -1) {
zous.write(buf, 0, len);
}

zous.closeArchiveEntry();
in.close();
file.delete();//删除这个文件
}
}
baseFile.delete();
zous.close();
os.flush();
os.close();
logger.info("完成 - 生成报表压缩文件信息");
} catch (IOException e) {
e.printStackTrace();
logger.error("回写文件流时,发生异常.", e.getMessage());
try {
System.gc();
zous.close();
os.flush();
os.close();
} catch (IOException ex) {

} catch (Exception e1) {
e1.printStackTrace();
}
File baseFile = new File(fileBasePath);
if (baseFile.isFile() || !baseFile.exists()) {
logger.info("存放的文件路径找不到......");
}
File[] files = baseFile.listFiles();
for (File file : files) {
if (file.isDirectory()) {
//若是文件夹，则不处理
continue;
} else {
file.delete();//删除这个文件
}
}
baseFile.delete();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
Excel 文件内容形如下图!

喜欢记得点赞 / 收藏 / 评论~~~
————————————————
版权声明：本文为CSDN博主「大力夯」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zyj0070/article/details/116270341

竹林幽深

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Easy Excel生成压缩包文件，自定义表头样式

大力夯 2021-04-29 15:50:11 84 收藏分类专栏： Excel 文章标签： java excel版权Excel专栏收录该内容1 篇文章0 订阅订阅专栏Excel 文件内容形如下图!需求场景导出超10w左右的数据，涉及源数据查。组装业务数据解析数据库中的json数据内容，并组装相应的信息生成Excel文件内容到磁盘将文件压缩，并写回到浏览器。触发自动下载上线之后，功能运行正常。问题（频繁Out of memory异常）调查之后发现是，程序中的...
复制链接

扫一扫