Reading Excel Spreadsheets with C# and ADO.NetHow to read and write to Excel Spreadsheets with C# using ADO.Net and Microsoft Office data connectors for OleDb with examples.
Microsoft .Net provides various methods to interact with Microsoft Office programs through the use of ADO.Net. This tutorial will look at the methods and techniques for reading from and writing to Excel spreadsheets with C# ADO.Net.
Reading Excel Spreadsheets Using ADO.Net and OleDB Provider
We can use the in-built OleDB provider for .Net to access Excel .xls spreadsheets, but before we do so, they need a little preparation. You can optionally create a Named Range to identify the table name, we also need column headings so if the spreadsheet does not contain column headings then you will need to add them.
Creating a Named Range in Excel
- With the spreadsheet open, select the data you wish to include in the query, including headings.
- Select the Insert Menu, then Name and select Define....
- Enter a name for the table and click Add.
- Close the dialogue and save the workbook.
Reading Excel Spreadsheets with C# ADO.Net
I am just going to create a simple console application that will run through all the customers and display them on the screen. The code itself is pretty straightforward, instead of SqlConnection, SqlCommand etc... we are using OleDbConnection and OleDbCommand.
using System;
using System.Data.OleDb;
namespace ConsoleApplication1
{
class Program
{
static void Main()
{
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; data source=c:customers.xls; Extended Properties=Excel 8.0;";
/ Select using a Named Range
string selectString = "SELECT * FROM Customers";
/ Select using a Worksheet name
string selectString = "SELECT * FROM [Sheet1$]";
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(selectString,con);
try
{
con.Open();
OleDbDataReader theData = cmd.ExecuteReader();
while (theData.Read())
{
Console.WriteLine("{0}: {1} ({2}) - {3} ({4})", theData.GetString(0),theData.GetString(1),theData.GetString(2),theData.GetString(3),theData.GetString(4));
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
con.Dispose();
}
}
}
}
Updating Excel Spreadsheets and Inserting Data with SQL
You can update records and insert data using regular SQL commands and execute the command through OleDb.
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; data source=c:customers.xls; Extended Properties=Excel 8.0;";
string selectString = "INSERT INTO Customers VALUES('12345', 'Acme Inc', 'Acme Way, CA', 'Testy McTest', '01234-987654')";
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(selectString, con);
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
con.Dispose();
} 
