Read and Import Excel Sheet into ASP.Net GridView Control

In this article I am explaining: How to Read or Import Excel Sheet Data into ASP.Net GridView Control using OLEDB and ADO.Net.

 

Concept

1. User browses and selects an Excel Workbook.

2. User selects whether Header row is present in Excel Sheet or not using radio buttons.

3. User uploads the Excel Workbook

4. Uploaded File is read by the application and displayed on the web page using GridView Control.

5. GridView has paging enabled so that user can view the records easily.

 

Connection Strings

Since there Excel 97-2003 and Excel 2007 use different providers I have placed two connection strings keys in the Web.Config.

<connectionStrings>

  <add name ="Excel03ConString"

       connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};

                         Extended Properties='Excel 8.0;HDR={1}'"/>

  <add name ="Excel07ConString"

       connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};

                         Extended Properties='Excel 8.0;HDR={1}'"/>

</connectionStrings>

 

You will notice above there are two connection strings one for Excel 97 – 2003 format which uses Microsoft Jet driver and another one is for Excel 2007 format which uses Microsoft Ace driver.

I have used Placeholders for Data Source {0} and the HDR {1} property so that I can easily replace fill it in the front end based on the Excel File Selected and the Headers property selected by the user through the checkbox respectively

 

Front End design

The front end design of the web page contains a FileUpload Control, a Button which will be used to upload the Excel File, RadioButtonList for the user to select whether headers are present or not by default Yes is selected and finally the GridView control in which I have set AllowPaging property to true.

 

<asp:FileUpload ID="FileUpload1" runat="server" />

<asp:Button ID="btnUpload" runat="server" Text="Upload"

            OnClick="btnUpload_Click" />

<br />

<asp:Label ID="Label1" runat="server" Text="Has Header ?" />

<asp:RadioButtonList ID="rbHDR" runat="server">

    <asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" >

    </asp:ListItem>

    <asp:ListItem Text = "No" Value = "No"></asp:ListItem>

</asp:RadioButtonList>

<asp:GridView ID="GridView1" runat="server"

OnPageIndexChanging = "PageIndexChanging" AllowPaging = "true">

</asp:GridView>

 

 

Uploading and Reading the Excel Sheet

Next comes the part to upload the file and reading it. As you will notice in the aspx I have added OnClick event handler to the Upload button which will be triggered when the upload button is clicked

When the upload button is clicked the uploaded File is saved to a Folder whose path is defined in the App Settings section in the Web.Config using the following key

<appSettings>

  <add key ="FolderPath" value ="Files/"/>

</appSettings >

 

Once the File is saved in the folder the Import_To_Grid method is called up which is described later. Below is the code snippet for the Upload button event handler

 

C#

protected void btnUpload_Click(object sender, EventArgs e)

{

    if (FileUpload1.HasFile)

    {

        string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

        string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

 

        string FilePath = Server.MapPath(FolderPath + FileName);

        FileUpload1.SaveAs(FilePath);

        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);

    }

}

 

 

VB.Net

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs)

  If FileUpload1.HasFile Then

     Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)

     Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

     Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")

 

     Dim FilePath As String = Server.MapPath(FolderPath + FileName)

     FileUpload1.SaveAs(FilePath)

     Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text)

  End If

End Sub

 

Now the saved Excel file is read using OLEDB. Based on the extension I decide the driver to be used in order to read the excel file and also whether Excel will be read along with header row or not based on the RadioButtonList. All these values are filled in the placeholders of the connection string.

After that I read the schema of the Excel Workbook in order to find out the Sheet Name of the first sheet. Once I get that I fire a select query on the first Excel sheet and fill a datatable which is then passed to the GridView as data source. You can refer the complete function below

                              

 

 

C#

 

private void Import_To_Grid(string FilePath, string Extension, string isHDR)

{

    string conStr="";

    switch (Extension)

    {

        case ".xls": //Excel 97-03

            conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]

                     .ConnectionString;

            break;

        case ".xlsx": //Excel 07

            conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]

                      .ConnectionString;

            break;

    }

    conStr = String.Format(conStr, FilePath, isHDR);

    OleDbConnection connExcel = new OleDbConnection(conStr);

    OleDbCommand cmdExcel = new OleDbCommand();

    OleDbDataAdapter oda = new OleDbDataAdapter();

    DataTable dt = new DataTable();

    cmdExcel.Connection = connExcel;

 

    //Get the name of First Sheet

    connExcel.Open();

    DataTable dtExcelSchema;

    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

    connExcel.Close();

 

    //Read Data from First Sheet

    connExcel.Open();

    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

    oda.SelectCommand = cmdExcel;

    oda.Fill(dt);

    connExcel.Close();

 

    //Bind Data to GridView

    GridView1.Caption = Path.GetFileName(FilePath);

    GridView1.DataSource = dt;

    GridView1.DataBind();

}

 

     

 

VB.Net

Private Sub Import_To_Grid(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR As String)

  Dim conStr As String = ""

  Select Case Extension

      Case ".xls"

           'Excel 97-03

           conStr = ConfigurationManager.ConnectionStrings("Excel03ConString") _

                      .ConnectionString

           Exit Select

      Case ".xlsx"

           'Excel 07

            conStr = ConfigurationManager.ConnectionStrings("Excel07ConString") _

                      .ConnectionString

            Exit Select

  End Select

  conStr = String.Format(conStr, FilePath, isHDR)

 

  Dim connExcel As New OleDbConnection(conStr)

  Dim cmdExcel As New OleDbCommand()

  Dim oda As New OleDbDataAdapter()

  Dim dt As New DataTable()

 

  cmdExcel.Connection = connExcel

 

  'Get the name of First Sheet

  connExcel.Open()

  Dim dtExcelSchema As DataTable

  dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

  Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()

  connExcel.Close()

 

  'Read Data from First Sheet

   connExcel.Open()

   cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"

   oda.SelectCommand = cmdExcel

   oda.Fill(dt)

   connExcel.Close()

 

   'Bind Data to GridView

    GridView1.Caption = Path.GetFileName(FilePath)

    GridView1.DataSource = dt

    GridView1.DataBind()

End Sub

 

 

    

Pagination in GridView

Now in order to implement paging we will need to read the excel sheet each time and then rebind datatable to the GridView. Refer below

 

C#

protected void PageIndexChanging(object sender, GridViewPageEventArgs e)

{

    string FolderPath = ConfigurationManager.AppSettings["FolderPath"] ;

    string FileName = GridView1.Caption;

    string Extension = Path.GetExtension(FileName);

    string FilePath = Server.MapPath(FolderPath + FileName);

 

    Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text); 

    GridView1.PageIndex = e.NewPageIndex;

    GridView1.DataBind(); 

}

 

VB.Net

Protected Sub PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)

   Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")

   Dim FileName As String = GridView1.Caption

   Dim Extension As String = Path.GetExtension(FileName)

   Dim FilePath As String = Server.MapPath(FolderPath + FileName)

 

   Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text)

   GridView1.PageIndex = e.NewPageIndex

   GridView1.DataBind()

End Sub

 

The figure below displays the GridView populated from the excel workbook that is uploaded using the FileUpload Control.

Important thing to note that always close the connections after reading the Excel Workbook to avoid the following error

Server Error in 'ASP.Net' Application.

 

---

The process cannot access the file 'F:\ReadExcelToGrid\Files\Excel07.xlsx' because it is being used by another process.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IO.IOException: The process cannot access the file 'F:\ReadExcelToGrid\Files\Excel07.xlsx' because it is being used by another process.