读取大数据量xlsx

public class SaxXlsxParser implements AutoCloseable {
    Logger logger = LoggerFactory.getLogger(SaxXlsxParser.class);

    private final OPCPackage opcPackage;
    private final ReadOnlySharedStringsTable table;
    /**
     * 临时文件
     */
    private final File tmp = Files.createTempFile(UUID.randomUUID().toString(), ".tmp").toFile();

    /**
     * 存储临时文件
     * @param file
     */
    public SaxXlsxParser(MultipartFile file) throws IOException, SAXException, InvalidFormatException {
        file.transferTo(tmp);
        this.opcPackage = OPCPackage.open(tmp, PackageAccess.READ);
        this.table = new ReadOnlySharedStringsTable(opcPackage);
    }

    public SaxXlsxParser(File file) throws IOException, SAXException, InvalidFormatException {
        this.opcPackage = OPCPackage.open(file, PackageAccess.READ);
        this.table = new ReadOnlySharedStringsTable(opcPackage);
    }

    public SaxXlsxParser(InputStream inputStream) throws IOException, SAXException, InvalidFormatException {
        this.opcPackage = OPCPackage.open(inputStream);
        this.table = new ReadOnlySharedStringsTable(opcPackage);
    }

    /**
     * 扫描文件
     *
     * @param sheetContentsHandler
     */
    public void scan(SheetContentsHandler sheetContentsHandler) {
        try {
            XSSFReader reader = new XSSFReader(opcPackage);
            StylesTable styles = reader.getStylesTable();
            DataFormatter dataFormatter = new DataFormatter();
            XSSFSheetXMLHandler handler = new XSSFSheetXMLHandler(styles, null, table, sheetContentsHandler, dataFormatter, false);
            XMLReader sheetParser = SAXHelper.newXMLReader();
            sheetParser.setContentHandler(handler);
            SheetIterator sheetIterator = (SheetIterator) reader.getSheetsData();
            while (sheetIterator.hasNext()) {
                try (InputStream stream = sheetIterator.next()) {
                    InputSource sheetSource = new InputSource(stream);
                    sheetParser.parse(sheetSource);
                }
            }
        } catch (IOException | OpenXML4JException | SAXException | ParserConfigurationException e) {
            e.printStackTrace();
            logger.error("",e);
        }
    }

    /**
     * 关闭资源
     *
     * @throws Exception
     */
    @Override
    public void close() throws Exception {
        opcPackage.close();
        if (!tmp.delete()) {
            tmp.deleteOnExit();
        }
    }

public abstract class AbstractHandler implements SheetContentsHandler {
    public static final Logger LOGGER = LoggerFactory.getLogger(AbstractHandler.class);
    public static final String APPEND_IMPORT_WAY = "append";
    public static final String OVERRIDE_IMPORT_WAY = "override";
    protected StringBuilder errorBuilder = new StringBuilder();
    //当前行编号1,2,3
    private int currentRowNo = -1;
    //某一行内容单元格列表值
    private final ArrayList<String> currentRowContent;
    private int maxColLen = 24;

    private int size = 0;

    protected AbstractHandler() {
        this(20);
    }

    /**
     * 初始化为空
     *
     * @param columnSize 单元格列数
     */
    private AbstractHandler(int columnSize) {
        currentRowContent = new ArrayList<>(columnSize);
        for (int i = 0; i < maxColLen; i++) {
            currentRowContent.add(null);
        }
    }

    /**
     * 每行开始
     *
     * @param rowNum
     */
    @Override
    public void startRow(int rowNum) {
        for (int i = 0; i < maxColLen; i++) {
            //每个单元格设置为空
            currentRowContent.set(i, null);
        }
        size++;
        this.currentRowNo = rowNum;
    }

    /**
     * 每行结束
     *
     * @param rowNum
     */
    @Override
    public void endRow(int rowNum) {
        if (this.currentRowNo == rowNum) {
            this.handle(this.currentRowNo, currentRowContent);
        }
    }

    /**
     * 处理当前行数据
     *
     * @param currentRow        当前行
     * @param row 当前行内容
     */
    protected abstract void handle(int currentRow, List<String> row);

    /**
     * @param cellReference  单元格名称
     * @param formattedValue 单元格里面的格式化字符串值
     * @param comment        备注
     */
    @Override
    public void cell(String cellReference, String formattedValue, XSSFComment comment) {
        //单元格对象
        CellReference reference = new CellReference(cellReference);
        //单元格所在行索引
        int rowNum = reference.getRow();
        //单元格所在列索引
        int colNum = reference.getCol();
        //
        if (this.currentRowNo == rowNum) {
            this.currentRowContent.set(colNum, formattedValue);
        }
    }

    /**
     * 头部
     *
     * @param text     文本内容
     * @param isHeader
     * @param tagName  标签名
     */
    @Override
    public void headerFooter(String text, boolean isHeader, String tagName) {
    }

    public abstract void finish();

    public int getProcessedCurrentRowNo(){
        return currentRowNo+1;
    }

    public int getSize(){
        return size;
    }

    public void hasError(){
        if(errorBuilder.length()>0)
            throw new RuntimeException(errorBuilder.toString());
    }
}

}

public class PermissionHandler extends AbstractHandler {



    private final ResourceCache resourceCache;
    private final IConsumer consumer;
    private final Locale locale;
    public PermissionHandler(PermissionCacheService permissionCacheService, IConsumer consumer, Locale locale) {
        this.resourceCache= permissionCacheService.getResourceCache();
        this.consumer = consumer;
        this.locale = locale;
    }

    @Override
    protected void handle(int currentRow, List<String> row) {
        if (currentRow == 0 || XlsxUtils.isEmpty(row)) {
            return;
        }
        String id = row.get(0);
        String type = toType(row.get(1));
        RuleId ruleId = RuleId.valueOf(type,id);
        RuleId parentRuleId = resourceCache.getParentId(ruleId);
        if(parentRuleId==null){
            throw  NPException.badRequest(400,"NotFound","camera",row.get(1));
        }
        String groupIp=parentRuleId.id;
        JsonObject object = new JsonObject();
        object.addProperty("type",type);
        object.addProperty("id",id);
        object.addProperty("parentId",groupIp);
        object.addProperty("list",convertStatus(row.get(3)));
        object.addProperty("live",convertStatus(row.get(4)));
        object.addProperty("history",convertStatus(row.get(5)));
        object.addProperty("ptz",convertStatus(row.get(6)));
        object.addProperty("manualRecord",convertStatus(row.get(7)));
        object.addProperty("download",convertStatus(row.get(8)));
        consumer.consume(object);
    }

    @Override
    public void finish() {

    }

    private String convertStatus(String value){
        if(StringUtil.isEmpty(value))
            return Status.reject.name();
        if(value.equals(I18nUtils.i18n(locale,"permit"))){
            return Status.permit.name();
        }else if(value.equals(I18nUtils.i18n(locale,"temporary"))){
            return Status.temporary.name();
        }
        return Status.reject.name();       //其他值都当做reject
    }

    public String toType(String type){
        if("分组".equals(type)){
            return RuleId.Type.group.name();
        }else if("分组本部".equals(type)){
            return RuleId.Type.self.name();
        }else
            return RuleId.Type.resource.name();
    }
}