POI 提供操作 Office 文档的 API，推荐使用阿里 EasyExcel。
- 保证每个 sheet 的数据量小于 1048575 行
- 数据进行分页读取，并进行分页写入到 excel
- 使用JDBC流式查询，分批写入到 sheet
- 分批读取，多线程处理需要控制同时处理总数
Apache POI - the Java API for Microsoft Documents
EasyExcel
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便
1
2
3
4
5
|
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
|
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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
|
/**
* EasyExcel 工具类
*
* <p>导出只推荐 xlsx
*
* <p>https://www.yuque.com/easyexcel/doc
*
* @author ethan wang
* @param <T>
*/
@Slf4j
public class ExcelUtil<T> {
/** 每个sheet存储的记录数 100W 一个sheet 2003版最大行数是65536行 2007后最多1048576行 */
public static final Integer PER_SHEET_ROW_COUNT = 1000000;
/** 每次向EXCEL写入的记录数(查询每页数据大小) 20W */
public static final Integer PER_WRITE_ROW_COUNT = 200000;
private int headRowNumber = 1;
/** 数据行对应的实体类型 */
private final Class<T> clazz;
public ExcelUtil(Class<T> clazz) {
this.clazz = clazz;
}
public ExcelUtil<T> setHeadRowNumber(int headRowNumber) {
this.headRowNumber = headRowNumber;
return this;
}
/**
* 20w 以内一个SHEET一次查询导出
*
* @param out 目标流
* @param data 导出数据
*/
public void write(
String sheetName, OutputStream out, List<T> data, SheetWriteHandler... sheetWriteHandlers) {
if (!data.isEmpty()) {
ExcelWriterSheetBuilder sheetBuilder =
EasyExcelFactory.write(out, clazz).autoCloseStream(Boolean.FALSE).sheet(sheetName);
if (null != sheetWriteHandlers && sheetWriteHandlers.length > 0) {
for (SheetWriteHandler sheetWriteHandler : sheetWriteHandlers) {
sheetBuilder.registerWriteHandler(sheetWriteHandler);
}
}
sheetBuilder.doWrite(data);
}
}
/**
* 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link ReadDataListener}
*
* @param input 导入文件流
* @param consumer 处理读取数据
*/
public void read(InputStream input, Consumer<List<T>> consumer) {
EasyExcelFactory.read(input, clazz, new ReadDataListener<>(consumer))
.sheet()
.headRowNumber(headRowNumber)
.doRead();
}
/**
* 支持大数据量导出 分批使用内存
*
* @param out 目标流
* @param total 总数据量
* @param function 条件 -> 结果 通过条件每次查询 {@link ExcelUtil#PER_WRITE_ROW_COUNT} 大小数据
*/
public void write(
String sheetName, OutputStream out, int total, Function<Page<T>, List<T>> function) {
ExcelWriter excelWriter = EasyExcelFactory.write(out, clazz).build();
// 这里注意 如果同一个sheet只要创建一次
WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetName).build();
int pages = total / PER_WRITE_ROW_COUNT;
List<T> apply;
Page<T> page = new Page<>();
for (int i = 0; i < pages; i++) {
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
page.setCurrent(i + 1);
page.setSize(PER_WRITE_ROW_COUNT);
apply = function.apply(page);
excelWriter.write(apply, writeSheet);
}
// 关闭流
excelWriter.finish();
}
/**
* 模板填充导出
*
* @param template 模板输入流
* @param out 输出流
* @param total 总记录条数
* @param function 分组查询数据
*/
public void fill(
InputStream template, OutputStream out, int total, Function<Page<T>, List<T>> function)
throws IOException {
int sheets =
total / PER_SHEET_ROW_COUNT + (Math.floorMod(total, PER_SHEET_ROW_COUNT) > 0 ? 1 : 0);
if (sheets > 1) {
template = cloneSheetZero(template, sheets - 1);
}
ExcelWriter excelWriter = EasyExcelFactory.write(out, clazz).withTemplate(template).build();
// 这里注意 如果同一个sheet只要创建一次 0开始
WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();
for (int i = 1; i <= sheets; i++) {
if (i != 1) {
writeSheet = EasyExcelFactory.writerSheet(i - 1).build();
}
int startPage = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT * (i - 1);
int pages = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT * i;
if (i == sheets) {
pages =
total / PER_WRITE_ROW_COUNT + (Math.floorMod(total, PER_WRITE_ROW_COUNT) > 0 ? 1 : 0);
}
List<T> apply;
Page<T> page = new Page<>();
for (int p = startPage; p < pages; p++) {
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
page.setCurrent(p + 1);
page.setSize(PER_WRITE_ROW_COUNT);
apply = function.apply(page);
excelWriter.fill(apply, writeSheet);
}
}
// 关闭流
excelWriter.finish();
}
/**
* 文件名防止重复
*
* @param fileName 文件名不带后缀
* @return 文件名
*/
public String encodingFileName(String fileName) {
return fileName + "-" + Instant.now().toEpochMilli() + ExcelTypeEnum.XLSX.getValue();
}
private InputStream cloneSheetZero(InputStream fileInputStream, int count) throws IOException {
ByteArrayInputStream result;
try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream)) {
String sheetName = workbook.getSheetName(0);
for (int i = 0; i < count; i++) {
XSSFSheet newSheet = workbook.cloneSheet(0);
int newSheetIndex = workbook.getSheetIndex(newSheet);
workbook.setSheetName(newSheetIndex, sheetName + newSheetIndex);
}
outputStream.flush();
workbook.write(outputStream);
result = new ByteArrayInputStream(outputStream.toByteArray());
}
return result;
}
}
|
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
|
@Getter
@AllArgsConstructor
public enum GenderEnum implements ExcelEnum {
FEMALE(0, "女性"),
MALE(1, "男性"),
;
private final int value;
private final String label;
}
@Getter
@Setter
@EqualsAndHashCode
@ExcelIgnoreUnannotated
@ColumnWidth(15)
public class DemoData {
@ExcelProperty(value = "ID")
private Long id;
@ColumnWidth(10)
@ExcelProperty(value = "年龄")
private Integer age;
@ExcelProperty(value = "姓名")
private String name;
@ExcelProperty(value = "创建时间")
@ColumnWidth(25)
private LocalDateTime createTime;
@ExcelProperty(value = "性别", converter = ExcelEnumConverter.class)
private GenderEnum gender;
}
|
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
|
public interface ExcelEnum {
/**
* 显示
*
* @return 显示
*/
String getLabel();
}
public class ExcelEnumConverter implements Converter<Enum<? extends ExcelEnum>> {
public static final Logger log = LoggerFactory.getLogger(ExcelEnumConverter.class);
@Override
public Class<Enum> supportJavaTypeKey() {
return Enum.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Enum<? extends ExcelEnum> convertToJavaData(ReadConverterContext<?> context)
throws Exception {
String stringValue = context.getReadCellData().getStringValue();
Class<?> type = context.getContentProperty().getField().getType();
for (Object enumConstant : type.getEnumConstants()) {
if (enumConstant instanceof ExcelEnum
&& ((ExcelEnum) enumConstant).getLabel().equalsIgnoreCase(stringValue)) {
return (Enum<? extends ExcelEnum>) enumConstant;
}
}
log.warn("枚举 {} 需要实现接口 ExcelEnum 值:{}", type.getName(), stringValue);
return null;
}
@Override
public WriteCellData<?> convertToExcelData(
WriteConverterContext<Enum<? extends ExcelEnum>> context) throws Exception {
Enum<? extends ExcelEnum> value = context.getValue();
String result;
if (value instanceof ExcelEnum) {
result = ((ExcelEnum) value).getLabel();
} else {
result = value.name();
log.warn("枚举 {} 不合法, 需要实现接口 ExcelEnum", value.getDeclaringClass().getName());
}
return new WriteCellData<>(result);
}
}
|
MySQL 大数据量导出，分页需要考虑 offset 性能问题，可以采用 JDBC 流式查询分批处理。可以使用多线程加速。
1
2
3
4
|
@Select("select * from xxx order by xx desc")
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
@ResultType(XxxObject.class)
void queryStreamResult(ResultHandler<XxxObject> handler);
|
分区处理
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
|
//按每3个一组分割
private static final Integer BATCH_SIZE = 1000;
public static void batch(Stream<User> userStream, int total) {
int limit = countStep(total);
//方法一：使用流遍历操作
List<List<User>> mglist = new ArrayList<>();
Stream.iterate(0, n -> n + 1).limit(limit).forEach(i -> {
mglist.add(userStream.skip(i * MAX_SEND).limit(MAX_SEND).collect(Collectors.toList()));
});
//方法二：获取分割后的集合
List<List<User>> splitList = Stream.iterate(0, n -> n + 1).limit(limit).parallel().map(a -> userStream.skip(a * MAX_SEND).limit(MAX_SEND).parallel().collect(Collectors.toList())).collect(Collectors.toList());
}
/**
* 计算切分次数
*/
private static Integer countStep(Integer size) {
return (size + BATCH_SIZE - 1) / BATCH_SIZE;
}
// 其线程数默认为 CPU 数量减1
final var sublist = new AtomicInteger();
var result = new ArrayList<int[]>();
var futures =
users.stream()
.collect(Collectors.groupingBy(t -> sublist.getAndIncrement() / Constant.BATCH_SIZE))
.values()
.stream()
.map(
sectionList ->
CompletableFuture.supplyAsync(() -> batchUpdate(sql, sectionList))
.whenComplete((r, e) -> result.add(r)))
.toArray(CompletableFuture[]::new);
CompletableFuture.allOf(futures).join();
|
附录