当前位置：首页 >服务端 > POI和EasyExcel

POI和EasyExcel

概述

将用户信息导出为excel表格
将excel表中的信息录入到网站数据库
Apache POI和阿里巴巴easyExcel

POI

HSSF 03版excel xls
XSSF 07版excel xlsx
HWPF word
HSLF ppt
HDGF Visio

导入依赖

 <!--03版--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><!--07版--><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><!--日期格式化工具--><dependency><groupId>joda-time</groupId><artifactId>joda-time</artifactId><version>2.10.9</version></dependency><!--单元测试--><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13</version></dependency></dependencies>

EasyExcel

easyExceleasyExcel是阿里巴巴开源的一个excel，简单，节省内存
官网：https://github.com/alibaba/easyexcel
java解析excel，简单，节省内存
原因在于：解析excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析
内存问题：

POI=100W数据先加载到内存，再写入到文件
EasyExcel：一次写一行

注意：easyexcel里自带poi版本，cell.getCellType()会返回int类型，此时判断数据类型switch case时，case HSSFCell_TYPE_STRING等等

EasyExcel和POI在解析excel时的对比图

官方文档：https://www.yuque.com/easyexcel/doc/easyexcel

主要对象：1.工作薄 2.工作表 3.行 4.列

POI-Excel写

String path = "D:\\tools\\poi\\";//1.创建工作薄Workbook workbook = new HSSFWorkbook();//2.创建工作表Sheet sheet = workbook.createSheet("观众统计表");//3.创建行Row row1 = sheet.createRow(0);//4.创建单元格(1,1)Cell cell1 = row1.createCell(0);cell1.setCellValue("今日新增观众");Cell cell2 = row1.createCell(1);cell2.setCellValue(666);//第二行(2,1)Row row2 = sheet.createRow(1);Cell cell21 = row2.createCell(0);cell21.setCellValue("统计时间");//(2,2)Cell cell22 = row2.createCell(1);String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell22.setCellValue(time);//生成一张表(IO流)FileOutputStream os = new FileOutputStream(path + "观众统计表03.xls");workbook.write(os);//关闭流os.close();System.out.println("生成完毕");

07版换成 XSSFWorkbook对象，文件格式变为xslx
加速版：对07加速SXSSFWorkbook

测试

 @Testpublic void test03() throws Exception {long start = System.currentTimeMillis();Workbook workbook = new HSSFWorkbook();Sheet sheet = workbook.createSheet("sheet1");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);}}FileOutputStream os = new FileOutputStream(path + "03测试.xls");workbook.write(os);os.close();long end = System.currentTimeMillis();System.out.println("03用时：" + (end - start) / 1000);//2秒}@Testpublic void test07() throws Exception {long start = System.currentTimeMillis();Workbook workbook = new XSSFWorkbook();Sheet sheet = workbook.createSheet("sheet1");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);}}FileOutputStream os = new FileOutputStream(path + "07测试.xlsx");workbook.write(os);os.close();long end = System.currentTimeMillis();System.out.println("07用时：" + (end - start) / 1000);//16秒}

07比03慢，所以大文件写用SXSSFWorkbook

原因：过程中会产生临时文件，需要清理临时文件，默认有100条记录保存在内存中，如果超过，则最前面的数据呗写如临时文件中

自定义内存中数据数量：new SXSSFWorkbook(数量)；

测试07加速版

@Testpublic void test07s() throws Exception {long start = System.currentTimeMillis();Workbook workbook = new SXSSFWorkbook();Sheet sheet = workbook.createSheet("sheet1");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);}}FileOutputStream os = new FileOutputStream(path + "07加速版测试.xlsx");workbook.write(os);os.close();//清除临时文件((SXSSFWorkbook)workbook).dispose();long end = System.currentTimeMillis();System.out.println("07加速版用时：" + (end - start) / 1000);//3秒}

注意：例如合并区域，注意等操作仍储存在内存中，广泛使用，需要大量内存。

POI-Excel读

03|07

