POI和easyExcel

POI和easyExcel

常用信息

1. 将用户信息导出Excel表格（导出数据）

2. 将Excel表中的信息录入到网站数据库（习题上传），大大减轻网站的录入量

Apache POI

1. 介绍

   Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

2. 基本功能

   HSSF － 提供读写Microsoft Excel格式档案的功能。（03版本，最多支持行数65535行，后缀名.xls） XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。（07版本，无限制，后缀名.xlsx） HWPF － 提供读写Microsoft Word格式档案的功能。 HSLF － 提供读写Microsoft PowerPoint格式档案的功能。 HDGF － 提供读写Microsoft Visio格式档案的功能。

EasyExcel

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

easyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节约内存著称。

EasyExcel能够大大减少占用内存的主要原因是在解析excel时没有将文件数据一次性全部加载到内存中，而是从磁盘里一行行读取数据，逐个解析。

POI

1. 使用说明

   创建一个工作簿-->workbook

   一个工作簿中有多个表 -->sheet

   表中的每一行 -->row

   表中的每一列 -->column

   行与列组成了单元格 -->cell

2. 使用的依赖

   org.apache.poi - poi - 3.9

   org.apache.poi - poi-ooxml - 3.9

POI-Excel写

03 | 07

03版本

 public void testExport() throws Exception {
         //表头部分
         //1.创建工作簿
         HSSFWorkbook hwb = new HSSFWorkbook();
         //2.创建一张表
         HSSFSheet userSheet = hwb.createSheet("userInfo");
         //3.创建三行row
         HSSFRow row1 = userSheet.createRow(0);
         //4.创建单元格
         HSSFCell row1Cell1 = row1.createCell(0);
         HSSFCell row1Cell2 = row1.createCell(1);
         //5.合并单元格
         userSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
         //6.插入数据
         row1Cell1.setCellValue("学生信息");
 ​
         //列名部分
         HSSFRow row2 = userSheet.createRow(1);
         HSSFCell row2Cell1 = row2.createCell(0);
         HSSFCell row2Cell2 = row2.createCell(1);
         row2Cell1.setCellValue("姓名");
         row2Cell2.setCellValue("年龄");
 ​
         //表数据部分
         HSSFRow row3 = userSheet.createRow(2);
         HSSFCell row3Cell1 = row3.createCell(0);
         HSSFCell row3Cell2 = row3.createCell(1);
         row3Cell1.setCellValue("张三");
         row3Cell2.setCellValue(23);
 ​
         HSSFRow row4 = userSheet.createRow(3);
         HSSFCell row4Cell1 = row4.createCell(0);
         HSSFCell row4Cell2 = row4.createCell(1);
         row4Cell1.setCellValue("李四");
         row4Cell2.setCellValue(24);
 ​
         HSSFRow row5 = userSheet.createRow(4);
         HSSFCell row5Cell1 = row5.createCell(0);
         HSSFCell row5Cell2 = row5.createCell(1);
         row5Cell1.setCellValue("王五");
         row5Cell2.setCellValue(25);
 ​
         //把数据导入到服务器指定的位置中去
         File file = new File("C:\Users\zxc98\Desktop\user.xls");
         FileOutputStream fos = new FileOutputStream(file);
         hwb.write(fos);
 }

07版本

 public void testWrite07() throws IOException {
 ​
     // 创建新的Excel 工作簿, 只有对象变了
     Workbook workbook = new XSSFWorkbook();
 ​
     // 如要新建一名为"user"的工作表，其语句为：
     Sheet sheet = workbook.createSheet("user");
 ​
     // 创建行（row 1）
     Row row1 = sheet.createRow(0);
 ​
     // 创建单元格（col 1-1）
     Cell cell11 = row1.createCell(0);
     cell11.setCellValue("今日新增关注");
 ​
     // 创建单元格（col 1-2）
     Cell cell12 = row1.createCell(1);
     cell12.setCellValue(666);
 ​
     // 创建行（row 2）
     Row row2 = sheet.createRow(1);
 ​
     // 创建单元格（col 2-1）
     Cell cell21 = row2.createCell(0);
     cell21.setCellValue("统计时间");
 ​
     //创建单元格（第三列）
     Cell cell22 = row2.createCell(1);
     String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
     cell22.setCellValue(dateTime);
 ​
     // 新建一输出文件流（注意：要先创建文件夹）
     FileOutputStream out = new FileOutputStream(path + "user统计表07.xlsx");
     // 把相应的Excel 工作簿存盘
     workbook.write(out);
     // 操作结束，关闭文件
     out.close();
 ​
     System.out.println("文件生成成功");
 }

