使用 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))) {&nbsp; &nbsp; // Getting the Sheet at index zero&nbsp; &nbsp; Sheet sheet = workbook.getSheet("XSL_RULES");&nbsp; &nbsp; // Create a DataFormatter to format and get each cell's value as String&nbsp; &nbsp; DataFormatter dataFormatter = new DataFormatter();&nbsp; &nbsp; //obtain a rowIterator and columnIterator and iterate over them&nbsp; &nbsp; System.out.println("\n\nIterating over Rows and Columns using Iterator\n");&nbsp; &nbsp; Iterator <Row> rowIterator = sheet.rowIterator();&nbsp; &nbsp; while (rowIterator.hasNext()) {&nbsp; &nbsp; &nbsp; &nbsp; Row row = rowIterator.next();&nbsp; &nbsp; &nbsp; &nbsp; // Now let's iterate over the columns of the current row&nbsp; &nbsp; &nbsp; &nbsp; Iterator <Cell> cellIterator = row.cellIterator();&nbsp; &nbsp; &nbsp; &nbsp; if (cellIterator.hasNext()) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Cell cell = cellIterator.next();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String cellValue = dataFormatter.formatCellValue(cell);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DocumentBuilder docBuilder = null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; docBuilder = docFactory.newDocumentBuilder();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } catch (ParserConfigurationException e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e.printStackTrace();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Document doc = docBuilder.newDocument();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Element rootElement = doc.createElement("ROOT");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; doc.appendChild(rootElement);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // TOMORROW CREATE A LOOP TO GET ALL THE FILES and NODES.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for (int k = 1; k <= sheet.getLastRowNum(); k++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Element xslt_rule = doc.createElement("RULES");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xslt_rule.setAttribute("ATTR1"sheet.getRow (k).getCell (0).getStringCellValue ());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xslt_rule.setAttribute("ATTR2", sheet.getRow (k).getCell (1).getStringCellValue ());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xslt_rule.setAttribute("ATTR3", sheet.getRow (k).getCell (2).getStringCellValue ());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xslt_rule.appendChild(doc.createTextNode(sheet.getRow (k).getCell (28).getStringCellValue ());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rootElement.appendChild(xslt_rule);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TransformerFactory transformerFactory = TransformerFactory.newInstance();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Transformer transformer = null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; transformer = transformerFactory.newTransformer();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } catch (TransformerConfigurationException e)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e.printStackTrace();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DOMSource source = new DOMSource(doc);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StreamResult result = new StreamResult(new File(outputFileName));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; transformer.transform(source, result);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } catch (TransformerException e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e.printStackTrace();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; workbook.close();&nbsp; &nbsp; &nbsp; &nbsp; } catch (IOException e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e.printStackTrace();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }}}