Apache POI 可以对Microsoft Office 进行操作, 下面是工作中使用的对Excel进行读写操作的常用方式.
引入依赖
1
2
3
4
5
6
7
8
9
10
11
|
<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>
|
简单使用
主要介绍读写时, 分别常用到的一些对象及其含义, 方便自己编写util.
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
| package com.liuzhihang.tool.excel.poi;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
public class ExcelTest {
public static void main(String[] args) throws Exception {
writerTest();
}
private static void writerTest() throws IOException {
File file = new File("c:Users/liuzhihang/Desktop/test.xlsx");
if (file.exists()) {
System.out.println("读取的文件存在!");
file.delete();
}
file.createNewFile();
Workbook hssfWorkbook = new HSSFWorkbook();
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
XSSFSheet sheet = xssfWorkbook.createSheet();
Row row0 = sheet.createRow(0);
row0.createCell(0).setCellValue("序号");
OutputStream outputStream = new FileOutputStream(file);
xssfWorkbook.write(outputStream);
}
private static void readerTest() throws Exception {
File file = new File("c:Users/liuzhihang/Desktop/parkingLotTempLate.xlsx");
Workbook workBook = ExcelUtil.getWorkBook(file);
Sheet sheet = workBook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
Row row = sheet.getRow(0);
String value = row.getCell(0).getStringCellValue();
}
}
|
ExcelUtil 简单工具
poi读写 excel 的简单工具 ExcelUtil, 实际工作中可结合javaBean使用并重新编写util.
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
| package com.liuzhihang.tool.excel.poi;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
@Log4j2
public class ExcelUtil {
public static List<String> readExcelForTwoColumns(Workbook workbook) {
if (workbook == null) {
log.info("获取 workbook 为null");
return null;
}
List<String> list = new ArrayList<>();
try {
Sheet sheet = workbook.getSheetAt(0);
int rowNum = sheet.getLastRowNum();
for (int i = 0; i < rowNum; i++) {
Row row = sheet.getRow(i + 1);
String value = getCellValue(row.getCell(1)).toString();
if (StringUtils.isNotBlank(value)) {
list.add(value);
}
}
} catch (Exception e) {
log.error(e.getMessage());
}
return list;
}
public static void writerExcelForTwoColumns(File excelFile, List<String> list) {
OutputStream outputStream = null;
try {
outputStream = new FileOutputStream(excelFile);
Workbook workBook = null;
String fileName = excelFile.getName();
if (fileName.endsWith(".xls")) {
workBook = new HSSFWorkbook();
} else if (fileName.endsWith(".xlsx")) {
workBook = new XSSFWorkbook();
} else {
log.info("文件格式不正确!, 当前文件名:{}", fileName);
throw new Exception("文件格式不正确");
}
Sheet sheet = workBook.createSheet();
Row row1 = sheet.createRow(0);
row1.createCell(0).setCellValue("序号");
row1.createCell(1).setCellValue("编号");
for (int i = 0; i < list.size(); i++) {
Row row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(i + 1);
row.createCell(1).setCellValue(list.get(i));
}
workBook.write(outputStream);
} catch (Exception e) {
log.error("写excel失败", e);
} finally {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static Workbook getWorkBook(File file) throws Exception {
String fileName = file.getName();
Workbook workbook = null;
try {
InputStream inputStream = new FileInputStream(file);
if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(inputStream);
} else {
log.info("文件格式不正确!, 当前文件名:{}", fileName);
throw new Exception("文件格式不正确");
}
} catch (Exception e) {
throw e;
}
return workbook;
}
public static Object getCellValue(Cell cell) {
if (cell != null) {
switch (cell.getCellTypeEnum()) {
case NUMERIC:
return cell.getNumericCellValue();
case STRING:
return cell.getStringCellValue();
case FORMULA:
return cell.getCellFormula();
case BOOLEAN:
return cell.getBooleanCellValue();
case ERROR:
return cell.getErrorCellValue();
default:
return "";
}
}
return "";
}
}
|
