概述
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
|
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<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
|
@Test
public void testWrite03() throws Exception {
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("K1");
Row row1 = sheet.createRow(0);
Cell cell11 = row1.createCell(0);
cell11.setCellValue("ID");
Cell cell12 = row1.createCell(1);
cell12.setCellValue(1);
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("姓名");
Cell cell22 = row2.createCell(1);
cell22.setCellValue("K1");
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"));
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 {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("K1");
Row row1 = sheet.createRow(0);
Cell cell11 = row1.createCell(0);
cell11.setCellValue("ID");
Cell cell12 = row1.createCell(1);
cell12.setCellValue(1);
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("姓名");
Cell cell22 = row2.createCell(1);
cell22.setCellValue("K1");
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"));
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
|
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.75</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<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
| 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;
public class ExcelUtil {
private final static String EXCEL_TYPE_XLS = "xls";
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);
public static List<List<Object>> readExcel(File file) {
try {
checkFile(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
Workbook workBook = getWorkBook(file);
return readProcess(workBook);
}
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");
}
}
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;
}
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;
}
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;
}
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();
}
}
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;
}
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);
}
}
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));
}
}
}
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();
}
}
}
|
