POI及EasyExcel使用
★应用场景
1.将用户信息导出为excel表格（导出数据...）
2.将excel表中的信息录入到网站数据库
操作excel目前比较流行的就是Apache POI 和阿里巴巴的easyExcel
★Apache POI
官网：https://poi.apache.org/ 使用起来相对比较麻烦，消耗内存
基本功能
HSSF：提供读写excel格式档案的功能（03版本，行数最多65536行）
XSSF：提供读写excel、ooxml格式档案的功能（07版本。l理论 行数没有限制）
HWPF：提供读写我认得格式档案的功能
HSLF：提供读写PowerPoint格式档案的功能
HDGF：提供读写Visio格式档案的功能
★easyExcel
官网：https://github.com/alibaba/easyexcel
在poi基础上优化，降低内存使用，开发使用更简单。
★POI和easyExcel区别
POI：一次性将数据加载到内存，可能出现内存溢出。oom问题。
easyExcel：一次一行操作。
★POI-Excel写
1.创建一个空的Maven项目
2.引入xls(03版本)和xlsx(07版本)依赖
<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>
</dependency>
</dependencies>
步骤1：工作簿 --> 步骤2：工作表 --> 步骤3：行 --> 步骤4：列
package com.ckfuture; 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.joda.time.DateTime; import org.junit.Test; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.security.spec.RSAOtherPrimeInfo; public class ExcelWriteText { String PATH="H:\\javaProject\\javaExcel"; @Test public void testWrite03() throws Exception { //1.创建一个工作薄 Workbook workbook=new HSSFWorkbook(); //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(777); //第二行 Row row2=sheet.createRow(1);//第二行 Cell cell21 = row2.createCell(0);//第一列 cell21.setCellValue("统计时间"); Cell cell22 = row2.createCell(1);//第二列 String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell22.setCellValue(time); //生成一张表（IO流） FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/创客未来03.xls"); //输出 workbook.write(fileOutputStream); //关闭流 fileOutputStream.close(); System.out.println("创客未来03 生成完毕！"); } }
@Test public void testWrite07() throws Exception { //1.创建一个工作薄 Workbook workbook=new XSSFWorkbook(); //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(777); //第二行 Row row2=sheet.createRow(1);//第二行 Cell cell21 = row2.createCell(0);//第一列 cell21.setCellValue("统计时间"); Cell cell22 = row2.createCell(1);//第二列 String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell22.setCellValue(time); //生成一张表（IO流） FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/创客未来07.xlsx"); //输出 workbook.write(fileOutputStream); //关闭流 fileOutputStream.close(); System.out.println("创客未来07 生成完毕！"); }
注意对象的一个区别，文件后缀！
数据批量导入
03版本批量导入
@Test public void testWrite03BigData() throws Exception { //时间 long begin = System.currentTimeMillis(); //创建一个工作簿 Workbook workbook = new HSSFWorkbook(); //创建一个表 Sheet sheet = workbook.createSheet(); //写入数据 for (int rowNum = 0; rowNum < 65536; rowNum++) { Row row = sheet.createRow(rowNum);//每一行 for (int cellNum = 0; cellNum < 10; cellNum++) { row.createCell(cellNum).setCellValue(cellNum);//每一列 } } System.out.println("write over"); FileOutputStream outputStream = new FileOutputStream(PATH + "/testWrite03BigData.xls"); workbook.write(outputStream); outputStream.close(); long end = System.currentTimeMillis(); System.out.println((double)(end-begin)/1000); }
07版本批量导入
@Test public void testWrite07BigData() throws Exception { //时间 long begin = System.currentTimeMillis(); //创建一个工作簿 Workbook workbook = new XSSFWorkbook(); //创建一个表 Sheet sheet = workbook.createSheet(); //写入数据 for (int rowNum = 0; rowNum < 65537; rowNum++) { Row row = sheet.createRow(rowNum);//每一行 for (int cellNum = 0; cellNum < 10; cellNum++) { row.createCell(cellNum).setCellValue(cellNum);//每一列 } } System.out.println("write over"); FileOutputStream outputStream = new FileOutputStream(PATH + "/testWrite07BigData.xlsx"); workbook.write(outputStream); outputStream.close(); long end = System.currentTimeMillis(); System.out.println((double)(end-begin)/1000); }
07版本耗时比较长。如果优化可以才用 SXXFF
@Test public void testWrite07BigDataS() throws Exception { //时间 long begin = System.currentTimeMillis(); //创建一个工作簿 Workbook workbook = new SXSSFWorkbook(); //创建一个表 Sheet sheet = workbook.createSheet(); //写入数据 for (int rowNum = 0; rowNum < 65537; rowNum++) { Row row = sheet.createRow(rowNum);//每一行 for (int cellNum = 0; cellNum < 10; cellNum++) { row.createCell(cellNum).setCellValue(cellNum);//每一列 } } System.out.println("write over"); FileOutputStream outputStream = new FileOutputStream(PATH + "/testWrite07BigDataS.xlsx"); workbook.write(outputStream); outputStream.close(); //清除临时文件 ((SXSSFWorkbook)workbook).dispose(); long end = System.currentTimeMillis(); System.out.println((double)(end-begin)/1000); }
SXSSFWorkbook 允许写入非常大的文件而不会耗尽内存，但仍然会消耗大量内存，如果广泛使用需要大量内存。
★POI-Excel读
1.简单读取，读取的excel文件数据类型是相同
03版本读取
package com.ckfuture;
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.joda.time.DateTime;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.FileOutputStream;
public class ExcelReadText {
String PATH="H:\\javaProject\\javaExcel";
@Test
public void testRead03() throws Exception {
//获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "/创客未来03.xls");
//1.创建一个工作薄,使用excel能操作的这边它都可以操作
Workbook workbook=new HSSFWorkbook(inputStream);
//2.得到表
Sheet sheetAt = workbook.getSheetAt(0);
//3.得到行
Row row = sheetAt.getRow(0);//获取第一行
//4.得到列
Cell cell = row.getCell(0);//获取第一列
//读取值的时候，一定需要注意类型
//getStringCellValue 字符串类型
System.out.println(cell.getStringCellValue());
//关闭流
inputStream.close();
}
}
07版本读取
@Test public void testRead07() throws Exception { //获取文件流 FileInputStream inputStream = new FileInputStream(PATH + "/创客未来07.xlsx"); //1.创建一个工作薄,使用excel能操作的这边它都可以操作 Workbook workbook=new XSSFWorkbook(inputStream); //2.得到表 Sheet sheetAt = workbook.getSheetAt(0); //3.得到行 Row row = sheetAt.getRow(0);//获取第一行 //4.得到列 Cell cell = row.getCell(0);//获取第一列 //读取值的时候，一定需要注意类型 //getStringCellValue 字符串类型 System.out.println(cell.getStringCellValue()); //关闭流 inputStream.close(); }
2.读取不同类型的数据
@Test public void testCellType() throws Exception { //获取文件流 FileInputStream inputStream = new FileInputStream(PATH + "/创客未来03.xls"); //1.创建一个工作薄,使用excel能操作的这边它都可以操作 Workbook workbook=new HSSFWorkbook(inputStream); 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){ int cellType = cell.getCellType(); String cellValue = cell.getStringCellValue(); System.out.println(cellValue); } } System.out.println(); } //获取表中的内容 int rowCount = sheet.getPhysicalNumberOfRows();//获取excel表格中的行数 for (int rowNum = 1; rowNum < rowCount; rowNum++) { Row rowData = sheet.getRow(rowNum); if(rowData!=null){ //读取列 int cellCount = rowTitle.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { System.out.print("["+(rowNum+1)+"-"+(cellNum+1)+"]"); Cell cell = rowData.getCell(cellNum); //匹配列的数据类型,写到吐！ if(cell!=null){ int cellType = cell.getCellType(); String cellValue=""; switch (cellType){ case HSSFCell.CELL_TYPE_STRING://字符串 cellValue=cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN://布尔 cellValue=String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_BLANK://空 //空值不需要返回 break; case HSSFCell.CELL_TYPE_NUMERIC://数字（日期、普通数字） if(HSSFDateUtil.isCellDateFormatted(cell)){//日期 Date date = cell.getDateCellValue(); cellValue= new DateTime(date).toString("yyyy-MM-dd"); }else {//普通数字 //不是日期格式，防止数字过长！ cell.setCellType(HSSFCell.CELL_TYPE_STRING); cellValue=cell.toString(); } break; case HSSFCell.CELL_TYPE_ERROR://错误 //数据类型错误不需要返回 break; } System.out.println(cellValue); } } } } //关闭流 inputStream.close(); }
3.读取计算公式
@Test public void testFormula() throws Exception { FileInputStream inputStream = new FileInputStream(PATH + "/公式.xls"); Workbook workbook = new HSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(3); Cell cell = row.getCell(0); //拿到计算公式 eval 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操作（alibaba开源项目）
1.导入依赖
<!--导入easyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.0-beta2</version>
</dependency>
2.建立一个实体类（easyexcel的写入规则）
package com.ckfuture.easy; 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; public void setString(String s) { this.string=s; } public void setDate(Date date) { this.date=date; } public void setDoubleData(double v) { this.doubleData=v; } }
3.建立测试类 EasyTest 写操作
package com.ckfuture.easy;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class EasyTest {
String PATH="H:\\javaProject\\javaExcel";
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;
}
//根据list 写入excel
@Test
public void simpleWrite(){
//写法1
String fileName=PATH+"/EasyTest.xlsx";
//这里需要指定写用哪个class去写，然后写到第一给sheet，名字为模板 然后文件流会自动关闭
//write(fileName,格式类)
//sheet(表名)
//doWrite(数据)
EasyExcel.write(fileName,DemoData.class).sheet("模板").doWrite(data());
//写法2
// fileName=PATH+"/EasyTest2.xlsx";
// ExcelWriter excelWriter=EasyExcel.write(fileName,DemoData.class).build();
// WriteSheet writeSheet=EasyExcel.writerSheet("模板").build();
// excelWriter.write(data(),writeSheet);
// //finish帮忙关闭流
// excelWriter.finish();
}
}
4.读操作 首先创建 监听器
package com.ckfuture.easy; 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); private static final int BATCH_COUNT = 5; List<DemoData> list = new ArrayList<DemoData>(); private DemoDAO demoDAO; public DemoDataListener() { // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数 demoDAO = new DemoDAO(); } public DemoDataListener(DemoDAO demoDAO) { this.demoDAO = demoDAO; } // 读取数据会执行 invoke 方法 // DemoData 类型 // AnalysisContext 分析上问 @Override public void invoke(DemoData data, AnalysisContext context) { System.out.println(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("存储数据库成功！"); } }
5.读操作 再创建持久化层用于插入数据库
package com.ckfuture.easy; import java.util.List; public class DemoDAO { public void save(List<DemoData> list) { // 持久化操作！ // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入 } }
6.读操作 测试
@Test public void simpleRead() { // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去 // 写法1： String fileName = PATH + "/EasyTest.xlsx"; // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭 // 重点注意读取的逻辑 DemoDataListener EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead(); }
