POI

需要的约束

<dependencies>
    <!-- 操作03版本excel  XLS格式 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>
    <!-- 操作07版本excel  XLSX格式 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
</dependencies>

写入

03版本

这个版本最多写入65536行,但是速度较07版本更快

public void testWrite03() throws IOException {
    // 创建一个工作簿
    Workbook workbook = new HSSFWorkbook();
    // 创建一个工作表
    Sheet sheet = workbook.createSheet("1号表格");
    // 创建第一行
    Row row = sheet.createRow(0);
    // 第一行的第一个单元格
    Cell cell = row.createCell(0);
    // 给这个单元格写入数据
    cell.setCellValue("今日天气");
    // 文件输出流,生成一个真实的表 03版本必须以.xls结尾
    FileOutputStream fos = new FileOutputStream(PATH + "一号统计表.xls");
    workbook.write(fos);
    // 关闭流
    fos.close();
}

07版本只需要修改以下内容即可,其余一致

Workbook workbook = new XSSFWorkbook();
FileOutputStream fos = new FileOutputStream(PATH + "一号统计表.xlsx");

大文件写入

较之前速度更快,但是会生成临时文件需要删除

使用SXSSFWorkbook

public void testWrite07() throws IOException {
    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    for (int rowNum = 0; rowNum < 65556; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 11; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(cellNum);
        }
    }
    FileOutputStream fos = new FileOutputStream(PATH + "testExcel2.xls");
    workbook.write(fos);
    fos.close();
    //清除临时文件
    ((SXSSFWorkbook) workbook).dispose();
}

读取

public void testRead07() throws IOException {
    // 获取文件输入流
    FileInputStream fis = new FileInputStream(PATH + "一号统计表.xlsx");
    // 和写一样 .xlsx格式使用XSSFWorkbook .xls格式使用HSSFWorkbook
    Workbook workbook = new XSSFWorkbook(fis);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);
    fis.close();
    // 注意数据类型,与实际不匹配会出现异常
    System.out.println(cell.getStringCellValue());
}

类型判断

与类型不符会出现异常

int cellType = cell.getCellType(); //拿到该单元格数据类型代表的数值
String cellValue = "";
switch (cellType) {  // 然后与接口中对应的值进行匹配
    case Cell.CELL_TYPE_STRING: //字符串
        cellValue = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_BOOLEAN: //布尔
        cellValue = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_BLANK: //为空
        break;
    case Cell.CELL_TYPE_NUMERIC: //数字
        // 判断数字是否为日期
        if (HSSFDateUtil.isCellInternalDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            cellValue = new DateTime(date).toString("yyyy-MM-dd");
        } else {
            // 转换为字符串
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cellValue = cell.toString();
        }
        break;
    case Cell.CELL_TYPE_ERROR: //发生错误
        System.out.print("[发生错误]");
        break;
}

计算

public void testFormula() throws IOException {
    FileInputStream fis = new FileInputStream(PATH + "公式.xls");
    Workbook workbook = new HSSFWorkbook(fis);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(4);
    Cell cell = row.getCell(0);
    // 计算公式对象
    FormulaEvaluator fe = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
    int cellType = cell.getCellType();
    if (cellType == Cell.CELL_TYPE_FORMULA) { //公式
    // 拿到表格中的计算公式 如sum()
    String formula = cell.getCellFormula();
    CellValue evaluate = fe.evaluate(cell);
    // 通过公式进行计算
    String cellValue = evaluate.formatAsString();
}

web项目中使用

@GetMapping("/sendExcel")
public void sendExcel(
  HttpServletResponse response,
  @RequestParam(value = "count", required = true) Integer count) throws IOException {
  response.addHeader("Content-Type",
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  response.addHeader("Content-Disposition", "attachment;filename="+ filename +"+.xlsx");
 // ByteArrayOutputStream 对象,内部的代码没记录丢了... 
  ByteArrayOutputStream ops = null;
  response.getOutputStream().write(ops.toByteArray());
  // 需要重写close
  ops.close();
}

EasyExcel

easyexcel底层使用的就是POI

需要的约束

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.0-beta2</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.8</version>
</dependency>

定义一个实体类

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

写数据

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;
}
public void simpleWrite() {
    String fileName = PATH + "EasyTes.xlsx";
    // 文件流会自动关闭
    // write 文件名和实体类(标题列) sheet 表名 dowrite 数据
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}

web项目中使用多个sheet

@GetMapping("sendExcel")
public void downLoad(HttpServletResponse response,
                     @RequestParam(value = "count", defaultValue = "180") Integer count,
                     @RequestParam(value = "count2", defaultValue = "60") Integer count2)
  throws Exception {
  response.setContentType("application/vnd.ms-excel");
  response.setCharacterEncoding("UTF-8");
  String fileName = URLEncoder.encode("FileName", "UTF-8");
  response.setHeader("Content-disposition","attachment;filename=" + fileName + ".xlsx");

  ExcelWriter excelWriter = null;
  try {
    excelWriter = EasyExcel.write(response.getOutputStream())
      .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
      .build();
    WriteSheet writeSheet = EasyExcel.writerSheet(0,"sheet1").head(Pojo.class).build();
    // List集合 
    List<Pojo> datas = null;
    excelWriter.write(datas, writeSheet);

    WriteSheet writeSheet2 = EasyExcel.writerSheet(1,"sheet2").head(Pojo2.class).build();
    List<Pojo2> datas2 = null;
    excelWriter.write(datas2, writeSheet2);
    // ...
  } finally {
      // 多sheet需要手动关闭
    if (excelWriter != null) {
      excelWriter.finish();
    }
  }
}

准备监听器

配置EasyExcel用于读取excel的类

/**
 * DemoDataListener 不能被spring管理,要每次读取excel都要new,
 * 然后里面用到spring可以构造方法传进去
 */
public class DemoDataListener extends AnalysisEventListener<DemoData> {
    //每隔x条存储数据库,然后清理list,方便内存回收
    private static final int BATCH_COUNT = 5;
    //存储的List
    List<DemoData> list = new ArrayList<>();
    //业务中使用的Dao当然也可以是service
    private DemoDAO demoDAO;
    private DemoDataListener() {}
    //每次实例化的时候需要把spring管理的类传进来
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    //每一条数据解析都会调用
    // DemoData 数据实体类 AnalysisContext 分析上下路
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println("解析到一条数据" + JSON.toJSONString(data));
        list.add(data);
        // 防止内存泄漏
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }
    //所有数据解析完成来调用
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据,确保最后遗留的数据也存储到数据库
        saveData();
        System.out.println("所有数据解析完成!");
    }
    // 持久化操作
    private void saveData() {
        System.out.println("开始存储数据库!" + list.size());
        demoDAO.save(list); 
        System.out.println("存储数据库成功!");
    }
}

读取数据

public void simpleRead() {
    String fileName = PATH + "EasyTest.xlsx";
    // 读取的文件名称,实体类,监听器
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener(demoDao))
        .sheet()
        .doRead();
}