How to retrieve data from Excel into C#

In this post I will show you how to retrieve data from an Excel spreadsheet into a C# ASP.NET application. For illustration purposes, I have created a very simple Excel spreadsheet containing a list of URLs.



Let's suppose we have an N-Tier ASP.NET application with the usual Data tier and User Interface tier. We will add a class to our Data tier that will read the Excel spreadsheet, load the contents of the spreadsheet into a DataTable, and return the loaded DataTable. Then, in the User Interface tier, we will call the Data tier class and display the URLs in an ASP.NET Web Page.

1) Add a key to the web config file to store the path of the folder that contains your Excel spreadsheets.

<appSettings>

    <add key="ExcelPath" value="C:\AllWork\ExcelSpreadsheets"/>

</appSettings>

2) Create a class in your Data tier project for the purpose of retrieving data from Excel spreadsheets. Add a couple of using statements for the System.Configuration, System.Collections.Generic, System.Data.SqlClient, System.Data, System.Data.OleDb and System.IO namespaces:

using System;

using System.Linq;

using System.Text;

using System.Configuration;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data;

using System.Data.OleDb;

using System.IO;

namespace DataLayer

{

public class Spreadsheets

{

}

}

3) Add a method to the class to retrieve data from your Excel spreadsheet. In this example, the method will read the data from the first worksheet in the Excel spreadsheet and load it into a DataTable. Then, the method will return the DataTable loaded with all the contents of the spreadsheet worksheet.

Note that each line of code has a comment above it describing what it does. Anyway, this is what the entire class should look like.

using System.Configuration;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data;

using System.Data.OleDb;

using System.IO;

namespace DataLayer

{

    public class Spreadsheets

    {

        /// 

        /// Method for retrieving URLs from an Excel spreadsheet.

        /// 

        /// DataTable of URLs

        public static DataTable GetURLs()

        {

            //Retrieve the spreadsheet directory path from the web config file

            string path = ConfigurationManager.AppSettings["ExcelPath"];

            //Append the name of the Excel Spreadsheet to the path

            path += "\\URLs.xls";

            //Create a DataTable to be returned by this method

            DataTable dtURLs = new DataTable("dtURLs");

            //Create an OLEDB connection string to access the Excel spreadsheet

            string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

            conn += path;

            conn += ";Extended Properties=\"Excel 8.0;HDR=Yes;\"";

            //Check if the file exists

            if (File.Exists(path))

            {

                //Instantiate an OleDBConnection using the conn variable just created

                OleDbConnection dbConnection = new OleDbConnection(@conn);

                //If the OleDBConnection is not open, then open it

                if (dbConnection.State != ConnectionState.Open)

                {

                    dbConnection.Open();

                }

                //Declare a try-catch-finally statement

                try

                {

                    //Create a temporary DataTable using the opened OleDBConnection

                    //The GetOleDbSchemaTable method return the first worksheet in 

                    //the Excel spreadsheet in the form of a table

                    DataTable dtTableSchema = dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    //If no data was returned from the OleDBConnection throw an error

                    if (dtTableSchema == null || dtTableSchema.Rows.Count <> 
                    {
                        throw new Exception("Error: Could not determine the name of the first worksheet.");
                    }
 
                    //The name of the first worksheet in the Excel spreadsheet is contained
                    //in the name of the DataTable
                    string firstSheetName = dtTableSchema.Rows[0]["TABLE_NAME"].ToString();
 
                    //Create an OleDbCommand using a select statement and the connection to retrieve 
                    //the data from the Excel spreadsheet
                    OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM [" + firstSheetName + "]", dbConnection);
 
                    //Create an OleDbDataReader to store the results from 
                    //executing the select statement in the OleDbCommand
                    OleDbDataReader dbReader = dbCommand.ExecuteReader();
 
                    //Load the DataTable to be returned from this method
                    //using the OleDbDataReader.
                    dtURLs.Load(dbReader);
                }
                finally
                {
                    //Close and dispose the connection
                    if (dbConnection.State != ConnectionState.Closed)
                    {
                        dbConnection.Close();
                        dbConnection.Dispose();
                    }
                }
                //Return the DataTable
                return dtURLs;
            }
            return null;
        }
    }
}
 

4) Create an ASP.NET web page in your presentation tier with a label and a dropdownlist. Here is the html for the page.

<%@ Page="" Language="C#" AutoEventWireup="true" CodeBehind="RetrieveFromExcel.aspx.cs" Inherits="NTierWeb.Pages.RetrieveFromExcel" %>

  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

  <html xmlns="http://www.w3.org/1999/xhtml" >

    <head runat="server">

      <title></title>

    </head>

    <body>

      <form id="form1" runat="server">

        <div>

          <asp:Label ID="lblURL" runat="server" Text=""></asp:Label>

          <br />

          <br />

          <asp:DropDownList ID="ddlURLs" runat="server" Width="300px">

          </asp:DropDownList>

        </div>

      </form>

    </body>

  </html>

5) In the codebehind, we'll add a method that will accept a string parameter (for the company name.) Then, it will call the GetURLs method in the Spreadsheets class and will receive a DataTable loaded with the contents of the first worksheet in the Excel spreadsheet. Then, it will search the Datatable until a match is made between the company and the company's URL. If a match is made it will load the URL into the label in the web page. Finally, it will also load all the URLs into a dropdown list control.

We will call this method from the Page_Load event in the web page.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

//Add a using statement to refer to Data tier in your solution

using DataLayer;

namespace NTierWeb.Pages

{

    public partial class RetrieveFromExcel : System.Web.UI.Page

    {        

        protected void Page_Load(object sender, EventArgs e)

        {

            //Here we call the GetCompanyURL method to find the URL 

            //for Microsoft and display the URL in a label.           

            lblURL.Text = GetCompanyURL("Microsoft");            

        }

        private string GetCompanyURL(string strCompany)

        {

            string url = "";

            //Create a DataTable object

            DataTable dtURLs = new DataTable("dtURLs");

            //Call the GetURLs method in the DataTier to 

            //retrieve a DataTable loaded with the contents

            //of teh first worksheet in the Excel spreadsheet

            dtURLs = DataLayer.Spreadsheets.GetURLs();

            //Check if the DataTable has data

            if (dtURLs != null)

            {

                //Load the dropdown list with the contents of

                //the table for the column "Link".  The column

                //"Link" in our DataTable contains all the URLs 

                //in the spreadsheet under the "Link" header.

                ddlURLs.DataTextField = "Link";

                ddlURLs.DataSource = dtURLs;

                ddlURLs.DataBind();

                //Loop through each row in the table

                for (int y = 0; y < dtURLs.Rows.Count; y++)

                {

                    //Load the content of the column "Keyword" into a string.

                    //The column "Keyword" contains the Company names.

                    string strKeyCompany = dtURLs.Rows[y]["KeyWord"].ToString();

                    //Check to see if the Company name matches the one passed

                    //to this method.

                    if (String.Equals(strCompany.ToUpper(), strKeyCompany.ToUpper()))

                    {

                        //If a match is found retrieve the URL

                        //and exit the loop.

                        url = dtURLs.Rows[y]["Link"].ToString();

                        break;

                    }

                }

            }

            //Return the Company's URL

            return url;

        }        

    }

}

5) This is what the web page looks like after loading: