Web Design that taps into the haromny and vision of your dreams.

Reading Excel Spreadsheets with C# ADO.Net

Using OLE providers for reading Excel spreadsheets with C#

Written By on in C#

344 words, estimated reading time 2 minutes.

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 data stored in Excel spreadsheets.

Active Data Objects (ADO.Net) Series
  1. What are Active Data Objects?
  2. Connecting to a SQL Server Database with C#
  3. Consuming Data in Windows Forms C# Applications
  4. Using ADO.Net Controls in ASP.Net Pages
  5. Modifying Data and Updating Databases with C#
  6. Connecting to MySql Databases with C# / ASP.Net
  7. Reading Excel Spreadsheets with C# ADO.Net
  8. Creating SQL Server Stored Procedures in C#
  9. Calling SQL Server Stored Procedures from C#
  10. Importing and Exporting XML from a DataSet

Using ADO.Net and OleDB Provider

We can use the inbuilt 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

  1. With the spreadsheet open, select the data you wish to include in the query, including headings.
  2. Select the Insert Menu, then Name and select Define....
  3. Enter a name for the table and click Add.
  4. Close the dialogue and save the workbook.
Excel OLEDB
Excel OLEDB

Accessing the Data

I am just going to create a simple console application that will run through all the customers and display them on screen. The code itself is pretty straight forward, 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 Spreadsheets and Inserting Data

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();
}

Last updated on: Friday 23rd June 2017

 

Comments
Nirav Modi

Nirav Modi

I am given range for only one cell where data is "date". But it's not give me proper output. Please reply me ASAP. Thanks

Reply to Nirav Modi
Rhodri

Rhodri

Any idea how the Jet OleDB commands can recognise dynamic named ranges? I can't get Jet to recognise them, although a static range like your example works fine.

Thanks

Reply to Rhodri
Chris

Chris

The only problem I am having with reading an Excel file this way, is that it always returns my rows & fields in alphabetical order! I want the select statement to return everything in the order it was entered in the Excel file. Is there any way to prevent the provider from "automatically" ordering the columns & rows in alphabetical order? To see what I'm talking about, change the Account Number in your first row from "00001" to "10000" and watch it appear at the end of the result set 'all of a sudden'.

Reply to Chris
Sheetal

Sheetal

===The code below works===

using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string connectionString =@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsuserDesktopBook1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

string selectString = "SELECT * FROM Customers";
selectString = "SELECT * FROM [Sheet1$]";

OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(selectString,con);
con.Open();
try
{
//to read the data
OleDbDataReader theData = cmd.ExecuteReader();
while(theData.Read())
{
Response.Write(theData[0].ToString()+" ");
Response.Write(theData[1].ToString());
Response.Write("");
}
cmd.Dispose();

//To insert the data

cmd = new OleDbCommand("insert into [Sheet1$] values(103,"Company G","Address 7")", con);
cmd.ExecuteNonQuery();

}
catch(Exception ex)
{
Response.Write(ex.Message);
}
}
}

Reply to Sheetal
Chetankumar Akarte

Chetankumar Akarte

Hi,

If you want to read data from a .xlsx file, you have to use Microsoft.ACE.OLEDB.12.0 as the provider. This is the new Access database engine OLE DB driver and is also capable of reading Excel 2003. Use it to read xlsx (Excel 2007) data.

Get more info on Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET here...

http://www.tipsntracks.com/100/read-and-display-data-from-an-excel-file-xsl-or-xlsx-in-aspnet.html

Thanks & Regards
Chetankumar Akarte

Reply to Chetankumar Akarte
biham

biham

HI There
I am Getting the following error. I am using Vs 2008 and Excel 2007. I know the path and spelling are all correct any help will be greatly appreciated

string The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sur
e the object exists and that you spell its name and the path name correctly.
Press any key to continue . . .

Reply to biham
Tim Trott

Tim Trott

Hello,

Sheet1$ refers to the sheet name. If you have changed the sheet name then you must update the code accordingly. Also, I have not tried this with Excel 2007 which uses a different file format. If you are not doing so already you may wish to try exporting as Excel 2000.

Hope that helps

Reply to Tim Trott
Tim Trott

Tim Trott

viranjuda, It's difficult to diagnose a problem without seeing the code.

There are several things that may cause this error including file permissions, file location (local or network), are you calling the code from a console application or ASP.Net? What is the query you are trying to execute?

If you could provide more information we may be better able to assist.

Reply to Tim Trott
viranjuda

viranjuda

System.Data.OleDb.OleDbException: Operation must use an updateable query.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARA
MS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Ob
ject& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behav
ior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at ExcelConn.ExcelConnection.ExcelInsertPermintaan(String sNo, String sAppCod
e, String sTglMinta, String sNmDebitur, String sAlamat, String sNomor, String sN
oKTP_Passport, String sDatiII, String sUser, String sTglLahir, String sKodePos,
String sNPWP, String sTempatLahir, String sDIN, String sNmAlias, String sNoPassp
ort) in D:AJDSMegaConsoleTryExcelConnIExcelConnIConnectionForExcel.cs:li
ne 30
at ExcelConn.MySQLConnection.MySQLConnector() in D:AJDSMegaConsoleTryEx
celConnIExcelConnIConnectionForMySQL.cs:line 72
at ExcelConn.Program.Main(String[] args) in D:AJDSMegaConsoleTryExcelCo
nnIExcelConnIProgram.cs:line 16

Does anybody know that this exception means?

Reply to viranjuda
Tim Trott

Tim Trott

This code was built and compiled on Windows XP SP3 with Visual Studo 2005 and Office 2000 installed. It has also been tested on a fresh copy of Windows XP with SP2 and .Net framework 2, MS Office is not installed.

Create a standard console application with the above code, no additional references are required, System, System.Data and System.Xml automatically referenced.

@anonymous, please can you provide any more details of your error (project type, references, visual studio version etc..)

@Pratap, please could you also provide some more details, such as the error message?

Reply to Tim Trott

Error 2 The type 'System.ComponentModel.Component' is defined in an assembly that is not referenced. You must add a reference to assembly 'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. D:IdeasCSExplorerExplorer.cs 38 13 Explorer.

I'm getting this error message, hat .Net environment you are supose to have, what refrences, etc. very amateurish and not useful at all.

Reply to
Pratap

Pratap

how to get the connection string in Console Application.

It shows like I don't have proper OLE db.

Since whatever You have used in code may be for your system. My system settings are different. So how can I get the Connection string in my system for my project.

Reply to Pratap

 

Leave a Reply

Your email address will not be published.





If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.

Copyright © 2001-2018 Tim Trott, all rights reserved. Web Design by Azulia Designs

This web page is licensed for your personal, private, non-commercial use only.

Disclaimer, Privacy & LegalSitemapContact Me