String path = "D:\\tools\\poi\\";@Testpublic void testRead03() throws Exception {//1.获取文件流FileInputStream is = new FileInputStream(path + "观众统计表03.xls");//2.创建工作薄,放入文件流Workbook workbook = new HSSFWorkbook(is);//3.得到工作表Sheet sheet = workbook.getSheetAt(0);//4.获取行Row row = sheet.getRow(0);//5.获取列Cell cell = row.getCell(0);//6.获取数据String value = cell.getStringCellValue();//字符串类型/*cell.getBooleanCellValue();//布尔类型cell.getDateCellValue();//日期类型cell.getNumericCellValue();//数字类型*/System.out.println(value);//今日新增观众is.close();}

07版只需改变对象即可

读取不同数据类型

@Test//测试读取不同数据类型public void testReadCellType() throws Exception {FileInputStream is = new FileInputStream(path + "观众统计表03.xls");Workbook workbook = new HSSFWorkbook(is);Sheet sheet = workbook.getSheetAt(0);//1.获取标题内容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) {CellType cellType = cell.getCellType();}}}//获取表中内容int rowCount = sheet.getPhysicalNumberOfRows();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++) {Cell cell = rowData.getCell(cellNum);String cellValue = "";//匹配列的数据类型if (cell != null) {//列的类型，低版本返回int类型CellType cellType = cell.getCellType();switch (cellType) {//低版本依赖，case HSSFCell_TYPE_STRING(废弃)，case STRING:System.out.println("[String]");cellValue = cell.getStringCellValue();break;case BOOLEAN:System.out.println("[BOOLEAN]");cellValue = String.valueOf(cell.getBooleanCellValue());break;case BLANK:System.out.println("[BLANK]");cellValue = String.valueOf(cell.getBooleanCellValue());break;case NUMERIC://数字（日期，普通数字）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("普通数字转为字符串"); HSSFDataFormatter hssfDataFormatter = new HSSFDataFormatter(); cellValue = hssfDataFormatter.formatCellValue(cell); //直接toString//cellValue = String.valueOf(cell.getNumericCellValue());//弃用/* cell.setCellType(CellType.STRING); cellValue = cell.toString();*/}break;case ERROR:System.out.println("[数据类型错误]");break;}System.out.println(cellValue);}}}}is.close();}

注意：类型转换问题

计算公式

@Test//公式计算public void testFormula() throws Exception {//文件流FileInputStream is = new FileInputStream(path + "观众统计表03.xls");//创建工作薄Workbook workbook = new HSSFWorkbook(is);//获得工作表Sheet sheet = workbook.getSheetAt(0);//获得行Row row = sheet.getRow(4);//第五行是公式算出//获得列Cell cell = row.getCell(0);//拿到计算公式FormulaEvaluator fe = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);//输入单元格内容CellType cellType = cell.getCellType();switch (cellType) {case FORMULA://公式类型String formula = cell.getCellFormula();System.out.println("公式：" + formula); //计算CellValue evaluate = fe.evaluate(cell);String cellValue = evaluate.formatAsString();System.out.println(cellValue);break;}}

EasyExcel操作

快速，简单避免OOM：OutOfMemory (内存溢出)的java处理excel工具

导入依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.7</version></dependency>

注意：里面已经包含了poi相关，防止依赖冲突，自己项目删除poi相关依赖

写

测试类

String path = "D:\\tools\\poi\\";private List<DemoData> getData() {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);}retu list;}@Testpublic void simpleWrite() {String fileName = path + "easyTest.xlsx";// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭//write(fileName,格式类)//sheet(工作表名)//doWrite(数据)EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(getData());}

读

对象

@Datapublic class DemoData {private String string;private Date date;private Double doubleData;}

监听器

// 有个很重要的点 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>();//也可以是一个serviceprivate DemoDAO demoDAO;public DemoDataListener() {// 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数demoDAO = new DemoDAO();}public DemoDataListener(DemoDAO demoDAO) {this.demoDAO = demoDAO;}//读取数据会执行invoke方法，invoke(数据实体类，分析上下文)@Overridepublic void invoke(DemoData data, AnalysisContext context) {LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));list.add(data);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (list.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listlist.clear();}} //所有数据解析完成会调用此方法@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 这里也要保存数据，确保最后遗留的数据也存储到数据库saveData();LOGGER.info("所有数据解析完成！");} //存入数据库private void saveData() {LOGGER.info("{}条数据，开始存储数据库！", list.size());demoDAO.save(list);LOGGER.info("存储数据库成功！");}}

持久层

 //假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。public class DemoDAO {public void save(List<DemoData> list) {// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入}}

测试代码

@Testpublic void simpleRead() {// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去// 写法1：String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭//sheet()里可以写sheet索引，sheet名字EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();}

多行头

@Test//多行头public void complexHeaderRead() {String fileName = path + "03测试.xls";// 这里 需要指定读用哪个class去读，然后读取第一个sheetEasyExcel.read(fileName, com.jpy.easy.read.DemoData.class, new DemoDataListener()).sheet().headRowNumber(1).doRead();//1就是从第2行开始到最后}