PowerBuilder

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

PowerBuilder

integer li_rc
integer li_Success
oleobject loo_XlsDoc
oleobject loo_XWorksheet
oleobject x
integer li_NumChildren
integer i
string ls_Data0
string ls_Data1

li_Success = 0

loo_XlsDoc = create oleobject
li_rc = loo_XlsDoc.ConnectToNewObject("Chilkat.Xml")
if li_rc < 0 then
    destroy loo_XlsDoc
    MessageBox("Error","Connecting to COM object failed")
    return
end if

li_Success = loo_XlsDoc.LoadXmlFile("myFiles/ndc_nhric_labeler_codes_04_29_2015.xls")
if li_Success <> 1 then
    Write-Debug loo_XlsDoc.LastErrorText
    destroy loo_XlsDoc
    return
end if

// 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"
loo_XWorksheet = loo_XlsDoc.GetChildWithAttr("Worksheet","ss:Name","NDCLabelerCode")

// Next, get the Table node:
x = loo_XWorksheet.GetChildWithTag("Table")

// How many immediate child nodes?
li_NumChildren = x.NumChildren

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

for i = 0 to (li_NumChildren - 1)

    // Navigate without creating new XML objects
    li_Success = x.GetChild2(i)

    if x.TagEquals("Row") then

        ls_Data0 = x.ChilkatPath("Cell[0]|Data|*")
        Write-Debug ls_Data0

        ls_Data1 = x.ChilkatPath("Cell[1]|Data|*")
        Write-Debug ls_Data1
    end if

    // Navigate back up.
    li_Success = x.GetParent2()
next

destroy x
destroy loo_XWorksheet


destroy loo_XlsDoc