概述

POI

简介

Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

功能

HSSF - 提供读写Microsoft Excel格式档案的功能。

XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。

HWPF - 提供读写Microsoft Word格式档案的功能。

HSLF - 提供读写Microsoft PowerPoint格式档案的功能。

HDGF - 提供读写Microsoft Visio Visio格式档案的功能。

EasyExcel

简介

EasyExcel是alibaba的一个基于Java的简单、省内存的读写Excel的开源项目。

文档

yuque: https://www.yuque.com/easyexcel/doc/easyexcel

github: https://github.com/alibaba/easyexcel

POI

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
<!-- test -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!-- xls(2003) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- xlsx(2007) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!-- jodatime -->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.8</version>
</dependency>

write

HSSF创建xls(2003版本)

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
/**
* <p>HSSF创建xls</p>
* @throws Exception
*/
@Test
public void testWrite03() throws Exception {
// 1、创建一个工作簿
Workbook workbook = new HSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("K1");
// 3、创建第一行数据
Row row1 = sheet.createRow(0);
// 4、创建两个单元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("ID");
Cell cell12 = row1.createCell(1);
cell12.setCellValue(1);
// 5、创建第二行数据
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("姓名");
Cell cell22 = row2.createCell(1);
cell22.setCellValue("K1");
// 6、创建第三行数据
Row row3 = sheet.createRow(2);
Cell cell31 = row3.createCell(0);
cell31.setCellValue("时间");
Cell cell32 = row3.createCell(1);
cell32.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
// 7、生成一张表
FileOutputStream fileOutputStream = new FileOutputStream(System.getProperty("user.dir") + "/file/统计表03.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
}

XSSF创建xlsx(2007版本)

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
@Test
public void testWrite07() throws Exception {
// 1、创建一个工作簿
Workbook workbook = new XSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("K1");
// 3、创建第一行数据
Row row1 = sheet.createRow(0);
// 4、创建两个单元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("ID");
Cell cell12 = row1.createCell(1);
cell12.setCellValue(1);
// 5、创建第二行数据
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("姓名");
Cell cell22 = row2.createCell(1);
cell22.setCellValue("K1");
// 6、创建第三行数据
Row row3 = sheet.createRow(2);
Cell cell31 = row3.createCell(0);
cell31.setCellValue("时间");
Cell cell32 = row3.createCell(1);
cell32.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
// 7、生成一张表
FileOutputStream fileOutputStream = new FileOutputStream(System.getProperty("user.dir") + "/file/统计表07.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
}

注意区别

  • 对象不同,03版本是HSSFWorkBook,07版本是XSSFWorkBook
  • 文件后缀,03版本excel后缀是xls,07版本excel后缀是xlsx

大文件写HSSF

缺点:最多只能处理65536行,否则会抛异常

优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@Test
public void testWrite03BigData() throws Exception{
// 开始时间
long start = System.currentTimeMillis();
// 创建工作簿
Workbook workbook = new HSSFWorkbook();
// 创建一张表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
FileOutputStream fileOutputStream = new FileOutputStream(System.getProperty("user.dir") + "/file/testWrite03BigData.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println("耗时:" + (end - start) + "ms");
}

大文件写XSSF

缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条

优点:可以写较大的数据量,如20万条

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@Test
public void testWrite07BigData() throws Exception{
// 开始时间
long start = System.currentTimeMillis();
// 创建工作簿
Workbook workbook = new XSSFWorkbook();
// 创建一张表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
FileOutputStream fileOutputStream = new FileOutputStream(System.getProperty("user.dir") + "/file/testWrite07BigData.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println("耗时:" + (end - start) + "ms");
}

大文件写SXSSF

优点:可以写非常大的数据量,如100万条甚至更多条,写数据速度快,占用更少的内存

注意:

过程中会产生临时文件,需要清理临时文件。

默认由100条记录被保存在内存中,如果超过这数量,则最前面的数据被写入临时文件。

如果想自定义内存中数据的数量,可以使用new SXSSFWorkBook。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Test
public void testWrite07BigDataS() throws Exception{
// 开始时间
long start = System.currentTimeMillis();
// 创建工作簿
Workbook workbook = new SXSSFWorkbook();
// 创建一张表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 10 * 10000; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
FileOutputStream fileOutputStream = new FileOutputStream(System.getProperty("user.dir") + "/file/testWrite07BigDataS.xlsx");
workbook.write(fileOutputStream);
// 删除临时文件
((SXSSFWorkbook) workbook).dispose();
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println("耗时:" + (end - start) + "ms");
}

read

HSSF读取xls(2003版本)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
public void testRead03() throws Exception {
// 获取文件流
FileInputStream inputStream = new FileInputStream(System.getProperty("user.dir") + "/file/统计表03.xls");
// 创建一个工作簿
Workbook workbook = new HSSFWorkbook(inputStream);
// 获取表
Sheet sheet = workbook.getSheetAt(0);
// 获取行
Row row = sheet.getRow(0);
// 获取列
Cell cell = row.getCell(0);
System.out.println(cell.getStringCellValue());
// 关闭流
inputStream.close();
}

XSSF读取xlsx(2007版本)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
public void testRead07() throws Exception {
// 获取文件流
FileInputStream inputStream = new FileInputStream(System.getProperty("user.dir") + "/file/统计表07.xlsx");
// 创建一个工作簿
Workbook workbook = new XSSFWorkbook(inputStream);
// 获取表
Sheet sheet = workbook.getSheetAt(0);
// 获取行
Row row = sheet.getRow(0);
// 获取列
Cell cell = row.getCell(0);
System.out.println(cell.getStringCellValue());
// 关闭流
inputStream.close();
}

测试读取完整表

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
@Test
public void testReadCell() throws Exception {
// 获取文件流
FileInputStream inputStream = new FileInputStream(System.getProperty("user.dir") + "/file/学生花名册.xlsx");
// 创建工作簿
Workbook workbook = new XSSFWorkbook(inputStream);
// 获取表
Sheet sheet = workbook.getSheetAt(0);
// 获取标题
Row rowTitle = sheet.getRow(0);
// 输出标题
System.out.print("| ");
if (rowTitle != null) {
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell != null) {
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + " | ");
}
}
}
System.out.println();
// 获取行数
int rowCount = sheet.getLastRowNum();
// 获取数据
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null) {
int cellCount = rowData.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowData.getCell(cellNum);
// 匹配单元格的数据类型
if (cell != null) {
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + "\t");
}
}
System.out.println();
}
}
inputStream.close();
}

