DataFlex

Parse the XML of an Excel (XLS) Spreadsheet

See more XML Examples

Demonstrate how to access various items from the XML of an XLS (Excel) Spreadsheet. Our sample data is shown below, and can be downloaded from http://www.chilkatsoft.com/data/ndc_nhric_labeler_codes_04_29_2015.xls

<?xml version="1.0" version="1.0" encoding="utf-8" ?>
<mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
    <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        <Version>14.00</Version>
    </DocumentProperties>
    <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
        <AllowPNG />
    </OfficeDocumentSettings>
    <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>7800</WindowHeight>
        <WindowWidth>12000</WindowWidth>
        <WindowTopX>105</WindowTopX>
        <WindowTopY>105</WindowTopY>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
            <Alignment ss:Vertical="Top" />
            <Borders />
            <Font ss:FontName="Arial" />
            <Interior />
            <NumberFormat />
            <Protection />
        </Style>
        <Style ss:ID="s63">
            <Alignment ss:Vertical="Bottom" ss:WrapText="1" />
            <Font ss:FontName="Arial" x:Family="Swiss" ss:Bold="1" />
            <Interior ss:Color="#C0C0C0" ss:Pattern="Solid" />
        </Style>
        <Style ss:ID="s64">
            <NumberFormat ss:Format="@" />
        </Style>
    </Styles>
    <Worksheet ss:Name="NDCLabelerCode">
        <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="8186" x:FullColumns="1" x:FullRows="1">
            <Column ss:Index="2" ss:Width="580.5" />
            <Row ss:AutoFitHeight="0" ss:Height="38.25">
                <Cell ss:StyleID="s63">
                    <Data ss:Type="String">NDC Labeler Code</Data>
                </Cell>
                <Cell ss:StyleID="s63">
                    <Data ss:Type="String">Firm Name</Data>
                </Cell>
            </Row>
            <Row>
                <Cell ss:StyleID="s64">
                    <Data ss:Type="String">0002</Data>
                </Cell>
                <Cell ss:StyleID="s64">
                    <Data ss:Type="String">Eli Lilly and Company</Data>
                </Cell>
            </Row>
...

DataFlex

Use ChilkatAx-win32.pkg

Procedure Test
    Boolean iSuccess
    Handle hoXlsDoc
    Variant vXWorksheet
    Handle hoXWorksheet
    Variant vX
    Handle hoX
    Integer iNumChildren
    Integer i
    String sData0
    String sData1
    String sTemp1
    Boolean bTemp1

    Move False To iSuccess

    Get Create (RefClass(cComChilkatXml)) To hoXlsDoc
    If (Not(IsComObjectCreated(hoXlsDoc))) Begin
        Send CreateComObject of hoXlsDoc
    End

    Get ComLoadXmlFile Of hoXlsDoc "myFiles/ndc_nhric_labeler_codes_04_29_2015.xls" To iSuccess
    If (iSuccess <> True) Begin
        Get ComLastErrorText Of hoXlsDoc To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    // This example will access the following pieces of data:
    // NDC Labeler Code, Firm Name, 0002, Eli Lilly and Company, ...

    // First navigate to the Worksheet named "NDCLabelerCode"
    Get ComGetChildWithAttr Of hoXlsDoc "Worksheet" "ss:Name" "NDCLabelerCode" To vXWorksheet
    If (IsComObject(vXWorksheet)) Begin
        Get Create (RefClass(cComChilkatXml)) To hoXWorksheet
        Set pvComObject Of hoXWorksheet To vXWorksheet
    End

    // Next, get the Table node:
    Get ComGetChildWithTag Of hoXWorksheet "Table" To vX
    If (IsComObject(vX)) Begin
        Get Create (RefClass(cComChilkatXml)) To hoX
        Set pvComObject Of hoX To vX
    End

    // How many immediate child nodes?
    Get ComNumChildren Of x To iNumChildren

    // Loop over each Row and access the cell contents.

    For i From 0 To (iNumChildren - 1)

        // Navigate without creating new XML objects
        Get ComGetChild2 Of x i To iSuccess

        Get ComTagEquals Of x "Row" To bTemp1
        If (bTemp1) Begin

            Get ComChilkatPath Of x "Cell[0]|Data|*" To sData0
            Showln sData0

            Get ComChilkatPath Of x "Cell[1]|Data|*" To sData1
            Showln sData1
        End

        // Navigate back up.
        Get ComGetParent2 Of x To iSuccess
    Loop

    Send Destroy of x
    Send Destroy of hoXWorksheet


End_Procedure