Cyberkruz.com

I wrote this little WPF application as the demonstration for my previous article on reading excel spreadsheets. I figured everyone should be able to test it and play around with it. Just a note: The code was written very fast and inefficiently so don’t consider it anything more than proof of concept. At any rate, here you go.

ExcelImporter.zip (18.51 kb)

C#, Excel excel, c#, solution, wpf

Overview

This article explains how to read Excel documents with C#. After two or three projects dealing with copying and pasting data from sql server to excel, I decided that it would be a good time to write some code to help with the process. Here is the basic code for reading an excel spreadsheet.

Code

// Create the connection string
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
    location + @";Extended Properties=""Excel 8.0;HDR=YES;""";

// Create a factory
DbProviderFactory factory =
       DbProviderFactories.GetFactory("System.Data.OleDb");

// Create an adapter
DbDataAdapter adapter = factory.CreateDataAdapter();

// Create a select command to get the dataset
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = "SELECT * FROM [" + sheetName + "$]";

// Set up the connection and the connection string
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
selectCommand.Connection = connection;

// Set up the command
adapter.SelectCommand = selectCommand;

// Create the dataset
DataSet ds = new DataSet();

// Load the results
adapter.Fill(ds);

return ds;

Basically, I am just passing in a location of the document and the sheet name for the particular excel spreadsheet. OLEDB supports Outlook so bringing it into a dataset isn’t that much work. I realize that the article is relatively slim, but I hope it gets viewers pointed in the right direction. Thank you Dave Hayden for my step.

Happy Coding

Excel, C#