区别：创建的对象不一样，生成的文件的后缀名不一样

大文件写-HSSF（03版）

缺点：最多只能处理65536.否则会抛出异常

java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)

优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快

大文件写-XSSF（07版）

缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出

优点：可以写较大的数据量

大文件写-SXSSF（07版的优化）

优点：可以写非常大的数据量，如百万条甚至更多，写数据速度非常的快，占用更少的内存

注意：

• 过程中会产生临时文件，需要清理

• 默认由100条记录被保存在内存中，如果超过这个数量，则最前面的数据被写入临时文件

• 如果想自定义内存中数据的量，可以使用new SXSSFWorkbook（数量）

 public void testWrite07BigDataFast() throws IOException {
         //记录开始时间
         long begin = System.currentTimeMillis();
 ​
         //创建一个SXSSFWorkbook
         Workbook workbook = new SXSSFWorkbook();
 ​
         //创建一个sheet
         Sheet sheet = workbook.createSheet();
 ​
         //xls文件最大支持65536行
         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("done");
         FileOutputStream out = new FileOutputStream(path + "bigdata07-fast.xlsx");
         workbook.write(out);
         // 操作结束，关闭文件
         out.close();
 ​
         //清除临时文件
         ((SXSSFWorkbook) workbook).dispose();
 ​
         //记录结束时间
         long end = System.currentTimeMillis();
         System.out.println((double) (end - begin) / 1000);
     }
 ​
 }

POI-Excel读

03 | 07

03版本

  public void testImport() throws Exception {
         //从指定文件路径获取文件
         File file = new File("C:\Users\zxc98\Desktop\user.xls");
         FileInputStream fis = new FileInputStream(file);
         //1.创建工作簿
         HSSFWorkbook hwb = new HSSFWorkbook(fis);
         //2.从工作簿中读取第一页数据
         //2.1 根据表名读取
         //HSSFSheet userInfo = hwb.getSheet("userInfo");
         //2.2 根据索引读取
         HSSFSheet userSheet = hwb.getSheetAt(0);
         //3.从表里面读取行数据
         //3.1 创建一个存储空间
         List<User> users = new ArrayList<>();
         for (Row row : userSheet) {
             int rowNum = row.getRowNum();
             if (rowNum < 2) {
                 continue;
             }
             //成行读取
             String username = row.getCell(0).getStringCellValue();
             int age = (int) row.getCell(1).getNumericCellValue();
             users.add(new User(username, age));
         }
         for (User user : users) {
             System.out.println(user);
         }
 }

07版本

 public void testRead07() throws Exception {
     InputStream is = new FileInputStream(path + "user.xlsx");
 ​
     Workbook workbook = new XSSFWorkbook(is);
     Sheet sheet = workbook.getSheetAt(0);
 ​
     // 读取第一行第一列
     Row row = sheet.getRow(0);
     Cell cell = row.getCell(0);
 ​
     // 输出单元内容
     System.out.println(cell.getStringCellValue());
 ​
     // 操作结束，关闭文件
     is.close();
 }

读取不同的数据类型

 public void testCellType() throws Exception {
 ​
         InputStream is = new FileInputStream(path + "会员消费商品明细表.xls");
         Workbook workbook = new HSSFWorkbook(is);
         Sheet sheet = workbook.getSheetAt(0);
 ​
         // 读取标题所有内容
         Row rowTitle = sheet.getRow(0);
         if (rowTitle != null) {// 行不为空
             // 读取cell，获取当前行的所有单元格
             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.print(cellValue + "|");
                 }
             }
             System.out.println();
         }
 ​
         // 读取商品列表数据
         int rowCount = sheet.getPhysicalNumberOfRows();
         for (int rowNum = 1; rowNum < rowCount; rowNum++) {
 ​
             Row rowData = sheet.getRow(rowNum);
             if (rowData != null) {// 行不为空
 ​
                 // 读取cell
                 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://字符串
                                 System.out.print("【STRING】");
                                 cellValue = cell.getStringCellValue();
                                 break;
 ​
                             case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                                 System.out.print("【BOOLEAN】");
                                 cellValue = String.valueOf(cell.getBooleanCellValue());
                                 break;
 ​
                             case HSSFCell.CELL_TYPE_BLANK://空
                                 System.out.print("【BLANK】");
                                 break;
 ​
                             case HSSFCell.CELL_TYPE_NUMERIC:
                                 System.out.print("【NUMERIC】");
                                 //cellValue = String.valueOf(cell.getNumericCellValue());
 ​
                                 if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
                                     System.out.print("【日期】");
                                     Date date = cell.getDateCellValue();
                                     cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                 } else {
                                     // 不是日期格式，则防止当数字过长时以科学计数法显示
                                     System.out.print("【转换成字符串】");
                                     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                     cellValue = cell.toString();
                                 }
                                 break;
 ​
                             case Cell.CELL_TYPE_ERROR:
                                 System.out.print("【数据类型错误】");
                                 break;
                         }
 ​
                         System.out.println(cellValue);
                     }
                 }
             }
         }
 ​
         is.close();
     }

