使用SXSSFWorkbook的sheet.getRow(0)为null及解决方案

陈三一
2,681 阅读4分钟

问题描述

POI提供了HSSF、XSSF、SXSSF三种方式操作Excel。其中SXSSF的构造器有一个rowAccessWindowSize，SXSSF的每一个sheet对应一个临时文件，当行数大于rowAccessWindowSize时，就会向临时文件中flush，内存中只保留最后的rowAccessWindowSize条记录，以保证内存的低占用率，假设 rowAccessWindowSize 为100，那么当写入第101条记录时，第一条记录即 sheet.getRow(0) 的结果为 null。

解决方案

由于业务场景需要，在处理excel数据导出时需要回溯之前已写入的数据，那么可以有如下三种方案：

  1. 设置合适的 rowAccessWindowSize

    这个值需要根据具体的场景设置合理值

  2. 取消rowAccessWindowSize限制

    通过源码发现rowAccessWindowSize 值设置为 -1，这样就是不限制大小了。

  3. 自定义缓存记录已写入的历史数据

这里因为业务的场景限制，无法推测出一个合理的 rowAccessWindowSize 值，而第二种方案则直接失去了SXSSFWorkbook本身的使用场景的意义即降低内存占用率。因为在实际的需求中，只需要获取到已写入的数据单元格值，对于其他的 style等excel属性并不关心，且在缓存数据时可只缓存涉及的单元格数据（假设有20列，但只关注其中3列），这同样可以降低内存占用率，故采用第三种方案。当然为了保证在使用时对已有代码的改动最小，采用了自定义 Workbook类的设计。

详细设计及实现

自定义CSXSSFCell类

package org.apache.poi.xssf.streaming;

import org.apache.poi.ss.usermodel.CellType;

public class CSXSSFCell extends SXSSFCell {
    public CSXSSFCell(SXSSFRow row, CellType cellType) {
        super(row, cellType);
    }

    @Override
    public void setCellValue(String value) {
        super.setCellValue(value);
        //针对String的value做增强
        SXSSFSheet sxSheet = this.getSheet();
        if (sxSheet instanceof CSXSSFSheet) {
            ((CSXSSFSheet) sxSheet).setCellValue(this.getRowIndex(), this.getColumnIndex(), value);
        }
    }
}

该类集成SXSSFCell并重写了 setCellValue(String value)方法，只针对 String的value做增强缓存处理，如需增强其他，则可重写 其他 数据类型的 setCellValue方法。

自定义CSXSSFRow类

package org.apache.poi.xssf.streaming;

import cn.hutool.core.util.ReflectUtil;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.CellType;
import java.util.SortedMap;

public class CSXSSFRow extends SXSSFRow {
    public CSXSSFRow(SXSSFSheet sheet) {
        super(sheet);
    }

    @Override
    public SXSSFCell createCell(int column, CellType type) {
        checkBounds(column);
        CSXSSFCell cell = new CSXSSFCell(this, type);
        //this._cells.put(column, cell);
        SortedMap<Integer, SXSSFCell> _cells = (SortedMap<Integer, SXSSFCell>) ReflectUtil.getFieldValue(this, "_cells");
        _cells.put(column, cell);

        return cell;
    }

    private static void checkBounds(int cellIndex) {
        SpreadsheetVersion v = SpreadsheetVersion.EXCEL2007;
        int maxcol = SpreadsheetVersion.EXCEL2007.getLastColumnIndex();
        if (cellIndex < 0 || cellIndex > maxcol) {
            throw new IllegalArgumentException("Invalid column index (" + cellIndex + ").  Allowable column range for " + v.name() + " is (0.." + maxcol + ") or ('A'..'" + v.getLastColumnName() + "')");
        }
    }
}

该类继承SXSSFRow并重写createCell方法返回自定义CSXSSFCell类，因为SXSSFRow类本身有些属性是private私有的，这里使用了反射绕开权限控制。

自定义CSXSSFSheet类

package org.apache.poi.xssf.streaming;

import cn.hutool.core.util.ReflectUtil;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import java.util.TreeMap;

/**
 * 由于 SXSSFSheet 类有方法（如上）的访问修饰符是default，只限于相同包底下的类访问，
 * 所以这里新建的子类包名与父类相同 为 org.apache.poi.xssf.streaming
 */
public class CSXSSFSheet extends SXSSFSheet {
    private Map<Integer, Map<Integer, Object>> sheetData;

    public void clearSheetData() {
        if (sheetData != null) {
            sheetData.clear();
            sheetData = null;
        }
    }

    /**
     * 清除 rowStart与rowEnd之间，cellStart与cellEnd之间的单元格数据
     * @param rowStart
     * @param rowEnd
     * @param cellStart
     * @param cellEnd
     */
    public void clearSheetData(int rowStart, int rowEnd, int cellStart, int cellEnd) {
        if (sheetData != null) {
            for (int rowIndex = rowStart; rowIndex <= rowEnd; rowIndex++) {
                Map<Integer, Object> rowData = sheetData.get(rowIndex);
                if (rowData != null) {
                    for (int cellIndex = cellStart; cellIndex <= cellEnd; cellIndex++) {
                        rowData.remove(cellIndex);
                    }
                    if (rowData.size() == 0) {
                        //数据为空了，则清除row
                        rowData = null;
                        sheetData.remove(rowIndex);
                    }
                }
            }
        }
    }

    public CSXSSFSheet setCellValue(int rowIndex, int cellIndex, Object value) {
        if (value != null) {
            if (sheetData == null) {
                sheetData = new HashMap<>();
            }
            Map<Integer, Object> rowData = sheetData.get(rowIndex);
            if (rowData == null) {
                rowData = new HashMap<>();
                sheetData.put(rowIndex, rowData);
            }
            rowData.put(cellIndex, value);
        }
        return this;
    }

    public Object getCellValue(int rowIndex, int cellIndex) {
        if (sheetData == null) {
            return null;
        }
        Map<Integer, Object> rowData = sheetData.get(rowIndex);
        if (rowData == null) {
            return null;
        }
        return rowData.get(cellIndex);
    }

    public CSXSSFSheet(SXSSFWorkbook workbook, XSSFSheet xSheet) throws IOException {
        super(workbook, xSheet);
    }

    @Override
    public SXSSFRow createRow(int rownum) {
        int maxrow = SpreadsheetVersion.EXCEL2007.getLastRowIndex();
        if (rownum >= 0 && rownum <= maxrow) {
            if (rownum <= this.getSheetDataWriter().getLastFlushedRow()) {
                throw new IllegalArgumentException("Attempting to write a row[" + rownum + "] " + "in the range [0," + this.getSheetDataWriter().getLastFlushedRow() + "] that is already written to disk.");
            } else if (this._sh.getPhysicalNumberOfRows() > 0 && rownum <= this._sh.getLastRowNum()) {
                throw new IllegalArgumentException("Attempting to write a row[" + rownum + "] " + "in the range [0," + this._sh.getLastRowNum() + "] that is already written to disk.");
            } else {
                CSXSSFRow newRow = new CSXSSFRow(this);
                //this._rows.put(rownum, newRow);
                TreeMap<Integer, SXSSFRow> _rows = (TreeMap<Integer, SXSSFRow>) ReflectUtil.getFieldValue(this, "_rows");
                _rows.put(rownum, newRow);

                //this.allFlushed = false;
                ReflectUtil.setFieldValue(this, "allFlushed", false);

                //int randomAccessWindowSize = this._randomAccessWindowSize;
                int randomAccessWindowSize = (int) ReflectUtil.getFieldValue(this, "_randomAccessWindowSize");
                int rowsSize = _rows.size();

                if (randomAccessWindowSize >= 0 && rowsSize > randomAccessWindowSize) {
                    try {
                        this.flushRows(randomAccessWindowSize);
                    } catch (IOException var5) {
                        throw new RuntimeException(var5);
                    }
                }
                return newRow;
            }
        } else {
            throw new IllegalArgumentException("Invalid row number (" + rownum + ") outside allowable range (0.." + maxrow + ")");
        }
    }
}

