文章目录
1.POI与easyExcelg概述2.POI-Excel写入3.POI-Excel读取4.EasyExcel操作1.POI与easyExcelg概述
概述
开发中经常会设计到excel的处理,如导出excel,导入excel到数据库中的功能!
而目前操作Excel目前比较主流的技术有Apache的POI和阿里巴巴的easyExcel技术
将用户信息,部门信息,等信息导出为excel表格(导出数据)将Excel表格信息录入到网站数据库(习题上传…)大大减轻网站录入量！常用场景
Apache PIO
Apache POI官网地址为:/
easyExcelg
easyExcelg官网地址:/alibaba/easyexcel
EasyExcel是阿里巴巴开源的一个excel处理框架,以使用简单节省内存著称。
EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行一行读取数据,逐个进行解析.
内存问题:POI=100W先加载到内存OOM,在写入文件
下图是EasyExcel与POI在解析Excel文档时的对比图:
2.POI-Excel写入
创建一个普通的Maven项目引入pom依赖如下创建项目
<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>5.2.2</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.13.2</version></dependency></dependencies>
版本介绍
03(xls)与07(xlsx)版本的写,就是对象不同，方法都是一样的!
需要注意:版本和版本存在兼容性问题!03最多只有65535行!
步骤为:
工作簿工作表行列
基本写入操作
工作薄的接口为Workbook这个接口下有三个实现类为:
HSSFWorkbook(03版本的xls)对象XSSFWorkbook(07版本的xlsx)对象SXSSFWorkbook(升级版增加读写效率)对象
这里除了工作薄的对象不一样其他的操作基本一只代码如:
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 java.io.FileOutputStream;import java.io.IOException;public class Test {public static void main(String[] args) throws IOException {//1.创建一个工作簿Workbook hssfWorkbook = new HSSFWorkbook(); //03版本的对象//2.创建一个工作表Sheet testTable = hssfWorkbook.createSheet("小步测试的表");//3.创建一个行Row row = testTable.createRow(0); //表示创建第0行//4.创建一个单元格Cell cell = row.createCell(0); //表示创建第0行的第0个单元格cell.setCellValue("测试数值为:"); //向单元格设值Cell cell1 = row.createCell(1);//表示创建第0行的第1个单元格cell1.setCellValue(666666);Cell cell2 = row.createCell(2);//表示创建第0行第2个单元格cell2.setCellValue("时间为:");Cell cell3 = row.createCell(3);//表示创建第0行第3个单元格cell3.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));//生成一张表FileOutputStream fileOutputStream = new FileOutputStream("crawl/test.xls");//这里为需要输出的文件地址hssfWorkbook.write(fileOutputStream);fileOutputStream.close();}}
注意对象的区别所生成的后缀名需要对应!
大文件写HSSF
缺点:最多只能处理65536行,否则会抛出异常
Invalid row number (65536) outside allowable range (0..65535)
优点:过程中写入缓存,不操作磁盘,最后一次性写入,速度块
03(xls)测试如:
public static void testWrite03BigData() throws IOException {long startTime = System.currentTimeMillis();//1.创建薄HSSFWorkbook hssfWorkbook = new HSSFWorkbook();//2.创建表HSSFSheet sheet = hssfWorkbook.createSheet("小步的测试表");//3.写入数据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(i+":"+j);}}long endTime = System.currentTimeMillis();FileOutputStream fileOutputStream = new FileOutputStream("crawl/test.xls");hssfWorkbook.write(fileOutputStream);System.out.println("创建时间为:"+(endTime-startTime)+"毫秒");}
07(xlsx)测试如:
07则没有65536行的限制
public static void testWrite07BigData() throws IOException {long startTime = System.currentTimeMillis();//1.创建薄Workbook hssfWorkbook = new XSSFWorkbook();//2.创建表Sheet sheet = hssfWorkbook.createSheet("小步的测试表");//3.写入数据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(i+":"+j);}}long endTime = System.currentTimeMillis();FileOutputStream fileOutputStream = new FileOutputStream("crawl/test.xlsx");hssfWorkbook.write(fileOutputStream);System.out.println("创建时间为:"+(endTime-startTime)+"毫秒");}
进行更快的写入测试如:
使用SXSSFWorkbook类进行写入如
public static void testWrite07BigData() throws IOException {long startTime = System.currentTimeMillis();//1.创建薄Workbook hssfWorkbook = new SXSSFWorkbook();//2.创建表Sheet sheet = hssfWorkbook.createSheet("小步的测试表");//3.写入数据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(i+":"+j);}}long endTime = System.currentTimeMillis();FileOutputStream fileOutputStream = new FileOutputStream("crawl/test.xlsx");//清除临时文件((SXSSFWorkbook)hssfWorkbook).dispose();hssfWorkbook.write(fileOutputStream);System.out.println("创建时间为:"+(endTime-startTime)+"毫秒");}
3.POI-Excel读取
如果需要读取都大致相同,需要与之前一样注意一下03与07版的对象不一就行了如:
基本读取
public static void ReaderData() throws IOException {FileInputStream fileInputStream = new FileInputStream("crawl/testt.xlsx"); //读取的文件HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);HSSFSheet test = hssfWorkbook.getSheetAt(0);HSSFRow row = test.getRow(0);HSSFCell cell = row.getCell(0);//读取值的时候需要注意类型否则会失败System.out.println(cell.getStringCellValue());//如:无法从文本单元格中获取布尔值System.out.println(cell.getBooleanCellValue());}
注意:读取值的时候需要注意类型否则会失败
不同类型读取
public static void ReaderData() throws IOException {FileInputStream fileInputStream = new FileInputStream("crawl/test.xls");HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);HSSFSheet sheet = hssfWorkbook.getSheetAt(0); //获取第零个工作簿int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();//获取这个工作簿有多少行数for (int i = 0; i < physicalNumberOfRows; i++) {HSSFRow row = sheet.getRow(i);//获取每一行int physicalNumberOfCells = row.getPhysicalNumberOfCells();//获取每一行有多少单元格for (int i1 = 0; i1 < physicalNumberOfCells; i1++) {HSSFCell cell = row.getCell(i1);//获取单元格System.out.println(cell.getCellType());//判断单元格的类型值switch (cell.getCellType()){case Cell.CELL_TYPE_NUMERIC://数字类型则需要判断是不是日期if (HSSFDateUtil.isCellDateFormatted(cell)){System.out.println(cell.getDateCellValue());}else {System.out.println(cell.getNumericCellValue());}break;case Cell.CELL_TYPE_STRING:case Cell.CELL_TYPE_BLANK://空白类型和字符串类型System.out.println(cell.getStringCellValue());break;case Cell.CELL_TYPE_BOOLEAN://布尔类型System.out.println(cell.getBooleanCellValue());break;case Cell.CELL_TYPE_ERROR://布尔类型System.out.println(cell.getErrorCellValue());break;}}}}
计算公式(了解即可)
FileInputStream fileInputStream = new FileInputStream("crawl/test.xls");HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);HSSFSheet sheet = hssfWorkbook.getSheetAt(0);HSSFRow row = sheet.getRow(4);HSSFCell cell = row.getCell(0);//拿到计算公司FormulaEvaluator hssfFormulaEvaluator = new HSSFFormulaEvaluator(hssfWorkbook);//输出单元格的内容int cellType = cell.getCellType();switch (cellType){case Cell.CELL_TYPE_FORMULA://如果为公式则String cellFormula = cell.getCellFormula(); //信息System.out.println(cellFormula);//计算CellValue evaluate = hssfFormulaEvaluator.evaluate(cell);String s = evaluate.formatAsString();System.out.println(s);break;}
4.EasyExcel操作
导入依赖
<!--easyExcel--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13.2</version></dependency>
写入测试
实体类:DemoData.java
import com.alibaba.excel.annotation.ExcelIgnore;import com.alibaba.excel.annotation.ExcelProperty;import lombok.Data;import java.util.Date;@Datapublic class DemoData {@ExcelProperty("字符串标题")private String string;@ExcelProperty("日期标题")private Date date;@ExcelProperty("数字标题")private Double doubleData;//忽略这个字段@ExcelIgnoreprivate String ignore;}
写入测试:
package com.test;import com.alibaba.excel.EasyExcel;import org.junit.Test;import java.util.ArrayList;import java.util.Date;import java.util.List;public class EasyTest {//获取数据public List<DemoData> getData(){List<DemoData> demoData = new ArrayList<>();for (int i = 0; i <10 ; i++) {DemoData demoData1 = new DemoData();demoData1.setString("字符串"+i);demoData1.setDate(new Date());demoData1.setDoubleData(3.1415);demoData.add(demoData1);}return demoData;}//测试方法@Testpublic void testEasy(){String fileName="test/test.xlsx";//写入的地址EasyExcel.write(fileName,DemoData.class).sheet("test").doWrite(getData());}}
读取测试
如果需要读取exel需要进行编写一个监听器如:
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去@Slf4jpublic class DemoDataListener implements ReadListener<DemoData> {/*** 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收*/private static final int BATCH_COUNT = 100;/*** 缓存的数据*/private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);/*** 假设这个是一个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*/@Overridepublic void invoke(DemoData data, AnalysisContext context) {log.info("解析到一条数据:{}", JSON.toJSONString(data));cachedDataList.add(data);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (cachedDataList.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listcachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}/*** 所有数据解析完成了 都会来调用** @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 这里也要保存数据，确保最后遗留的数据也存储到数据库saveData();log.info("所有数据解析完成！");}/*** 加上存储数据库*/private void saveData() {log.info("{}条数据，开始存储数据库！", cachedDataList.size());demoDAO.save(cachedDataList);log.info("存储数据库成功！");}}
读取:
//测试方法@Testpublic void testEasy(){String fileName="test/test.xlsx";//写入的地址EasyExcel.read(fileName,DemoData.class,new DemoDataListener()).sheet("test");}
详细文档可查看:/easyexcel/doc/read
