easyexcel java 导出模版 java导出excel工具

转载

mob64ca140f29e5 2023-11-08 17:27:02

文章标签 easyexcel java 导出模版 easyexcel poi excel apache 文章分类 Java 后端开发

前言

数据导出excel，一般有jxl，poi，在这两者中，poi性能更优。
今天推荐一款性能更优，更强大的工具EasyExcel，提供了各种便捷的API，以及定制化功能。

可以EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。（官方说明）

实例

分别定义两个接口，一个使用Apache POI的方式进行导出，另一个使用EasyExcel组件导出；数据样本分别是百量级别、千量级别、万量级别，从而观察两者组装数据的时间，导出excel的时间。（PS：JVM内存方面，感兴趣的可以自己尝试查看）

一、Apache POI

1、maven依赖

<!-- POI start -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.1.0</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.0</version>
    </dependency>
    <!-- POI end -->

2、导出工具类

package com.xiu.core.common.util;

import com.alibaba.fastjson.JSONObject;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.stream.IntStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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;

/**
 * @Author: MR.xiu
 * @Description: POI，Excel导出工具类
 * @Date: 2020/3/25
 */
public interface ExportExcelUtil<T> {

  /**
   * 将字节数组写出到servlet输出流
   *
   * @param response http回应对象，为excel回应的目的地
   * @param list 要导出到 excel的数据集合
   * @param titles excel的标题 通常取第一行作为excel的标题
   * @param fileName excel的文件名称
   */
  default void exportExcel(HttpServletResponse response, List<T> list, String[] titles, String fileName)
      throws IOException {
    byte[] bytes = selectExcel(list, titles);
    fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
    response.setContentType("application/x-msdownload");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
    response.setContentLength(bytes.length);
    response.getOutputStream().write(bytes);
    response.getOutputStream().flush();
    response.getOutputStream().close();
  }

  /**
   * 选择要导出的文件 导出的excel 属于office 2007格式的文件
   */
  default byte[] selectExcel(List<T> list, String[] titles) throws IOException {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    CellStyle headerStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontHeightInPoints((short) 15);
    font.setColor(HSSFFont.COLOR_NORMAL);
    font.setFontName("黑体");
    font.setBold(true);
    headerStyle.setFont(font);
    headerStyle.setAlignment(HorizontalAlignment.CENTER);
    headerStyle.setWrapText(true);

    generateExcelTitle(headerStyle, titles, sheet);
    eachListAndCreateRow(list, sheet);
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    workbook.write(out);
    return out.toByteArray();
  }

  /**
   * 遍历集合，并创建单元格行
   *
   * @param list 数据集合
   * @param sheet 工作簿
   */
  default void eachListAndCreateRow(List<T> list, Sheet sheet) {
    IntStream.range(0, list.size()).forEach(i -> generateExcelForAs(list.get(i), sheet.createRow((i + 1))));
    for (int k = 0; k < list.size(); k++) {
      sheet.autoSizeColumn(k);
    }
    setSizeColumn(sheet, list.size());
  }

  /**
   * 表格自适应
   */
  default void setSizeColumn(Sheet sheet, int size) {
    for (int columnNum = 0; columnNum < size; columnNum++) {
      int columnWidth = sheet.getColumnWidth(columnNum) / 256;
      for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
        Row currentRow;
        //当前行未被使用过
        if (sheet.getRow(rowNum) == null) {
          currentRow = sheet.createRow(rowNum);
        } else {
          currentRow = sheet.getRow(rowNum);
        }

        if (currentRow.getCell(columnNum) != null) {
          Cell currentCell = currentRow.getCell(columnNum);
          if (currentCell.getCellType() == CellType.STRING) {
            int length = currentCell.getStringCellValue().getBytes().length;
            if (columnWidth < length) {
              columnWidth = length;
            }
          }
        }
      }
      sheet.setColumnWidth(columnNum, columnWidth * 256);
    }
  }

  /**
   * 生成excel文件的标题
   */
  default void generateExcelTitle(CellStyle cellStyle, String[] titles, Sheet sheet) {
    Row row = sheet.createRow(0);
    for (int i = 0; i < titles.length; i++) {
      Cell cell = row.createCell(i);
      cell.setCellValue(titles[i]);
      cell.setCellStyle(cellStyle);
    }
  }

  /**
   * 创建excel内容文件
   *
   * @param t 组装excel 文件的内容
   * @param row 当前excel 工作行
   */
  void generateExcelForAs(T t, Row row);

  /**
   * 当发生错误时如此回应信息
   */
  default void errorResponse(HttpServletResponse response, String msg) {
    byte[] message = JSONObject.toJSONString(msg).getBytes();
    response.setContentType("text/json;charset=UTF-8");
    response.setContentLength(message.length);
    try {
      response.getOutputStream().write(message);
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
}

3、实验接口

@GetMapping("/export")
  public void export(HttpServletResponse response) {
    log.info("<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<测试导出耗时apache poi 开始>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
    long beginTime = System.currentTimeMillis();
    try {
      List<BizRulePo> dataList = bizRuleMapper.findAll(null);
      log.info("数据库数据量[{}],查询耗时[{}]ms",dataList.size(), (System.currentTimeMillis() - beginTime));
      if (CollectionUtils.isNotEmpty(dataList)) {
        String[] excelTitle = new String[]{"主键", "名称", "权重", "组合规则", "创建时间"};
        String fileName = "审核规则汇总".concat(DateUtils.formatDate(new Date(),"yyyyMMdd"));
        ((ExportExcelUtil<BizRulePo>) (obj, row) -> {
          row.createCell(0, CellType.STRING).setCellValue(obj.getId());
          row.createCell(1, CellType.STRING).setCellValue(obj.getName());
          row.createCell(2, CellType.STRING).setCellValue(obj.getPriority());
          row.createCell(3, CellType.STRING).setCellValue(obj.getCompositeType());
          row.createCell(4, CellType.STRING).setCellValue(DateUtils.formatDate(obj.getCreateTime()));
        }).exportExcel(response, dataList, excelTitle, fileName);
        log.info("组装耗时[{}]ms", (System.currentTimeMillis() - beginTime));
      }
    } catch (Exception e) {
      //默认返回
      ((ExportExcelUtil<BizRulePo>) (obj, row) -> {
      }).errorResponse(response, e.getMessage());
      log.error("/bizRule export fail,", e);
    }
    log.info("导出耗时[{}]ms", (System.currentTimeMillis() - beginTime));
    log.info("<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<测试导出耗时apache poi 结束>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
  }

二、EasyExcel API

1、maven依赖

<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.0.0</version>
    </dependency>

2、输出响应

/**
   * 导出文件时为Writer生成OutputStream
   * @param fileName 文件名
   * @param response 响应
   * @return 结果
   */
  private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
    fileName = URLEncoder.encode(fileName, "UTF-8");
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf8");
    response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
    response.setHeader("Pragma", "public");
    response.setHeader("Cache-Control", "no-store");
    response.addHeader("Cache-Control", "max-age=0");
    return response.getOutputStream();
  }

3、实验接口

@GetMapping("/export/easy")
  public void easyExport(HttpServletResponse response) {
    log.info("<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<测试导出耗时easy-excel API 开始>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
    long beginTime = System.currentTimeMillis();
    try {
      List<BizRulePo> dataList = bizRuleMapper.findAll(null);
      log.info("数据库数据量[{}],查询耗时[{}]ms",dataList.size(), (System.currentTimeMillis() - beginTime));
      String fileName = "审核规则汇总".concat(DateUtils.formatDate(new Date(),"yyyyMMdd"));
      EasyExcel.write(getOutputStream(fileName,response), BizRuleDto.class)
          .sheet("审核规则汇总")
          .doWrite(dataList);
    } catch (Exception e) {
      log.error("/matchExport fail,", e);
    }
    log.info("导出耗时[{}]ms", (System.currentTimeMillis() - beginTime));
    log.info("<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<测试导出耗时easy-excel API 结束>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
  }