该类继承SXSSFSheet，内部定义了 sheetData 缓存单元格数据，及封装了操作单元格数据的clearSheetData、setCellValue、getCellValue方法，并重写了 createRow 方法以便返回自定义的 CSXSSFRow，通过反射绕开权限控制操作SXSSFSheet的私有属性。

自定义CSXSSFWorkbook类

package org.apache.poi.xssf.streaming;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.io.IOException;

/**
 * 由于 SXSSFWorkbook 类有方法的访问修饰符是default，只限于相同包底下的类访问，
 * 所以这里新建的子类包名与父类相同 为 org.apache.poi.xssf.streaming
 */
public class CSXSSFWorkbook extends SXSSFWorkbook {
    public CSXSSFWorkbook(int rowAccessWindowSize) {
        super(rowAccessWindowSize);
    }

    @Override
    CSXSSFSheet createAndRegisterSXSSFSheet(XSSFSheet xSheet) {
        CSXSSFSheet CSXSSFSheet;
        try {
            CSXSSFSheet = new CSXSSFSheet(this, xSheet);
        } catch (IOException var4) {
            throw new RuntimeException(var4);
        }
        this.registerSheetMapping(CSXSSFSheet, xSheet);
        return CSXSSFSheet;
    }

    @Override
    public CSXSSFSheet createSheet(String sheetName) {
        return this.createAndRegisterSXSSFSheet(this.getXSSFWorkbook().createSheet(sheetName));
    }
}

该类继承SXSSFWorkbook并重写createSheet方法，由于SXSSFWorkbook底层操作的其实是 XSSFSheet，其映射了 XSSFSheet 与 SXSSFSheet的关系，这里同样需要自定义返回 CSXSSFSheet 并 处理好与 XSSFSheet的映射关系，及重写 createAndRegisterSXSSFSheet方法。

如何使用？

  1. 创建workbook时由SXSSFWorkbook替换为CSXSSFWorkbook，即：

    //workbook = new SXSSFWorkbook(rowAccessWindowSize);
workbook = new CSXSSFWorkbook(rowAccessWindowSize);

  2. getCellValue的替换，假设封装了如下方法：

    private static Object getCellValue(Sheet sheet, int rowIndex, int cellIndex) {
    if (sheet == null) {
        return null;
    }
    if (sheet instanceof CSXSSFSheet) {
        //CSXSSFSheet getValue的方式
        return ((CSXSSFSheet) sheet).getCellValue(rowIndex, cellIndex);
    } else {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            return null;
        }
        Cell cell = row.getCell(cellIndex);
        if(cell == null){
            return null;
        }
        CellType cellType = cell.getCellTypeEnum();
        Object cellValue = null;
        if (CellType.STRING.equals(cellType)) {
            cellValue = cell.getStringCellValue();
        } else if (CellType.NUMERIC.equals(cellType)) {
            cellValue = String.valueOf(cell.getNumericCellValue());
        } else if (CellType.FORMULA.equals(cellType)) {
            cellValue = cell.getCellFormula();
        } else if (CellType.BOOLEAN.equals(cellType)) {
            cellValue = String.valueOf(cell.getBooleanCellValue());
        } else if (CellType._NONE.equals(cellType)) {
            cellValue = null;
        } else if (CellType.BLANK.equals(cellType)) {
            cellValue = "";
        } else if (CellType.ERROR.equals(cellType)) {
            cellValue = String.valueOf(cell.getErrorCellValue());
        } else {
            cellValue = null;
        }
        return cellValue;
    }
}

  3. 同时为了降低内存占用率，可在适当时机 使用 CSXSSFSheet的 clearSheetData 方法清除缓存中的单元格数据

avatar
文章
阅读
粉丝