Apache POI 与 Alibaba EasyExcel 的使用
- Apache POI
<dependencies><!-- xls(03) --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><!-- xls(07) --><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><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version></dependency></dependencies>
- 测试写(03版)
String Path="D:\\data\\";@TestpublicvoidtestWrite03()throws Exception{// 创建工作簿对象 03
Workbook workbook=newHSSFWorkbook();// 创建一个工作表
Sheet sheet0= workbook.createSheet("BLU测试表");// 创建一行
Row row0= sheet0.createRow(0);// 创建一个单元格
Cell cell00= row0.createCell(0);
cell00.setCellValue("编号");
Cell cell01= row0.createCell(1);
cell01.setCellValue("姓名");
Row row1= sheet0.createRow(1);
Cell cell10= row1.createCell(0);
cell10.setCellValue(1);
Cell cell11= row1.createCell(1);
cell11.setCellValue("BLU");
Cell cell12= row1.createCell(2);
String time=newDateTime().toString("yyyy-MM-dd HH:mm:ss");
cell12.setCellValue(time);
FileOutputStream fos=newFileOutputStream(Path+"test03.xls");
workbook.write(fos);
fos.close();
System.out.println("文件生成完毕");}
- 测试写(07版)
String Path="D:\\data\\";@TestpublicvoidtestWrite07()throws Exception{
Workbook workbook=newXSSFWorkbook();
Sheet sheet0= workbook.createSheet("BLU测试表");
Row row0= sheet0.createRow(0);
Cell cell00= row0.createCell(0);
cell00.setCellValue("编号");
Cell cell01= row0.createCell(1);
cell01.setCellValue("姓名");
Row row1= sheet0.createRow(1);
Cell cell10= row1.createCell(0);
cell10.setCellValue(1);
Cell cell11= row1.createCell(1);
cell11.setCellValue("BLU");
Cell cell12= row1.createCell(2);
String time=newDateTime().toString("yyyy-MM-dd HH:mm:ss");
cell12.setCellValue(time);
FileOutputStream fos=newFileOutputStream(Path+"test07.xlsx");
workbook.write(fos);
fos.close();
System.out.println("文件生成完毕");}
- 测试大数据写入(03版)
String Path="D:\\data\\";/**
* 65536行数据耗时:2.815s
* 最多只能创建65536行
*/@TestpublicvoidtestWrite03BigData()throws Exception{long begin= System.currentTimeMillis();
HSSFWorkbook workbook=newHSSFWorkbook();
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 fos=newFileOutputStream(Path+"testWrite03BigData.xls");
workbook.write(fos);
fos.close();long end= System.currentTimeMillis();
System.out.println((double)(end- begin)/1000);}
- 测试大数据写入(07版)
String Path="D:\\data\\";/**
* 65536行数据耗时6.843s
* 10万行数据耗时13.028s
* 内存占用大
*/@TestpublicvoidtestWrite07BigData()throws Exception{long begin= System.currentTimeMillis();
XSSFWorkbook workbook=newXSSFWorkbook();
Sheet sheet= workbook.createSheet();for(int rowNum=0; rowNum<100000; 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 fos=newFileOutputStream(Path+"testWrite07BigData.xlsx");
workbook.write(fos);
fos.close();long end= System.currentTimeMillis();
System.out.println((double)(end- begin)/1000);}
- 使用SXSSFWorkbook 测试大数据写入
String Path="D:\\data\\";/**
* 10万行数据耗时1.916s
*/@TestpublicvoidtestWrite07BigDataS()throws Exception{long begin= System.currentTimeMillis();
SXSSFWorkbook workbook=newSXSSFWorkbook();
Sheet sheet= workbook.createSheet();for(int rowNum=0; rowNum<100000; 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 fos=newFileOutputStream(Path+"testWrite07BigDataS.xlsx");
workbook.write(fos);
fos.close();// 清除临时文件
workbook.dispose();long end= System.currentTimeMillis();
System.out.println((double)(end- begin)/1000);}
- 测试读(03版)
String Path="D:\\data\\";@TestpublicvoidtestRead03()throws Exception{
FileInputStream fis=newFileInputStream(Path+"test03.xls");
Workbook workbook=newHSSFWorkbook(fis);
Sheet sheet= workbook.getSheetAt(0);
Row row= sheet.getRow(1);
Cell cell0= row.getCell(0);double value0= cell0.getNumericCellValue();
System.out.println(value0);
Cell cell1= row.getCell(1);
String value1= cell1.getStringCellValue();
System.out.println(value1);
fis.close();}
1.0
BLU
- 测试读(07版)
String Path="D:\\data\\";@TestpublicvoidtestRead07()throws Exception{
FileInputStream fis=newFileInputStream(Path+"test07.xlsx");
Workbook workbook=newXSSFWorkbook(fis);
Sheet sheet= workbook.getSheetAt(0);
Row row= sheet.getRow(1);
Cell cell0= row.getCell(0);double value0= cell0.getNumericCellValue();
System.out.println(value0);
Cell cell1= row.getCell(1);
String value1= cell1.getStringCellValue();
System.out.println(value1);
fis.close();}
1.0
BLU
- 根据数据类型读取的示例:
BLU.xls文件:
String Path="D:\\data\\";@TestpublicvoidtestCellType()throws Exception{
FileInputStream fis=newFileInputStream(Path+"BLU.xls");
Workbook workbook=newHSSFWorkbook(fis);
Sheet sheet= workbook.getSheetAt(0);
Row rowTitle= sheet.getRow(0);if(rowTitle!= null){int cellCount= rowTitle.getPhysicalNumberOfCells();for(int cellNum=0; cellNum< cellCount; cellNum++){
Cell cell= rowTitle.getCell(cellNum);if(cell!= null){
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= rowData.getPhysicalNumberOfCells();for(int cellNum=0; cellNum< cellCount; cellNum++){
Cell cell= rowData.getCell(cellNum);if(cell!= null){int cellType= cell.getCellType();switch(cellType){case HSSFCell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue());break;case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.print(String.valueOf(cell.getBooleanCellValue()));break;case HSSFCell.CELL_TYPE_BLANK:break;case HSSFCell.CELL_TYPE_NUMERIC:if(HSSFDateUtil.isCellDateFormatted(cell)){
Date date= cell.getDateCellValue();
String s=newDateTime(date).toString("yyyy-MM-dd");
System.out.print(s);}else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
System.out.print(cell.toString());}break;case HSSFCell.CELL_TYPE_ERROR:break;}
System.out.print(" | ");}}
System.out.println();}}
fis.close();}
手机号 | 日期 | 订单号 | 商品编号 | 商品名称 | 价格 | 销售数量 | 销售金额 | 已发货 |
15651776666 | 2020-09-30 | 000001 | 1 | 蒙牛 | 65.5 | 1 | 65.5 | true |
15651776666 | 2020-10-01 | 000002 | 2 | 脑白金 | 100 | 10 | 1000 | false |
- 读取计算函数的示例:
func.xls文件:
String Path="D:\\data\\";@TestpublicvoidtestFormula()throws Exception{
FileInputStream fis=newFileInputStream(Path+"func.xls");
Workbook workbook=newHSSFWorkbook(fis);
Sheet sheet= workbook.getSheetAt(0);
Row row= sheet.getRow(4);
Cell cell= row.getCell(0);
FormulaEvaluator formulaEvaluator=newHSSFFormulaEvaluator((HSSFWorkbook) workbook);int cellType= cell.getCellType();switch(cellType){case Cell.CELL_TYPE_FORMULA:
String formula= cell.getCellFormula();
System.out.println(formula);
CellValue evaluate= formulaEvaluator.evaluate(cell);
String cellValue= evaluate.formatAsString();
System.out.println(cellValue);break;}}
SUM(A2:A4)
600.0
- Alibaba EasyExcel
<dependencies><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.0-beta2</version></dependency><!-- 日期格式化工具 --><dependency><groupId>joda-time</groupId><artifactId>joda-time</artifactId><version>2.10.1</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version