EasyExcel

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
<!-- test -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
<!-- easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
<!-- fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.75</version>
</dependency>
<!-- xls(2003) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- xlsx(2007) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>

write

准备数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DemoData implements Serializable, Cloneable {

@ExcelProperty("字符串字段")
private String stringData;

@ExcelProperty("日期字段")
private Date dateData;

@ExcelProperty("整数字段")
private Integer intData;

@ExcelProperty("浮点字段")
private Double douData;

@Override
protected Object clone() throws CloneNotSupportedException {
return super.clone();
}
}

测试写入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
private List<DemoData> data() {
List<DemoData> list = new ArrayList<>();
Random random = new Random();
for (int i = 1; i <= 10; i++) {
DemoData demoData = new DemoData();
demoData.setStringData(i + "号");
demoData.setDateData(new Date());
demoData.setIntData(random.nextInt(100));
demoData.setDouData(random.nextDouble());
}
return list;
}

@org.junit.Test
public void write() {
String fileName = System.getProperty("user.dir") + "/file/simpleWrite.xlsx";
EasyExcel.write(fileName, DemoData.class).sheet("表1").doWrite(data());
}

read

数据解析器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public class DemoDataListener extends AnalysisEventListener<DemoData> {
private static final int BATCH_COUNT = 5;
private static final List<DemoData> list = new ArrayList<>();

// 读取数据调用
@Override
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
System.out.println(demoData);
if (list.size() == BATCH_COUNT) {
list.clear();
}
}

// 解析完成调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成");
}
}

测试读取

1
2
3
4
5
@Test
public void read() {
String fileName = System.getProperty("user.dir") + "/file/simpleWrite.xlsx";
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

总结

对于单纯的表格数据操作,POI的使用还是比较好的。

EasyExcel的封装性比较强,OOP和AOP很有针对性。

EasyExcel做一些数据库的批量导出导入还是不错的。

鉴于POI操作上的自由性,自己封装了一个工具类:

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
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;

/**
* <p> Project: Excel </p>
* <p> Package: top.parak.poi </p>
* <p> FileName: ExcelUtil <p>
* <p> Description: Excel工具类 <p>
* <p> Created By IntelliJ IDEA </p>
*
* @author KHighness
* @since 2021/5/19
*/
public class ExcelUtil {
/** office2003 */
private final static String EXCEL_TYPE_XLS = "xls";
/** office2007 */
private final static String EXCEL_TYPE_XLSX = "xlsx";
/** 空字符串 */
private final static String CHAR_TYPE_BLANK = "";
/** 错误字符 */
private final static String CHAR_TYPE_ERROR = "error";
/** 未知字符 */
private final static String CHAR_TYPE_UNKNOWN = "unknown";
/** 列的长度单位 */
private final static Integer COLUMN_CELL_WIDTH = 1 << 10;
/** 时间字符串格式 */
private final static String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss.SSS";
/** 时间字符串格式化器 */
private final static DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern(DATE_FORMAT);

/**
* 读取表格文件并解析成字符串数组
* @param file 表格文件
* @return 数据集合
*/
public static List<List<Object>> readExcel(File file) {
try {
checkFile(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
Workbook workBook = getWorkBook(file);
return readProcess(workBook);
}

/**
* 检验文件是否存在,并且为表格文件
* @param file 文件
* @throws NullPointerException 未找到文件
* @throws FileNotFoundException 非表格文件
* @throws IllegalArgumentException 后缀名错误
*/
private static void checkFile(File file) throws FileNotFoundException {
if (file == null) {
throw new NullPointerException("File is null");
}
if(!file.exists()){
throw new FileNotFoundException("File " + file + " does not exist");
}
String fileName = file.getName();
if(!fileName.endsWith(EXCEL_TYPE_XLS) && !fileName.endsWith(EXCEL_TYPE_XLSX)){
throw new IllegalArgumentException(fileName + " is not a kind of excel file");
}
}

/**
* 获取文件对应的工作簿,分为XLS和XLSX类型
* @param file 表格文件
* @return 表格文件的工作簿
*/
private static Workbook getWorkBook(File file) {
String fileName = file.getName();
Workbook workbook = null;
try {
InputStream in = new FileInputStream(file);
workbook = fileName.endsWith(EXCEL_TYPE_XLS) ? new HSSFWorkbook(in) : new XSSFWorkbook(in);
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}

/**
* 读取工作簿,一行数据读作一个数组,所有行作为一个集合返回
* @param workbook 工作簿
* @return 数据集合
*/
private static List<List<Object>> readProcess(Workbook workbook) {
List<List<Object>> res = new ArrayList<>();
FormulaEvaluator evaluator = (workbook instanceof HSSFWorkbook) ?
new HSSFFormulaEvaluator((HSSFWorkbook) workbook) : new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
if(workbook != null) {
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
Sheet sheet = workbook.getSheetAt(sheetNum);
if (sheet == null)
continue;
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null)
continue;
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getPhysicalNumberOfCells();
List<Object> curr = new ArrayList<>(lastCellNum - firstCellNum + 1);
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
curr.add(getCellValue(cell, evaluator));
}
res.add(curr);
}
}
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return res;
}

/**
* 获取单元格的值
* @param cell 单元格
* @param evaluator 公式计算
* @return 单元格的值
*/
private static Object getCellValue(Cell cell, FormulaEvaluator evaluator){
Object cellValue = "";
if(cell == null){
return cellValue;
}
// 判断数据的类型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: // 数字类型
cellValue = cell.getNumericCellValue(); break;
case Cell.CELL_TYPE_STRING: // 字符串类型
cellValue = cell.getStringCellValue(); break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔类型
cellValue = cell.getBooleanCellValue(); break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = evaluator.evaluate(cell); break;
case Cell.CELL_TYPE_BLANK: // 空值
cellValue = CHAR_TYPE_BLANK; break;
case Cell.CELL_TYPE_ERROR: // 错误
cellValue = CHAR_TYPE_ERROR; break;
default: cellValue = CHAR_TYPE_UNKNOWN; break;
}
return cellValue;
}

/**
* 将标题和内容写入新文件
* @param file 文件
* @param sheetName 表格名
* @param titleList 标题列
* @param contentList 内容列
*/
public static void writeExcel(File file, String sheetName, List<String> titleList, List<List<Object>> contentList) {
String fileName = checkParam(file, sheetName, titleList, contentList);
Workbook workbook = fileName.endsWith(EXCEL_TYPE_XLS) ? new HSSFWorkbook() : new XSSFWorkbook();
Sheet sheet = workbook.createSheet(sheetName);
initHead(workbook, sheet, titleList);
writeContent(workbook, sheet, contentList);
try {
FileOutputStream out = new FileOutputStream(file);
workbook.write(out);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}

/**
* 检查参数是否合法,并返回文件名
* @param file 文件
* @param sheetName 表格名
* @param titleList 标题列
* @param contentList 内容列
* @return 文件名
*/
private static String checkParam(File file, String sheetName, List<String> titleList, List<List<Object>> contentList) {
if (file == null)
throw new NullPointerException("File is null");
String fileName = file.getName();
if (fileName.equals("") || !(fileName.endsWith(EXCEL_TYPE_XLS) || fileName.endsWith(EXCEL_TYPE_XLSX)))
throw new IllegalArgumentException("File name is illegal");
if (sheetName == null || sheetName.equals(""))
throw new IllegalArgumentException("Sheet name is blank");
if (titleList == null)
throw new IllegalArgumentException("Title list is null");
if (contentList == null)
throw new IllegalArgumentException("Content list is null");
return fileName;
}

/**
* 初始化标题行
* @param book 工作簿
* @param sheet 当前表
* @param titleList 标题列
*/
private static void initHead(Workbook book, Sheet sheet, List<String> titleList) {
// 标题样式
CellStyle style = book.createCellStyle();
// 居中对齐
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 背景填充
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 字体样式
Font font = book.createFont();
font.setFontName("华文楷体");
font.setFontHeightInPoints((short) 13);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setColor(IndexedColors.WHITE.getIndex());
style.setFont(font);
// 创建第一行
Row head = sheet.createRow(0);
head.setHeightInPoints(20);
// 列的宽度
int columnWidth = 0;
for (int i = 0; i < titleList.size(); i++) {
Cell cell = head.createCell(i);
cell.setCellStyle(style);
String s = titleList.get(i);
cell.setCellValue(s);
columnWidth = s.length() * COLUMN_CELL_WIDTH;
sheet.setColumnWidth(i, columnWidth);
}
}

/**
* 填充内容行
* @param book 工作簿
* @param sheet 当前表
* @param contentList 内容列
*/
private static void writeContent(Workbook book, Sheet sheet, List<List<Object>> contentList) {
for (int i = 0; i < contentList.size(); i++) {
Row curr = sheet.createRow(i + 1);
List<Object> content = contentList.get(i);
for (int c = 0; c < content.size(); c++) {
writeProcess(book, curr, c, content.get(c));
}
}
}

/**
* 将值填入单元格
* @param book 工作簿
* @param row 当前行
* @param col 当前列
* @param val 单元值
*/
private static void writeProcess(Workbook book, Row row, int col, Object val) {
// 内容样式
CellStyle style = book.createCellStyle();
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
// 居中对齐
style.setAlignment(CellStyle.ALIGN_CENTER);
// 字体样式
Font font = book.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 10);
style.setFont(font);
Cell cell = row.createCell(col);
cell.setCellStyle(style);
try {
if (val == null) {
cell.setCellValue(CHAR_TYPE_BLANK);
} else {
if (val instanceof String) {
cell.setCellValue((String) val);
} else if (val instanceof Integer) {
cell.setCellValue((int) val);
} else if (val instanceof Long) {
cell.setCellValue((Long) val);
} else if (val instanceof Double) {
cell.setCellValue((Double) val);
} else if (val instanceof Float) {
cell.setCellValue((Float) val);
} else if (val instanceof Boolean) {
cell.setCellValue((Boolean) val);
} else if (val instanceof TemporalAccessor) {
cell.setCellValue(DATE_TIME_FORMATTER.format((TemporalAccessor) val));
} else {
cell.setCellValue(val.toString());
}
}
} catch (Exception e) {
cell.setCellValue(CHAR_TYPE_ERROR);
e.printStackTrace();
}
}
}