Apache POI及easyExcel读取及写入excel文件

目录

1.excel

2.使用场景

3.Apache POI

4.easyExcel

5.总结

---

1.excel

excel分为两版，03版和07版。

03版的后缀为xls，最大有65536行。

07版的后缀为xlsx，最大行数没有限制。

2.使用场景

将用户信息导出到excel表格中。

将excel中的数据读取到数据库中。

3.Apache POI

(1)说明

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

HSSF - 提供读写MicrostExcel格式档案的功能（excel 03）。
XSSF - 提供读写MicrosofExcel OOXML各式档案的功能（excel 07）。
HWPF -提供读写Microsoft Word洛式档案的功能。
HSLF - 提供读写Microsof PowerPoint式档案的功能
HDGF - 提供读写Microsoft Visio各式档案的功能

使用起来比较麻烦，而且数据量大时会出现OOM异常。

使用poi读取不同格式的excel文件的使用jar包是不同的。如下：

        <!--        xls-03--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><!--        xls-07--><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency>

(2)读取说明

将excel分为4个对象，excel文件是一个工作簿；excel中有好多sheet，这个是工作表；表中有行，行中有列。使用apache poi读取或写入文件，就是先获取工作簿，然后获取工作表，再获取某行，最后获取行中某列的内容。

写入示例：

注意：03和07版excel的对象的区别和文件后缀。

读取示例：

   @Testpublic void read03() throws IOException {FileInputStream fileInputStream = new FileInputStream(path + "apache-03.xls");Workbook workbook = new HSSFWorkbook(fileInputStream);Sheet sheet = workbook.getSheetAt(0);Row row = sheet.getRow(2);Cell cell = row.getCell(1);System.out.println(cell.getCellType());double value = cell.getNumericCellValue();System.out.println(value);if(fileInputStream != null) fileInputStream.close();}@Testpublic void read07() throws IOException {FileInputStream fileInputStream = new FileInputStream(path + "apache-07.xlsx");Workbook workbook = new XSSFWorkbook(fileInputStream);Sheet sheet = workbook.getSheetAt(0);Row row = sheet.getRow(1);Cell cell = row.getCell(1);System.out.println(cell.getCellType());String value = cell.getStringCellValue();System.out.println(value);if(fileInputStream != null) fileInputStream.close();}

注意：

读取时注意单元格的类型，按照类型进行使用不同的方法进行读取，使用的方法和类型不一致时会报错 

读取类型示例：

@Testpublic void cellTypeTest() throws IOException {FileInputStream fileInputStream = new FileInputStream(path + "apache-03.xls");Workbook workbook = new HSSFWorkbook(fileInputStream);Sheet sheet = workbook.getSheetAt(0);Row row = sheet.getRow(1);if (row != null) {int cellNum = row.getPhysicalNumberOfCells();for (int i = 0; i < cellNum; i++) {Cell cell = row.getCell(i);if (cell != null) {System.out.println(cell.getStringCellValue());}}}int rowNum = sheet.getPhysicalNumberOfRows();for (int i = 1; i < rowNum; i++) {Row row1 = sheet.getRow(i);if (row1 != null) {int cells = row1.getPhysicalNumberOfCells();for (int j = 0; j < cells; j++) {Cell cell = row1.getCell(j);if (cell == null) {continue;}String value = "";CellType cellType = cell.getCellType();switch (cellType) {case STRING:System.out.println("String类型");value = cell.getStringCellValue();break;case BLANK:System.out.println("类型为空");break;case BOOLEAN:System.out.println("布尔类型");value = String.valueOf(cell.getBooleanCellValue());break;case NUMERIC:if (DateUtil.isCellDateFormatted(cell)) {System.out.println("日期类型时");Date dateCellValue = cell.getDateCellValue();value = new DateTime(dateCellValue).toString("yyyy-MM-dd");} else {System.out.println("数值格式时");// 有时候数值过长会出现问题value = String.valueOf(cell.getNumericCellValue());}break;case ERROR:System.out.println("数据类型错误");break;default:}}}}if(fileInputStream != null) fileInputStream.close();}

 读取公式示例：

 @Testpublic void testFormula() throws IOException {String path = "C:\\Users\\DELL\\Desktop\\新建 XLS 工作表.xls";FileInputStream fileInputStream = new FileInputStream(path);Workbook workbook = new HSSFWorkbook(fileInputStream);Sheet sheet = workbook.getSheetAt(0);Row row = sheet.getRow(3);Cell cell = row.getCell(0);CellType cellType = cell.getCellType();switch (cellType) {case FORMULA:String cellFormula = cell.getCellFormula();System.out.println("公式:" + cellFormula);FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);CellValue evaluate = formulaEvaluator.evaluate(cell);String value = evaluate.formatAsString();System.out.println(value);}}

 根据类型判断是公式时，获取工作簿的计算对象，调用计算方法获取计算结果。

(3)写入说明

使用HSSF：会写入缓存，不操作磁盘，最后一次性写入磁盘，速度快。

使用XSSF：写数据时非常慢，非常耗内存，数据量大时，会发生内存溢出问题。

使用SXSSF：可以写非常大的数据量，如百万级别的数据，写数据速度快，占用更少的内存。

写入过程中会生成临时文件，需要手动清理临时文件，默认是100条数据保存在内存中，超过100条，则最前面的100条数据会写入到临时文件中，如果想自定义存放在内存中数据的数量，创建时可以传入件数的参数。如果写入的excel结构比较复杂，例如合并单元格，注释等，广泛使用也可能消耗大量内存。内存问题可以使用Jprofile进行监控。

写入示例：

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;import java.io.FileOutputStream;
import java.io.IOException;/*** @Author linab* @Date 2023/10/29 20:26* @Version 1.0*/
public class ApacheTest {private String path = "E:\\learn\\excel\\excel-poi\\src\\main\\resources\\";@Testpublic void test03() throws IOException {Workbook workbook = new HSSFWorkbook();Sheet sheet = workbook.createSheet("03");Row row = sheet.createRow(0);Cell cell = row.createCell(0);cell.setCellValue("姓名");Cell cell1 = row.createCell(1);cell1.setCellValue("小李");Row row1 = sheet.createRow(1);Cell cell2 = row1.createCell(0);cell2.setCellValue("日期");Cell cell3 = row1.createCell(1);String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell3.setCellValue(time);FileOutputStream fileOutputStream = new FileOutputStream(path + "apache-03.xls");workbook.write(fileOutputStream);if(fileOutputStream != null) fileOutputStream.close();if(workbook != null) workbook.close();System.out.println("文件生成成功");}@Testpublic void test07() throws IOException {Workbook workbook = new XSSFWorkbook();Sheet sheet = workbook.createSheet("07");Row row = sheet.createRow(0);Cell cell = row.createCell(0);cell.setCellValue("姓名");Cell cell1 = row.createCell(1);cell1.setCellValue("小李");Row row1 = sheet.createRow(1);Cell cell2 = row1.createCell(0);cell2.setCellValue("日期");Cell cell3 = row1.createCell(1);String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell3.setCellValue(time);FileOutputStream fileOutputStream = new FileOutputStream(path + "apache-07.xlsx");workbook.write(fileOutputStream);if(fileOutputStream != null) fileOutputStream.close();if(workbook != null) workbook.close();System.out.println("文件生成成功");}
}

写入速度示例：

    @Testpublic void testBigHssf() throws IOException {Instant start = Instant.now();HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("hssf");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 fileOutputStream = new FileOutputStream(path + "bigHssf.xls");workbook.write(fileOutputStream);if (fileOutputStream != null) fileOutputStream.close();if (workbook != null) workbook.close();Instant end = Instant.now();System.out.println(Duration.between(start, end).toMillis());}@Testpublic void testBigXssf() throws IOException {Instant start = Instant.now();Workbook workbook = new XSSFWorkbook();Sheet sheet = workbook.createSheet("hssf");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);}}FileOutputStream fileOutputStream = new FileOutputStream(path + "bigXssf.xlsx");workbook.write(fileOutputStream);if (fileOutputStream != null) fileOutputStream.close();if (workbook != null) workbook.close();Instant end = Instant.now();System.out.println(Duration.between(start, end).toMillis());}@Testpublic void testBigSxssf() throws IOException {Instant start = Instant.now();Workbook workbook = new SXSSFWorkbook();Sheet sheet = workbook.createSheet("hssf");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);}}FileOutputStream fileOutputStream = new FileOutputStream(path + "bigsXssf.xlsx");workbook.write(fileOutputStream);if (fileOutputStream != null) fileOutputStream.close();((SXSSFWorkbook) workbook).dispose();if (workbook != null) workbook.close();Instant end = Instant.now();System.out.println(Duration.between(start, end).toMillis());}

4.easyExcel

        Java解析、生成Excel比较有名的架有Apache poi、jxl，但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大，easyexcel重写了poi对07版Excel的解析，能够将原本一个3M的excel用POl sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便。

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节省内存著称。

节省内存的主要原因是在解析excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。

读写excel只需要一行代码就可以完成。

详细的参照官网：读Excel | Easy Excel

5.总结

使用easyExcel让操作excel更加方便，如果数据量过大，不想一直存放在内存中，可以读取一些，处理一些，获取登录到数据库中。但有时要确保原子性，还要要放在内存中，所有数据校验都通过之后再存储到数据库中。