excel into dataset via xml

[regulardude]

Hi,

Trying to find the simplest way to import an excel file, in regular excel format, using the openfiledialog, into a untyped dataset.

Steps:
1. Use openFileDialog to browse for excel file to import.
2. Code reads excel file into dataset using xml

DataSet is untyped, schema varies from file to file.

I have the openDialog working, don't need that code.

Went through "Reading XML Data into a Dataset" Walkthrough.

Need Excel part, if possible would prefer not to connect to excel file.

Using Visual Studio 2005, C#

Thanks in advance

 

[jmeckley]

you can use a odbc connection to query an excel spreadsheet. use the file dialog to get a path to the file. use this in the connection string, and write a query to select data from the worksheet.

if the workbook is saved in xml format then you could load the workbook into an xmlready and parse the file that way as well.

your not going to be able to get data from the workbook without connecting to it. that's like wanting to query a database without opening a connection, or visiting a website without logging onto the internet.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[regulardude]

jmeckley,

I would prefer to transform the existing excel file into xml format and then fill the dataset.

Any thoughts?

 

[jmeckley]

if the file is saved as a workbook in xml then use xlst to transform the xls into xml a dataset can consume, pass the transformed xml to the dataset and dispose of the xls xml objects.

if the xls is saved in standard xls (binary) format you are better off querying the xls via ado.net.

the alternative is to use the Excel Interop assemblies. This requires office/excel is installed on the machine.

ADO.net is much more effecient at querying the data from, so there is less cpu usage than interops.

If this were my project I would use ADO.net in a distant second I would use xslt. I wouldn't touch Interop assemblies at all.

Jason Meckley
Programmer
Specialty Bakers, Inc.