使用SXSSFWorkbook读取Excel，内存无法释放？

Z时代
2024-03-02
分类：IT

使用SXSSFWorkbook读取Excel，1W行数据，188列（测试时只有前16列有数据）
读取操作完成后，JVM中存在大量的org.apache.xmlbeans.impl.store.Xobj$AttrXobj org.apache.xmlbeans.impl.store.Xobj$ElementXob类的实例，很长时间内不会被GC

@RestController
public class Test4 {
  @GetMapping("/hello")
  public String hello() {
    // 标题栏行数
    int hDefine = 7;
    // 有效数据列
    int headerSize = 188;
    // 最大读取行数
    int maxDataRow = 10000;
    // 行号列
    int rowNumDefine = 2;
    try (
        XSSFWorkbook wb = new XSSFWorkbook("D:\\Test.xlsx");
        SXSSFWorkbook swb = new SXSSFWorkbook(wb, 100);
        XSSFWorkbook newXssfWorkbook = swb.getXSSFWorkbook()) {
      Sheet sheet = newXssfWorkbook.getSheetAt(0);
      DataFormatter formatterExcel = new DataFormatter();
      int i;
      int j;
      Row row;
      Cell cell;
      boolean isEmptyRow;
      for (i = hDefine; i < maxDataRow + hDefine; i++) {
        row = sheet.getRow(i);
        if (row == null) {
          break;
        }
        isEmptyRow = true;
        List<String> rowString = new ArrayList<>();
        for (j = rowNumDefine; j < headerSize + rowNumDefine; j++) {
          cell = row.getCell(j);
          String stringValue = "";
          if (cell != null && !isBlankOrNull(formatterExcel.formatCellValue(cell))) {
            isEmptyRow = false;
            String text = formatterExcel.formatCellValue(cell);
            FontColor textColor = getCellTextColor(swb, cell);
            String formatter = textColor.getHtmlFormatter();
            stringValue = String.format(formatter, text);
          }
          rowString.add(stringValue);
        }
        if (isEmptyRow) {
          break;
        }
        // 省略其他业务代码
      }
      swb.dispose();
    } catch (EncryptedDocumentException | IOException e) {
      throw new RuntimeException("导入时发生错误", e);
    }
    return "hello";
  }
  public static boolean isBlankOrNull(String str) {
    return str == null || str.trim().isEmpty();
  }
  public static FontColor getCellTextColor(Workbook wk, Cell cell) {
    FontColor textColor = FontColor.BLACK;
    if (cell == null) {
      return textColor;
    }
    CellStyle cellStyle = cell.getCellStyle();
    Font font = wk.getFontAt(cellStyle.getFontIndexAsInt());
    XSSFColor color = Optional.ofNullable(((XSSFFont) font).getXSSFColor())
        .orElse(new XSSFColor(new java.awt.Color(0, 0, 0), new DefaultIndexedColorMap()));
    byte[] rgb = color.getRGB();
    textColor = FontColor.valueOf(rgb);
    return textColor;
  }
  public enum FontColor {
    BLACK, RED, BLUE;
    public String getHtmlFormatter() {
      switch (this) {
        case BLACK:
          return "%s";
        case RED:
          return "<span style=\"color: red;\">%s</span>";
        case BLUE:
          return "<span style=\"color: blue;\">%s</span>";
      }
      return "%s";
    }
    public static FontColor valueOf(byte[] rgb) {
      FontColor ret = FontColor.BLACK;
      int red = (rgb[0] < 0) ? (rgb[0] + 256) : rgb[0];
      int green = (rgb[1] < 0) ? (rgb[1] + 256) : rgb[1];
      int blue = (rgb[2] < 0) ? (rgb[2] + 256) : rgb[2];
      if (red == 255 && green == 0 && blue == 0) {
        ret = FontColor.RED;
      } else if (red == 0 && green == 0 && blue == 255) {
        ret = FontColor.BLUE;
      }
      return ret;
    }
  }
}