// 處理老式 .xls 文件
Workbook hssf = new HSSFWorkbook(new FileInputStream("data.xls"));

// 處理新版 .xlsx 文件  
Workbook xssf = new XSSFWorkbook(OPCPackage.open("data.xlsx"));

unzip -l report.xlsx

xl/
├── workbook.xml           # 工作簿元信息
├── worksheets/sheet1.xml  # 實(shí)際表格數(shù)據(jù)
├── sharedStrings.xml      # 全局字符串池
└── styles.xml             # 樣式定義
[Content_Types].xml        # MIME 類型映射
_rels/.rels                # 關(guān)系描述符

Workbook → Sheet → Row → Cell

Sheet sheet = workbook.getSheetAt(0);
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
    Row row = sheet.getRow(i);
    if (row == null) continue;

    for (int j = 0; j < row.getLastCellNum(); j++) {
        Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        System.out.print(getCellValue(cell) + "\t");
    }
    System.out.println();
}

public static Object getCellValue(Cell cell) {
    if (cell == null) return "";

    switch (cell.getCellType()) {
        case STRING:
            return cell.getStringCellValue().trim();
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else {
                return BigDecimal.valueOf(cell.getNumericCellValue())
                                .stripTrailingZeros()
                                .toPlainString(); // 避免科學(xué)計(jì)數(shù)法
            }
        case BOOLEAN:
            return cell.getBooleanCellValue();
        case FORMULA:
            FormulaEvaluator evaluator = cell.getSheet()
                                             .getWorkbook()
                                             .getCreationHelper()
                                             .createFormulaEvaluator();
            return evaluateFormula(evaluator.evaluate(cell));
        default:
            return "";
    }
}

graph TD
    A[Workbook] --> B[Sheet 1]
    A --> C[Sheet 2]
    B --> D[Row 0]
    B --> E[Row 1]
    D --> F[Cell A1]
    D --> G[Cell B1]
    E --> H[Cell A2]
    E --> I[Cell B2]

    style A fill:#f9f,stroke:#333
    style B fill:#bbf,stroke:#333,color:#fff
    style D fill:#ffd,stroke:#333
    style F fill:#dfd,stroke:#333

XSSFWorkbook wb = new XSSFWorkbook(fileInputStream);
XSSFSheet sheet = wb.getSheetAt(0);

for (Row row : sheet) {
    List<String> values = new ArrayList<>();
    for (Cell cell : row) {
        values.add(formatCellValue(cell));
    }
    saveToDatabase(values); // 批量插入
}

java.lang.OutOfMemoryError: Java heap space

try (OPCPackage pkg = OPCPackage.open("huge.xlsx")) {
    XSSFReader reader = new XSSFReader(pkg);
    SharedStringsTable sst = reader.getSharedStringsTable();

    XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) reader.getSheetsData();
    while (iter.hasNext()) {
        try (InputStream stream = iter.next()) {
            String sheetName = iter.getSheetName();
            System.out.println("Processing: " + sheetName);

            XMLReader xmlReader = XMLReaderFactory.createXMLReader();
            xmlReader.setContentHandler(new StreamingSheetHandler(sst));
            xmlReader.parse(new InputSource(stream));
        }
    }
} catch (Exception e) {
    e.printStackTrace();
}

class StreamingSheetHandler extends DefaultHandler {
    private boolean inCell = false;
    private boolean inValue = false;
    private StringBuilder contents = new StringBuilder();
    private String currentCellRef;

    private final SharedStringsTable sst;

    public StreamingSheetHandler(SharedStringsTable sst) {
        this.sst = sst;
    }

    @Override
    public void startElement(String uri, String localName, String qName, Attributes attributes) {
        if ("c".equals(qName)) {
            currentCellRef = attributes.getValue("r"); // 如 A1, B2
            inCell = true;
        } else if (inCell && "v".equals(qName)) {
            inValue = true;
        }
    }

    @Override
    public void characters(char[] ch, int start, int length) {
        if (inValue) {
            contents.append(ch, start, length);
        }
    }

    @Override
    public void endElement(String uri, String localName, String qName) {
        if ("v".equals(qName)) {
            inValue = false;
            String value = contents.toString().trim();

            // 若單元格類型為 's'，表示引用共享字符串表
            String cellType = attributes.getValue("t");
            if ("s".equals(cellType) && !value.isEmpty()) {
                int idx = Integer.parseInt(value);
                value = sst.getItemAt(idx).getString();
            }

            System.out.printf("%s: %s%n", currentCellRef, value);
            contents.setLength(0);
        } else if ("c".equals(qName)) {
            inCell = false;
        }
    }
}

// 創(chuàng)建基于 XSSF 的流式工作簿，保留 100 行在內(nèi)存
XSSFWorkbook xwb = new XSSFWorkbook();
SXSSFWorkbook sxwb = new SXSSFWorkbook(xwb, 100);

SXSSFSheet sheet = sxwb.createSheet("數(shù)據(jù)導(dǎo)出");

for (int i = 0; i < 1_000_000; i++) {
    Row row = sheet.createRow(i);
    for (int j = 0; j < 10; j++) {
        Cell cell = row.createCell(j);
        cell.setCellValue("Row" + i + "-Col" + j);
    }
}

try (FileOutputStream out = new FileOutputStream("output.xlsx")) {
    sxwb.write(out);
} finally {
    sxwb.dispose(); // 刪除臨時(shí)文件
}

