POI和EasyPoi
POI官网🏷https://poi.apache.org/
easyPoi地址: https://github.com/alibaba/easyexcel
使用场景
- 将用户信息导出为Excel表格
- 将Excel数据进行导入数据库
操作Excel目前比较流行的就是Apache POI 和 阿里巴巴的 easyExcel
EasyExcel 是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称
EasyExcel能够大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行一行读取数据,逐个解析。
POI操作
注意:2003版本和2007版本存在兼容性问题,03最多65535行!操作对象不同,操作方法相同
导入依赖
<!--POI相关依赖-->
<!--03版本-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!--07版本-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.10</version>
</dependency>
一个Excel存在 工作簿(workbook)、工作表(Sheet)、行(Row)、列(Cell)四个基本内容,使用api操作时也是通过这几个对象来操作的、
03版本写入Excel实例:
//创建工作薄
Workbook workbook = new HSSFWorkbook();
//创建工作表
Sheet sheet = workbook.createSheet("小王的搬家记录");
//创建行
Row row = sheet.createRow(0);
//创建单元格(1,1)
Cell cell1 = row.createCell(0);
cell1.setCellValue("搬家第一天");
//创建单元格(1,2)
Cell cell2 = row.createCell(1);
cell2.setCellValue("搬家第二天");
//创建单元格(1,3)
Cell cell3 = row.createCell(2);
cell3.setCellValue("搬家第三天");
//创建行 (2,)
Row row2 = sheet.createRow(1);
//创建单元格(2,1)
Cell cell2_1 = row2.createCell(0);
cell2_1.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
//输出文件
FileOutputStream fileOutputStream = new FileOutputStream("D:\\Desktop\\搬家日记.xls");
//输出
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("文件生成成功!");
07版本写入实例
//创建工作薄
Workbook workbook = new XSSFWorkbook();
//创建工作表
Sheet sheet = workbook.createSheet("小王的搬家记录");
//创建行
Row row = sheet.createRow(0);
//创建单元格(1,1)
Cell cell1 = row.createCell(0);
cell1.setCellValue("搬家第一天");
//创建单元格(1,2)
Cell cell2 = row.createCell(1);
cell2.setCellValue("搬家第二天");
//创建单元格(1,3)
Cell cell3 = row.createCell(2);
cell3.setCellValue("搬家第三天");
//创建行 (2,)
Row row2 = sheet.createRow(1);
//创建单元格(2,1)
Cell cell2_1 = row2.createCell(0);
cell2_1.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
//输出文件
FileOutputStream fileOutputStream = new FileOutputStream("D:\\Desktop\\搬家日记.xlsx");
//输出
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("文件生成成功!");
**区别:**注意对象区别 和 文件后缀
写HSSF
缺点:最多只能处理65536行数据,否则会抛出异常
优点:过程中写入缓存,不操作磁盘,最后一次写入磁盘,速度快
Workbook workbook = new HSSFWorkbook();
long start = System.currentTimeMillis();
//创建工作表
Sheet sheet = workbook.createSheet();
//循环交叉设置数据
for(int i=0; i<65536;i++){
Row row = sheet.createRow(i);
for(int j=0;j<10;j++){
Cell cell = row.createCell(j);
cell.setCellValue("测试数据");
}
}
FileOutputStream fileOutputStream = new FileOutputStream("D:\\Desktop\\测试03.xls");
workbook.write(fileOutputStream);
long end = System.currentTimeMillis();
System.out.println("耗时:"+(double) (end-start)/1000);
fileOutputStream.close();
写XSSF
缺点:写入数据非常慢,非常消耗内存,也会发生内存溢出
优点:可以写较大的数据量
Workbook workbook = new XSSFWorkbook();
long start = System.currentTimeMillis();
//创建工作表
Sheet sheet = workbook.createSheet();
//循环交叉设置数据
for(int i=0; i<65536;i++){
Row row = sheet.createRow(i);
for(int j=0;j<10;j++){
Cell cell = row.createCell(j);
cell.setCellValue("测试数据");
}
}
FileOutputStream fileOutputStream = new FileOutputStream("D:\\Desktop\\测试07.xlsx");
workbook.write(fileOutputStream);
long end = System.currentTimeMillis();
System.out.println("耗时:"+(double) (end-start)/1000);
fileOutputStream.close();
写SXSSF
优点:可以写非常大的数据量,如100万条甚至更多,写入速度快,占用内存少
//使用sxss速度更快 07版本
SXSSFWorkbook workbook = new SXSSFWorkbook();
long start = System.currentTimeMillis();
//创建工作表
Sheet sheet = workbook.createSheet();
//循环交叉设置数据
for(int i=0; i<65536;i++){
Row row = sheet.createRow(i);
for(int j=0;j<10;j++){
Cell cell = row.createCell(j);
cell.setCellValue("测试数据");
}
}
FileOutputStream fileOutputStream = new FileOutputStream("D:\\Desktop\\测试07.xlsx");
workbook.write(fileOutputStream);
//清理临时文件
workbook.dispose();
long end = System.currentTimeMillis();
System.out.println("耗时:"+(double) (end-start)/1000);
fileOutputStream.close();
POI读Excel
03版本与07版本基本一致,操作对象不一致罢了 (HSSF、XSSF)
简单读取实例
FileInputStream fileInputStream = new FileInputStream("D:\\Desktop\\test.xlsx");
Workbook workbook = new XSSFWorkbook(fileInputStream);
//获取第一个工作表
Sheet sheet = workbook.getSheetAt(0);
//获取(1,1)单元格书
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
//注意数据类型
String stringCellValue = cell.getStringCellValue();
System.out.println(stringCellValue);
fileInputStream.close();
处理复杂数据(各种数据都有的情况下)
FileInputStream fileInputStream = new FileInputStream("D:\\Desktop\\test.xlsx");
Workbook workbook = new XSSFWorkbook(fileInputStream);
fileInputStream.close();
//第一个读取工作表
Sheet sheet = workbook.getSheetAt(0);
//获取行数
int rowNumber = sheet.getPhysicalNumberOfRows();
//遍历行数
for (int i = 0; i < rowNumber; i++) {
//获取当前行
Row row = sheet.getRow(i);
//获取当前行列数
int cellNumber = row.getPhysicalNumberOfCells();
for(int j = 0; j < cellNumber; j++){
//遍历当前行的第i个单元格
Cell cell = row.getCell(j);
//因为每次的表格数据类型都不一致,会导致程序出错。这里全部都转为String进行打印
String cellValue = "";
if(cell!=null){
//处理不同数据类型
switch (cell.getCellTypeEnum()){
case _NONE:
break;
case BLANK: //为空
break;
case ERROR: //数据类型错误
break;
case STRING: //字符串类型
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: //boolean类型
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: //计算公式
break;
case NUMERIC: //数字(包含日期、普通数字)
//区分数字和日期
if(HSSFDateUtil.isCellDateFormatted(cell)){
//处理日期 需要转换格式的话
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue = sdf.format(cell.getDateCellValue());
}else{
//数字 避免过长 转换为string类型
cell.setCellType(CellType.STRING);
cellValue = cell.getStringCellValue();
}
break;
}
}
//打印数据
System.out.print(cellValue+"|");
}
System.out.println("");
}
结果:
计算公式
FileInputStream fileInputStream = new FileInputStream("D:\\Desktop\\test.xlsx");
Workbook workbook = new XSSFWorkbook(fileInputStream);
fileInputStream.close();
Sheet sheet = workbook.getSheetAt(0);
// 第一行,第七个是计算公式类型数据
Row row = sheet.getRow(1);
Cell cell = row.getCell(6);
//拿到计算公式
XSSFFormulaEvaluator FormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
//打印公式 因为类型是计算公式所以也需要这样取值,但是无法取到计算后的值 需要使用FormulaEvaluator
System.out.println("默认取到的值:"+cell.getCellFormula());
//获取计算结果 也就是表格中计算后的值
CellValue evaluate = FormulaEvaluator.evaluate(cell);
//转换为string进行打印 默认是打印CellValue对象
System.out.println("计算后的值:"+evaluate.formatAsString());
结果: