目录
POI 读写excel表格
POI是Apache软件基金会的，POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。
所以POI的主要功能是可以用Java操作Microsoft Office的相关文件，这里我们主要讲Excel
1. 写入
1.1 pom依赖
<!--2003(xls)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!--2007(xlsx)版本-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
所谓03版和07版最大的区别在于 03版表格最多支持65536行, 而07版则没有上限
1.2 基本功能
结构
- HSSF - 提供读写Microsoft Excel格式文档的功能 (03版)
- XSSF - 提供读写Microsoft Excel OOXML格式文档的功能(07版)
- HWPF - 提供读写Microsoft Word格式文档的功能(word文档)
- HSLF - 提供读写Microsoft PowerPoint格式文档的功能(PPT幻灯片)
- HDGF - 提供读写Microsoft Visio格式文档的功能
1.3 Workbook接口的三个实现类
| 实现类 | 描述 |
|---|---|
| HSSFWorkbook() | 03版写入 |
| XSSFWorkbook() | 07版写入 (把所有的数据保存到内存 ,再写入到文件. 可能发生内存泄露) |
| SXSSFWorkbook() | 写入速度快, 占用更少的内存. 中间会产生一个临时文件, 需要清理临时文件, 默认由100条记录被保存到内存中, 如果超出这个范围,则被写入到临时文件中,如果想自 定义内存中的数量 ,可以 new SXSSFWorkbook(数量) 来进行定义 |
1.4 写入文件演示 (03版)
// 生成文件路径定义
String PATH = "/home/zpk/DEV/idea/workspace/poi/";
@Test
public void testWrite03() throws Exception {
// 1. 创建一个工作簿
Workbook workbook = new HSSFWorkbook();
// 2. 创建一个工作表
Sheet sheet = workbook.createSheet("统计表");
// 3. 创建一个行 (第一行)
Row row1 = sheet.createRow(0);
// 创建单元格(1,1)
Cell cell11 = row1.createCell(0);
// 单元格(1,1)写入内容
cell11.setCellValue("今日新增观众");
// 创建单元格(1,2)
Cell cell12 = row1.createCell(1);
// 单元格(1,2)写入内容
cell12.setCellValue(666);
// 第二行
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
// 生成一张表(IO流) 03 版本就是使用xls结尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "统计表03版.xls");
// 写出文件
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
System.out.println("文件生成完毕");
}
1.5 写入文件演示 (07版)
// 生成文件路径定义
String PATH = "/home/zpk/DEV/idea/workspace/poi/";
@Test
public void testWrite07() throws Exception {
// 1. 创建一个工作簿
Workbook workbook = new XSSFWorkbook();
// 2. 创建一个工作表
Sheet sheet = workbook.createSheet("统计表");
// 3. 创建一个行 (第一行)
Row row1 = sheet.createRow(0);
// 创建单元格(1,1)
Cell cell11 = row1.createCell(0);
// 单元格(1,1)写入内容
cell11.setCellValue("今日新增观众");
// 创建单元格(1,2)
Cell cell12 = row1.createCell(1);
// 单元格(1,2)写入内容
cell12.setCellValue(666);
// 第二行
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
// 生成一张表(IO流) 03 版本就是使用xls结尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "统计表07版.xlsx");
// 写出文件
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
System.out.println("文件生成完毕");
}
可见 若生成07版只需要把Workbook workbook = new HSSFWorkbook();改为 Workbook workbook = new XSSFWorkbook(); 并且生成的文件后缀改为xlsx就可以了 ,这就是面向借口编程的好处
1.6 (HSSF) 03版写入大量数据
默认写入65536行数据, 查看所需要的时间
// 生成文件路径定义
String PATH = "/home/zpk/DEV/idea/workspace/poi/";
@Test
public void testWrite03BigData() throws Exception {
long begin = System.currentTimeMillis(); // 开始时间
// 创建一个簿
Workbook workbook = new HSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65536; rowNum++) {
// 一共65536行
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
// 每列十条数据
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
// 输出流
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigData03");
// 输出
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
long end = System.currentTimeMillis(); // 结束时间
// 时间结算
System.out.println((double)(end - begin)/1000 + " s");
}
执行结果: 总共耗时2.036秒
over
2.036 s
注意: 03版如果行数大于65536则抛出异常
1.7 (XSSF) 07版写入大量数据
// 生成文件路径定义
String PATH = "/home/zpk/DEV/idea/workspace/poi/";
@Test
public void testWrite07BigData() throws Exception {
// 时间
long begin = System.currentTimeMillis();
// 创建一个簿
Workbook workbook = new XSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigData07.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double)(end - begin)/1000 + " s");
}
执行结果: 总共耗时8.015 秒
over
8.015 s
1.8 (SXSSF) 写入大量数据
// 生成文件路径定义
String PATH = "/home/zpk/DEV/idea/workspace/poi/";
@Test
public void testWriteSXSSFBigData() throws Exception {
// 时间
long begin = System.currentTimeMillis();
// 创建一个簿
Workbook workbook = new SXSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigDataSXSSF.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
// 清除临时文件
((SXSSFWorkbook) workbook).dispose();
long end = System.currentTimeMillis();
System.out.println((double)(end - begin)/1000 + " s");
}
结果: 2.192 秒
over
2.192 s
注意: 使用SXSSFWorkbook时需要清理临时文件
// 清除临时文件
((SXSSFWorkbook) workbook).dispose();
1.9 写入大量数据结论
-
HSSF 写入大量数据v
- 优点: 可以写入快速写入数据
- 缺点: 只能写入65536行数据, 非常有限
-
XSSF 写入大量数据
-
优点: 可以写入任意条数据
-
缺点: 写入速度慢的一批
-
-
SXSSF 写入大量数据
- 优点: 快速而且占用内存少
- 缺点: 仍然可能会消耗大量内存, 这些内存基于正在使用的功能,例如合并区域, 注释仍然会储存在内存中, 因此如果广泛使用, 可能需要大量的内存
2. 读取
2.1读取文件演示(03版)
// 路径
String PATH = "/home/zpk/DEV/idea/workspace/poi/";
@Test
public void testRead03() throws Exception {
// 获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "统计表03版.xls");
// 1. 创建一个工作簿
Workbook workbook = new HSSFWorkbook(inputStream);
// 2. 获取一个工作表, getSheetAt()方法是根据下标获取
Sheet sheet = workbook.getSheetAt(0);
// 3. 得到行
Row row = sheet.getRow(0);
// 4. 得到单元格
Cell cell = row.getCell(0);
// 显示(1,1)的内容, 获取字符串
System.out.println(cell.getStringCellValue());
// 关闭流
inputStream.close();
}
结果: (获取到了上面写入中的1.4中生成的文件)
今日新增观众
2.2 读取文件演示(07版)
// 路径
String PATH = "/home/zpk/DEV/idea/workspace/poi/";
@Test
public void testRead07() throws Exception {
// 获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "统计表07版.xlsx");
// 1. 创建一个工作簿
Workbook workbook = new XSSFWorkbook(inputStream);
// 2. 获取一个工作表, getSheetAt()方法是根据下标获取
Sheet sheet = workbook.getSheetAt(0);
// 3. 得到行
Row row = sheet.getRow(0);
// 4. 得到单元格
Cell cell = row.getCell(1);
// 显示(1,1)的内容, 获取数字类型
System.out.println(cell.getNumericCellValue());
// 关闭流
inputStream.close();
}
结果: (获取到了上面写入中的1.5中生成的文文件)
666.0
2.3 获取一行的数据(重点)
Row rows = sheet.getRow(0); // 获取第一行
if (rows != null) {
int cellCount = rows.getPhysicalNumberOfCells(); // 计数,次行多少列存入cellCount中
for (int cellNum = 0; cellNum < cellCount; cellNum++){ // 遍历列
Cell cell = rows.getCell(cellNum); // 获取每一列
if (cell != null) {
int cellType = cell.getCellType(); // 每一列的数据类型(枚举)
String cellValue = cell.getStringCellValue(); // 获取字符串内容
System.out.print(cellValue + " | "); // 分割线
}
}
System.out.println() // 换行
}
2.4 获取整张表的内容
(工具类), 只需要传入输入流即可
// 提取为工具类
public void read07Excel(FileInputStream inputStream) throws Exception {
// 1. 创建一个工作簿
Workbook workbook = new XSSFWorkbook(inputStream);
// 2. 获取一个工作表, getSheetAt()方法是根据下标获取
Sheet sheet = workbook.getSheetAt(0);
Row rows = sheet.getRow(0); // 获取第一行
if (rows != null) {
int cellCount = rows.getPhysicalNumberOfCells(); // 计数,次行多少列存入cellCount中
for (int cellNum = 0; cellNum < cellCount; cellNum++){ // 遍历列
Cell cell = rows.getCell(cellNum); // 获取每一列
if (cell != null) {
int cellType = cell.getCellType(); // 每一列的数据类型(枚举)
String cellValue = cell.getStringCellValue(); // 获取字符串内容
System.out.print(cellValue + " | "); // 分割线
}
}
System.out.println(); // 换行
}
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null) {
// 读取列
int cellCount = rows.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.println("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
Cell cell = rowData.getCell(cellNum);
// 匹配列的数据类型
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING : // 字符串
System.out.print("[String]");
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN : // 布尔型
System.out.print("[Boolean]");
cellValue = String.valueOf(cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK : // 空
System.out.print("[Blank]");
break;
case HSSFCell.CELL_TYPE_NUMERIC : // 数字(日期和普通数字
System.out.print("[Number]");
if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期
System.out.print("[日期]");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else { // 数字, 直接转换为字符串输出
// 不是日期格式, 防止数字过长!
System.out.print("[转为字符串输出]");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = String.valueOf(cell.getStringCellValue());
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_ERROR : // 布尔型
System.out.print("[数据类型错误]");
break;
}
System.out.println(cellValue);
}
}
}
}
// 关闭流
inputStream.close();
}