I am having a problem reading an Excel file that is XML based. The directory
I am reading contains Excel files that can be of two types. Either generic Microsoft
based or XML based. I am reading the Microsoft based files with an OleDbDataAdapter.
Then filling the contents of the first worksheet into a dataset.
However when I try to add the XML based file to my dataset using an XmlTextReader
I can never seem to get it to save to a different table in the dataset in the same
format as when I use an OleDbDataAdapter for the generic Excel files. If I save
it(using ds.ReadXml) to my existing DataSet (which already has one table in it)
and no table gets created.
I tried saving it to a brand new DataSet and saw that 10 tables were created. I assume
mostly all are these are style sheet information. The last table (entitled "data") seems
to have all my data. I then tried to do a copy of that table and place it in my existing
DataSet. However column header information is not captured and I can't read the
table because when I open up the DataSet at a (later point in my program) I retrieve
datarow elements by specifying column names and the OleDb connection complains
it can't find them.
Perhaps there is a tweak I need to the XmlTextReader or perhaps my XML based
Excel file? I was thinking worse case I would try to find some OS command to convert
the file to a real Microsoft based format but obviously it would be easier to tweak
the XmlTextReader or the file itself. Here is a snippet of the code along with a
sample of the XML text file. If anyone has any solutions, it would be greatly appreciated!!
OleDbConnection objConn = new OleDbConnection(fileConnectionString);
try
{
objConn.Open(); //<---- This will trigger an exception if the file is XML based
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(fileContents,filename);
}
catch
{
DataSet ds = new DataSet();
// Create new FileStream to read schema with.
System.IO.FileStream fsReadXml = new System.IO.FileStream (filename, System.IO.FileMode.Open);
// Create an XmlTextReader to read the file.
System.Xml.XmlTextReader myXmlReader = new System.Xml.XmlTextReader(fsReadXml);
// Read the XML document into the DataSet.
ds.ReadXml(myXmlReader, XmlReadMode.ReadSchema);
// Close the XmlTextReader
myXmlReader.Close();
DataTable xmlData = ds.Tables["data"].Copy();
xmlData.TableName = filename;
fileContents.Tables.Add(xmlData);
}
finally
{
objConn.Close();
}
<?xml version='1.0'?>
<ss:Workbook xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>
<ss:Styles>
<ss:Style ss:ID='1'>
<ss:Font ss:Bold='1'/>
</ss:Style>
<ss:Style ss:ID="s22">
<ss:NumberFormat ss:Format="Short Date"/>
</ss:Style>
</ss:Styles>
<ss:Worksheet ss:Name='Survey'>
<ss:Table><ss:Column ss:Width='60'/>
<ss:Column ss:Width='60'/>
<ss:Column ss:Width='60'/>
<ss:Column ss:Width='60'/>
<ss:Column ss:Width='60'/>
<ss:Column ss:Width='60'/>
<ss:Column ss:Width='60'/>
<ss:Column ss:Width='60'/>
<ss:Row ss:StyleID='1'>
<ss:Cell><ss:Data ss:Type='String'>MEM_ID</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>CALL_DATE</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>SURVEY_FORM</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>SOURCE</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>REASON_CODE</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>ACTION_CODE</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>OPERATOR_ID</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>COMMENTS</ss:Data></ss:Cell>
</ss:Row>
<ss:Row>
<ss:Cell><ss:Data ss:Type='String'>555555555</ss:Data></ss:Cell>
<ss:Cell ss:StyleID="s22"><ss:Data ss:Type='DateTime'>2005-10-21T12:00:00.000</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>DNTL</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>MSPA</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>ee</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>ff</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'>elmer_fudd</ss:Data></ss:Cell>
<ss:Cell><ss:Data ss:Type='String'></ss:Data></ss:Cell>
</ss:Row>
</ss:Table> </ss:Worksheet> </ss:Workbook>
