Excel

1 POI

• Apache提供API给java对MicrosoftOffice格式文档的读和写的功能

• HSSF：

  • Excel03版，最大行数65536；一次性从内存写入文档，效率快

  • 文件名后缀以.xls

• XSSF：

  • Excel OOXML07版，无限制，能写入更多数据，效率慢；可能抛出内存溢出问题

  • 文件名后缀.xlsx

  • 优化：新的实现类SXSSF：效率快，占用内存少；会产生用来保存临时数据的临时文件，需要清理即workbook.dispose();

• HWPF：Word

• HSLF：PowerPoint

• HDGF：Visio

• 写：

   public static final String filePath = "E:\\Projects\\security\\";
   @Test
   void writeExcel() throws IOException {
       // 创建工作簿
       // Workbook workbook = new HSSFWorkbook();
       Workbook workbook = new XSSFWorkbook();
       // 创建工作表，表名默认sheet0
       Sheet sheet = workbook.createSheet("数据表");
       // 创建行列
       Row row1 = sheet.createRow(0);
       Cell cell11 = row1.createCell(0);
       cell11.setCellValue("第一行一列");
       Cell cell12 = row1.createCell(1);
       cell12.setCellValue("第一行第二列");
       Cell cell13 = row1.createCell(2);
       cell13.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
       // 生成表（IO流）
       // FileOutputStream fileOutputStream = new FileOutputStream(filePath + "03版本Excel生成测试.xls");
       FileOutputStream fileOutputStream = new FileOutputStream(filePath + "07版本Excel生成测试.xlsx");
       workbook.write(fileOutputStream);
       // (SXSSFWorkbook)workbook.dispose(); // 清除临时文件
       fileOutputStream.close();
   }

• 读：

   @Test
   void readExcel() throws IOException {
       // 获取文件流
       FileInputStream fileInputStream = new FileInputStream(filePath + "03版本Excel生成测试.xls");
       // 创建工作簿-将流转为Workbook对象
       Workbook workbook = new HSSFWorkbook(fileInputStream);
       // Workbook workbook = new XSSFWorkbook();
       // 得到工作表
       Sheet sheet = workbook.getSheetAt(0);
       // 得到行列
       Row row1 = sheet.getRow(0);
       Cell cell11 = row1.getCell(0);
       // 得到不同类型的数据
       String cell11Value = cell11.getStringCellValue();
       System.out.println(cell11Value);
       fileInputStream.close();
   }

• 获取不同类型数据：

   private void readValue(Workbook workbook, Sheet sheet) {
       if (null != sheet) {
           // 表中所有行
           int numberOfRows = sheet.getPhysicalNumberOfRows();
           for (int rowNumber = 0; rowNumber < numberOfRows; rowNumber++) {
               // 一般将标题行和实际内容行分开读取
               Row row = sheet.getRow(0);
               if (null != row) {
                   // 行的所有列
                   int cellCount = row.getPhysicalNumberOfCells();
                   for (int cellNumber = 0; cellNumber < cellCount; cellNumber++) {
                       Cell cell = row.getCell(cellNumber);
                       // 匹配列的数据类型
                       if (null != cell) {
                           CellType cellType = cell.getCellTypeEnum();
                           String cellValue = "";
                           switch (cellType) {
                               case STRING:
                                   cellValue = cell.getStringCellValue();
                                   break;
                               case BLANK:
                                   cellValue = "";
                                   break;
                               case BOOLEAN:
                                   cellValue = String.valueOf(cell.getBooleanCellValue());
                                   break;
                               case NUMERIC: // 数字，分为日期和普通数字
                                   if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                       Date dateCellValue = cell.getDateCellValue();
                                       cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd");
                                   } else {
                                       cell.setCellType(CellType.STRING);
                                       cellValue = cell.toString();
                                   }
                                   break;
                               case FORMULA: // 特殊数据格式，如单元格存在计算公式
                                   FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
                                   String formula = cell.getCellFormula(); // 公式字符串
                                   // 公式计算结果
                                   CellValue evaluate = formulaEvaluator.evaluate(cell);
                                   cellValue = evaluate.formatAsString();
                                   break;
                               case _NONE:
                               case FORMULA:
                               case ERROR:
                               default:
                                   break;
                           }
                       }
                   }
               }
           }
       }
   }

2 EasyExcel

• Alibaba提供解析Excel的工具，简单，快速

• POI写数据到Excel时，先将数据全部加载到内存，再写入文件；在数据庞大时可能导致内存溢出问题；而重写后的EasyExcel读一行写一行，避免了问题

• 编写表头映射的实体类：

   @Data
   public class HeadData {
       @ExcelProperty("value = "姓名", index = "列索引") // 表头；而下面的字段值为这一列的数据
       private String name;
       @ExcelProperty("年龄")
       private String age;
       @ExcelIgnore // 或者类上用@ExcelIgnoreUnanoteated忽略此字段写入Excel
       private String ignore;
   }

• 根据实体类设置数据：

  public List<User> getData() {
      List<User> userList = new ArrayList()<>;
      for (int i = 0; i < rowCount; i++) {
          User data = new User();
          data.setName("标题1下的数据" + i);
          data.setAge("标题2下的数据" + i);
          datas.add(data);
      }
      return datas;
  }

• 把设置好的数据写入Excel：

  public void exportTemplate(HttpServletResponse response) {
      try {
          String fileName = URLEncoder.encode("XXX文件模板" + ExcelTypeEnum.XLSX.getValue(), "UTF-8");
          response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
          response.setContentType(MdediaType.APPLICATION_OCTET_STREAM_VALUE);
          // 导出一个空白模板文件
          EasyExcel.write(response.getOutputStream())
              .head(Collections.singletonList(Collections.singletonList("XXX")))
              .sheet("XXXX").doWrite(Collections.emptyList());
          
          // 导出实际数据
          EasyExcel.write(response.getOutputStream(), User.class)
  				.sheet("XXXX").doWrite(userList);
      } catch(IOException e) {
          ...
      }
  }

• 数据读取：具体查看官方文档

  public void readData() {
      String fileName = filePath + "...xlsx";
      EasyExcel.read(fileName, User.class, new DemoDataListener).sheet().doRead();
  }

• 日期格式或数字处理：可能遇到时间转换异常

  • 字段使用LocalDate或LocalDateTime类型

  • 日期字段少的，可直接使用注解@DataTimeFormat("yyyy-MM-dd HH:mm:ss")

  • 对数字的格式化（long类型太长，如果不格式化的话excel中会以科学计数法显示）则将数据改为字符串类型；或者使用注解@NumberFormat(value = "#")；其他格式如"##.00"

  • 需要格式化的字段较多的，需要自定义实现alibaba.Converter转换器来对时间或数字进行转换

  • 调用EasyExcel.write(response.getOutputStream(), User.class).registerConverter(new LocalDtaTimeConverter()).sheet("...").doWrite(data)；可以同时注册多个Converter

• excel生成并DSM加密：文件名fileName，文件路径filePath；加密文件名（.dsm后缀）dsmFileName，加密文件路径dsmFilePath；结合上面的导出数据实例，在filePath上生成文件；加密则调用提供的加密API(如华为的makeDsmFile(filePath,dsmFilePath)，生成加密文件)；配合SFTP的文件上传下载，将文件上传到服务器；配合流，下载加密文件