Using System.Xml.XmlDocument to parse an XML Excel document

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am loading an xml excel document into the XmlDocument class, and using the
SelectSingleNode method to try and select the node of the Worksheet in the
document. It is not returning any results, but my XPath is right as far as I
can tell.

This is my code:
xmlDoc.Load( "Export\\antivirus.xml" );
XmlNode original = null;

original = xmlDoc.SelectSingleNode( "/Workbook/Worksheet" );
worksheet = original.Clone();

But no node is returned and original is null. Am I doing something wrong?
Here is a copy of the xml being loaded:

<?xml version="1.0"?>
<?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">
<Author>Chris Balmer</Author>
<LastAuthor>Chris Balmer</LastAuthor>
<LastPrinted>2005-11-28T14:21:24Z</LastPrinted>
<Created>2005-11-28T14:15:25Z</Created>
<Company>SMW Automotive</Company>
<Version>11.6568</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>12015</WindowHeight>
<WindowWidth>17115</WindowWidth>
<WindowTopX>360</WindowTopX>
<WindowTopY>120</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Font x:Family="Swiss" ss:Size="18" ss:Bold="1"/>
</Style>
<Style ss:ID="s22">
<Font x:Family="Swiss" ss:Size="14" ss:Italic="1"/>
</Style>
<Style ss:ID="s23">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/>
</Borders>
<Font x:Family="Swiss" ss:Size="12"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="5" x:FullColumns="1"
x:FullRows="1">
<Column ss:AutoFitWidth="0" ss:Width="178.5"/>
<Column ss:AutoFitWidth="0" ss:Width="78"/>
<Row ss:AutoFitHeight="0" ss:Height="23.25">
<Cell ss:StyleID="s21"><Data ss:Type="String">Antivirus
Audit</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="18.75">
<Cell ss:StyleID="s22"><Data ss:Type="String">Location</Data></Cell>
</Row>
<Row ss:Index="4" ss:AutoFitHeight="0" ss:Height="15.75">
<Cell ss:StyleID="s23"><Data ss:Type="String">Computer</Data></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="String">Date</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">Test</Data></Cell>
<Cell><Data ss:Type="String">Test</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>3</ActiveRow>
<ActiveCol>2</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>


Can anyone help me figure out how to get the Worksheet node without using
static numbers (i.e. XmlDocument.ChildNodes[2])?

Thanks,
Chris
 
Hi

Because the Workbook has namespace definition, we need to use the
XmlNamespaceManager to call the SelectSingleNode.

Here is the sample code for your reference.
static void Main(string[] args)
{
try
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(@"C:\temp\testdoc.xml");
XmlNode original = null;

XmlNamespaceManager context = new
XmlNamespaceManager(xmlDoc.NameTable);
context.AddNamespace("myNameSpace",
"urn:schemas-microsoft-com:office:spreadsheet");
original =
xmlDoc.SelectSingleNode("/myNameSpace:Workbook/myNameSpace:Worksheet",
context);
Console.WriteLine(original.OuterXml);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top