Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read XML based file that is recognized by Excel
I am having a problem reading an Excel file that is XML based through C#. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
All columns not being recognized in .csv file. | Excel Discussion (Misc queries) | |||
How do you save an excel file to be read as IBM-type text file ? | Excel Worksheet Functions | |||
excel 2002 -how can you convert a file to a pdf read only file? | Excel Discussion (Misc queries) | |||
Pasword protected Excel file encrypted, how do I read this file? | Excel Discussion (Misc queries) | |||
"Unable to read file" error message when opening a Excel file that contains a PivotTable report. | Charts and Charting in Excel |