CODEWITHSUNDEEP
c#excel
ctbram
ctbram

Reputation: 65

Query an Excel spreadsheet

I have a project where I need to query an Excel spreadsheet. Basically I need to read a two column Excel spreadsheet into a dictionary table. I have a database of error codes and descriptions for each code. The project requires Excel (Excel 2007 compatible .xlsx) because the user wants to add new Key/Description pairs by editing an Excel spread sheet.

The idea is that I am going to get a list of error codes and I simply want to be able to display a table in a worksheet with error code and the associated description from the Excel spreadsheet, or no description found. I figure if I can get the table (which is not very long) into a dictionary table the rest would straight forward but I cannot even find a starting point using the current version C# and 4.5.2 .NET API. I just need to open the worksheet and read the two column table into a dictionary table and I am off to the races.

Can someone please get me started in the right direction please?

Upvotes: 0

Views: 120

Answers (1)

Mohit S
Mohit S

Reputation: 14044

You can probably use OleDbConnection to get the data from Excel.

OleDbConnection con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source="
                +  xlsx file with path
                + ";Extended Properties=Excel 8.0;");

StringBuilder stbQuery = new StringBuilder();
stbQuery.Append("SELECT * FROM [" + SHEETNAME_HERE + "$A1:B65]");
OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.ToString(), con);

DataSet dsXLS = new DataSet();
adp.Fill(dsXLS);

Once you have the data filled up, you can fetch the data in dictionary or any other object of List Enumerable.

Upvotes: 1

Related Questions