简介
Apache POI 是用 Java 编写的免费开源的跨平台的 Java API，Apache POI 提供 API 给 Java 程式对 Microsoft Office（Excel、WORD、PowerPoint、Visio 等）格式档案读和写的功能。
maven 坐标：
1
2
3
4
5
6
7
8
9
10
| <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>
|
Apache POI 常用的类
POI 结构：
HSSF － 提供读写 Microsoft Excel XLS 格式档案的功能
XSSF － 提供读写 Microsoft Excel OOXML XLSX 格式档案的功能（我们使用）
HWPF － 提供读写 Microsoft Word DOC 格式档案的功能
HSLF － 提供读写 Microsoft PowerPoint 格式档案的功能
HDGF － 提供读 Microsoft Visio 格式档案的功能
HPBF － 提供读 Microsoft Publisher 格式档案的功能
HSMF － 提供读 Microsoft Outlook 格式档案的功能
向 Excel 文件写入数据
使用 POI 可以在内存中创建一个 Excel 文件并将数据写入到这个文件，最后通过输出流将内存中的 Excel 文件下载到磁盘
【路径】
1.创建工作簿对象 2.创建工作表对象 3.创建行对象 4.创建列(单元格)对象, 设置内容 5.通过输出流将 workbook 对象保存到磁盘
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
| @Test
public void importExcel() throws IOException {
XSSFWorkbook workbook=new XSSFWorkbook();
XSSFSheet sheets = workbook.createSheet();
XSSFRow row = sheets.createRow(0);
row.createCell(0).setCellValue("编号");
row.createCell(1).setCellValue("姓名");
row.createCell(2).setCellValue("年龄");
XSSFRow row1 = sheets.createRow(1);
row1.createCell(0).setCellValue("001");
row1.createCell(1).setCellValue("张三");
row1.createCell(2).setCellValue("98");
XSSFRow row2 = sheets.createRow(2);
row2.createCell(0).setCellValue("002");
row2.createCell(1).setCellValue("李四");
row2.createCell(2).setCellValue("18");
FileOutputStream outputStream = new FileOutputStream("D:\\hello.xlsx");
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
workbook.close();
}
|
从 Excel 文件读取数据
【路径】
1：创建工作簿对象
2：获得工作表对象
3：遍历工作表对象 获得行对象
4：遍历行对象 获得单元格（列）对象
5：获得数据
6：关闭
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| @Test
public void exportExcel() throws IOException {
XSSFWorkbook workbook=new XSSFWorkbook();
XSSFSheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
String value = cell.getStringCellValue();
System.out.println(value);
}
}
workbook.close();
}
|
还有一种方式就是获取工作表最后一个行号，从而根据行号获得行对象，通过行获取最后一个单元格索引，从而根据单元格索引获取每行的一个单元格对象
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| @Test
public void exportExcel_lastRow() throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook("D:\\hello.xlsx");
XSSFSheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i <= lastRowNum; i++) {
XSSFRow row = sheet.getRow(i);
short lastCellNum = row.getLastCellNum();
for (short j = 0; j < lastCellNum; j++) {
String value = row.getCell(j).getStringCellValue();
System.out.println(value);
}
}
workbook.close();
}
|
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
| import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
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 org.springframework.web.multipart.MultipartFile;
public class POIUtils {
private final static String xls = "xls";
private final static String xlsx = "xlsx";
private final static String DATE_FORMAT = "yyyy/MM/dd";
public static List<String[]> readExcel(MultipartFile file) throws IOException {
checkFile(file);
Workbook workbook = getWorkBook(file);
List<String[]> list = new ArrayList<String[]>();
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+1;rowNum <= lastRowNum;rowNum++){
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getPhysicalNumberOfCells();
String[] cells = new String[row.getPhysicalNumberOfCells()];
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
workbook.close();
}
return list;
}
public static void checkFile(MultipartFile file) throws IOException{
if(null == file){
throw new FileNotFoundException("文件不存在！");
}
String fileName = file.getOriginalFilename();
if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
throw new IOException(fileName + "不是excel文件");
}
}
public static Workbook getWorkBook(MultipartFile file) {
String fileName = file.getOriginalFilename();
Workbook workbook = null;
try {
InputStream is = file.getInputStream();
if(fileName.endsWith(xls)){
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith(xlsx)){
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
public static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
String dataFormatString = cell.getCellStyle().getDataFormatString();
if(dataFormatString.equals("m/d/yy")){
cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
return cellValue;
}
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cell.setCellType(Cell.CELL_TYPE_STRING);
}
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}
|