计算公式

 public void testFormula() throws Exception {
 ​
     InputStream is = new FileInputStream(path + "计算公式.xls");
 ​
     Workbook workbook = new HSSFWorkbook(is);
     Sheet sheet = workbook.getSheetAt(0);
 ​
     // 读取第五行第一列
     Row row = sheet.getRow(4);
     Cell cell = row.getCell(0);
 ​
     //公式计算器
     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 = String.valueOf(evaluate.getNumberValue());
             String cellValue = evaluate.formatAsString();
             System.out.println(cellValue);
 ​
             break;
     }
 }

文件的上传与下载

数据库获取数据生成Excel并下载

 @RequestMapping("/export.do")
     public void downloadExcel(HttpServletResponse response)throws Exception{
         //首先从数据库取出相对应条件的数据
         List<User> users = userService.queryAllUser();
         //创建工作簿
         HSSFWorkbook hwb = new HSSFWorkbook();
         HSSFSheet userSheet = hwb.createSheet("第一张用户表");
         //创建行与列
         HSSFRow row1 = userSheet.createRow(0);
         row1.createCell(0).setCellValue("用户信息");
         //合并单元格
         userSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
         //创建第二行表头
         HSSFRow row2 = userSheet.createRow(1);
         row2.createCell(0).setCellValue("id");
         row2.createCell(1).setCellValue("姓名");
         row2.createCell(2).setCellValue("性别");
         row2.createCell(3).setCellValue("邮箱");
         //往Excel输入数据，迭代遍历users
         for (int i = 0; i < users.size(); i++) {
             HSSFRow row = userSheet.createRow(i+2);
             row.createCell(0).setCellValue(users.get(i).getuId());
             row.createCell(1).setCellValue(users.get(i).getuName());
             row.createCell(2).setCellValue(users.get(i).getuSex());
             row.createCell(3).setCellValue(users.get(i).getuEmail());
         }
         //导出Excel表格
         OutputStream os = response.getOutputStream();
         //设置响应头
         response.setHeader("Content-Disposition", "attachment;filename=user.xls");
         //设置响应的数据类型
         response.setContentType("application/msexcel");
         hwb.write(os);
         hwb.close();
 }

web上传Excel文件

需要上传方法上添加一个属性：@RequestParam("excelFile") MultipartFile

EasyExcel操作

导入依赖

<!--easyexcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.7</version>
</dependency>
<!--lombok-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
</dependency>
<!--fastjson-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.62</version>
</dependency>

快速开始

写Excel

1. 创建pojo

   @Data
   public class DemoData {
       @ExcelProperty("字符串标题")
       private String string;
       @ExcelProperty("日期标题")
       private Date date;
       @ExcelProperty("数字标题")
       private Double doubleData;
       /**
        * 忽略这个字段
        */
       @ExcelIgnore
       private String ignore;
   }

2. 写Excel

   方式一

   String fileName = "***.xlsx";
   // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
   // 如果这里想使用03 则 传入excelType参数即可
   //write(文件名、pojo类)
   //sheet(表名)
   //doWrite(List<pojo类>的数据)
   EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

   方式二

读Excel

1. 需要一个监听器

   // 有个很重要的点 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>();
       /**
        * 假设这个是一个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
        */
       @Override
       public void invoke(DemoData data, AnalysisContext context) {
           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("存储数据库成功！");
       }
   }

2. 提供持久层操作

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

3. 读Excel

   方式一

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

   方式二