Click here to Skip to main content
Click here to Skip to main content

Working with MS Excel(xls / xlsx) Using MDAC and Oledb

, 9 Jun 2009
Rate this:
Please Sign up or sign in to vote.
This article simplifies your work with MS Excel (both xls and xlsx) using Oledb and Microsoft Data Access. Simple demonstration to create/modify/delete excel for both windows and web is provided.

Contents

  1. Introduction
  2. Available Ways to work with Excel Workbooks 
  3. Background 
  4. Working with Excel Workbook
    1. Anatomy of ConnectionString 
    2. Creating Excel Workbook 
    3. Getting Schema Defination 
    4. Retrieve Data By Worksheet Name 
    5. Retrieve Data Using Range 
    6. Manipulating Data (Insert / Update / Delete) 
    7. Drop Excel Worksheet 
  5. Description and Usage of Sample Tool 
  6. Code Explanation and Usage Info 
  7. History


Introduction   

Hi Folks, It’s long since I wrote my last article. Meanwhile, I came across with a lot of stuffs, and want to share with you one by one. This article is regarding all we need to work with Excel through our programs. 

 

While searching in google with this topic I came across some of the links, but none of them could make you a clear and concise idea of how to work with data in Excel in the most easiest way from .NET.  So I decided to jot down everything that may appear with this topic in this article.   

 

 

To work with Excel workbooks, you can do through 3 different ways :  

  1. You need 3rd party library which acts as an interface between your program and the Excel.

  2. You can make use of Excel InterOp Objects, but this requires you to have Excel installed in the development environment. This is a binding if you are going to make a product which is to be distributed.

  3. You can use OleDb data providers for Excel which comes for free with Windows. But there is one limitation though, you can access only data using this technique. You cannot do formatting through this technique.

  4. You can use XML to create excel objects which will open in MSExcel correctly. This is easier, just you need to work with xml through programming. It also supports xml stylesheets. I will also try to discuss this in another article, for the time being you may look into ExcelXMLDemo  . 

 

In this topic I am going to discuss about the 3rd method which is the most common one that we use while working with Excel.   

Background 

Excel is the most common and popular format of showing data to the client nowadays. After the most common one (PDF) you need to place another format which may show the reports to the client. Excel would be the right choice for that. Now we often come up with a requirement to generate the data in an Excel Workbook. Recently while developing, I have got one requirement to dump some data in excel sheet. Thus I thought of writing this one.

Another important requirement is to read data from MS Excel 2007 format, which is also an unusual task to learn the entire structure of Excel 2007 objects. Using MDac one can easily work with both of them without changing any of the code whatsoever.

Working with Excel workbook

The rows and columns of Excel workbook closely resembles the rows and columns of a database table. We can use MDac (Microsoft Data Access Tool) that comes free with windows update to work with excel worksheet. In case of Excel Workbooks, each worksheet acts as a table and each workbook is actually a database. You can create, insert drop excel objects through OleDb data clients from your program.

Now let us look how the connectionstring will look like : 

Normal ConnectionString : (work for xls files)

Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;\""

Office 2007 ConnectionString : (work for xlsx files)

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;\""

Here Data Source will be placed with a proper filename like C:\\test.xls or C:\\test.xlsx. If you want to create a workbook just place the one that is not existing and use Create Table to create a workbook.

The connectionstring has some parts :
1. Provider : It is the main oledb provider that is used to open the excel sheet. This will be Microsoft.Jet.OLEDB.4.0 for Excel 97 onwards Excel file format and Microsoft.ACE.OLEDB.12.0 for Excel 2007 or higher Excel file format (One with xlsx extension)

2. Data Source : It is the entire path of the excel workbook. You need to mention a dospath that corresponds to an excel file. Thus it will look like  : Data Source=C:\\testApp.xls".

3. Extended Properties (Optional) : Extended properties can be applied to Excel workbooks which may change the overall activity of the excel workbook from your program. The most common one are the following :

  • HDR : It represents Header of the fields in the excel table. Default is YES. If you dont have fieldnames in the header of your worksheet, you can specify HDR=NO which will take the columns of the tables that it finds as f1,f2 etc.
  • ReadOnly : You can also open excel workbook in readonly mode by specifying ReadOnly=true; By Default Readonly attribute is false, so you can modify data within your workbook.
  • FirstRowHasNames : It is the same as HDR, it is always set to 1 ( which means true) you can specify it as false if you dont have your header row. If HDR is YES provider disregards this property. You can change the default behaviour of your environment by changing the Registry Value [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\FirstRowHasNames] to 00 (which is false)  
  • MaxScanRows : Excel does not provide the detailed schema defination of the tables it finds. It need to scan the rows before deciding the data types of the fields. MaxScanRows specifies the number of cells to be scanned before deciding the data type of the column. By default the value of this is 8. You can specify any value from 1 - 16 for 1 to 16 rows. You can also make the value to 0 so that it searches all existing rows before deciding the data type. You can change the default behaviour of this property by changing the value of [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows] which is 8 by default.  Currently MaxScanRows is ignored, so you need only to depend on TypeGuessRows Registry value. Hope Microsoft fixes this issue to its later versions.
  • IMEX : (A Caution) As mentioned above excel have to guess a number or rows to select the most appropriate data type of the column, a serious problem may occur of you have mixed data in one column. Say you have data of both integer and text on a single column, in that case excel will choose its data type based on majority of the data. Thus it selects the data for the majority data type that is selected, and returns NULL for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.
    For example,  In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.   
    To work around this problem for data, set "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting.  You can change the enforcement of type by changing [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes] to numeric as well. 

    Thus if you look into the simple connectionstring with all of them, it will look like:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\testexcel.xls;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=FALSE\""

    or

    
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\testexcel.xlsx;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=FALSE\""


    We need to place extended properties into Quotes(") as there are multiple no of values.

Can we create Excel Workbook through this technique? 

If you are eager to know if we can create excel workbook directly through OleDB, your answer is yes. The only thing that you need to do is to specify a non-existing file in the Data Source of the connectionstring.  

string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;
                          Data Source=c:\\testexcel.xls;
                          Extended Properties\"Excel 8.0;HDR=YES\"";
string createTableScript = "CREATE TABLE newTable(a1 MEMO,a2 INT,a3 CHAR(255))";
using(conObj = new OleDbConnection(connectionstring))
{
   using (OleDbCommand cmd = new OleDbCommand(createTableScript, conObj)
   {
     if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
     cmd.ExecuteNonQuery();
   }
}

This will create a new workbook with one worksheet if the datasource file (testexcel.xls) is not existing in the location.

To Retrieve Schema Information of Excel Workbook : 

You can get the worksheets that are present in the excel workbook using GetOleDbSchemaTable. Use the following snippet.

DataTable dtSchema = null;
dtSchema = conObj.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

Here dtSchema will hold the list of all workbooks. Say we have two workbooks : wb1, wb2. The above code will return a list of wb1, wb1$,wb2,wb2$. We need to filter out $ elements.

Selecting Data From a WorkBook (Specifying Range)  

You can run simple query to select Data from an excel workbook. Say your workbook contains tables like w1,w2. Now If write SELECT * FROM [w1] or SELECT * FROM 'w1' , it will return you the whole datatable with all the data.

You can also specify the Range of selection, just write query like :

SELECT * FROM [w1$A10:B10]

Thus it will select the data only from excel Cell A10 : B10 Range.

string cmdText = "SELECT * FROM [w1$A10:B10]";
using(OleDbCommand cmd = new OleDbCommand(cmdText))
{
      cmd.Connection = this.Connection;
      OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
      DataSet ds = new DataSet();
      adpt.Fill(ds,"w1");
}

NOTE :  

A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheeet title above and to the left of the data in cell A1.

A caution about specifying ranges: When you specify a worksheet as your source, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range. Using MDAC you cannot add new rows beyond the defined limits of the range, otherwise you will receive Exception: "Cannot expand named range"

Runing DML Statement: 

You can run any DML statement like the same way you do for other databases. Samples :

INSERT INTO [w1] VALUES('firsttextcol', 2, '4/11/2009', '10:20');
[We assume First Column is either memo or Char field, 2nd col is int, 3rd is Date, 4th is Time data type]

DELETE FROM [w1] Where secondintcol=2;

UPDATE [w1] SET secondintcol = 3 where firsttextcol = 'firsttextcol';

We can use [] (Square brackets) to allow spaces within columnnames and tablenames as we do for databases.

Droping Excel WorkSheet

To drop an excel worksheet, just use  

Drop Table [w1] 

This will drop the worksheet.

If this is the last worksheet, it will not delete the workbook file. You need to do it yourself.

Using the sample Tool : 

I have added one sample application that demonstrates the problem. It includes one class  called ExcelObject which allows you to work with excel. You can use the code to work in your own application easily.

cool_image.JPG

1. Choose Browse and select a xls file. If you want to create the workbook just click on Create table to Create a table with workbook.

cool_image1.JPG

2. Click on Retrieve to get the Tables present in the workbook. These are mainly worksheets.

cool_image3.JPG

3. You can create tables using the window. Just write the column name and click on Insert. Specify Tablename and a new worksheet will be created for you.

cool_image4.JPG

4. Generate Insert statements from the dynamic screen.

cool_image5.JPG

5. You can use Go to get the data loaded into the Grid.

NOTE :

This is just a demo application. You can use the Class associated with the application call functions to do your job easy.

Using the Code 

The code for ExcelObject Class will be like this :  

using System.IO;
using System.Data.OleDb;
using System.Text;
using System.Data;
using System.Windows.Forms;

public class ExcelObject
{
     private string excelObject = = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};
                                     Extended Properties=\"Excel {3};HDR=YES\"";
     private string filepath = string.Empty;
     private OleDbConnection con = null;

        public delegate void ProgressWork(float percentage);
        private event ProgressWork Reading;
        private event ProgressWork Writeing;
        private event EventHandler connectionStringChange;

        public event ProgressWork ReadProgress
        {
            add
            {
                Reading += value;
            }
            remove
            {
                Reading -= value;
            }
        }

        public virtual void onReadProgress(float percentage)
        {
            if (Reading != null)
                Reading(percentage);
        }


        public event ProgressWork WriteProgress
        {
            add{ Writeing += value; }
            remove{ Writeing -= value; }
        }

        public virtual void onWriteProgress(float percentage)
        {
            if (Writeing != null)
                Writeing(percentage);
        }


        public event EventHandler ConnectionStringChanged
        {
            add{ connectionStringChange += value; }
            remove { connectionStringChange -= value; }
        }

        public virtual void onConnectionStringChanged()
        {
            if (this.Connection != null && 
                !this.Connection.ConnectionString.Equals(this.ConnectionString))
            {
                if (this.Connection.State == ConnectionState.Open)
                    this.Connection.Close();
                this.Connection.Dispose();
                this.con = null;

            }
            if (connectionStringChange != null)
            {
                connectionStringChange(this, new EventArgs());
            }
        }
        //ConnectionString
        public string ConnectionString
        {
            get
            {
                if (!(this.filepath == string.Empty))
                {
                   //Check for File Format
                    FileInfo fi = new FileInfo(this.filepath);
                    if (fi.Extension.Equals(".xls"))
                    {
                        // For Excel Below 2007 Format
                        return string.Format(this.excelObject, 
                                   "Jet", "4.0", this.filepath, "8.0");
                    }
                    else if (fi.Extension.Equals(".xlsx"))
                    {
                        // For Excel 2007 File  Format
                        return string.Format(this.excelObject, 
                                   "Ace", "12.0", Me.filepath, "12.0");
                    }
                }
                else
                {
                    return string.Empty;
                }
            }
        }
        //OleDbConnection to the current File
        public OleDbConnection Connection
        {
            get
            {
                if (con == null)
                {
                    OleDbConnection _con = new OleDbConnection { 
                                ConnectionString = this.ConnectionString };
                    this.con = _con;
                }
                return this.con;
            }
        }


        public ExcelObject(string path)
        {

            this.filepath = path;
            this.onConnectionStringChanged();
        }
        // Reads the Schema Information
        public DataTable GetSchema()
        {
            DataTable dtSchema = null;
            if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
            dtSchema = this.Connection.GetOleDbSchemaTable(
                   OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            return dtSchema;
        }
        //Reads table and returns the DataTable
        public DataTable ReadTable(string tableName)
        {
            return this.ReadTable(tableName, "");
        }

        public DataTable ReadTable(string tableName, string criteria)
        {

            try
            {
                DataTable resultTable = null;
                if (this.Connection.State != ConnectionState.Open)
                {
                    this.Connection.Open();
                    onReadProgress(10);
                    
                }
                string cmdText = "Select * from [{0}]";
                if (!string.IsNullOrEmpty(criteria))
                {
                    cmdText += " Where " + criteria;
                }
                OleDbCommand cmd = new OleDbCommand(string.Format(cmdText, tableName));
                cmd.Connection = this.Connection;
                OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
                onReadProgress(30);
                
                DataSet ds = new DataSet();
                onReadProgress(50);
                
                adpt.Fill(ds, tableName);
                onReadProgress(100);
                
                if (ds.Tables.Count == 1)
                {
                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                MessageBox.Show("Table Cannot be read");
                return null;
            }
        }
        //Generates DropTable statement and executes it.
        public bool DropTable(string tablename)
        {

            try
            {
                if (this.Connection.State != ConnectionState.Open)
                {
                    this.Connection.Open();
                    onWriteProgress(10);
                    
                }
                string cmdText = "Drop Table [{0}]";
                using (OleDbCommand cmd = new OleDbCommand(
                         string.Format(cmdText, tablename), this.Connection))
                {
                    onWriteProgress(30);
                    
                    cmd.ExecuteNonQuery();
                    onWriteProgress(80);
                    
                }
                this.Connection.Close();
                onWriteProgress(100);
                
                return true;
            }
            catch (Exception ex)
            {
                onWriteProgress(0);
                
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        // Creates Create Table Statement and runs it.
        public bool WriteTable(string tableName, Dictionary<string, string> 
                                                             tableDefination)
        {
            try
            {
                using (OleDbCommand cmd = new OleDbCommand(
                this.GenerateCreateTable(tableName, tableDefination), this.Connection))
                {
                    if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }
        // Generates Insert Statement and executes it
        public bool AddNewRow(DataRow dr)
        {

            using (OleDbCommand cmd = new OleDbCommand(
                          this.GenerateInsertStatement(dr), this.Connection))
            {
               cmd.ExecuteNonQuery();
            }
            return true;
        }
        // Create Table Generation based on Table Defination
        private string GenerateCreateTable(string tableName, 
                            Dictionary<string, string> tableDefination)
        {

            StringBuilder sb = new StringBuilder();
            bool firstcol = true;
            sb.AppendFormat("CREATE TABLE [{0}](", tableName);
            firstcol = true;
            foreach (KeyValuePair<string, string> keyvalue in tableDefination)
            {
                if (!firstcol)
                {
                    sb.Append(",");
                }
                firstcol = false;
                sb.AppendFormat("{0} {1}", keyvalue.Key, keyvalue.Value);
            }

            sb.Append(")");
            return sb.ToString();
        }
        //Generates InsertStatement from a DataRow.
        private string GenerateInsertStatement(DataRow dr)
        {
            StringBuilder sb = new StringBuilder();
            bool firstcol = true;
            sb.AppendFormat("INSERT INTO [{0}](", dr.Table.TableName);


            foreach (DataColumn dc in dr.Table.Columns)



            {
                if (!firstcol)
                {
                    sb.Append(",");
                }
                firstcol = false;

                sb.Append(dc.Caption);
            }

            sb.Append(") VALUES(");
            firstcol = true;
            for (int i = 0; i <= dr.Table.Columns.Count - 1; i++)
            {
                if (!object.ReferenceEquals(dr.Table.Columns[i].DataType, typeof(int)))
                {
                    sb.Append("'");
                    sb.Append(dr[i].ToString().Replace("'", "''"));
                    sb.Append("'");
                }
                else
                {
                    sb.Append(dr[i].ToString().Replace("'", "''"));
                }
                if (i != dr.Table.Columns.Count - 1)
                {
                    sb.Append(",");
                }
            }

            sb.Append(")");
            return sb.ToString();
        }


    }

After looking through the code you are clear that we are actually generating DDL and DML statements based on the Schema Defination. I know we can easily do this using OleDbCommandBuilder object, but I thought of making them myself. Funcions Exposed through this class are :

Methods :

1. GetSchema :  It returns the Schema defination datatable of the currently selected xls file. You can call this if you have connected with an existing Excel Workbook.
2. ReadTable : It automatically generates Select statement on the tablename passed and based on the criteria provided. It returns the DataTable of the currently selected excel worksheet.
3. DropTable : Drops the table name passed, and which results in actual deletion of one worksheet from the workbook. The Function returns true if successful.
4. AddNewRow : This function creates an Insert statement and inserts a new row based on the DataRow passed in.

Properties :

1. ConnectionString : You can get connectionstring of the filepath passed.
2. Connection : Returns OleDbConnection Object.

Events :   

1. ReadProgress : It generates a callback to the calling procedure on the percentage of Read of the file. You can handle this event to get the percentage progress value.
2. WriteProgress : Same as ReadProgress, only it is called during actual insert of data.
3. ConnectionStringChanged: This event occurs if FileName is changed somehow or a new file is created.

I have also provided the same class in VB.NET for those people who wants it in VB.NET.

You can find both of them from here :

Version 1:

Download ExcelWrite_Csharp.zip - 24.61 KB
Download ExcelWrite_VB.NET.zip - 26.28 KB

Version 2: 

Download ExcelWrite_Csharp_V2.zip - 24.99 KB
Download ExcelWrite_VBNET_V2.zip - 26.88 KB

I have also added one example for ASP.NET users to dynamically create one excel file and download it to clients.

You can find that from here:
Download ExcelDownload.zip - 21.62 KB

History    

1st Release : 07th June, 2009
Looking forward to update the article with new things. Hope you like this article.  
2nd Release : 10th June, 2009
Support for xlsx files (Office 2007 Files). Hope this would help you.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Abhishek Sur
Architect
India India
Did you like his post?
 
Oh, lets go a bit further to know him better.
Visit his Website : www.abhisheksur.com to know more about Abhishek.
 
Abhishek also authored a book on .NET 4.5 Features and recommends you to read it, you will learn a lot from it.
http://bit.ly/EXPERTCookBook
 
Basically he is from India, who loves to explore the .NET world. He loves to code and in his leisure you always find him talking about technical stuffs.
 
Presently he is working in WPF, a new foundation to UI development, but mostly he likes to work on architecture and business classes. ASP.NET is one of his strength as well.
Have any problem? Write to him in his Forum.
 
You can also mail him directly to abhi2434@yahoo.com
 
Want a Coder like him for your project?
Drop him a mail to contact@abhisheksur.com
 
Visit His Blog

Dotnet Tricks and Tips



Dont forget to vote or share your comments about his Writing
Follow on   Twitter   Google+

Comments and Discussions

 
QuestionTHanks! Very Good PinmemberMember 1051796411-Jan-14 5:07 
QuestionJoin/left/right Pinmemberharish411.ece22-Aug-13 12:10 
Questionthanks a lot Pinmembersolomonfox30-May-13 18:42 
GeneralGreat article! Pinmembershalsema27-Oct-12 7:14 
QuestionSome bugs i've found [modified] PinmemberMember 785799726-Oct-12 10:56 
Questionextra line with field names PinmemberPhyllis Smith24-Feb-12 12:52 
GeneralMy vote of 5 Pinmembermanoj kumar choubey19-Feb-12 21:12 
QuestionHow to search for particular record PinmemberJavedsamrt13-Jan-12 4:17 
GeneralMy vote of 5 PinmemberAlejandro Mera7-Dec-11 17:31 
Generalthanks!!! PinmemberHari Om Prakash Sharma10-Aug-11 1:37 
GeneralRe: thanks!!! PinmvpAbhishek Sur10-Aug-11 3:58 
GeneralRe: thanks!!! PinmemberMember 89232876-May-12 4:34 
GeneralRe: thanks!!! Pinmemberanil_kumar_bhakta9-Mar-14 3:31 
GeneralMy vote of 5 PinmemberTelekinetic3-Mar-11 5:10 
GeneralMy vote of 5 Pinmemberbombarderonegro16-Feb-11 4:26 
Generalupdate single cell of excel sheet using oledb connection Pinmembersameermaske20-Oct-10 23:24 
Generalupdate single cell of excel sheet using oledb connection Pinmemberravi12111120-Oct-10 23:21 
QuestionWhat about Delete and Update [modified] Pinmemberrctaubert12-Oct-10 10:38 
AnswerRe: What about Delete and Update [modified] PinmemberCosetter4-Mar-14 22:31 
QuestionMissing Code ??? Pinmemberrctaubert12-Oct-10 7:56 
AnswerRe: Missing Code ??? Pinmemberrctaubert12-Oct-10 10:33 
GeneralFault found and corrected PinmemberTelefisch8-Jun-10 0:11 
GeneralRe: Fault found and corrected PinmvpAbhishek Sur8-Jun-10 9:49 
GeneralRe: Fault found and corrected PinmemberRedDK21-Sep-10 8:59 
GeneralRe: Fault found and corrected PinmemberBiodrome19692-Dec-11 21:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 9 Jun 2009
Article Copyright 2009 by Abhishek Sur
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid