一、Excel导入导出的应用场景
1、数据导入:减轻录入工作量
2、数据导出:统计信息归档
3、数据传输:异构系统之间数据传输
二、POI简介(Apache POI)
1、什么是POI
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
- HSSF - 提供读写Microsoft Excel格式档案的功能。(.xls)
- XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。(.xlsx)
- HWPF - 提供读写Microsoft Word格式档案的功能。
- HSLF - 提供读写Microsoft PowerPoint格式档案的功能。
- HDGF - 提供读写Microsoft Visio格式档案的功能。
2、官网
http://poi.apache.org/
官网可以找到文档和每个版本的下载地址
三、POI向Excel中写操作的演示
1.excel一般分为两种版本
(1)03版本:xls
(2)07版本:xlsx
两种版本的excel的区别:03版本excel最大存储行数为65536,07版本excel理论上没有限制
2.excel操作术语
(1)整个excel文档称为 workbook
(2)在每个workbook里面有很多 sheet
(3)在每个sheet里面有很多 row
(4)在每个row里面有很多 cell
(5)在每个cell里面有很多 内容
3.poi向03版本excel写内容
3.1 创建一个maven工程并引入poi相关依赖
<dependencies>
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
3.2 testWrite03Excel
- HSSF - 提供读写Microsoft Excel格式档案的功能。(.xls)
@Test
public void testWrite03Excel() throws Exception{
// 1.创建新的Excel工作簿(workbook)
// 1.1 03版本的Excel需要HSSFWorkbook对象
Workbook workbook = new HSSFWorkbook();
// 2.使用workbook创建sheet
// 2.1在Excel工作簿中建一工作表(sheet),其名为缺省值 Sheet0
//Sheet sheet = workbook.createSheet();
// 2.2如要新建一名为"会员登录统计"的工作表,其语句为:
Sheet sheet = workbook.createSheet("会员登录统计");
// 3.使用sheet创建行(row 1)
// 3.1行索引从0开始
Row row1 = sheet.createRow(0);
// 4.使用row创建单元格(cell 1-1)
Cell cell11 = row1.createCell(0);
// 5.通过cell向其中设置内容
cell11.setCellValue("今日人数");
// 使用row创建单元格(col 1-2)
Cell cell12 = row1.createCell(1);
// 5.通过cell向其中设置内容
cell12.setCellValue(666);
// 可使用sheet创建多行(row 2)
Row row2 = sheet.createRow(1);
// 使用row创建单元格(col 2-1)
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
//创建单元格(第三列)
Cell cell22 = row2.createCell(1);
String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(dateTime);
// 6.新建一输出文件流(注意:要先创建文件夹)
FileOutputStream out = new FileOutputStream("F:/PoiTest/01.xls");
// 7.把相应的Excel 工作簿存盘
workbook.write(out);
// 8.操作结束,关闭文件
out.close();
System.out.println("文件生成成功");
}
测试结果:
4.poi向07版本excel写内容
4.1 testWrite07Excel
- XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。(.xlsx)
@Test
public void testWrite07Excel() throws Exception{
// 1.创建新的Excel工作簿(workbook)
// 1.1 07版本的Excel需要XSSFWorkbook对象
Workbook workbook = new XSSFWorkbook();
// 2.使用workbook创建sheet
// 2.1在Excel工作簿中建一工作表(sheet),其名为缺省值 Sheet0
//Sheet sheet = workbook.createSheet();
// 2.2如要新建一名为"会员登录统计"的工作表,其语句为:
Sheet sheet = workbook.createSheet("会员登录统计");
// 3.使用sheet创建行(row 1)
// 3.1行索引从0开始
Row row1 = sheet.createRow(0);
// 4.使用row创建单元格(cell 1-1)
Cell cell11 = row1.createCell(0);
// 5.通过cell向其中设置内容
cell11.setCellValue("今日人数");
// 使用row创建单元格(col 1-2)
Cell cell12 = row1.createCell(1);
// 5.通过cell向其中设置内容
cell12.setCellValue(666);
// 可使用sheet创建多行(row 2)
Row row2 = sheet.createRow(1);
// 使用row创建单元格(col 2-1)
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
//创建单元格(第三列)
Cell cell22 = row2.createCell(1);
String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(dateTime);
// 6.新建一输出文件流(注意:要先创建文件夹)
FileOutputStream out = new FileOutputStream("F:/PoiTest/01.xlsx");
// 7.把相应的Excel 工作簿存盘
workbook.write(out);
// 8.操作结束,关闭文件
out.close();
System.out.println("文件生成成功");
}
测试结果:
四、POI大文件写操作-HSSF、XSSF和SXSSF
1.使用HSSF(03版本excel)
缺点:最多只能处理65536行,否则会抛出异常
java.lang.IllegalArgumentException: Invalid row number (65536) outside
allowable range (0…65535)优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快
@Test
public void testWrite03BigData() throws IOException {
//记录开始时间
long begin = System.currentTimeMillis();
//创建一个SXSSFWorkbook
//-1:关闭 auto-flushing,将所有数据存在内存中
Workbook workbook = new HSSFWorkbook();
//创建一个sheet
Sheet sheet = workbook.createSheet();
//xls文件最大支持65536行
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("done");
FileOutputStream out = new FileOutputStream("F:/PoiTest/01.xls");
workbook.write(out);
// 操作结束,关闭文件
out.close();
//记录结束时间
long end = System.currentTimeMillis();
System.out.println((double)(end - begin)/1000);
}
2.使用XSSF(07版本excel)
缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条
优点:可以写较大的数据量,如20万条
@Test
public void testWrite07BigData() throws IOException {
//记录开始时间
long begin = System.currentTimeMillis();
//创建一个XSSFWorkbook
Workbook workbook = new XSSFWorkbook();
......
FileOutputStream out = new FileOutputStream("F:/PoiTest/01.xlsx");
......
}
3.使用SXSSF
优点:可以写非常大的数据量,如100万条甚至更多条,写数据速度快,占用更少的内存
注意:
过程中会产生临时文件,需要清理临时文件(C:\Users\helen\AppData\Local\Temp)
默认由100条记录被保存在内存中,如果查过这数量,则最前面的数据被写入临时文件
如果想自定义内存中数据的数量,可以使用new SXSSFWorkbook(数量)
@Test
public void testWrite07BigDataFast() throws IOException {
//记录开始时间
long begin = System.currentTimeMillis();
//创建一个SXSSFWorkbook
Workbook workbook = new SXSSFWorkbook();
......
FileOutputStream out = new FileOutputStream("F:/PoiTest/01.xlsx");
workbook.write(out);
// 操作结束,关闭文件
out.close();
//清除临时文件
((SXSSFWorkbook)workbook).dispose();
//记录结束时间
long end = System.currentTimeMillis();
System.out.println((double)(end - begin)/1000);
}
SXSSFWorkbook-来至官方的解释:实现“BigGridDemo”策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存,因为任何时候只有可配置的行部分被保存在内存中。
请注意,仍然可能会消耗大量内存,这些内存基于您正在使用的功能,例如合并区域,注释…仍然只存储在内存中,因此如果广泛使用,可能需要大量内存。
五、POI向Excel读操作的演示
测试数据:
1.poi读03版本excel
@Test
public void testRead03() throws Exception{
// 1.获取操作文件输入流
InputStream in = new FileInputStream("F:/PoiTest/01.xls");
// 2.创建workbook
Workbook workbook = new HSSFWorkbook(in);
// 3.通过workbook获取工作表sheet
Sheet sheet = workbook.getSheetAt(0);
// 4.通过sheet获取第一行row
Row row = sheet.getRow(0);
// 5.通过row获取第一列cell1、第二列cell2、第三列cell3
Cell cell1 = row.getCell(0);
Cell cell2 = row.getCell(1);
Cell cell3 = row.getCell(2);
// 6.通过cell获取三列的内容
String stringCellValue1 = cell1.getStringCellValue();
String stringCellValue2 = cell2.getStringCellValue();
String stringCellValue3 = cell3.getStringCellValue();
// 输出单元内容
System.out.println(stringCellValue1);
System.out.println(stringCellValue2);
System.out.println(stringCellValue3);
// 7.操作结束,关闭文件
in.close();
}
2.poi读07版本excel
@Test
public void testRead03() throws Exception{
// 1.获取操作文件输入流
InputStream in = new FileInputStream("F:/PoiTest/01.xlsx");
// 2.创建workbook
Workbook workbook = new XSSFWorkbook(in);
// 3.通过workbook获取工作表sheet
Sheet sheet = workbook.getSheetAt(0);
// 4.通过sheet获取第一行row
Row row = sheet.getRow(0);
// 5.通过row获取第一列cell1、第二列cell2、第三列cell3
Cell cell1 = row.getCell(0);
Cell cell2 = row.getCell(1);
Cell cell3 = row.getCell(2);
// 6.通过cell获取三列的内容
String stringCellValue1 = cell1.getStringCellValue();
String stringCellValue2 = cell2.getStringCellValue();
String stringCellValue3 = cell3.getStringCellValue();
// 输出单元内容
System.out.println(stringCellValue1);
System.out.println(stringCellValue2);
System.out.println(stringCellValue3);
// 7.操作结束,关闭文件
in.close();
}
六、poi读取不同类型的数据
测试数据:
@Test
public void testCellType() throws Exception {
InputStream in = new FileInputStream("F:/PoiTest/01.xls");
Workbook workbook = new HSSFWorkbook(in);
Sheet sheet = workbook.getSheetAt(0);
// 读取标题所有内容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {// 行不为空
// 读取cell
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.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) {// 行不为空
// 读取cell
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("【" + (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.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK://空
System.out.print("【BLANK】");
break;
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.print("【NUMERIC】");
//cellValue = String.valueOf(cell.getNumericCellValue());
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 = cell.toString();
}
break;
case Cell.CELL_TYPE_ERROR:
System.out.print("【数据类型错误】");
break;
}
System.out.println(cellValue);
}
}
}
}
in.close();
}
测试结果: