SXSSFWorkbook的使用

使用SXSSFWorkbook解决海量数据导出问题

1.导入maven jar

<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.47</version>
    </dependency>

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId>xerces</groupId>
      <artifactId>xercesImpl</artifactId>
      <version>2.12.0</version>
    </dependency>

    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-collections4</artifactId>
      <version>4.1</version>
    </dependency>
    <dependency>
      <groupId>commons-lang</groupId>
      <artifactId>commons-lang</artifactId>
      <version>2.5</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>

 

package execl.demo.utils;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author Gaojie.Shen
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelExportColumn {

  /**
   * 表头名称
   *
   * @return
   */
  String value() default "";

  /**
   * 表头第几个
   *
   * @return
   */
  int order();

  /**
   * 是否图片
   * 注意：只能加在String上面,加在其他类型不起作用
   * 使用方法：加载图片网络链接地址上面
   * @return
   */
  boolean isImage() default false;
}

package execl.demo.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URL;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.function.Function;
import java.util.stream.Collectors;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import lombok.Data;
import org.apache.commons.collections4.ListUtils;
import org.apache.commons.lang.StringUtils;
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.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.springframework.util.CollectionUtils;

/**
 * excel 导出工具类
 *
 * @author Gaojie.Shen
 * @date 2022-05-01
 */
public final class ExportUtils {

  /**
   * 一页最大长度
   */
  public static final Integer MAXIMUM_LENGTH_OF_A_PAGE = 100000;


  private ExportUtils() {
  }

  /**
   * execl 导出
   *
   * @param sheetTitle 标题
   * @param resultData 导出的结果
   * @param dateFormat 时间导出的格式
   * @param response
   * @throws IOException
   */
  public static void exportExcel(String sheetTitle, List<?> resultData,
      String dateFormat,
      HttpServletResponse response) throws IOException {
    if (CollectionUtils.isEmpty(resultData)) {
      throw new RuntimeException("获取到数据为空异常");
    }
    Class<?> clazz = resultData.get(0).getClass();
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    int size = resultData.size();
    int sheetNum = size % MAXIMUM_LENGTH_OF_A_PAGE == 0 ? size / MAXIMUM_LENGTH_OF_A_PAGE
        : size / MAXIMUM_LENGTH_OF_A_PAGE + 1;
    // 将数据拆分sheetNum份
    List<? extends List<?>> partition = ListUtils.partition(resultData, MAXIMUM_LENGTH_OF_A_PAGE);
    try (OutputStream outputStream = response.getOutputStream()) {
      for (int i = 1; i <= sheetNum; i++) {
        exportExcelData(workbook, i, sheetTitle, clazz, partition.get(i - 1),
            dateFormat);
      }
      String fileName =
          "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xlsx";
      String headStr = "attachment; filename=\"" + fileName + "\"";
      response.setContentType("APPLICATION/OCTET-STREAM");
      response.setHeader("Content-Disposition", headStr);
      workbook.write(outputStream);
    }
  }


  /**
   * 表头的样式
   *
   * @param wb
   * @return
   */
  private static CellStyle getHeaderStyle(SXSSFWorkbook wb) {
    Font headFont = wb.createFont();
    headFont.setFontName("微软雅黑");
    headFont.setFontHeightInPoints((short) 16);
    // 表头设置居中
    CellStyle headStyle = wb.createCellStyle();
    headStyle.setAlignment(HorizontalAlignment.CENTER); // 垂直居中
    // 设置字体
    headStyle.setFont(headFont);
    // 设置背景色
    headStyle.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
    headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    return headStyle;
  }


  /**
   * 结果的样式
   *
   * @param wb
   * @return
   */
  private static CellStyle getDataStyle(SXSSFWorkbook wb) {
    CellStyle cellStyle = wb.createCellStyle();
    //设置水平对齐的样式为居中对齐;
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    //设置垂直对齐的样式为居中对齐;
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    Font cellFont = wb.createFont();
    cellFont.setFontName("微软雅黑");
    cellStyle.setFont(cellFont);
    return cellStyle;
  }

  /**
   * @param wb         工作簿
   * @param sheetNum   第几个sheet
   * @param sheetTitle 标题
   * @param clazz      要导出的bean类型
   * @param resultData 要导出的数据
   * @param dateFormat yyyy-MM-dd HH:mm:ss(默认)
   * @throws IOException
   */
  private static void exportExcelData(SXSSFWorkbook wb, int sheetNum, String sheetTitle,
      Class<?> clazz, List<?> resultData, String dateFormat) {
    SXSSFSheet sheet = wb.createSheet(sheetTitle + sheetNum);
    sheet.setDefaultColumnWidth(10);
    sheet.setDefaultRowHeightInPoints(75);
    // 获取表头样式
    CellStyle headerStyle = getHeaderStyle(wb);
    // 数据样式
    CellStyle dataStyle = getDataStyle(wb);
    // 反射获取所有要导出的头实体信息
    List<ExcelColumn> list = getExcelHeadColumns(clazz);
    if (CollectionUtils.isEmpty(list)) {
      throw new RuntimeException("没有获取到表列头");
    }
    Map<String, ExcelColumn> headMap = list.stream()
        .collect(Collectors.toMap(ExcelColumn::getFieldName, Function.identity()));
    // 适应宽度map
    Map<String, Integer> adaptCollect = list.stream().map(ExcelColumn::getFieldName)
        .collect(Collectors.toMap(Function.identity(), t -> 0));
    // 设置表头
    setExcelHeader(sheet, headerStyle, headMap);
    // 日期格式为空
    if (StringUtils.isBlank(dateFormat)) {
      dateFormat = "yyyy-MM-dd HH:mm:ss";
    }
    String json = JSON.toJSONStringWithDateFormat(resultData, dateFormat,
        SerializerFeature.WriteDateUseDateFormat);
    JSONArray jsonArray = JSON.parseArray(json);
    String fieldName = null;
    Cell cell = null;
    Row row = null;
    int newStartRow = sheet.getLastRowNum() + 1;
    JSONObject recordObj = null;
    Object valueObject = null;
    ByteArrayOutputStream byteArrayOutputStream = null;
    ExcelColumn value = null;
    // 获取画图工具 一个sheet只能获取一个
    Drawing patriarch = null;
    for (Object obj : jsonArray) {
      recordObj = (JSONObject) JSONObject.toJSON(obj);
      row = sheet.createRow(newStartRow);
      row.setHeightInPoints(75);
      for (Entry<String, ExcelColumn> stringExcelColumnEntry : headMap.entrySet()) {
        fieldName = stringExcelColumnEntry.getKey();
        value = stringExcelColumnEntry.getValue();
        int index = value.getIndex();
        cell = row.createCell(index - 1);
        cell.setCellStyle(dataStyle);
        if (fieldName != null) {
          valueObject = recordObj.get(fieldName);
          if (headMap.get(fieldName) != null) {
            valueObject = recordObj.get(fieldName);
          }
          if (valueObject == null) {
            cell.setCellValue("");
          } else {
            // 如果是网络图片类型
            if (value.isImage()) {
              if (patriarch == null) {
                patriarch = sheet.createDrawingPatriarch();
              }
              // 地址不为空 && 并且地址为 http开头
              if (StringUtils.isNotBlank((String) valueObject) && ((String) valueObject)
                  .startsWith("http")) {
                byteArrayOutputStream = getByteArrayOutputStream((String) valueObject);
                if (byteArrayOutputStream != null) {
                  outExcelImage(byteArrayOutputStream, wb, patriarch, newStartRow, index - 1,
                      SXSSFWorkbook.PICTURE_TYPE_JPEG);
                } else {
                  cell.setCellValue("图片下载失败");
                }
              } else {
                cell.setCellType(CellType.STRING);
                cell.setCellValue(valueObject.toString());
              }
            } else {
              cell.setCellType(CellType.STRING);
              cell.setCellValue(valueObject.toString());
            }
          }
          // 获取当格子数据的宽度
          int length = cell.getStringCellValue().getBytes().length;
          if (adaptCollect.get(fieldName).compareTo(length) < 0) {
            adaptCollect.put(fieldName,length);
          }
        }
      }
      newStartRow++;
    }
    //让列宽随着导出的列长自动适应
//    for (int i = 0, len = headMap.size(); i < len; i++) {
//      // 获取当前列宽
//      int columnWidth = sheet.getColumnWidth(i) / 256;
//      columnWidth = getColumnWidth(sheet, i, columnWidth);
//      // 如果宽大于251像素
//      if (columnWidth > 251) {
//        columnWidth = 251;
//      }
//      sheet.setColumnWidth(i, (columnWidth + 4) * 256);
//    }

    // 列宽自动适应
    for (Entry<String, ExcelColumn> excelColumnEntry : headMap.entrySet()) {
      Integer len = adaptCollect.get(excelColumnEntry.getKey());
      if (null != len) {
        // 获取当前列的长度
        int columnIndex = excelColumnEntry.getValue().getIndex() - 1;
        int columnWidth = sheet.getColumnWidth(columnIndex) / 256;
        // 如果宽大于251像素
        if (columnWidth > 251) {
          columnWidth = 251;
        }
        if (len.compareTo(columnWidth) > 0) {
          sheet.setColumnWidth(columnIndex, (len + 4) * 256);
        }
      }
    }
    jsonArray = null;
  }

  private static void setExcelHeader(SXSSFSheet sheet, CellStyle headerStyle,
      Map<String, ExcelColumn> headMap) {
    Row rowHeader = sheet.createRow(0);
    rowHeader.setHeightInPoints(20);
    Cell headerCell = null;
    // 遍历excel表头
    for (Entry<String, ExcelColumn> stringExcelColumnEntry : headMap.entrySet()) {
      ExcelColumn value = stringExcelColumnEntry.getValue();
      int index = value.getIndex();
      headerCell = rowHeader.createCell(index - 1);
      headerCell.setCellValue(value.getFieldDipName());
      headerCell.setCellStyle(headerStyle);
    }
  }

  /**
   * 获取表头信息
   *
   * @param clazz bean class
   * @return
   */
  private static List<ExcelColumn> getExcelHeadColumns(Class<?> clazz) {
    Field[] fields = clazz.getDeclaredFields();
    ExcelExportColumn exportProperty = null;
    ExcelColumn excelColumn = null;
    String fieldDispName = null;
    String fieldName = null;
    List<ExcelColumn> list = new ArrayList<>(fields.length);
    for (Field field : fields) {
      exportProperty = field.getAnnotation(ExcelExportColumn.class);
      if (exportProperty != null && StringUtils.isNotEmpty(field.getName())) {
        excelColumn = new ExcelColumn();
        fieldDispName = exportProperty.value();
        int order = exportProperty.order();
        fieldName = field.getName();
        boolean isImage = exportProperty.isImage();
        if (StringUtils.isNotBlank(fieldDispName)) {
          excelColumn.setFieldDipName(fieldDispName);
        } else {
          excelColumn.setFieldDipName(fieldName);
        }
        excelColumn.setFieldName(fieldName);
        excelColumn.setImage(isImage);
        excelColumn.setIndex(order);
        list.add(excelColumn);
      }
    }
    return list;
  }


  /**
   * 获取列最大宽度
   *
   * @param sheet       sheet工作表
   * @param num         列数
   * @param columnWidth 列宽
   * @return 自适应列宽
   */
  private static int getColumnWidth(SXSSFSheet sheet, int num, int columnWidth) {
    Row row;
    Cell cell;
    for (int i = 0, len = sheet.getLastRowNum(); i <= len; i++) {
      // 当前行未被使用过
      if (sheet.getRow(i) == null) {
        row = sheet.createRow(i);
      } else {
        row = sheet.getRow(i);
      }
      if (row.getCell(num) != null) {
        cell = row.getCell(num);
        // 如果是字符串类型
        if (CellType.STRING.equals(cell.getCellTypeEnum())) {
          // 获取数据的长度
          int length = cell.getStringCellValue().getBytes().length;
          // 获取最大的长度
          if (columnWidth < length) {
            columnWidth = length;
          }
        }
      }
    }
    return columnWidth;
  }

