时间戳

首页 > 解决方案 > 如何读取 Excel XML 电子表格（.XML）中的特定数据列？

java - 如何读取 Excel XML 电子表格（.XML）中的特定数据列？

问题描述

我正在做 selenium Java 自动化。

在应用程序中有一项功能可以通过 Excel XML 电子表格 (2003) 上传项目数据（以 .XML 格式保存）。上传数据后，我需要使用电子表格中的数据验证上传的数据

例如。项目标识。

所以我需要阅读 XML 电子表格中的项目 ID 列。谁能告诉我如何使用 selenium Java 访问和读取此类文件。

下面是xml文件。

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel">
   <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
   </DocumentProperties>
   <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
      <AllowPNG />
   </OfficeDocumentSettings>
   <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
      <WindowHeight>7545</WindowHeight>
      <WindowWidth>20490</WindowWidth>
      <WindowTopX>0</WindowTopX>
      <WindowTopY>0</WindowTopY>
      <ProtectStructure>False</ProtectStructure>
      <ProtectWindows>False</ProtectWindows>
   </ExcelWorkbook>
   <Styles>
      <Style ss:ID="Default" ss:Name="Normal">
         <Alignment ss:Vertical="Bottom" />
         <Borders />
         <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000" />
         <Interior />
         <NumberFormat />
         <Protection />
      </Style>
      <Style ss:ID="s62">
         <Alignment ss:Vertical="Bottom" />
         <Borders />
         <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000" />
         <Interior />
         <NumberFormat ss:Format="@" />
         <Protection />
      </Style>
      <Style ss:ID="s65">
         <Alignment ss:Vertical="Bottom" />
         <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" />
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" />
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" />
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" />
         </Borders>
         <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="8" ss:Color="#FFFFFF" ss:Bold="1" />
         <Interior ss:Color="#75923C" ss:Pattern="Solid" />
         <NumberFormat />
         <Protection />
      </Style>
      <Style ss:ID="s66">
         <Alignment ss:Vertical="Bottom" />
         <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" />
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" />
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" />
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" />
         </Borders>
         <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="8" ss:Color="#FFFFFF" ss:Bold="1" />
         <Interior ss:Color="#333399" ss:Pattern="Solid" />
         <NumberFormat />
         <Protection />
      </Style>
      <Style ss:ID="s81">
         <Alignment ss:Vertical="Top" />
      </Style>
   </Styles>
   <Worksheet ss:Name="Submit Project">
      <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="5" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">
         <Column ss:StyleID="s62" ss:Width="110.25" />
         <Column ss:Width="110.25" />
         <Column ss:StyleID="s62" ss:Width="110.25" ss:Span="2" />
         <Row ss:AutoFitHeight="0">
            <Cell ss:StyleID="s65">
               <Data ss:Type="String">Fields</Data>
            </Cell>
            <Cell ss:StyleID="s66">
               <Data ss:Type="String">Spreadsheet Key*</Data>
            </Cell>
            <Cell ss:StyleID="s66">
               <Data ss:Type="String">Project</Data>
            </Cell>
            <Cell ss:StyleID="s66">
               <Data ss:Type="String">Worker</Data>
            </Cell>
            <Cell ss:StyleID="s66">
               <Data ss:Type="String">Project ID</Data>
            </Cell>
         </Row>
         <Row ss:AutoFitHeight="0" ss:Height="14.4375">
            <Cell ss:Index="2">
               <Data ss:Type="Number">1</Data>
            </Cell>
            <Cell ss:Index="5" ss:StyleID="s81">
               <Data ss:Type="String">Project.2018931</Data>
            </Cell>
         </Row>
         <Row ss:AutoFitHeight="0" ss:Height="14.4375">
            <Cell ss:Index="5" ss:StyleID="s81" />
         </Row>
         <Row ss:AutoFitHeight="0" ss:Height="14.4375">
            <Cell ss:Index="5" ss:StyleID="s81" />
         </Row>
         <Row ss:AutoFitHeight="0" ss:Height="14.4375">
            <Cell ss:Index="5" ss:StyleID="s81" />
         </Row>
      </Table>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
         <Unsynced />
         <Selected />
         <LeftColumnVisible>1</LeftColumnVisible>
         <Panes>
            <Pane>
               <Number>3</Number>
               <ActiveRow>7</ActiveRow>
               <ActiveCol>2</ActiveCol>
            </Pane>
         </Panes>
         <ProtectObjects>False</ProtectObjects>
         <ProtectScenarios>False</ProtectScenarios>
      </WorksheetOptions>
   </Worksheet>
</Workbook>

标签： javaxml

解决方案

在 Java 中，您可以使用它DocumentBuilderFactory, DocumentBuilder,XpathFactory and Xpath来读取 XML 文件。以下示例取自此处：

<?xml version="1.0"?>
<company>
    <staff id="1001">
        <firstname>yong</firstname>
        <lastname>mook kim</lastname>
        <nickname>mkyong</nickname>
        <salary>100000</salary>
    </staff>
    <staff id="2001">
        <firstname>low</firstname>
        <lastname>yin fong</lastname>
        <nickname>fong fong</nickname>
        <salary>200000</salary>
    </staff>
</company>

为此，我们可以编写以下代码：

public static void main(String argv[]) {

try {

File fXmlFile = new File("staff.xml");
DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
Document doc = dBuilder.parse(fXmlFile);

//optional, but recommended
//read this - http://stackoverflow.com/questions/13786607/normalization-in-dom-parsing-with-java-how-does-it-work
doc.getDocumentElement().normalize();

System.out.println("Root element :" + doc.getDocumentElement().getNodeName());

NodeList nList = doc.getElementsByTagName("staff");

System.out.println("----------------------------");

for (int temp = 0; temp < nList.getLength(); temp++) {

    Node nNode = nList.item(temp);

    System.out.println("\nCurrent Element :" + nNode.getNodeName());

    if (nNode.getNodeType() == Node.ELEMENT_NODE) {

        Element eElement = (Element) nNode;

        System.out.println("Staff id : " + eElement.getAttribute("id"));
        System.out.println("First Name : " + eElement.getElementsByTagName("firstname").item(0).getTextContent());
        System.out.println("Last Name : " + eElement.getElementsByTagName("lastname").item(0).getTextContent());
        System.out.println("Nick Name : " + eElement.getElementsByTagName("nickname").item(0).getTextContent());
        System.out.println("Salary : " + eElement.getElementsByTagName("salary").item(0).getTextContent());

    }
}
} catch (Exception e) {
e.printStackTrace();
}
   }

上述程序的输出将是：

    Root element :company
----------------------------

Current Element :staff
Staff id : 1001
First Name : yong
Last Name : mook kim
Nick Name : mkyong
Salary : 100000

Current Element :staff
Staff id : 2001
First Name : low
Last Name : yin fong
Nick Name : fong fong
Salary : 200000

您可以编写类似的代码来从 xml 文件中读取特定列。在您的 xml 文件中，根元素是Workbook. 您需要获取Worksheet节点，然后才能获取所需的值。

推荐阅读