赞
踩
开发中经常会设计到excel的处理,如导出Excel ,导入Excel到数据库中!
操作ExceI目前比较流行的就是Apache POI
和阿里巴巴的easyExcel
!
Apache POI官网: https://poi.apache.org/
POI是Apache软件基金会的,POI为“Poor Obfuscation Implementation”的首字母缩写,意为“简洁版的模糊实现”。
所以POI的主要功能是可以用Java操作Microsoft Office的相关文件,这里我们主要讲Excel
<!--xls--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <!--xlsx--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</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>
03 | 07 版本的写,就是对象不同,方法一样
需要注意: 2003版本和2007版本存在兼容性的问题! 03最多只有65535行
03版本:
package com.chen; 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; public class ExcelWriteTest { String PATH = "E:\\Directory\\假期\\Poi-EasyExcel\\chen-poi\\"; @Test public void testWrite03() throws Exception { // 1、创建一个工作簿 Workbook workbook = new HSSFWorkbook(); // 2、创建一个工作表 Sheet sheet = workbook.createSheet("复联观众统计表"); // 3、创建一个行 (1,1) Row row1 = sheet.createRow(0); // 4、创建一个单元格 Cell cell11 = row1.createCell(0); cell11.setCellValue("今日新增观众"); // 单元格:(1,1) Cell cell12 = row1.createCell(1); cell12.setCellValue(666); // 单元格:(1,2) // 第二行 Row row2 = sheet.createRow(1); Cell cell21 = row2.createCell(0); cell21.setCellValue("统计时间"); // 单元格:(2,1) Cell cell22 = row2.createCell(1); String time = new DateTime().toString("yyyy-MM-dd hh:mm:ss"); cell22.setCellValue(time); // 单元格:(2,2) // 生成一张表(IO流) 03版本就是xls结尾! FileOutputStream fileOutputStream = new FileOutputStream(PATH + "复联观众统计表03.xls"); workbook.write(fileOutputStream); // 关闭流 fileOutputStream.close(); System.out.println("生成完毕"); } }
07版本:
package com.chen; 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.FileNotFoundException; import java.io.FileOutputStream; public class ExcelWriteTest { String PATH = "E:\\Directory\\假期\\Poi-EasyExcel\\chen-poi\\"; @Test public void testWrite07() throws Exception { // 1、创建一个工作簿 Workbook workbook = new XSSFWorkbook(); // 2、创建一个工作表 Sheet sheet = workbook.createSheet("复联观众统计表"); // 3、创建一个行 (1,1) Row row1 = sheet.createRow(0); // 4、创建一个单元格 Cell cell11 = row1.createCell(0); cell11.setCellValue("今日新增观众"); // 单元格:(1,1) Cell cell12 = row1.createCell(1); cell12.setCellValue(666); // 单元格:(1,2) // 第二行 Row row2 = sheet.createRow(1); Cell cell21 = row2.createCell(0); cell21.setCellValue("统计时间"); // 单元格:(2,1) Cell cell22 = row2.createCell(1); String time = new DateTime().toString("yyyy-MM-dd hh:mm:ss"); cell22.setCellValue(time); // 单元格:(2,2) // 生成一张表(IO流) 07版本就是xlsx结尾! FileOutputStream fileOutputStream = new FileOutputStream(PATH + "复联观众统计表07.xlsx"); workbook.write(fileOutputStream); // 关闭流 fileOutputStream.close(); System.out.println("生成完毕"); } }
注意对象的一个区别,文件后缀!
缺点:最多只能处理65536行,否则会抛出异常
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快
@Test public void testWrite03BidData() 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++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite03BidData.xls"); workbook.write(outputStream); outputStream.close(); long end = System.currentTimeMillis(); System.out.println((double)(end-begin)/1000); }
缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条
优点:可以写比较大的数据,如20万条
//耗时较长 @Test public void testWrite07BidData() throws Exception { // 时间 long begin = System.currentTimeMillis(); // 创建一个簿 Workbook workbook = new XSSFWorkbook(); // 创建表 Sheet sheet = workbook.createSheet(); // 写入数据 for (int rowNum = 0; rowNum < 100000; rowNum++) { Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite07BidData.xlsx"); workbook.write(outputStream); outputStream.close(); long end = System.currentTimeMillis(); System.out.println((double)(end-begin)/1000); }
优点:可以写非常大的数据,如100万条甚至更多,写数据速度快,占用更少的内存
注意:
过程中会产生临时文件,需要清理临时文件
默认由100条记录被保存再内存中,如果超过这个数量,则最前面的数据被写入临时文件
如果想自定义内存中数据的数量,可以使用new SXSSFWorkbook(数量)
@Test public void testWrite07BidDataS() throws Exception { // 时间 long begin = System.currentTimeMillis(); // 创建一个簿 Workbook workbook = new SXSSFWorkbook(); // 创建表 Sheet sheet = workbook.createSheet(); // 写入数据 for (int rowNum = 0; rowNum < 100000; rowNum++) { Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("over"); FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite07BidDataS.xlsx"); workbook.write(outputStream); outputStream.close(); // 清楚临时文件 ((SXSSFWorkbook) workbook).dispose(); long end = System.currentTimeMillis(); System.out.println((double)(end-begin)/1000); }
SXSSFWorkbook-来至官方的解释:实现"BigGridDemo"策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存,因为任何时候只有可配置的行部分被保存在内存中。
请注意,仍然可能会消耗大量内存,这些内存基于您正在使用的功能,例如合并区域,注释…仍然只存储在内存中,因此如果广泛使用,可能需要大量内存。
03版本
@Test public void testWrite03() throws Exception { // 1、获取文件流 FileInputStream inputStream = new FileInputStream(PATH + "复联观众统计表03.xls"); // 2、创建一个工作簿 使用Excel能操作的,他这里都能操作 Workbook workbook = new HSSFWorkbook(inputStream); // 3、得到表 Sheet sheet = workbook.getSheetAt(0); // 4、得到行 Row row = sheet.getRow(0); // 5、得到列 Cell cell = row.getCell(0); // 读取值得时候,一定需要注意类型 // getStringCellValue 字符串类型 System.out.println(cell.getStringCellValue()); inputStream.close(); }
07版本
@Test public void testWrite07() throws Exception { // 1、获取文件流 FileInputStream inputStream = new FileInputStream(PATH + "复联观众统计表07.xlsx"); // 2、创建一个工作簿 使用Excel能操作的,他这里都能操作 Workbook workbook = new XSSFWorkbook(inputStream); // 3、得到表 Sheet sheet = workbook.getSheetAt(0); // 4、得到行 Row row = sheet.getRow(0); // 5、得到列 Cell cell = row.getCell(0); // 读取值得时候,一定需要注意类型 // getStringCellValue 字符串类型 System.out.println(cell.getStringCellValue()); inputStream.close(); }
@Test public void testCellType() throws IOException { // 1、获取文件流 FileInputStream inputStream = new FileInputStream(PATH + "会员消费商品明细表.xls"); // 2、创建一个工作簿 使用Excel能操作的,他这里都能操作 Workbook workbook = new HSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); // 3、获取标题内容 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.getCellTypeEnum(); String cellValue = cell.getStringCellValue(); System.out.print(cellValue+" | "); } } System.out.println(); } // 获取表中得内容 int rowCount = sheet.getPhysicalNumberOfRows(); for (int rowNum = 0; 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) + "]"); Cell cell = rowData.getCell(cellNum); //匹配列得数据类型 if (cell!=null){ CellType cellType = cell.getCellTypeEnum(); String cellValue = ""; switch (cellType){ case STRING: // 字符串 System.out.print("【字符串】"); cellValue = cell.getStringCellValue(); break; case BOOLEAN: // 布尔 System.out.print("【BOOLEAN】"); cellValue = String.valueOf(cell.getBooleanCellValue()); break; case BLANK: // 空 System.out.print("【BLANK】"); break; case _NONE: break; case NUMERIC: // 数字(日期、普通数字) System.out.print("【NUMERIC】"); if(HSSFDateUtil.isCellDateFormatted(cell)){ //日期 System.out.println("【日期】"); Date date = cell.getDateCellValue(); cellValue = new DateTime(date).toString("yyyy-MM-dd hh:mm:ss"); }else { // 如果不是日期格式,防止数字过长! System.out.println("【普通数字转换为字符串输出】"); cell.setCellType(CellType.STRING); cellValue = cell.toString(); } break; case ERROR: // 布尔 System.out.print("【数据类型错误】"); break; case FORMULA: break; } System.out.println(cellValue); } } } } inputStream.close(); }
@Test public void testFormula() throws IOException { FileInputStream inputStream = new FileInputStream(PATH+"计算公式.xls"); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(4); Cell cell = row.getCell(0); // 拿到计算公式 FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); // 输出单元格的内容 CellType cellType = cell.getCellTypeEnum(); switch (cellType){ case FORMULA: // 公式 String formula = cell.getCellFormula(); System.out.println(formula); // 计算 CellValue evaluate = formulaEvaluator.evaluate(cell); String cellValue = evaluate.formatAsString(); System.out.println(cellValue); break; } }
easyExcel官网地址: https://github.com/alibaba/easyexcel
EasyExcel是阿里巴巴开源的一个excel处理框架 ,以使用简单、节省内存著称。
EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上-行行读取数据,逐个解析。
下图是EasyExcel和POI在解析Excel时的对比图。
官方文档: https://www.yuque.com/easyexcel/doc/easyexcel
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
@Data
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/*
* 忽略这个字段
* */
@ExcelIgnore
private String ignore;
}
public class EasyTest { String PATH = "E:\\Directory\\假期\\Poi-EasyExcel\\chen-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; } @Test public void simpleWrite() { // 写法1 String fileName =PATH+ "EasyTest.xlsx"; // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭 // write (fileName, 格式类) // sheet (表名) // doWrite (数据) EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data()); }
package com.chen.east; 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; } // 读取数据会执行这个方法 // DemoData 读取类型 // AnalysisContext 分析上下文 @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("存储数据库成功!"); } }
@Test
public void simpleRead() {
// 写法1
String fileName =PATH+ "EasyTest.xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// write (fileName, 格式类)
// sheet (表名)
// doWrite (数据)
EasyExcel.read(fileName, DemoData.class,new DemoDataListener()).sheet("模板").doRead();
}
package com.chen.east;
import java.util.List;
/**
* 假设这个是你的DAO存储。当然还要这个类让spring管理,当然你不用需要存储,也不需要这个类。
**/
public class DemoDAO {
public void save(List<DemoData> list) {
// 持久化操作
// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
}
}
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。