How to read a particular column of data in Excel XML spreadsheet(.XML)?

Question

I am doing selenium Java automation.

In the application there is a feature to upload Project data through Excel XML spreadsheet(2003) (saved in .XML format). Once I uploaded the data I need to verify the uploaded data with the data in the spreadsheet

eg. Project ID.

So that I need to read Project ID column in XML spreadsheet. Can anyone tell me how to access and read such file using selenium Java.

Below is the xml file.

<?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>

Removed selenium and UI related tags since they arent required here and added XML. — JeffC, Sep 06 '18 at 17:36

Yug Singh · Answer 1 · 2018-09-06T17:35:49.493

In Java you can use DocumentBuilderFactory, DocumentBuilder,XpathFactory and Xpath to read an XML file. Following example has been taken from here:

<?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>

We can write the following code to do so:

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();
}
   }

The output of the above program will be:

    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

You can write a similar code to read the particular column from your xml file. In your xml file the root elemnt is Workbook. You need to get the Worksheet node and then you can get the required value.

How to read a particular column of data in Excel XML spreadsheet(.XML)?

1 Answers1