1、什么是poi
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
2、导入poi的maven坐标
<?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.wx</groupId>
<artifactId>wx-poi</artifactId>
<version>1.0-SNAPSHOT</version>
<!--导入依赖-->
<dependencies>
<!--xls(03)-->
<!--<dependency>-->
<!-- <groupId>org.apache.poi</groupId>-->
<!-- <artifactId>poi</artifactId>-->
<!-- <version>3.9</version>-->
<!--</dependency>-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.62</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>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
</project>
3、Execl分为03版本和07版本
03版本的excel只能有65536行
07版本的excel行数可以超过65536行
4、演示写代码
package com.wx;
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.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
public class ExcelWriteTest {
private final String PATH="D:\\IdeaProjects\\wx-poi\\";
@Test
public void testWrite03() throws IOException {
// 1,创建一个工作簿
Workbook workbook = new HSSFWorkbook();
// 2,创建一个工作表
Sheet sheet = workbook.createSheet("统计表");
// 3,创建一行数据
Row row = sheet.createRow(0);
// 4,创建一个单元格
Cell cell = row.createCell(0);
//给(0,0)添加数据
cell.setCellValue("统计人数");
Cell cell12 = row.createCell(1);
cell12.setCellValue("时间");
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue(666);
Cell cell22 = row2.createCell(1);
String date = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(date);
// 生成一张表,用IO流写出
// 03版本xls最大行数为65536,后缀为xls
File file = new File(PATH + "统计表03.xls");
FileOutputStream fos = new FileOutputStream(file);
//关闭流对象
workbook.write(fos);
System.out.println("xls文件生成完毕!");
fos.close();
}
@Test
public void testWrite07() throws IOException {
// 1.生成一个工作簿
Workbook workbook = new SXSSFWorkbook();
// 2.生成一个工作表
Sheet sheet = workbook.createSheet("统计表");
// 3.生成一行
Row row1 = sheet.createRow(0);
// 4.生成一个单元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("统计人数");
Cell cell12 = row1.createCell(1);
cell12.setCellValue("时间");
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue(666);
Cell cell22 = row2.createCell(1);
String date = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(date);
// 5.生成一个文件,输出IO流,07版本后缀为xls
workbook.write(new FileOutputStream(new File(PATH + "统计表07.xlsx")));
System.out.println("文件生成完毕!");
}
/**
* 03版本有65536限制,但是速度想对较快
* @throws IOException
*/
@Test
public void testWrite03BigData() throws IOException {
//时间
long begin = System.currentTimeMillis();
// 1.生成一个工作簿
Workbook workbook = new HSSFWorkbook();
// 2.生成一个工作表
Sheet sheet = workbook.createSheet();
// 3.写入数据 最大只能65536行,03版本
//java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
long end = System.currentTimeMillis();
System.out.println("over");
// 4.存储数据
FileOutputStream fos = new FileOutputStream(new File(PATH + "testWrite03BigData.xls"));
workbook.write(fos);
fos.close();
System.out.println((double) (end - begin) / 1000);
}
/**
* 07版本没有65536限制,可以写很多数据,但是速度较慢
* @throws IOException
*/
@Test
public void testWrite07BigData() throws IOException {
//时间
long begin = System.currentTimeMillis();
// 1.生成一个工作簿
Workbook workbook = new XSSFWorkbook();
// 2.生成一个工作表
Sheet sheet = workbook.createSheet();
// 3.写入数据
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
long end = System.currentTimeMillis();
System.out.println("over");
// 4.存储数据
FileOutputStream fos = new FileOutputStream(new File(PATH + "testWrite07BigData.xlsx"));
workbook.write(fos);
fos.close();
System.out.println((double) (end - begin) / 1000);
}
/**
* 07版本,使用缓存技术,速度相比07版本用XSSFWorkbook快一点
* @throws IOException
*/
@Test
public void testWrite07BigDataS() throws IOException{
//时间
long begin = System.currentTimeMillis();
// 1.生成一个工作簿
Workbook workbook = new SXSSFWorkbook();
// 2.生成一个工作表
Sheet sheet = workbook.createSheet();
// 3.写入数据
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
long end = System.currentTimeMillis();
System.out.println("over");
// 4.存储数据
FileOutputStream fos = new FileOutputStream(new File(PATH + "testWrite07BigDataS.xlsx"));
workbook.write(fos);
fos.close();
System.out.println((double) (end - begin) / 1000);
}
}
5、演示写代码
package com.wx;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;
public class ExcelReadTest {
private final String PATH="D:\\IdeaProjects\\wx-poi\\";
@Test
public void testRead03() throws IOException {
//获取文件流
FileInputStream fis = new FileInputStream(new File(PATH + "统计表03.xls"));
// 1,创建一个工作簿
Workbook workbook = new HSSFWorkbook(fis);
// 2.获取工作表
Sheet sheet = workbook.getSheet("统计表");
Row row1 = sheet.getRow(1);
Cell cell11 = row1.getCell(0);
//目前获取的是字符串
// System.out.println(cell11.getStringCellValue());
System.out.println(cell11.getNumericCellValue());
}
@Test
public void testRead07() throws IOException {
//获取文件流
FileInputStream fis = new FileInputStream(new File(PATH + "统计表07.xlsx"));
// 1,创建一个工作簿
Workbook workbook = new XSSFWorkbook(fis);
// 2.获取工作表
Sheet sheet = workbook.getSheet("统计表");
Row row1 = sheet.getRow(1);
Cell cell11 = row1.getCell(0);
//目前获取的是字符串
// System.out.println(cell11.getStringCellValue());
System.out.println(cell11.getNumericCellValue());
}
@Test
public void testCollType() throws IOException {
//获取文件流
FileInputStream fis = new FileInputStream(new File(PATH + "会员消费商品明细表.xls"));
// 1,创建一个工作簿
Workbook workbook = new HSSFWorkbook(fis);
// 2.获取工作表
Sheet sheet = workbook.getSheetAt(0);
if (sheet != null){
int rowTitle = sheet.getPhysicalNumberOfRows();
for (int rowNum = 0; rowNum < rowTitle; rowNum++) {
Row row = sheet.getRow(rowNum);
if (row != null){
int cellCount = row.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell != null){
int cellType = cell.getCellType();
//判断单元格中的数据类型
String cellValue = "";
switch (cellType) {
case Cell.CELL_TYPE_STRING://字符串
System.out.print("【STRING】");
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN://布尔
System.out.print("【BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK://空
System.out.print("【BLANK】");
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print("【NUMERIC】");
if (HSSFDateUtil.isCellDateFormatted(cell)){//日期
System.out.print("【日期】");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd HH:mm:ss");
}else {
System.out.print("【转换为字符串输出】");
//防止数字过长,装换成字符串
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case Cell.CELL_TYPE_ERROR:
System.out.print("【数据类型错误】");
break;
}
System.out.print(cellValue + " | ");
}
}
System.out.println();
}
}
}
}
@Test
public void testFormula() throws IOException {
FileInputStream fis = new FileInputStream(PATH + "计算公式.xls");
Workbook workbook = new HSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
Row row4 = sheet.getRow(4);
Cell cell41 = row4.getCell(0);
//拿到计算公式
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
//得到单元格中内容
int cellType = cell41.getCellType();
switch (cellType){
case Cell.CELL_TYPE_FORMULA://公式
String formula = cell41.getCellFormula();
System.out.println(formula);
//计算
CellValue evaluate = formulaEvaluator.evaluate(cell41);
String s = evaluate.formatAsString();
System.out.println(s);
break;
}
fis.close();
}
}
6、阿里的Easypoi
创建实体类:
package com.wx.esay; import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; import java.util.Date; @Data public class DemoData { @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; @ExcelProperty("数字标题") private Double doubleData; /** * 忽略这个字段 */ @ExcelIgnore private String ignore; }
dao层接口:
package com.wx.esay; import java.util.List; /** * 假设这个是你的DAO存储。当然还要这个类让spring管理,当然你不用需要存储,也不需要这个类。 **/ public class DemoDAO { public void save(List<DemoData> list) { // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入 } }
监听类:
package com.wx.esay; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSON; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.ArrayList; import java.util.List; // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去 public class DemoDataListener extends AnalysisEventListener<DemoData> { private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class); /** * 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收 */ private static final int BATCH_COUNT = 5; List<DemoData> list = new ArrayList<DemoData>(); /** * 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。 */ private DemoDAO demoDAO; public DemoDataListener() { // 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数 demoDAO = new DemoDAO(); } /** * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来 * * @param demoDAO */ public DemoDataListener(DemoDAO demoDAO) { this.demoDAO = demoDAO; } /** * 这个每一条数据解析都会来调用 * * @param data * one row value. Is is same as {@link AnalysisContext#readRowHolder()} * @param context */ @Override public void invoke(DemoData data, AnalysisContext context) { System.out.println(JSON.toJSONString(data)); LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data)); list.add(data); // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list list.clear(); } } /** * 所有数据解析完成了 都会来调用 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据,确保最后遗留的数据也存储到数据库 saveData(); LOGGER.info("所有数据解析完成!"); } /** * 加上存储数据库 */ private void saveData() { LOGGER.info("{}条数据,开始存储数据库!", list.size()); demoDAO.save(list); LOGGER.info("存储数据库成功!"); } }
测试方法调用:
package com.wx.esay; import com.alibaba.excel.EasyExcel; import org.junit.Test; import java.util.ArrayList; import java.util.Date; import java.util.List; public class EasyTest { private final String PATH="D:\\IdeaProjects\\wx-poi\\"; private List<DemoData> data() { List<DemoData> list = new ArrayList<DemoData>(); for (int i = 0; i < 10; i++) { DemoData data = new DemoData(); data.setString("字符串" + i); data.setDate(new Date()); data.setDoubleData(0.56); list.add(data); } return list; } /** * 最简单的写 * <p>1. 创建excel对应的实体对象 参照{@link DemoData} * <p>2. 直接写即可 */ @Test public void simpleWrite() { // 写法1 String fileName = PATH + "easyexcel.xlsx"; // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭 // 如果这里想使用03 则 传入excelType参数即可 EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data()); } /** * 最简单的读 * <p>1. 创建excel对应的实体对象 参照{@link DemoData} * <p>2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link DemoDataListener} * <p>3. 直接读即可 */ @Test public void simpleRead() { // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去 // 写法1: String fileName = PATH + "easyexcel.xlsx"; // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭 EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead(); } }
阿里的Easy POI的参考网址:https://www.yuque.com/easyexcel/doc/read