Click here to Skip to main content
11,484,402 members (65,397 online)
Click here to Skip to main content

Excel Adapter for ADO.NET

, 28 Mar 2007 64.4K 958 55
Rate this:
Please Sign up or sign in to vote.
An article on converting a Microsoft Excel Workbook to ADO.NET Dataset using C# and Microsoft Office 2003 Interop Assemblies.

Introduction

On the recent project I worked on, I had a requirement to load data from an Excel spreadsheet to a database on a regular basis. My goal was to load the data from Excel to a ADO.NET Dataset and run my business rules. I couldn't find any articles addressing this problem and so I decided to write one.

There are two projects included in the source code package.

  • ExcelADOAdapter builds the library, ExcelADOAdapter.dll.
  • ExcelAdapterTest is a test Windows Form application.

In addition, you need to add the following references.

  • Microsoft Excel 11.0 Object Library
  • Microsoft Office 11.0 Object Library

All sample codes in this articles are in C#.

This component uses Microsoft Office 2003 Interop Assemblies to handle the Excel source and the code is written in C#. The individual worksheets on the Excel workbook will be loaded into separate Datatables on the DataSet. I have a workbook, PurchaseOrder, with 5 worksheets; Customers, Orders, OrderDetails, Shippers and Products. The load process will create a dataset with 5 tables. The name of the dataset will be set to the name of the Excel workbook, in this example PurchaseOrder. The DataTable names will be the name of the worksheets.

Screenshot - Excel_DataSource.gif

Screenshot - DataSetViewer2.gif

Background

For more information on the Office 2003 Interop Assemblies, refer MSDN Reference.

Using the code

The most important thing to remember when working with Excel is to get a valid range of cells on the worksheet to work with. We certainly don't want all the empty cells to get loaded into the DataTables. The logic that I used to get the valid range is depicted below:

/// <summary />
/// Gets the valid range of cells to work with.
/// </summary />
/// <param name="sheet" />The sheet.</param />
/// <returns />Excel Range</returns />
private Microsoft.Office.Interop.Excel.Range getValidRange(Worksheet sheet)
{
    string downAddress = "";
    string rightAddress = "";
    long indexOld = 0;
    long index = 0;
    Microsoft.Office.Interop.Excel.Range startRange;
    Microsoft.Office.Interop.Excel.Range rightRange;
    Microsoft.Office.Interop.Excel.Range downRange;

    try
    {
        // get a range to work with
        startRange = sheet.get_Range("A1", Missing.Value);
        // get the end of values to the right 
        // (will stop at the first empty cell)
        rightRange = startRange.get_End(XlDirection.xlToRight);

        /*get_End method scans the sheet in the direction specified 
        *until it finds an empty cell and returns the previous cell.
        *We need to scan all the columns and find the column with 
        *the highest number of rows (row count).
        *Then use the Prefix character on the right cell and the 
        *row count to determine the address for the valid range.
        */
        while (true)
        {
            downRange = rightRange.get_End(XlDirection.xlDown);
            downAddress = downRange.get_Address(false, false, 
            eferenceStyle.xlA1, Type.Missing, Type.Missing);
            index = getIndex(downAddress);
            if (index >= 65536) index = 0;
            if (index > indexOld) indexOld = index;
            if (rightRange.Column == 1) break;
            rightRange = rightRange.Previous;
        }

        rightRange = startRange.get_End(XlDirection.xlToRight);
        rightAddress = rightRange.get_Address(false, false, 
        ReferenceStyle.xlA1, Type.Missing, Type.Missing);

        return sheet.get_Range("A1", getPrefix(rightAddress) + indexOld);
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        startRange = null;
        rightRange = null;
        downRange = null;
    }
}

startRange = sheet.get_Range("A1", Missing.Value); sets the starting cell as A1 which is the top left cell on the Excel worksheet. The get_End method on the Range object scans the sheet in the direction specified until it finds a empty cell and returns the previous cell. But a blank cell can also hold valid values as shown below.

Screenshot - Excel_DataSource2.gif

To avoid this problem, you need to check all the columns and find the column with the highest number of rows and use that as the boundary for the valid range. But still if all the cells in the row are blank, the get_End method will not find any rows after that row.

Once after a valid range is found, we can create a two dimensional array and load the array to a DataTable. See the code segment below.

/// <summary />
/// Loads the data table.
/// </summary />
/// <param name="sheet" />The sheet.</param />
/// <returns /></returns />
private System.Data.DataTable loadDataTable(Worksheet sheet)
{
    object[] columnValues;
    System.Data.DataTable dt = new System.Data.DataTable();
    dt.TableName = sheet.Name;
    Microsoft.Office.Interop.Excel.Range range;

    try
    {
        range = getValidRange(sheet);
        object[,] values = (object[,])range.Value2;
        columnValues = new object[values.GetLength(1)];

        for (int i = 1; i <= values.GetLength(0); i++)
        {
            if (i == 1 && _firstRowHeader)
            {
                for (int j = 1; j <= values.GetLength(1); j++)
                {
                    object value = values[i, j];
                    if (value != null) dt.Columns.Add(value.ToString());
                    else dt.Columns.Add("");
                }
            }
            else
            {
                for (int j = 1; j <= values.GetLength(1); j++)
                {
                    object value = values[i, j];
                    if (value != null)
                    {
                        columnValues[j - 1] = value.ToString();
                    }
                    else
                    {
                        columnValues[j - 1] = "";
                    }
                }
            dt.Rows.Add(columnValues);
            }
        }
        return dt;
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        range = null;
    }
} 

I hope you will find the code useful. Thank you.

History

  • 28 March 2007 - Updated source

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Tomy Rhymond
Architect Dedicated Technologies, Inc
United States United States
No Biography provided
Follow on   Twitter

Comments and Discussions

 
GeneralA Faster Copy... Pin
PeterMoon24-May-09 15:37
memberPeterMoon24-May-09 15:37 
GeneralUpdating individual cells Pin
Member 44670355-Feb-09 20:20
memberMember 44670355-Feb-09 20:20 
GeneralProblem & solution for cells containing dates Pin
Ernest_Paez23-Oct-08 5:36
memberErnest_Paez23-Oct-08 5:36 
GeneralRe: Problem & solution for cells containing dates Pin
tedirgin28-Apr-10 4:20
membertedirgin28-Apr-10 4:20 
GeneralWorks like a charm! Pin
joshua_rivers16-Jul-08 17:07
memberjoshua_rivers16-Jul-08 17:07 
GeneralDosn't work with Visual Studio.Net 2005 Pin
Majdi Jamal17-Nov-07 11:28
memberMajdi Jamal17-Nov-07 11:28 
Question"Old Format or invalid type library" error??? Pin
[SK]SpooK30-Oct-07 5:30
member[SK]SpooK30-Oct-07 5:30 
GeneralInstall excel on server.` Pin
SinghalManu7-Jun-07 4:57
memberSinghalManu7-Jun-07 4:57 
GeneralRe: Install excel on server.` Pin
fandijunior7-Sep-07 12:42
memberfandijunior7-Sep-07 12:42 
GeneralRe: Install excel on server.` Pin
gg423713-Nov-09 0:59
membergg423713-Nov-09 0:59 
Generalwork with web application Pin
Nigam SAMir28-Mar-07 19:34
memberNigam SAMir28-Mar-07 19:34 
GeneralRe: work with web application Pin
jw12329-Mar-07 2:04
memberjw12329-Mar-07 2:04 
GeneralUse Odbc Pin
Kevin I28-Mar-07 16:57
memberKevin I28-Mar-07 16:57 
GeneralRe: Use Odbc Pin
chai9332328-Mar-07 18:45
memberchai9332328-Mar-07 18:45 
GeneralRe: Use Odbc Pin
thund3rstruck29-Mar-07 4:52
memberthund3rstruck29-Mar-07 4:52 

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 | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 28 Mar 2007
Article Copyright 2007 by Tomy Rhymond
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid