Reading excel files

Question

Can anyone help me with reading Excel files from within a C# code? i am trying to read a set of excel files each one having multiple worksheets. any thoughts/ideas?

gr8bhangi · Answer

stefan: It was rude of me not to acknowledge your responses - i was able to connect to each excel file using the simple connection string of the form Provider=Microsoft.Jet.OLEDB.4.0;Extended properties=Excel 8.0; + Data Source= + strPathName + strFileName I was making a trivial syntax error earlier and hence could not get the connection at all. My sincere thanks to you for your suggestions.

S_Kiryazov · Answer

I just realised that maybe this is not all that you ask. You mentioned multiple excel files that may eventually be dynamically generated. Then you can't create a system dsn for every one. So you may create a file dsn (just the same way as system dsn just use the next tab in data sources). The difference is that the file dsn is stored in a file and you can explore it's structure and dynamically create file dns's for every excel file. Multiple worksheets in a file were also mentioned. If you need to manage the xls file structure you've got to reference the Microsoft.Office.Interop.Excell assembly - there is a fine excel document object model there. Haven't tried any of there just suggestions.

S_Kiryazov · Answer

Well there are two ways to achieve this. The first and more civilised one is with ODBC data source through OleDB. I have never tried this but it should work. Do the following: Run Data sources (ODBC) from Administrative tools. Go to tab System DSN Click add then choose Microsoft Excel driver. Type the name of the data source say Excel test. Click finish and then Select workbook. This normally is enough but you've got to build the ConnectionString by hand. Here is a workaround. Create a new text file change it's extension to .udl and click properties. You get the datalink properties. Go to Connection tab and from the Use data source name combo choose your newly created data source. It must be there. That's all click OK and open the .udl file with a text editor and you've got the connection string. Mine is Provider=MSDASQL.1;Persist Security Info=False;Data Source=Excel test Now create the OleDbConnection object and bla bla bla And the second way to perform sql queries on excel which I have tested and surely works is the following: Create a new access database or use an existing one with an appropriate table. Select and copy all rows from the excel application and paste them into the access table. Now you've got a convinient ms access database to query through OleDb. If you modify the data you can later paste it back in excel. The most powerful feature of this approach is that you can import data from almost anywhere - html table word document etc. But ms access will not allow you to paste the rows from an html table directly into the table. You've got to paste the data into excell and then copy it again and paste in acces. That's all. Hope it helps