Apache POI与Alibaba EasyExcel 的使用

2022-07-19 14:06:57

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
  • 作者:BLUcoding
  • 原文链接:https://blucoding.blog.csdn.net/article/details/108882419
    更新时间:2022-07-19 14:06:57