How to display data from Excel file in to a GridView? Articles, samples and tutorials

Let us start with creating a web application in VS 2010 and follow the below given steps to upload excel and display excel data into a GridView.

Here our goal is to upload Excel file into a folder and display the data in Gridview control using ASP.NET. There are couple of ways to read excel using Microsoft Interop assemblies and oledb. Here I have used oledb to connect to the excel.

To learn more about uploading files to web server, read

Front end Design of application for displaying data from Excel file in to a GridView

First we will design the front end. Here we use one FileUpload control to select the Excel file, a Button control to upload and display the data and finally a GridView to display the excel data in a tabular format.


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="UploadExcel.aspx.cs" 
Inherits="Practice2010.UploadExcel" %>

<!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:FileUpload ID="FileUpdExcel" runat="server" />
        <asp:Button ID="btnDisplay" runat="server" onclick="btnDisplay_Click" 
            Text="Display Data" />
        <br />
        <asp:GridView ID="grdExcel" runat="server">
        </asp:GridView>
        <br />
        <asp:Label ID="lblMsg" runat="server"></asp:Label>
    
    </div>
    </form>
</body>
</html>

Connection Strings used in the application to display data from Excel file in to a GridView

To upload the excel file using ASP.NET we will be using two connection strings in Web.config because Excel 2003 and Excel 2007 use different providers.

Connection string for Excel 2003 (.XLS):-


<add name ="ExcelCon2003" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'"/>

Connection string for Excel 2007 (.XLSX):-


<add name ="ExcelCon2007" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'"/>

In the above connectionString I have used a place holder for DataSource because DataSource will be decided at run time when user selects the Excel file.

When user select the excel file we will be saving the same in a folder. Below web.config entry is used to define the folder name.


<appSettings>
		<add key ="UploadFolderPath" value ="Upload/"/>
</appSettings >

Excel file sample used to upload and display in Gridview
Excel File sample to upload and display in Gridview using ASP.NET

ASP.NET code to upload Excel and display the data into Gridview

We have to add three namespaces,


using System.IO;
using System.Data;
using System.Data.OleDb;

Below is the complete code from code behind.

First we have to create a folder in our root folder based on the name we have mentioned in web.config file. As per the code we have to create a folder with the name "UploadFolderPath". Then on button click event we save the selected excel file in this folder.

To display the data from the uploaded excel we will be using oledb connection. Please make sure to close the oledb connection once you display the data in Gridview otherwise you may get error as "The process cannot access the file because it is being used by another process."


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.OleDb;

namespace Practice2010
{
    public partial class UploadExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnDisplay_Click(object sender, EventArgs e)
        {
            if (FileUpdExcel.HasFile)
            {
                string strExcelFileName = 
                    Path.GetFileName(FileUpdExcel.PostedFile.FileName);
                string strfileExt =
                    Path.GetExtension(FileUpdExcel.PostedFile.FileName);
                string FolderPath =
                    System.Configuration.ConfigurationManager.
                    AppSettings["UploadFolderPath"];

                string FilePath =
                    Server.MapPath(FolderPath + strExcelFileName);
                FileUpdExcel.SaveAs(FilePath);

                string strConString = string.Empty;
                if (strfileExt == ".xls")
                {
                    strConString = 
                        System.Configuration.ConfigurationManager.
                        ConnectionStrings["ExcelCon2003"].ConnectionString;

                }
                else
                {
                    strConString =
                        System.Configuration.ConfigurationManager.
                        ConnectionStrings["ExcelCon2007"].ConnectionString;
                }
                strConString =
                    String.Format(strConString, FilePath);
                OleDbConnection oledbCon = new OleDbConnection(strConString);
                try
                {
                    oledbCon.Open();
                    OleDbCommand oledbCmd =
                        new OleDbCommand("select * from [Sheet1$]", oledbCon);
                    OleDbDataAdapter oledbDa = new OleDbDataAdapter();
                    oledbDa.SelectCommand = oledbCmd;
                    DataSet dsExcel = new DataSet();
                    oledbDa.Fill(dsExcel, "Employee");
                    grdExcel.DataSource = dsExcel.Tables[0].DefaultView;
                    grdExcel.DataBind();

                }
                catch (Exception ex)
                {
                    string strError = ex.Message;
                    lblMsg.Text = strError;
                }
                finally
                {
                    oledbCon.Close();
                }

            }

        }

    }
}

Excel uploaded on click of the button

Excel file after uploading to the folder using ASP.NET

Final output, after uploading and displaying the data in Gridview
Uploaded Excel after displaying in Gridview using ASP.NET

Another common error you may get when you upload files in the server is "Access denied". In such cases you have to enable Impersonation.

You may get another error if the Excel sheet name is different than the "Sheet1". You need to make sure that you are using the correct sheet name.

Online Members

How to display data from Excel file in to a GridView?

Front end Design of application for displaying data from Excel file in to a GridView

Connection Strings used in the application to display data from Excel file in to a GridView

ASP.NET code to upload Excel and display the data into Gridview

Post Comment:

Email subscription