Java：apache.poi读写Excel文件

依赖

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16</version>
</dependency>

  

 

  
1
  
2
  
3
  
4
  
5
 

备注：poi-ooxml 已经包含了poi、poi-ooxml-schemas、curvesapi，不需要单独引入

文件格式对应处理类

.xls  => HSSFWorkbook
.xlsx => XSSFWorkbook

  

 

  
1
  
2
 

快速入门

写入文件

package com.demo.excel;

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.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

public class WriteExcel { public static void main(String[] args) throws IOException { // 创建工作表 Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("名称"); // 行 Row row = sheet.createRow(0); // 单元格 Cell cell = row.createCell(0); cell.setCellValue("姓名"); // 写到文件 OutputStream outputStream = new FileOutputStream("demo.xlsx"); workbook.write(outputStream); outputStream.close(); //关闭 workbook.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
 

读取文件

package com.demo.excel;

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.*;

public class ReadExcel { public static void main(String[] args) throws IOException { // 创建工作表 InputStream inputStream = new FileInputStream("demo.xlsx"); Workbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheet("名称"); Row row = sheet.getRow(0); Cell cell = row.getCell(0); String value = cell.getStringCellValue(); System.out.println(value); //关闭 inputStream.close(); workbook.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
 

Excel读写工具

package com.demo.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel读写工具，兼容xls 和 xlsx格式
 */
public class ExcelUtil { /** * 获取扩展名 * * @param filename 文件路径 * @return */ public static String getSuffix(String filename) { return filename.substring(filename.lastIndexOf(".")); } /** * 兼容xls xlsx文件格式 * * @param filename 文件路径 * @return */ public static Workbook readWorkBook(String filename) throws IOException { // 获取文件扩展名 String ext = getSuffix(filename); InputStream inputStream = new FileInputStream(filename); Workbook workbook = null; // xls格式的需要使用HSSFWorkbook类来解析 // xlsx格式的需要使用XSSFWorkbook格式来解析 if (".xls".equals(ext.toLowerCase())) { workbook = new HSSFWorkbook(inputStream); } else { workbook = new XSSFWorkbook(inputStream); } return workbook; } /** * 创建一个文件 * * @param filename * @return */ public static Workbook createWorkBook(String filename) { // 获取文件扩展名 String ext = getSuffix(filename); Workbook workbook = null; // xls格式的需要使用HSSFWorkbook类来解析 // xlsx格式的需要使用XSSFWorkbook格式来解析 if (".xls".equals(ext.toLowerCase())) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } return workbook; } /** * 读取Excel 文件 * * @param filename * @return 列表数据 * @throws Exception */ public static List<List<String>> readExcel(String filename) throws Exception { // 创建工作簿的对象 Workbook workBook = readWorkBook(filename); // 定义要返回的数据结构 List<List<String>> list = new ArrayList<>(); // 根据索引获取Sheet对象 Sheet sheet = workBook.getSheetAt(0); // 定义表头 List<String> rowList; // 获取所有行内容， 从第0 行开始 base 0 for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) { rowList = new ArrayList<>(); Row row = sheet.getRow(rowIndex); // 获取所有列值 base 1 for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) { Cell cell = row.getCell(columnIndex); //首先强制设置成string类型 cell.setCellType(CellType.STRING); String value = cell.getStringCellValue(); // System.out.println(value); rowList.add(value); } // System.out.println(map); list.add(rowList); } // 关闭工作簿 workBook.close(); return list; } /** * 写入到excel * * @param filename * @param list * @throws Exception */ public static void writeExcel(String filename, List<List<Object>> list) throws Exception { Workbook workbook = createWorkBook(filename); Sheet sheet = workbook.createSheet(); Row row; Cell cell; List<Object> rowList; for (int i = 0; i < list.size(); i++) { row = sheet.createRow(i); rowList = list.get(i); for (int j = 0; j < rowList.size(); j++) { cell = row.createCell(j); String value = String.valueOf(rowList.get(j)); cell.setCellValue(value); } } // 写到文件 OutputStream outputStream = new FileOutputStream(filename); workbook.write(outputStream); outputStream.close(); workbook.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
  
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
 

测试

package com.demo.excel;


import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

public class ExcelUtilTest { @Test public void testReadExcel() throws Exception { String filename = "demo.xlsx"; List<List<String>> list = ExcelUtil.readExcel(filename); System.out.println(list); // [[姓名, 年龄], [刘备, 42]] } @Test public void testWriteExcel() throws Exception { String filename = "demo.xlsx"; List<List<Object>> list = new ArrayList<>(); List<Object> list1 = new ArrayList<>(); list1.add("姓名"); list1.add("年龄"); List<Object> list2 = new ArrayList<>(); list2.add("刘备"); list2.add(42); list.add(list1); list.add(list2); System.out.println(list); // [[姓名, 年龄], [刘备, 42]] ExcelUtil.writeExcel(filename, list); }
}


  

 

  
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
 

文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。

原文链接：pengshiyu.blog.csdn.net/article/details/110931550