java - 使用带有 XSSF 和 SAX 的 poi 在 JAVA 中实现内存高效的 XLSX 阅读器，但不知道如何获取公式？

...您可以获取底层 XML 数据，并自己处理它。这适用于愿意学习一点 .xlsx 文件的低级结构并且乐于在 java 中处理 XML 的中级开发人员。它使用起来相对简单，但需要对文件结构有基本的了解。

因此，首先您需要了解*.xlsx文件的结构及其各XML部分的含义。您还需要知道XML使用解析是如何SAX工作的。例如 a有ContentHandler方法startElement和是什么意思。您还需要知道它们何时被调用以及给定参数的含义。endElementcharacters

如果这一切都清楚了，那么你就可以开始编程了。XSSF 和 SAX（事件 API）中的ExampleEventUserModel示例具有非常基本的功能来理解基础知识。它只从共享字符串表中获取字符串内容，以及与存储在元素中的所有其他内容完全相同。您的链接示例更加简单。它只从共享字符串表中获取字符串内容。vDZone

我可以提供一个更完整的示例，该示例还可以从元素中获取公式（如果有），并且如果属性指向单元格样式，则f还使用StylesTable附加的SharedStringsTable来获取单元格的。然后，它包含数字格式，还包含字体设置、边框设置……，如果有的话。XSSFCellStylesXSSFCellStyle

例子：

import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.ooxml.util.SAXHelper;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;

import javax.xml.parsers.ParserConfigurationException;

public class ExampleEventUserModel {

    public void processAllSheets(String filename) throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader(pkg);

        SharedStringsTable sst = r.getSharedStringsTable();
        StylesTable st = r.getStylesTable();
        XMLReader parser = fetchSheetParser(sst, st);

        Iterator<InputStream> sheets = r.getSheetsData();
        while(sheets.hasNext()) {
            System.out.println("Processing new sheet:\n");
            InputStream sheet = sheets.next();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
            System.out.println("");
        }
    }


    public XMLReader fetchSheetParser(SharedStringsTable sst, StylesTable st) throws SAXException, ParserConfigurationException {
        XMLReader parser = SAXHelper.newXMLReader();
        ContentHandler handler = new SheetHandler(sst, st);
        parser.setContentHandler(handler);
        return parser;
    }


    private static class SheetHandler extends DefaultHandler {

        private SharedStringsTable sst;
        private StylesTable st;
        private String lastCharacters; // characters cache to collect character content between startElement and eneElement
        private String formula; // stores the formula, if any
        private String content; // stores the content, if any
        private boolean nextValueIsSSTString; // indicates that next value is from SharedStringsTable 
        private boolean nextValueIsStyledNumeric; // indicates that next value is a styled numeric value
        private XSSFCellStyle cellStyle; // stores the cell style, if any
        private DataFormatter formatter; // used to format the styled numeric values

        private SheetHandler(SharedStringsTable sst, StylesTable st) {
            this.sst = sst;
            this.st = st;
            this.formatter = new DataFormatter(java.util.Locale.US, true);
        }

        public void startElement(String uri, String localName, String name,
                                 Attributes attributes) throws SAXException {
            // c => start of cell
            if(name.equals("c")) {
                // print the cell reference
                System.out.print(attributes.getValue("r") + " - ");

                // get the cell type
                String cellType = attributes.getValue("t");

                // figure out if the value is an index in the SST
                this.nextValueIsSSTString = false;
                if(cellType != null && cellType.equals("s")) {
                    this.nextValueIsSSTString = true;
                } 

                // figure out if the cell has style
                this.cellStyle = null;
                String styleIdx = attributes.getValue("s");
                if (styleIdx != null) {
                    int styleIndex = Integer.parseInt(styleIdx);
                    this.cellStyle = st.getStyleAt(styleIndex);
                    // print that there is cell style for this cell
                    System.out.print("CellStyle: " + this.cellStyle + " - ");
                }

                // figure out if the value is an styled numeric value or date
                this.nextValueIsStyledNumeric = false;
                if(cellType != null && cellType.equals("n") || cellType == null) {
                    if (this.cellStyle != null) {
                        this.nextValueIsStyledNumeric = true;
                    }
                } 

            }

            // clear characters cache after each element
            this.lastCharacters = "";
        }

        public void endElement(String uri, String localName, String name)
                throws SAXException {

            // f => end of formula in a cell
            if(name.equals("f")) {
                this.formula = lastCharacters;
                // print formula
                System.out.print("Formula: " + this.formula + " - ");
            }

            // v => end of value of a cell
            if(name.equals("v")) {

                this.content = this.lastCharacters;

                // process shared string value
                if(this.nextValueIsSSTString) {
                    int idx = Integer.parseInt(lastCharacters);
                    this.content = sst.getItemAt(idx).getString();
                    nextValueIsSSTString = false;
                }

                // process styled numeric value
                if(this.nextValueIsStyledNumeric) {
                    String formatString = cellStyle.getDataFormatString();
                    int formatIndex = cellStyle.getDataFormat();                    
                    if (formatString == null) {
                        // formatString could not be found, so it must be a builtin format.
                        formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
                    }
                    double value = Double.valueOf(this.content);
                    this.content = formatter.formatRawCellContents(value, formatIndex, formatString);
                    nextValueIsStyledNumeric = false;
                }

            }

            // c => end of a cell
            if(name.equals("c")) {
                // print content
                System.out.println("Content: " + this.content);
                this.content = "";
            }
        }

        public void characters(char[] ch, int start, int length) {
            this.lastCharacters += new String(ch, start, length);
        }
    }

    public static void main(String[] args) throws Exception {
        ExampleEventUserModel example = new ExampleEventUserModel();
        //example.processAllSheets(args[0]);
        example.processAllSheets("ExcelExample.xlsx");
    }
}

java - 使用带有 XSSF 和 SAX 的 poi 在 JAVA 中实现内存高效的 XLSX 阅读器，但不知道如何获取公式？

问题描述

解决方案

推荐阅读