Apache POI:解决数据库和Excel之间相互转换的烦恼~

目录引言一、简介二、POI-Excel 写1、创建项目2、引入依赖3、步骤1. 创建工作簿2. 创建工作表3. 创建行4. 创建单元格5. 单元格中填入数据6. 通过IO流生成表03版本完整代码07版本完整代码大文件写 HSSF(03)大文件写 XSSF(07)大文件写 SXSSF(07)三、POI-Excel 读03版本07版本不同的数据类型的读取含计算公式的单元格读取

引言

开发中经常会设计到 Excel 的处理,如导出 Excel,导入Excel 到数据库中!

关于表格导入导出第三方组件工具,市面上比较知名的有两个:

Apache 的 POI
阿里巴巴 的 EasyExcel

今天我们来了解了解 Apache POI

一、简介

官网: http://poi.apache.org/

Apache POI:解决数据库和Excel之间相互转换的烦恼~

结构:

HSSF - 提供读写Microsoft Excel格式档案的功能。excel 2003 版本
XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。excel 2007 版本
HWPF - 提供读写Microsoft Word格式档案的功能。
HSLF - 提供读写Microsoft PowerPoint格式档案的功能。
HDGF - 提供读写Microsoft Visio格式档案的功能。

二、POI-Excel 写
1、创建项目

创建一个空项目和一个普通Maven的Module
Apache POI:解决数据库和Excel之间相互转换的烦恼~

2、引入依赖

Excel有两种不同的版本,需要导入两种不同的依赖

Excel 2003 版本的后缀名为.xls
Excel 2007 版本的后缀名为.xlsx

<dependencies>
<!-- xls03 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>

<!-- xlsx07 -->
<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>

<!--junit-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>

3、步骤

Java万物都是对象,所以用Java操作Excel,就是操作其中一个一个的对象

Excel表中的对象

工作簿
工作表

单元格

Apache POI:解决数据库和Excel之间相互转换的烦恼~

我们首先编写03版本的代码:

注:

03 | 07 版本的写操作,就是对象不一样,方法都是一样的。

2003 版本和 2007 版本存在兼容性问题, 03 版本最多只有 65535 行。

1. 创建工作簿

首先要创建一个工作簿Workbook,他有三个实现类
Apache POI:解决数据库和Excel之间相互转换的烦恼~

HSSFWorkbook:操作03版本的Excel
SXSSFWorkbookXSSFWorkbook的升级版,加速处理
XSSFWorkbook:操作07版本的Excel

操作07版本的Excel时,我们一般使用SXSSFWorkbook

//创建一个03版本的工作簿
Workbook workbook = new HSSFWorkbook();

2. 创建工作表

再通过工作簿创建工作表

//创建一个工作表
Sheet sheet=workbook.createSheet("POI写操作");

3. 创建行

再通过工作表创建行

//创建行
Row row1 = sheet.createRow(0);//第一行
Row row2 = sheet.createRow(1);//第二行

4. 创建单元格

最后通过行创建单元格

//创建单元格
Cell cell11 = row1.createCell(0);//(1,1)
Cell cell12 = row1.createCell(1);//(1,2)

5. 单元格中填入数据

//向单元格中写入数据
cell11.setCellValue("POI");
cell12.setCellValue("first");

6. 通过IO流生成表

//生成一张表(IO流)
String path = "D:\\学习\\IDEA project\\POI";
FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();

03版本完整代码

将上述代码汇总

@Test
public void testWrite03() throws Exception {
//创建一个03版本的工作簿
Workbook workbook = new HSSFWorkbook();
//创建一个工作表
Sheet sheet = workbook.createSheet("POI写操作");
//创建行
Row row1 = sheet.createRow(0);//第一行
Row row2 = sheet.createRow(1);//第二行
//创建单元格
Cell cell11 = row1.createCell(0);//(1,1)
Cell cell12 = row1.createCell(1);//(1,2)
//向单元格中写入数据
cell11.setCellValue("POI");
cell12.setCellValue("first");
//生成一张表(IO流)
String path = "D:\\学习\\IDEA project\\POI";
FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("03表生成完毕");
}

然后点击运行,可以看到左边新生成的.xls文件
Apache POI:解决数据库和Excel之间相互转换的烦恼~
我们打开查看结果,正确
Apache POI:解决数据库和Excel之间相互转换的烦恼~

07版本完整代码

同样,以此类推,我们写出07版本的代码

@Test
public void testWrite07() throws Exception {
//创建一个07版本的工作簿
Workbook workbook = new SXSSFWorkbook();
//创建一个工作表
Sheet sheet = workbook.createSheet("POI写操作2");
//创建行
Row row = sheet.createRow(0);
//创建单元格
Cell cell11 = row.createCell(0);
Cell cell12 = row.createCell(1);
//向单元格中写入数据
cell11.setCellValue("POI");
cell12.setCellValue("second");
//生成一张表(IO流)
String path = "D:/学习/IDEA project/POI/";
FileOutputStream fileOutputStream = new FileOutputStream(path + "07.xlsx");
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("07表生成完毕");
}


大文件写 HSSF(03)

**缺点:**最多只能处理 65536 行,否则会抛出异常

**优点:**过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快。

@Test
public void HSSFWriteBigData() throws Exception {
//起始时间
long begin = System.currentTimeMillis();
//创建工作簿
Workbook workbook = new HSSFWorkbook();
//创建工作表
Sheet sheet = workbook.createSheet("大数据写HSSF");
//写入数据
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);
}
}
//生成表
String path = "D:/学习/IDEA project/POI/";
FileOutputStream fileOutputStream = new FileOutputStream(path + "BigData03.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
//结束时间
long end = System.currentTimeMillis();
System.out.println(end - begin + "ms");
System.out.println("BigData03表生成完毕");
}

点击运行,整个过程运行时间为1.93s,速度很快
Apache POI:解决数据库和Excel之间相互转换的烦恼~
查看我们生成的表
Apache POI:解决数据库和Excel之间相互转换的烦恼~
如果我们超过了65536,则报错,提示范围为0~65535
Apache POI:解决数据库和Excel之间相互转换的烦恼~

大文件写 XSSF(07)

**缺点:**写数据时速度非常慢,非常耗内存,也会发生内存溢出,如 100 万条数据。

**优点:**可以写较大的数据量,如 20 万条。

@Test
public void XSSFWriteBigData() throws Exception {
//起始时间
long begin = System.currentTimeMillis();
//创建工作簿
Workbook workbook = new XSSFWorkbook();
//创建工作表
Sheet sheet = workbook.createSheet("大数据写XSSF");
//写入数据
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
//生成表
String path = "D:/学习/IDEA project/POI/";
FileOutputStream fileOutputStream = new FileOutputStream(path + "BigData07.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
//结束时间
long end = System.currentTimeMillis();
System.out.println(end - begin + "ms");
System.out.println("BigData07表生成完毕");
}

点击运行,整个过程报名时间为7s,耗时很慢
Apache POI:解决数据库和Excel之间相互转换的烦恼~

大文件写 SXSSF(07)

优点:

可以写非常大的数据量,如 100 万条甚至更多,写数据速度快,占用更少的内存。

注意:

过程总会产生临时文件,需要清理临时文件。
默认由 100 条记录被保存在内存中,则最前面的数据被写入临时文件。
如果想要自定义内存中数据的数量,可以使用 new SXSSFWorkbook (数量)。

SXSSFWorkbook 来自官方的解释:实现“BigGridDemo” 策略的流式 SXSSFWorkbook 版本。这允许写入非常大的文件而不会耗尽内存,因为任何时候只有可配置的行部分被保存在内存中。

请注意,仍然可能会消耗大量内存,这些内存基于你正在使用的功能,例如合并区域,注释。。。仍然只存储在内存中,因此如果广泛使用,可能需要大量内存。

@Test
public void SXSSFWriteBigData() throws Exception {
//起始时间
long begin = System.currentTimeMillis();
//创建工作簿
Workbook workbook = new SXSSFWorkbook();
//创建工作表
Sheet sheet = workbook.createSheet("大数据写XSSF");
//写入数据
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
//生成表
String path = "D:/学习/IDEA project/POI/";
FileOutputStream fileOutputStream = new FileOutputStream(path + "BigDataS07.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
//清除临时文件
((SXSSFWorkbook) workbook).dispose();
//结束时间
long end = System.currentTimeMillis();
System.out.println(end - begin + "ms");
System.out.println("BigDataS07表生成完毕");
}

点击运行,整个过程运行时间为2.10s,速度相比XSSF有了很大的提升
Apache POI:解决数据库和Excel之间相互转换的烦恼~

三、POI-Excel 读

实验表格会员消费商品明细表.xls
Apache POI:解决数据库和Excel之间相互转换的烦恼~

03版本

String path = "D:/学习/IDEA project/POI/会员消费商品明细表.xls";

@Test
public void testRead03() throws Exception {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path);
//创建工作簿,传入文件流
Workbook workbook = new HSSFWorkbook(fileInputStream);
//得到工作表
Sheet sheet = workbook.getSheetAt(0);
//得到行
Row row = sheet.getRow(0);
//得到列
Cell cell = row.getCell(0);
//注意获取值的类型,不同类型,需要调用不同的方法
System.out.println(cell.getStringCellValue());
//关闭流
fileInputStream.close();
}

运行结果:
Apache POI:解决数据库和Excel之间相互转换的烦恼~

07版本

实验表格会员消费商品明细表.xlsx
Apache POI:解决数据库和Excel之间相互转换的烦恼~

String path = "D:/学习/IDEA project/POI/会员消费商品明细表.xlsx";

@Test
public void testRead03() throws Exception {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path);
//创建工作簿,传入文件流
Workbook workbook = new XSSFWorkbook(fileInputStream);
//得到工作表
Sheet sheet = workbook.getSheetAt(0);
//得到行
Row row = sheet.getRow(0);
//得到列
Cell cell = row.getCell(0);
//注意获取值的类型,不同类型,需要调用不同的方法
System.out.println(cell.getStringCellValue());
//关闭流
fileInputStream.close();
}

运行结果:
Apache POI:解决数据库和Excel之间相互转换的烦恼~

不同的数据类型的读取

实验表格会员消费商品明细表.xls
Apache POI:解决数据库和Excel之间相互转换的烦恼~

String path = "D:/学习/IDEA project/POI/会员消费商品明细表.xls";

@Test
public void ReadDifferentType() throws Exception {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path);
//创建工作簿,传入文件流
Workbook workbook = new HSSFWorkbook(fileInputStream);
//得到工作表
Sheet sheet = workbook.getSheetAt(0);
//得到行
//遍历标题行(第一行)每个单元格,输出其内容
Row row = sheet.getRow(0);
if (row != null) {
int cellCount = row.getPhysicalNumberOfCells();//获取单元格数量
for (int cellNum = 0; cellNum < cellCount; cellNum++) {//遍历单元格
Cell cell = row.getCell(cellNum);
if (cell != null) {
int cellType = cell.getCellType();//获得单元格内容数据类型
String cellValue = cell.getStringCellValue();
System.out.print(" | " + cellValue);
}
}
}
//获取表中的内容
int numberOfRows = sheet.getPhysicalNumberOfRows();//获得总行数
for (int i = 1; i < numberOfRows; i++) {//从第二行开始遍历所有的行
Row row2 = sheet.getRow(i);
if (row2 != null) {
int numberOfCells = row2.getPhysicalNumberOfCells();//获取每行单元格数量
for (int j = 0; j < numberOfCells; j++) {
Cell cell = row2.getCell(j);
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = "";//单元格内容
switch (cellType) {
//字符串
case Cell.CELL_TYPE_STRING:
System.out.print("【STRING】");
cellValue = cell.getStringCellValue();
break;
//布尔
case Cell.CELL_TYPE_BOOLEAN:
System.out.print("【BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
//空
case Cell.CELL_TYPE_BLANK:
System.out.print("【BLANK】");
break;
//数字
case Cell.CELL_TYPE_NUMERIC:
System.out.print("【NUMERIC】");
if (DateUtil.isCellDateFormatted(cell)) {//日期
System.out.print("【日期】");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString();
} else {
// 不是日期格式,防止数字过长
System.out.print("【装换为字符串输出】");
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_ERROR:
System.out.print("【数据类型错误】");
break;
}
System.out.println(cellValue);
}
}
}
}
//关闭流
fileInputStream.close();
}

运行结果:
Apache POI:解决数据库和Excel之间相互转换的烦恼~

含计算公式的单元格读取

实验表格计算公式.xls
Apache POI:解决数据库和Excel之间相互转换的烦恼~

@Test
public void Formula() throws Exception {
String path = "D:/学习/IDEA project/POI/计算公式.xls";
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path);
//创建工作簿,传入文件流
Workbook workbook = new HSSFWorkbook(fileInputStream);
//得到工作表
Sheet sheet = workbook.getSheetAt(0);
//得到行(有公式的那一行)
Row row = sheet.getRow(4);
//得到计算公式
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
//获取单元格
Cell cell = row.getCell(0);
//通过公式计算输出单元格的内容
int cellType = cell.getCellType();
if (cellType == Cell.CELL_TYPE_FORMULA) {
String cellFormula = cell.getCellFormula();
System.out.println(cellFormula);
//计算并打印计算结果
CellValue value = formulaEvaluator.evaluate(cell);
String cellValue = value.formatAsString();
System.out.println(cellValue);
}
//关闭流
fileInputStream.close();
}

运行测试,结果与表格相同:
Apache POI:解决数据库和Excel之间相互转换的烦恼~

原创:https://www.panoramacn.com
源码网提供WordPress源码,帝国CMS源码discuz源码,微信小程序,小说源码,杰奇源码,thinkphp源码,ecshop模板源码,微擎模板源码,dede源码,织梦源码等。

专业搭建小说网站,小说程序,杰奇系列,微信小说系列,app系列小说

Apache POI:解决数据库和Excel之间相互转换的烦恼~

免责声明,若由于商用引起版权纠纷,一切责任均由使用者承担。

您必须遵守我们的协议,如果您下载了该资源行为将被视为对《免责声明》全部内容的认可-> 联系客服 投诉资源
www.panoramacn.com资源全部来自互联网收集,仅供用于学习和交流,请勿用于商业用途。如有侵权、不妥之处,请联系站长并出示版权证明以便删除。 敬请谅解! 侵权删帖/违法举报/投稿等事物联系邮箱:2640602276@qq.com
未经允许不得转载:书荒源码源码网每日更新网站源码模板! » Apache POI:解决数据库和Excel之间相互转换的烦恼~
关注我们小说电影免费看
关注我们,获取更多的全网素材资源,有趣有料!
120000+人已关注
分享到:
赞(0) 打赏

评论抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

您的打赏就是我分享的动力!

支付宝扫一扫打赏

微信扫一扫打赏