  /**
   * 插入excel图片
   *
   * @param byteArrayOut 字节流
   * @param wb
   * @param patriarch    画图的顶级管理器
   * @param x            所在行
   * @param y            所在列
   * @param format       图片格式  HSSFWorkbook.PICTURE_TYPE_JPEG
   * @author Gaojie.Shen
   */
  private static void outExcelImage(ByteArrayOutputStream byteArrayOut, SXSSFWorkbook wb,
      Drawing patriarch, int x, int y, int format) {
    XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) y, x, (short) y + 1,
        x + 1);
    patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), format));
  }

  /**
   * 获取图片字节流
   *
   * @param imgUrl 图片地址
   * @return 字节流对象
   * @throws IOException
   * @author Gaojie.Shen
   */
  private static ByteArrayOutputStream getByteArrayOutputStream(String imgUrl) {
    try (ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream()) {
      URL url = new URL(imgUrl);
      //获取文件后缀名
      String suffix = imgUrl.substring(imgUrl.lastIndexOf(".") + 1);
      BufferedImage bufferImg = ImageIO.read(url);
      //这里要注意formatName要缓存后缀名
      if (ImageIO.write(bufferImg, suffix, byteArrayOut)) {
        return byteArrayOut;
      }
      return null;
    } catch (Exception e) {
      return null;
    }
  }
}

@Data
class ExcelColumn {

  private int index;
  private String fieldName;
  private String fieldDipName;
  private boolean isImage;
}

 

2.测试

package execl.demo.controller;

import execl.demo.entity.User;
import execl.demo.utils.ExportUtils;
import java.io.IOException;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

/**
 * @author Gaojie.Shen
 */
@Controller
public class TestController {

  @RequestMapping("/export")
  public void export(HttpServletResponse response) throws IOException {

    User user1 = new User().setAge("18").setName("大小姐11111111111111111111111").setSex("女").setImageUrl(
        "https://img.syt5.com/2021/1115/20211115083254112.jpg.420.554.jpg").setBirthday(new Date());
    User user2 = new User().setAge("18").setName("大小姐").setSex("女").setImageUrl(
        "https://img.syt5.com/2021/0820/20210820044257444.jpg.420.554.jpg").setBirthday(new Date());
    User user3 = new User().setAge("18").setName("大小姐").setSex("女").setImageUrl(
        "https://img.syt5.com/2021/0809/20210809093913829.jpg.420.554.jpg").setBirthday(new Date());
    User user4 = new User().setAge("18").setName("大小姐").setSex("女").setImageUrl(
        "https://img.syt5.com/2021/0430/20210430034616659.jpg.420.554.jpg").setBirthday(new Date());
    User user5 = new User().setAge("18").setName("大小姐").setSex("女").setImageUrl(
        "https://img.syt5.com/2020/1203/20201203090328140.jpg.420.554.jpg").setBirthday(new Date());
    User user6 = new User().setAge("18").setName("大小姐").setSex("女").setImageUrl(
        "https://img.syt5.com/2020/1123/20201123011752822.gif.420.420.jpg").setBirthday(new Date());
    User user7 = new User().setAge("18").setName("大小姐").setSex("女").setImageUrl(
        "https://img.syt5.com/2020/1123/20201123011758928.gif.420.420.jpg").setBirthday(new Date());
    List<User> userList = Arrays.asList(user1, user2, user3, user4, user5, user6, user7);
    ExportUtils.exportExcel("金水湾ktv",userList,null,response);
  }
}