POI和 easyExcel学习
POI-Excel写
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.llq</groupId>
<artifactId>llq-poi</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!-- xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx(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>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
</dependencies>
</project>
package com.llq;
public class ExcelWriteTest {
@Test
public void testWrite03(){
String path = "F:\\llq-poi\\";
// 1. 创建一个工作簿【03：HSS、07：XSS】
Workbook Workbook = new HSSFWorkbook();
// 2. 创建一个工作表
Sheet sheet = Workbook.createSheet("爱新觉罗LQ 五杀集锦");
// 3. 创建一个行
Row row1 = sheet.createRow(0);
// 4. 创建一个单元格
Cell cell1 = row1.createCell(0);
cell1.setCellValue("霞无敌"); // (1, 1)
Cell cell2 = row1.createCell(1);
cell2.setCellValue("卡莎无敌"); // (1, 2)
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间"); // (2, 1)
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time); // (2, 2)
// 生成一张表（IO 流）03 xls 结尾
try {
FileOutputStream fileOutputStream = new FileOutputStream(path + "ACLQ.xls");
Workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("生成完毕");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
大批量写入
@Test
public void testWrite03BigData() throws IOException {
long start = System.currentTimeMillis();
Workbook workbook = new HSSFWorkbook();
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(j);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWirte03.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println(end - start);
}
大文件写（SXSSF）：100万条，写数据更快，占用更少内存，但是会临时文件，需要清理
// 清除临时文件
((SXSSFWorkbook)workbook).dispose();
@Test
public void testWrite03BigDataS() throws IOException {
long start = System.currentTimeMillis();
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int i = 0; i < 100000; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(j);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWirte07s.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
// 清除临时文件
((SXSSFWorkbook)workbook).dispose();
long end = System.currentTimeMillis();
System.out.println(end - start);
}
Excel 基本读取及注意
@Test
public void testRead() throws Exception{
FileInputStream fileInputStream = new FileInputStream(path + "testWirte03.xls");
// 1. 工作薄
Workbook Workbook = new HSSFWorkbook(fileInputStream);
// 2. 得到表
Sheet sheet = Workbook.getSheetAt(0);
// 3. 拿到行
Row row = sheet.getRow(0);
// 4. 拿到列
Cell cell = row.getCell(0);
System.out.println(cell.getNumericCellValue()); // 注意类型
fileInputStream.close();
}
处理不同类型的数据
public void testCellType() throws Exception{
FileInputStream fileInputStream = new FileInputStream(path + "testWirte03.xls");
Workbook Workbook = new HSSFWorkbook(fileInputStream);
Sheet sheet = Workbook.getSheetAt(0);
// 获取标题内容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null){
int cellCount = rowTitle.getPhysicalNumberOfCells(); // 列数
for (int i = 0; i < cellCount; i++) {
Cell cell = rowTitle.getCell(i);
if (cell != null){
int cellType = cell.getCellType();
String cellValue = cell.getStringCellValue();
System.out.println(cellValue + " ");
}
}
}
// 获取表中的内容
int rowCount = sheet.getPhysicalNumberOfRows(); // 有多少行
for (int i = 1; i < rowCount; i++) {
Row rowData = sheet.getRow(i);
if (rowData != null){
// 读取行中的列
int cellCount = rowData.getPhysicalNumberOfCells();
for (int j = 0; j < cellCount; j++) {
Cell cell = rowData.getCell(j);
// 难点：不知道 cell 数据类型
if (cell != null){
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType){
case HSSFCell.CELL_TYPE_STRING: // 字符串
System.out.println("[String]");
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // boolean
System.out.println("[Boolean]");
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // boolean
System.out.println("[BLANK]");
break;
case HSSFCell.CELL_TYPE_NUMERIC: // boolean
System.out.println("[NUMERIC]");
if (HSSFDateUtil.isCellDateFormatted(cell)){
System.out.println("【日期】");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
}else {
// 不是日期格式，防止数字过长
System.out.println("转换为字符串输出");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_ERROR: // Error
System.out.println("[数据类型错误]");
break;
}
System.out.println(cellValue);
}
}
}
}
fileInputStream.close();
}
计算公式
@Test
public void testFormula() throws IOException {
FileInputStream fileInputStream = new FileInputStream(path + "testWirte04.xls");
Workbook Workbook = new HSSFWorkbook(fileInputStream);
Sheet sheet = Workbook.getSheetAt(0);
Row row = sheet.getRow(4);
Cell cell = row.getCell(0);
// 拿到计算公式
FormulaEvaluator FormulaEvaluator = new HSSFFormulaEvaluator((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;
}
}
EasyExcel使用
快速、简单避免 OOM 的 java 处理Excel 工具
https://github.com/alibaba/easyexcel
文档地址：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/read
