C# – MS Excel XML format accessing with Ole connection

cexcelms-officexml

How can I access a XML Excel format file with an OLE Connection? Can it be done? I have accessed the normal Excel Format 2003 xlsx file but when I generate the Excel XML format I can't access with OLE. Thanks in advance for any help.

I can open the file manually in Excel but I'm unable to connect to it through the OleDb Connection. I'm dumping my own XML in the Excel file through a memory stream on a shared drive. If I manually save a file from Excel as a Excel 97-2003 format. But when I save it as a XML file I can't access it through the dataconnection.

My connection looks like this….

String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" + file_path + ";Extended Properties=Excel 8.0;";

Thanks for the help in advanced.

This is the code that I save to an xls file and open in Excel it works fine. I generate the XML from my app. I'm trying to access the same file with Ole?

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40"
<head>
<style>
<!--table
@page
{margin:0.0in .0in 0.34in .0in;
mso-header-margin:.5in;
mso-footer-margin:.18in;
mso-footer-data:"Page &P of  &N";
mso-page-orientation:landscape;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Summary</x:Name>
<x:WorksheetOptions>
<x:FitToPage/>
<x:FitToPage/>
<x:Print>
<x:FitHeight>32767</x:FitHeight><x:ValidPrinterInfo/>
<x:HorizontalResolution>600</x:HorizontalResolution>
<x:VerticalResolution>600</x:VerticalResolution>
<x:DoNotDisplayGridlines/>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>

<Worksheet ss:Name="DATE1">
  <Table ss:ExpandedColumnCount="13" ss:ExpandedRowCount="2" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
   <Row ss:Height="63.75">
   <Cell ss:StyleID="s63"><Data ss:Type="String">DATE</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something1</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something2</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something3</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something4</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something5</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something6</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something7</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something8</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something9</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something10</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something11</Data></Cell>
   <Cell ss:StyleID="s63"><Data ss:Type="String">something12</Data></Cell>
  </Row>
  <Row>
   <Cell ss:StyleID="s65"><Data ss:Type="String">something</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">268763</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">1331476</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">48267</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">1283209</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">34589</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">0.97304492097546003</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">23921</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">10668</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">0.99168646728631105</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">1730</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">8938</Data></Cell>
   <Cell ss:StyleID="s65"><Data ss:Type="Number">99.303464985049203</Data></Cell>
  </Row>
 </Table>
 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  <FitToPage/>
   <Print>
   <FitHeight>32767</FitHeight>
   <ValidPrinterInfo/>
    <HorizontalResolution>600</HorizontalResolution>
   <VerticalResolution>600</VerticalResolution>
  </Print>
  <Selected/>
  <Panes>
   <Pane>
    <Number>3</Number>
     <RangeSelection>R1C1:R2C13</RangeSelection>
   </Pane>
  </Panes>
  <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

</xml><![endif]-->

</head>
</html>

Best Answer

From your updated question it seems as if you are using Excel's HTML export (which embeds some sort of XML into the HTML head of the exported page that might be rendered by Internet Explorer. I think this format dates back to Excel 2000 and does not support data binding.

Are you required to use such a format for legacy reasons? By now there are much better options, e.g. with the new XML-based Office OpenXML formats of Office 2007 or the XML Spreadsheet 2003 format introduced by Excel 2003.

Related Topic