百万级 Excel导入数据库 效率太低？ 基于 SAX 的事件模型 导入，将会解决 效率问题

// 准备写入的输出流
OutputStream os = new BufferedOutputStream(new FileOutputStream(outputFile));
SXSSFWorkbook writer = new SXSSFWorkbook(new XSSFWorkbook(), 10000);
SXSSFSheet outSheet = writer.createSheet();

// 定义 SAX 事件处理程序
DefaultHandler handler = new DefaultHandler() {
    private boolean isRow = false;
    private int rowIndex = 0;
    private int colIndex = 0;
    private Row outRow = null;

    // 开始解析 Workbook
    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        if (qName.equals("row")) {
            isRow = true;
            rowIndex++;
            colIndex = 0;
            outRow = outSheet.createRow(rowIndex - 1);
        } else if (qName.equals("c")) {
            String colRef = attributes.getValue("r");
            colIndex = CellReference.convertColStringToIndex(colRef.replaceAll("\\d", ""));
        }
    }

    // 解析 Cell 的值
    public void characters(char[] ch, int start, int length) throws SAXException {
        if (isRow) {
            String cellValue = new String(ch, start, length);
            Cell cell = outRow.createCell(colIndex, CellType.STRING);
            cell.setCellValue(cellValue);
        }
    }

    // 结束解析 Workbook
    public void endElement(String uri, String localName, String qName) throws SAXException {
        if (qName.equals("row")) {
            isRow = false;

            // 每隔 10000 行进行一次缓存写入
            if (rowIndex % 10000 == 0) {
                ((SXSSFSheet) outSheet).flushRows();
            }
        }
    }
};

// 获取 Excel 文件输入流
InputStream is = new BufferedInputStream(new FileInputStream(inputFile));
OPCPackage pkg = OPCPackage.open(is);
XSSFReader reader = new XSSFReader(pkg);

// 获取 Workbook 中每个 Sheet 的 XML 输入流，并解析处理
XMLReader parser = XMLReaderFactory.createXMLReader();
parser.setContentHandler(handler);
Iterator<InputStream> sheets = reader.getSheetsData();
while (sheets.hasNext()) {
    InputStream sheetStream = sheets.next();
    InputSource sheetSource = new InputSource(sheetStream);
    parser.parse(sheetSource);
    sheetStream.close();
}

// 写入缓存的数据
writer.write(os);
os.flush();
os.close();
writer.dispose();