使用 Java 将 Excel 单元格值附加到 XML 中

Question

我一直在尝试使用循环将 Excel 值附加到某些特定的 xml 节点中。我现在使用的代码： public ExcelReaderAndWriter(String inputFileName,String outputFileName) throws IOException, InvalidFormatException {    // Creating a Workbook from an Excel file (.xls or .xlsx)    try (Workbook workbook = WorkbookFactory.create(new File(inputFileName))) {        // Getting the Sheet at index zero        Sheet sheet = workbook.getSheet("XSL_RULES");        // Create a DataFormatter to format and get each cell's value as String        DataFormatter dataFormatter = new DataFormatter();        //obtain a rowIterator and columnIterator and iterate over them        System.out.println("\n\nIterating over Rows and Columns using Iterator\n");        Iterator <Row> rowIterator = sheet.rowIterator();        while (rowIterator.hasNext()) {            Row row = rowIterator.next();            // Now let's iterate over the columns of the current row            Iterator <Cell> cellIterator = row.cellIterator();            if (cellIterator.hasNext()) {                Cell cell = cellIterator.next();                String cellValue = dataFormatter.formatCellValue(cell);                DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();                DocumentBuilder docBuilder = null;                try {                    docBuilder = docFactory.newDocumentBuilder();                } catch (ParserConfigurationException e) {                    e.printStackTrace();                }所以这段代码“以某种方式”工作，但我在递归获取每个元素时遇到问题。当我执行它时，它会将文件创建到某个位置（通常是文件的末尾和最后一个单元格值。我想我在 While 循环中也犯了一个错误，因为它在一次执行后永远不会停止，多次循环在最后停止。这里的错误在哪里，我怎样才能以更好、更干净的方式改进它？谢谢！编辑：我找到了解决方案并编辑了代码。这是工作。问题是我的 for 循环，我找不到如何计算行号并根据该循环获取数字。这是关于整张纸和行号。毕竟没有那么难。希望对其他人有所帮助。

Answer 1

工作解决方案是：

public ExcelReaderAndWriter(String inputFileName,String outputFileName) throws IOException, InvalidFormatException {

// Creating a Workbook from an Excel file (.xls or .xlsx)

try (Workbook workbook = WorkbookFactory.create(new File(inputFileName))) {

    // Getting the Sheet at index zero

    Sheet sheet = workbook.getSheet("XSL_RULES");

    // Create a DataFormatter to format and get each cell's value as String

    DataFormatter dataFormatter = new DataFormatter();

    //obtain a rowIterator and columnIterator and iterate over them

    System.out.println("\n\nIterating over Rows and Columns using Iterator\n");

    Iterator <Row> rowIterator = sheet.rowIterator();

    while (rowIterator.hasNext()) {

        Row row = rowIterator.next();

        // Now let's iterate over the columns of the current row

        Iterator <Cell> cellIterator = row.cellIterator();

        if (cellIterator.hasNext()) {

            Cell cell = cellIterator.next();

            String cellValue = dataFormatter.formatCellValue(cell);

            DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();

            DocumentBuilder docBuilder = null;

            try {

                docBuilder = docFactory.newDocumentBuilder();

            } catch (ParserConfigurationException e) {

                e.printStackTrace();

            }

            Document doc = docBuilder.newDocument();

            Element rootElement = doc.createElement("ROOT");

            doc.appendChild(rootElement);

            // TOMORROW CREATE A LOOP TO GET ALL THE FILES and NODES.

            for (int k = 1; k <= sheet.getLastRowNum(); k++) {

                Element xslt_rule = doc.createElement("RULES");

                    xslt_rule.setAttribute("ATTR1"sheet.getRow (k).getCell (0).getStringCellValue ());

                    xslt_rule.setAttribute("ATTR2", sheet.getRow (k).getCell (1).getStringCellValue ());

                    xslt_rule.setAttribute("ATTR3", sheet.getRow (k).getCell (2).getStringCellValue ());

                }

                xslt_rule.appendChild(doc.createTextNode(sheet.getRow (k).getCell (28).getStringCellValue ());

                rootElement.appendChild(xslt_rule);

            }

            TransformerFactory transformerFactory = TransformerFactory.newInstance();

            Transformer transformer = null;

            try

            {

                transformer = transformerFactory.newTransformer();

            } catch (TransformerConfigurationException e)

            {

                e.printStackTrace();

            }

            DOMSource source = new DOMSource(doc);

            StreamResult result = new StreamResult(new File(outputFileName));

            try {

                transformer.transform(source, result);

            } catch (TransformerException e) {

                e.printStackTrace();

            }

        }

        try {

            workbook.close();

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

}

}