public interface CTWorkbook extends XmlObject {
    List<CTSheet> getSheetsList();
    CTSheets addNewSheets();
    boolean isSetSheets();
}

CTWorksheet ct = sheet.getCTWorksheet();
ct.addNewSheetViews().addNewSheetView().setRightToLeft(true);

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
    <exclusions>
        <exclusion>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
        </exclusion>
    </exclusions>
</dependency>

<!-- 單獨(dú)引入受控版本 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>2.6.0</version>
</dependency>

ZipFile zip = new ZipFile("report.xlsx");
ZipEntry entry = zip.getEntry("customXml/item1.xml");

if (entry != null) {
    InputStream is = zip.getInputStream(entry);
    SAXReader reader = new SAXReader();
    Document doc = reader.read(is);

    Element root = doc.getRootElement();
    String bizId = root.elementText("businessId");
    String status = root.element("metadata").attributeValue("status");

    log.info("Found biz context: id={}, status={}", bizId, status);
}

Map<String, String> ns = Map.of("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
reader.getDocumentFactory().setXPathNamespaceContext(new SimpleNamespaceContext(ns));

List<Node> redCells = doc.selectNodes("http://x:c[x:v/@color='red']");

@Service
@Slf4j
public class ExcelImportService {

    public List<Map<String, Object>> importFromStream(InputStream inputStream, int sheetIndex) {
        List<Map<String, Object>> result = new ArrayList<>();

        try (OPCPackage pkg = OPCPackage.open(inputStream);
             XSSFReader reader = new XSSFReader(pkg)) {

            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
            XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) reader.getSheetsData();
            int currentIndex = 0;

            while (iter.hasNext()) {
                if (currentIndex++ == sheetIndex) {
                    try (InputStream sheetStream = iter.next()) {
                        parseSheetStream(sheetStream, strings, result);
                        break;
                    }
                } else {
                    iter.next().close(); // 跳過未選中的 sheet
                }
            }

        } catch (NotOfficeXmlFileException e) {
            throw new IllegalArgumentException("無效的 .xlsx 文件", e);
        } catch (OutOfMemoryError e) {
            log.error("內(nèi)存不足，建議啟用流式解析", e);
            throw new RuntimeException("文件過大，無法處理");
        } catch (Exception e) {
            log.error("Excel 解析失敗", e);
            throw new RuntimeException("解析異常：" + e.getMessage(), e);
        }

        return result;
    }

    private void parseSheetStream(InputStream stream, 
                                  ReadOnlySharedStringsTable strings,
                                  List<Map<String, Object>> result) throws Exception {

        XMLInputFactory factory = XMLInputFactory.newInstance();
        XMLStreamReader xr = factory.createXMLStreamReader(stream);

        Map<String, Object> currentRow = null;
        StringBuilder cellValue = new StringBuilder();
        String cellRef = "";
        String cellType = "";

        while (xr.hasNext()) {
            int event = xr.next();

            switch (event) {
                case START_ELEMENT:
                    String tagName = xr.getLocalName();

                    if ("row".equals(tagName)) {
                        currentRow = new LinkedHashMap<>();
                    } else if ("c".equals(tagName)) {
                        cellRef = xr.getAttributeValue(null, "r");
                        cellType = xr.getAttributeValue(null, "t");
                        cellValue.setLength(0);
                    } else if ("v".equals(tagName) || "t".equals(tagName)) {
                        // 準(zhǔn)備接收字符數(shù)據(jù)
                    }
                    break;

                case CHARACTERS:
                    cellValue.append(xr.getText());
                    break;

                case END_ELEMENT:
                    tagName = xr.getLocalName();

                    if ("v".equals(tagName) || "t".equals(tagName)) {
                        String value = cellValue.toString().trim();
                        if (!value.isEmpty() && "s".equals(cellType)) {
                            try {
                                int idx = Integer.parseInt(value);
                                value = strings.getEntryAt(idx).getString();
                            } catch (NumberFormatException | ArrayIndexOutOfBoundsException ignored) {}
                        }
                        if (currentRow != null && cellRef.matches("[A-Z]+\\d+")) {
                            currentRow.put(extractColumnName(cellRef), value);
                        }
                    } else if ("row".equals(tagName) && currentRow != null) {
                        result.add(currentRow);
                    }
                    break;
            }
        }
    }

    private String extractColumnName(String ref) {
        return ref.replaceAll("\\d+", "");
    }
}

@Test
void should_parse_small_excel_correctly() throws Exception {
    try (InputStream is = getClass().getResourceAsStream("/test-data.xlsx")) {
        List<Map<String, Object>> data = service.importFromStream(is, 0);
        assertThat(data).hasSize(3);
        assertThat(data.get(0)).containsKey("姓名");
    }
}

<properties>
    <poi.version>3.17</poi.version> <!-- 或 5.2.3 最新版 -->
</properties>

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>${poi.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>${poi.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>${poi.version}</version>
    </dependency>
    <dependency>
        <groupId>dom4j</groupId>
        <artifactId>dom4j</artifactId>
        <version>1.6.1</version>
    </dependency>
</dependencies>

ext {
    poiVersion = '3.17'
}

dependencies {
    implementation "org.apache.poi:poi:${poiVersion}"
    implementation "org.apache.poi:poi-ooxml:${poiVersion}"
    implementation "org.apache.poi:poi-ooxml-schemas:${poiVersion}"
    implementation 'dom4j:dom4j:1.6.1'
}

mvn dependency:tree | grep poi