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 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 they need a little preparation before we do so. 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, 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 will create a simple console application to run through all the customers and display them on the screen. The code is straightforward; instead of SqlConnection, SqlCommand, etc., we use 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, 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();
} 
