Java操作Excel的示例详解
糊涂涂是个小盆友 人气:0java操作Excel数据
在 平时 可以使用IO流对Excle进行操作
但是现在使用更加方便的第三方组件来实现
使用场景
1、将用户信息导出为Excel表格,导入数据
2、将Excel表中的数据录入到网站数据库 (习题上传) 减轻网站的录入量
3、开发中经常会设计到Excel的处理,导入Excel到数据库中
目前最流行的是 Apache POI以及阿里巴巴easyExcel
excel 03 和 07的区别
HSSF 对应excel中的03版本 该版本要求excel中最多只能写65536行
后缀名为 03.xls
XSSF 对应excel中的07版本 该版本对于行数没有要求
后缀名为 07.xlsx
POI
Apache提供的,会比较麻烦,比较原生
开放源码函式库,POI提供API给java程序对Office格式档案读和写的功能
但是存在内存问题 => POI将数据会先写入内存中,一旦写入的内容过多时会产生OOM,也叫做内存溢出
easyExcel
https://github.com/alibaba/easyexcel
对POI进行了一些优化,可以使开发者更加简单,读和写代码只需要1行
存在时间的问题 => easyExcel在写数据时是一行一行往磁盘中写,所以解决了POI的内存问题,但是带来了时间问题
解析excel表中的对象
由于java中万物皆对象,所以需要先观察一张excel表中有哪些对象~
1、工作簿
2、工作表
3、行
4、列 => 单元格
POI使用步骤
第一步:创建Maven项目
第二步:导入依赖
<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> <scope>test</scope> </dependency> </dependencies>
POI 写数据
第一步:基本文件的写入
private String PATH = "E:\\JavaCode\\Maven\\excel-demo\\src";
03版本测试
@Test public void test03() throws Exception { // 1 创建工作簿 Workbook workbook = new HSSFWorkbook(); // 2 创建工作表 Sheet sheet1 = workbook.createSheet("表1"); // 3 创建行 下标从0开始 第一行 Row row1 = sheet1.createRow(0); // 4 创建单元格 (1,1) Cell cell1 = row1.createCell(0); // 5 往第一个单元格填入数据 cell1.setCellValue("今日新加入"); // 6 创建第二个单元格(1,2) Cell cell2 = row1.createCell(1); cell2.setCellValue("统计时间"); // 创建第二行 Row row2 = sheet1.createRow(1); // (2,1) Cell cell3 = row2.createCell(0); cell3.setCellValue("11000"); // (2,2) Cell cell4 = row2.createCell(1); cell4.setCellValue(new DateTime().toString("yyyy-MM-dd hh:mm:ss")); // 生成表的IO流 03 FileOutputStream fos = new FileOutputStream(PATH + "03版本excel.xls"); // 将工作簿写入 workbook.write(fos); // 释放流 fos.close(); System.out.println("创建成功"); }
07版本测试
@Test public void test07() throws Exception { // 1 创建工作簿 Workbook workbook = new SXSSFWorkbook(); //todo // 2 创建工作表 Sheet sheet1 = workbook.createSheet("表1"); // 3 创建行 下标从0开始 第一行 Row row1 = sheet1.createRow(0); // 4 创建单元格 (1,1) Cell cell1 = row1.createCell(0); // 5 往第一个单元格填入数据 cell1.setCellValue("今日新加入"); // 6 创建第二个单元格(1,2) Cell cell2 = row1.createCell(1); cell2.setCellValue("统计时间"); // 创建第二行 Row row2 = sheet1.createRow(1); // (2,1) Cell cell3 = row2.createCell(0); cell3.setCellValue("11000"); // (2,2) Cell cell4 = row2.createCell(1); cell4.setCellValue(new DateTime().toString("yyyy-MM-dd hh:mm:ss")); // 生成表的IO流 03 FileOutputStream fos = new FileOutputStream(PATH + "07版本excel.xlsx"); // 将工作簿写入 workbook.write(fos); // 释放流 fos.close(); System.out.println("创建成功"); }
第二步:大数据写入
HSSF 写入
缺点:最多只能处理65536行数据,否则会抛出异常 java.lang.IllegalArgumentException
优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快
// 03 版本 多数据写入 @Test public void test03BigData() throws Exception { long start = System.currentTimeMillis(); Workbook workbook = new HSSFWorkbook(); Sheet s1 = workbook.createSheet("表1"); for (int row = 0; row < 65536; row++) { Row rows = s1.createRow(row); for (int cell = 0; cell < 10; cell++) { Cell cells = rows.createCell(cell); cells.setCellValue(cell); } } FileOutputStream fos = new FileOutputStream(PATH + "03BigData.xls"); workbook.write(fos); fos.close(); long end = System.currentTimeMillis(); System.out.println("总耗时:" + (double) (end - start) / 1000 + "秒"); }
XSSF 写入
缺点:写数据使速度非常慢,非常耗费内存,也会发生内存溢出 ,如写100万条数据
优点:可以写比HSSF大的数据量,如20万条数据
// 07 版本 低性能 XSSF @Test public void test07BigData() throws Exception { long start = System.currentTimeMillis(); Workbook workbook = new XSSFWorkbook(); Sheet s1 = workbook.createSheet("表1"); for (int row = 0; row < 65536; row++) { Row rows = s1.createRow(row); for (int cell = 0; cell < 10; cell++) { Cell cells = rows.createCell(cell); cells.setCellValue(cell); } } FileOutputStream fos = new FileOutputStream(PATH + "07BigData-XSSF.xlsx"); workbook.write(fos); fos.close(); long end = System.currentTimeMillis(); System.out.println("总耗时:" + (double) (end - start) / 1000 + "秒"); }
SXSSF 写入
优点:可以写非常大的数据量,如100万条,写的速度也非常快,占用更少的内存
SXSSFWorkbook-来至官方的解释∶实现"BigGridDemo"策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存,因为任何时候只有可配置的行部分被保存在内存中。
请注意,仍然可能会消耗大量内存,这些内存基于您正在使用的功能,例如合并区域,注.…….然只存储在内存中,因此如果广泛使用,可能需要大量内存。 可以使用jprofile
来监控
注意
过程中会产生临时文件,需要清理临时文件
默认由100条记录被保存到内存中,如果超过这个数量,则最前面的数据就被写入临时文件,如果想自定义内存中数据的数量,可以使用 new SXSSFWorkbook(数量)
// 07 版本 高性能 SXSS 性能优化 @Test public void test07BigData2() throws Exception { long start = System.currentTimeMillis(); Workbook workbook = new SXSSFWorkbook(); Sheet s1 = workbook.createSheet("表1"); for (int row = 0; row < 65536; row++) { Row rows = s1.createRow(row); for (int cell = 0; cell < 10; cell++) { Cell cells = rows.createCell(cell); cells.setCellValue(cell); } } FileOutputStream fos = new FileOutputStream(PATH + "07BigData-SXSS.xlsx"); workbook.write(fos); fos.close(); // todo 清除临时文件 需要强转类型 ((SXSSFWorkbook) workbook).dispose(); long end = System.currentTimeMillis(); System.out.println("总耗时:" + (double) (end - start) / 1000 + "秒"); }
POI 读数据
在读取excel表格中单元格中的数据的时候,需要注意一点的是:单元格中的数据可以有String类型、Number类型、Date类型等,所以需要通过Switch-case来进行判断获取,否则会报错
private String PATH = "E:\\JavaCode\\Maven\\excel-demo\\src";
不用清除临时文件
HSSF 读数据
@Test public void Read03() throws Exception { // 0、得到文件输入流 FileInputStream fis = new FileInputStream(PATH + "03版本excel.xls"); // 1、工作簿 Workbook workbook = new HSSFWorkbook(fis); // 2.得到表 可以根据索引也可以根据表的名称 Sheet sheet = workbook.getSheetAt(0); // 3、得到行 Row row = sheet.getRow(1); // 4、得到列 锁定单元格 Cell cell = row.getCell(0); // 5、根据类型得到单元格中的内容 String value = cell.getStringCellValue(); System.out.println(value); }
XSSF 读数据
@Test public void Read07() throws Exception { // 0、得到文件输入流 FileInputStream fis = new FileInputStream(PATH + "07版本excel.xls"); // 1、工作簿 Workbook workbook = new XSSFWorkbook(fis); // 2.得到表 可以根据索引也可以根据表的名称 Sheet sheet = workbook.getSheetAt(0); // 3、得到行 Row row = sheet.getRow(1); // 4、得到列 锁定单元格 Cell cell = row.getCell(0); // 5、根据类型得到单元格中的内容 String value = cell.getStringCellValue(); System.out.println(value); }
循环读取多个不同类型的数据
// 读取多个 @Test public void Read03teset02() throws Exception { String path = PATH + "人员.xlsx"; getData(path); } public static void getData(String path){ // 0、得到文件输入流 FileInputStream fis = null; try { fis = new FileInputStream(path); Workbook workbook = new XSSFWorkbook(fis); Sheet sheet = workbook.getSheetAt(0); // 1.拿到第一行所有为String类型的数据 Row row = sheet.getRow(0); if (row != null) { int cellCount = row.getPhysicalNumberOfCells(); // 该行总共的单元格数 for (int cellNum = 0; cellNum < cellCount; cellNum++) { Cell cell = row.getCell(cellNum); if (cell != null) { String cellTitle = cell.getStringCellValue(); System.out.print(cellTitle + "|"); } } System.out.println(""); } // 2.拿到剩下行数中的数据 int rowCount = sheet.getPhysicalNumberOfRows(); // 总行数 for (int rowNum = 1; rowNum < rowCount; rowNum++) { Row rowData = sheet.getRow(rowNum); int cellCount = rowData.getPhysicalNumberOfCells(); //总单元格数 String cellValue = ""; for (int cellNum = 0; cellNum < cellCount; cellNum++) { Cell cell = rowData.getCell(cellNum); if (cell != null) { // 判断类型 int cellType = cell.getCellType(); switch (cellType) { case Cell.CELL_TYPE_STRING: // todo 字符串 cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: // todo 数值 || 日期 if (HSSFDateUtil.isCellDateFormatted(cell)){ // 日期 Date date= cell.getDateCellValue(); cellValue = new DateTime().toString("yyyy-MM-dd hh:mm:ss"); }else{ // 数字 防止数字过长 cell.setCellType(XSSFCell.CELL_TYPE_STRING); cellValue = cell.toString(); } break; case Cell.CELL_TYPE_BOOLEAN: // todo 布尔 boolean boolean_value = cell.getBooleanCellValue(); cellValue = String.valueOf(boolean_value); break; case Cell.CELL_TYPE_BLANK: // todo 为空 System.out.print("[BLANK]"); break; case Cell.CELL_TYPE_ERROR: // todo 数据类型错误 System.out.print("[数据类型错误]"); break; } System.out.print(cellValue+"|"); } } System.out.println(""); } } catch (IOException e) { e.printStackTrace(); }finally { // 3 释放资源 try { fis.close(); } catch (IOException e) { e.printStackTrace(); } } }
计算公式
在excel表格中存在着很多个公式 如:sum、排序、求平均值等。这个时候就需要进行判断。了解即可,需要时可以再看
@Test public void test1() throws Exception { FileInputStream fis = new FileInputStream("E:\\JavaCode\\Maven\\excel-demo\\公式.xls"); Workbook workbook = new HSSFWorkbook(fis); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(2); Cell cell = row.getCell(0); // 得到表中的计算公式 FormulaEvaluator FormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); // 得到单元格的内容 int cellType = cell.getCellType(); switch (cellType){ case Cell.CELL_TYPE_FORMULA: // 公式 // 拿到公式 String cellFormula = cell.getCellFormula(); System.out.println(cellFormula); //计算 CellValue evaluate = FormulaEvaluator.evaluate(cell); System.out.println("evaluate---"+evaluate); // org.apache.poi.ss.usermodel.CellValue [700.0] String value = evaluate.formatAsString(); // 将数据格式化为字符串 System.out.println(value); break; } }
easyExcel读写数据
根据实体类自动生成表
第一步:导入依赖
该依赖中自带了很多种依赖,如lombok、spring-boot等,需要我们在引入依赖时将自己已经导入的依赖删除,不然会报依赖冲突的错误
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.4</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.8</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.75</version> </dependency>
写数据
第二步:创建excel表对应的实体类
@Data public class DemoData { @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; @ExcelProperty("数字标题") private Double aDouble; // 忽略该字段 @ExcelIgnore private String ignore; }
第三步:编写设置数据的方法
使用集合list来写入excel
public List easyTest1(){ 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.setADouble(0.12); list.add(data); } return list; }
第四步:编写测试类
使用链式编写的方式
write(文件路径,excel表对应的java类)
sheet(设置表名)
doWrite(数据)
@Test public void test1(){ String fileName ="E:\\JavaCode\\Maven\\excel-demo\\easyEasyData.xlsx"; EasyExcel.write(fileName,DemoData.class).sheet("表1").doWrite(data()); }
读数据
1、每执行一条excel表中的数据都会执行一次监听文件中的invoke方法,所以如果需要修改可以修改invoke方法中的内容
2、DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
第二步:准备一个对应excel表中字段的类
与写操作中使用同一个类
@Data public class DemoData { @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; @ExcelProperty("数字标题") private Double aDouble; // 忽略该字段 @ExcelIgnore private String ignore; }
第三步:创建数据层 Mapper || Dao
public class DemoDAO { public void save(List<DemoData> list) { // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入 } }
第四步:创建监听器
package excel.readEasy; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.metadata.CellExtra; import com.alibaba.excel.metadata.data.ReadCellData; import com.alibaba.excel.read.listener.ReadListener; import com.alibaba.excel.util.ListUtils; import com.alibaba.fastjson.JSON; import excel.easy.DemoData; import lombok.extern.slf4j.Slf4j; import java.util.List; import java.util.Map; // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去 @Slf4j public 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; // 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数 public DemoDataListener() { demoDAO = new DemoDAO(); } //如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来 public DemoDataListener(DemoDAO demoDAO) { this.demoDAO = demoDAO; } @Override public void onException(Exception exception, AnalysisContext context) throws Exception { } @Override public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) { System.out.println("111"); } //这个每一条数据解析都会来调用 @Override public void invoke(DemoData data, AnalysisContext analysisContext) { System.out.println("2222"); System.out.println(JSON.toJSONString(data)); // log.info("解析到一条数据:{}", JSON.toJSONString(data)); cachedDataList.add(data); // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM if (cachedDataList.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); } } @Override public void extra(CellExtra extra, AnalysisContext context) { } //所有数据解析完成了 都会来调用 @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { saveData(); log.info("所有数据解析完成!"); } @Override public boolean hasNext(AnalysisContext context) { return false; } /** * 加上存储数据库 */ private void saveData() { log.info("{}条数据,开始存储数据库!", cachedDataList.size()); demoDAO.save(cachedDataList); log.info("存储数据库成功!"); } }
第五步:测试
@Test public void test3() { String fileName = "E:\\JavaCode\\Maven\\excel-demo\\easyEasyData.xlsx"; EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> { for (DemoData demoData : dataList) { System.out.println(JSON.toJSONString(demoData)); } })).sheet().doRead(); }
加载全部内容