使用POI对Excel进行读、写操作
Apache POI官网：https://poi.apache.org/
什么是POI？
基本功能？
- POI-Excel 写
主要步骤：①创建工作簿; ②创建工作表; ③创建行; ④创建列
(1) 在项目中引入pom依赖
|
<!--xls(03)-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx(07)-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--日期格式化工具-->
<!-- https://mvnrepository.com/artifact/joda-time/joda-time -->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
|
03版本：
|
//构建路径
String PATH="E:\workpaceid\Excel_test\poi_test";
/*测试Excel03版本*/
@Test
public void testWrite03() throws Exception {
//1.创建一个工作簿
Workbook workbook = new HSSFWorkbook();
//2.创建一个工作表
Sheet sheet=workbook.createSheet("信息表");
//3.创建一个行
Row row1=sheet.createRow(0);
//4.创建一个单元格 (构成了 A,1 这个单元格)
Cell cellA1 = row1.createCell(0);
//5.向单元格中填写数据
cellA1.setCellValue("姓名");
//(构成 A,2 坐标)
Cell cellB1 = row1.createCell(1);
cellB1.setCellValue("时间");
//创建第二行 并传入数据
Row row2 =sheet.createRow(1);
Cell cellA2 = row2.createCell(0);
cellA2.setCellValue("张三");
Cell cellB2 = row2.createCell(1);
String time= new DateTime().toString("yyyy-MM-DD");
cellB2.setCellValue(time);
//6.生成一张表（IO 流） 03版本后缀名使用xls
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "信息表03.xls");
//通过流输出输出
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("信息表03生成完毕！");
}
|
07版本（生成方法和03版本一样 ）
区别在于 对象的不同 文件后缀名的不同
|
/*测试Excel07版本*/
@Test
public void testWrite07() throws Exception {
//1.创建一个工作簿 使用excel操作的 workBook都能操作
Workbook workbook = new XSSFWorkbook();
//2.创建一个工作表 表中的设置
Sheet sheet=workbook.createSheet("信息表");
//3.创建一个行
Row row1=sheet.createRow(0);
//4.创建一个单元格 (构成了 A,1 这个单元格)
Cell cellA1 = row1.createCell(0);
//5.向单元格中填写数据
cellA1.setCellValue("姓名");
//(构成 A,2 坐标)
Cell cellB1 = row1.createCell(1);
cellB1.setCellValue("时间");
//创建第二行 并传入数据
Row row2 =sheet.createRow(1);
Cell cellA2 = row2.createCell(0);
cellA2.setCellValue("李四");
Cell cellB2 = row2.createCell(1);
String time= new DateTime().toString("yyyy-MM-DD");
cellB2.setCellValue(time);
//6.生成一张表（IO 流） 03版本后缀名使用xls
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "信息表07.xlsx");
//通过流输出输出
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("信息表07生成完毕！");
}
|
大文件写HSSF（03版）
优点：过程中写入缓存，不操作磁盘，最后一次写入磁盘，速度快
缺点：最多下入65536行，否则会抛出异常
|
/**
*测试03版本大数据写入
* 优点：过程中写入缓存，不操作磁盘，最后一次写入磁盘，速度快
* 缺点：最多写入65536行，否则抛出异常
*/
@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++){ //RowNum 行数
Row row = sheet.createRow(RowNum); //每一行
for ( int CellNum=0;CellNum<10;CellNum++){
Cell cell = row.createCell(CellNum); //每一个单元格
cell.setCellValue(CellNum+1);
}
}
System.out.println("over!");
//生成一张表
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigData03.xls");
//输出
workbook.write(fileOutputStream);
//关流
fileOutputStream.close();
//结束时间
long end=System.currentTimeMillis();
System.out.println((double)(end-begin)/1000);
}
|
大文件写XSSF（07版）
优点：可以写入大量的数据
缺点：写数据的速度慢，消耗内存，如超过100万条数据，也会发生内存溢出现象
|
/**
*测试07版本大数据写入
* 优点：可以写入较大的数据量
* 缺点：写数据的速度慢，消耗内存，如超过100万条数据，也会发生内存溢出现象
*/
@Test
public void testWrite07BigData() throws Exception {
//计算时间 开始时间
long begin=System.currentTimeMillis();
//创建工作薄
Workbook workbook=new XSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写入数据
for (int RowNum=0;RowNum<65537;RowNum++){ //RowNum 行数
Row row = sheet.createRow(RowNum); //每一行
for ( int CellNum=0;CellNum<10;CellNum++){
Cell cell = row.createCell(CellNum); //每一个单元格
cell.setCellValue(CellNum+1);
}
}
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);
}
|
大文件写SXSSF
优点：可以写入非常大的数据，如100万条甚至更多，写数据速度快，占用更少的内存
注意：过程中会产生临时文件，需要清理临时文件；
默认有100条数据会被存入内存中，如果超过这个数量，则最前面的数据会被写入临时文件；
自定义内存中的数量，可以使用new SXSSFWorkBook（数量）。
|
/**
*测试大文件写SXSSF
*优点：可以写入非常大的数据量，如100万条甚至更多，写数据数据快，占用更少内存
* 注意：过程中会产生临时文件，需要清理临时文件
* 默认有100条数据被存入内存中，如果超过这个数量，则最前面的数据被写入临时文件
* 自定义内存中的数量，可以使用new SXXFWorkBook（数量）
*/
@Test
public void testWrite07BigDatas() throws Exception {
//计算时间 开始时间
long begin=System.currentTimeMillis();
//创建工作薄
Workbook workbook=new SXSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写入数据
for (int RowNum=0;RowNum<65535;RowNum++){ //RowNum 行数
Row row = sheet.createRow(RowNum); //每一行
for ( int CellNum=0;CellNum<10;CellNum++){
Cell cell = row.createCell(CellNum); //每一个单元格
cell.setCellValue(CellNum+1);
}
}
System.out.println("over!");
//生成一张表
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigData07S.xlsx");
//输出
workbook.write(fileOutputStream);
//关流
fileOutputStream.close();
//清除临时文件
((SXSSFWorkbook)workbook).dispose();
//结束时间
long end=System.currentTimeMillis();
System.out.println((double)(end-begin)/1000);
}
|
- POI-Excel 读
03版本读
在读取Excel中的数据是需要注意获取值的类型
|
/*测试03版本读取*/
@Test
public void TestRead03() throws Exception {
//1.获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH+"poi_test信息表03.xls");
//2.读取工作簿中的内容
Workbook workbook=new HSSFWorkbook(fileInputStream);
//3.获取工作中的表
Sheet sheetAt = workbook.getSheetAt(0); //根据下标获取 也可根据表名查找
//4.获取表中的行
Row row1 = sheetAt.getRow(0);
//5.获取单元格
Cell cellA1 = row1.getCell(0);
//6.取出单元格中的值
// 读取值的时候需要注意表中数据的类型
System.out.println(cellA1.getStringCellValue()); //getStringCellValue() 获取的是制字符串类型
//关闭流
fileInputStream.close();
}
|
07版本读
|
/*测试07版本读取*/
@Test
public void TestRead07() throws Exception {
//1.获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH+"poi_test信息表07.xlsx");
//2.读取工作簿中的内容
Workbook workbook=new XSSFWorkbook(fileInputStream);
//3.获取工作簿中的表
Sheet sheetAt = workbook.getSheetAt(0);
//4.获取表中的行
Row row2 = sheetAt.getRow(1);
//5.获取单元格
Cell cellB2 = row2.getCell(1);
//6.取出单元格中的值
System.out.println(cellB2.getStringCellValue()); //getStringCellValue() 获取的是制字符串类型
//关闭流
fileInputStream.close();
}
|
获取Excel中不同类型的值
|
/*读取不同类型的数据*/
@Test
public void testCellType() throws Exception {
//1.获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH+"明细表.xls");
//2.读取工作簿中的内容
Workbook workbook=new HSSFWorkbook(fileInputStream);
//3.获取工作簿中的表
Sheet sheetAt = workbook.getSheetAt(0);
//4.获取标题内容
Row rowTitle = sheetAt.getRow(0); //第一行 标题
// 判断标题内容是否为空
if(rowTitle!=null){
//得到第一行标题的个数 （获取所有列数）
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();
}
//5.获取表中的内容
//获取所有行数
int rowCount= sheetAt.getPhysicalNumberOfRows();
for(int rowNum=1;rowNum<rowCount;rowNum++){
//获取行
Row rowData = sheetAt.getRow(rowNum);
//判断获取到每一行是否为空
if(rowData!=null){
//读取列
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 cellVaule="";
switch (cellType){
case HSSFCell.CELL_TYPE_STRING: //为字符串类型
System.out.print("[String]");
//获取值
cellVaule = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: //为布尔类型
System.out.print("[Boolean]");
//获取值
cellVaule = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK: //为空
System.out.print("[Blank]");
break;
case HSSFCell.CELL_TYPE_NUMERIC: //为数字 （日期和普通数字）
System.out.print("[NUMERIC]");
//再次判断数字为日期还是普通数字
if(HSSFDateUtil.isCellDateFormatted(cell)){ //日期
System.out.print("[日期]");
Date date = cell.getDateCellValue();
cellVaule = new DateTime(date).toString("yyyy-MM-dd");
}else{ //为普通数字
//如果不是日期，防止数字过长(科学计数法)，转换成字符串输出
System.out.print("[数字]");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellVaule=cell.toString();
}
break;
case HSSFCell.CELL_TYPE_ERROR: //数据类型错误
System.out.print("[数据类型错误]");
break;
}
System.out.println(cellVaule);
}
}
}
}
//关流
fileInputStream.close();
}
|
- POI-Excel 计算公式
|
public class TestFormula {
//路径变量
String PATH="E:\workpaceid\Excel_test\poi_test\";
/*测试计算公式*/
@Test
public void testFormula() throws Exception {
//1.创建流
FileInputStream fileInputStream = new FileInputStream(PATH+"计算公式.xls");
//2.获取工作簿
Workbook Workbook = new HSSFWorkbook(fileInputStream);
//3.获取表
Sheet sheetAt = Workbook.getSheetAt(0);
//4.获取计算公式的单元格
Row row = sheetAt.getRow(5);
Cell cell = row.getCell(0);
//5.拿到计算公式eval
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)Workbook);
//6.输出单元格的公式
//获取类型
int cellType = cell.getCellType();
//判断
switch (cellType){
case Cell.CELL_TYPE_FORMULA: //公式
String cellFormula = cell.getCellFormula();
//进行计算
CellValue evaluate = formulaEvaluator.evaluate(cell);
//得到计算结果的值
String value = evaluate.formatAsString();
System.out.println(value);
break;
}
}
}
|