JAVA 操作Excel之三剑客：JXL、POI、EasyExcel（二）

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

练习一： HSSFWrokbook VS XSSFWrokbook 写入65535行数据性能测试

#  HSSFWrokbook VS XSSFWrokbook 存取65535行数据，需要的时间
# HSSFWrokbook
long begin = System.currentTimeMillis();
HSSFWorkbook workbook = new HSSFWorkbook();
// XSSFWorkbook workbook = new XSSFWorkbook();

HSSFSheet sheet = workbook.createSheet();
for (int i = 0; i < 65536; i++) {
	HSSFRow row = sheet.createRow(i);
	for (int j = 0; j < 10; j++) {
		HSSFCell cell = row.createCell(j);
		cell.setCellValue(j);
	}
}

FileOutputStream fos = new FileOutputStream("E:\\test\\test044.xlsx");
workbook.write(fos);
long end = System.currentTimeMillis();

long useTime = (end - begin) / 1000;
System.out.println("耗用了：" + useTime + "秒");

workbook.close();
fos.close();


# XSSFWrokbook
long begin = System.currentTimeMillis();
// HSSFWorkbook workbook = new HSSFWorkbook();
XSSFWorkbook workbook = new XSSFWorkbook();

XSSFSheet sheet = workbook.createSheet();
for (int i = 0; i < 65536; i++) {
	XSSFRow row = sheet.createRow(i);
	for (int j = 0; j < 10; j++) {
		XSSFCell cell = row.createCell(j);
		cell.setCellValue(j);
	}
}

FileOutputStream fos = new FileOutputStream("E:\\test\\test044.xlsx");
workbook.write(fos);
long end = System.currentTimeMillis();

long useTime = (end - begin) / 1000;
System.out.println("耗用了：" + useTime + "秒");

workbook.close();
fos.close();

# 结果
HSSFWrokbook 耗用 1s
XSSFWrokbook 耗用 4s

练习二：XSSFWrokbook VS SXSSFWrokbook 写入10万条数据性能测试

# XSSFWrokbook
public void wBigData() throws IOException {
	long begin = System.currentTimeMillis();
	XSSFWorkbook workbook = new XSSFWorkbook();
	XSSFSheet sheet = workbook.createSheet();
	for (int i = 0; i < 100000; i++) {
		XSSFRow row = sheet.createRow(i);
		for (int j = 0; j < 5; j++) {
			XSSFCell cell = row.createCell(j);
			cell.setCellValue(j);
		}
	}

	FileOutputStream fos = new FileOutputStream("E:\\test\\test05.xlsx");
	workbook.write(fos);

	workbook.close();
	fos.close();
	long end = System.currentTimeMillis();
	long useTime = (end - begin) / 1000;
	System.out.println("耗用了：" + useTime + "秒");
}


# SXSSFWrokbook
public void wBigData() throws IOException {
	long begin = System.currentTimeMillis();
	// 创建 SXSSFWorkboo k对象
	SXSSFWorkbook workbook = new SXSSFWorkbook();
	SXSSFSheet sheet = workbook.createSheet();
	for (int i = 0; i < 100000; i++) {
		SXSSFRow row = sheet.createRow(i);
		for (int j = 0; j < 5; j++) {
			SXSSFCell cell = row.createCell(j);
			cell.setCellValue(j);
		}
	}

	FileOutputStream fos = new FileOutputStream("E:\\test\\test06.xlsx");
	workbook.write(fos);

	// 清除临时文件
	workbook.dispose();
	fos.close();
	long end = System.currentTimeMillis();
	long useTime = (end - begin) / 1000;
	System.out.println("耗用了：" + useTime + "秒");
}


# 结果
XSSFWrokbook 耗用4s
SXSSFWrokbook 耗用1s

EasyExcel 读写Excel

重写了 POI 对07版 Excel 的解析，再大的 Exce l都不会出现内存溢出。

EasyExcel 操作Excel

总结

1. JXL: 消耗内存，会出现OOM
2. POI: 可以一定程度上解决OOM的问题，但POI内存消耗依然很大。
3. EasyExcel: 不会出现内存溢出。

	// 读取值的时候，一定要注意数据类型
	// System.out.println(cell.getStringCellValue()); // 只能获取String类型的值
	// System.out.println(cell.getNumericCellValue()); // 只能获取数字类型的值

	fis.close();
}