Home > Articles > .Net Framework Articles > Upload and display excel file content in gridview using asp.net

Upload and display excel file content in gridview using asp.net

by Thamilselvan J   on Jul 19, 2013   Category: .Net Framework  | Level: Intermediate  |  Views: 16880    |  Points: 100   
Like this article? Bookmark and Share:
In this article, I will show you how to read contents from excel file and display in gridview control in asp.net




Introduction


Sometime we may have a requirement to read excel file content and display in gridview control in asp.net projects, this example will show you how to upload excel file into server path and read excel file contents then display into gridview control. Follow the below steps to achieve this. 

Step1:


Create asp.net project then drag and drop the file upload and gridview control into aspx page and format the gridview control as like below.

<div>
        <asp:FileUpload ID="FileUploadToServer" Width="300px" runat="server" />
        <asp:Button ID="btnUpload" runat="server" Text="Upload File" OnClick="btnUpload_Click"
            ValidationGroup="vg" style="width: 99px" />
        <br />
        <br />
        <asp:Label ID="lblMsg" runat="server" ForeColor="Green" Text=""></asp:Label>
        <br />
        <asp:GridView ID="GridView1" runat="server" EmptyDataText="No record found!"
            Height="25px">
            <RowStyle Width="175px" />
            <EmptyDataRowStyle BackColor="Silver" BorderColor="#999999" BorderStyle="Solid"
                BorderWidth="1px" ForeColor="#003300" />
            <HeaderStyle BackColor="#6699FF" BorderColor="#333333" BorderStyle="Solid"
                BorderWidth="1px" VerticalAlign="Top" Width="200px"  Wrap="True" />
             
        </asp:GridView>
    </div>


File upload control which is used to upload the excel file into server and gridview to display the excel contents.

Step2:


Code behind part, place the below code into btnUpload_Click event.

Namespace used:

using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Data.OleDb;

Upload Click event:
protected void btnUpload_Click(object sender, EventArgs e)
        {
            //Get path from web.config file to upload
            string FilePath = ConfigurationManager.AppSettings["FilePath"].ToString();
            string filename = string.Empty;
            //To check whether file is selected or not to uplaod
            if (FileUploadToServer.HasFile)
            {
                try
                {
                    string[] allowdFile = { ".xls", ".xlsx" };
                    //Here we are allowing only excel file so verifying selected file pdf or not
                    string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
                    //Check whether selected file is valid extension or not
                    bool isValidFile = allowdFile.Contains(FileExt);
                    if (!isValidFile)
                    {
                        lblMsg.ForeColor = System.Drawing.Color.Red;
                        lblMsg.Text = "Please upload only Excel";
                    }
                    else
                    {
                        // Get size of uploaded file, here restricting size of file
                        int FileSize = FileUploadToServer.PostedFile.ContentLength;
                        if (FileSize <= 1048576)//1048576 byte = 1MB
                        {
                            //Get file name of selected file
                            filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));
                           
                            //Save selected file into server location
                            FileUploadToServer.SaveAs(Server.MapPath(FilePath) + filename);
                            //Get file path
                            string filePath = Server.MapPath(FilePath) + filename;
                            //Open the connection with excel file based on excel version
                            OleDbConnection con = null;
                            if (FileExt == ".xls")
                            {
                                con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;");
                           
                            }
                            else if (FileExt == ".xlsx")
                            {
                                con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;");
                            }
                            con.Open();
                            //Get the list of sheet available in excel sheet
                            DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                            //Get first sheet name
                            string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString();
                            //Select rows from first sheet in excel sheet and fill into dataset
                            OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con);
                            OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
                            DataSet ExcelDataSet = new DataSet();
                            ExcelAdapter.Fill(ExcelDataSet);
                            con.Close();
                            //Bind the dataset into gridview to display excel contents
                            GridView1.DataSource = ExcelDataSet;
                            GridView1.DataBind();
                        }
                        else
                        {
                            lblMsg.Text = "Attachment file size should not be greater then 1 MB!";
                        }
                    }
                }
                catch (Exception ex)
                {
                    lblMsg.Text = "Error occurred while uploading a file: " + ex.Message;
                }
            }
            else
            {
                lblMsg.Text = "Please select a file to upload.";
            }
        }

 

The above code will upload the excel file into server location and read the content then display it. First store the path details in web.config file where excel file needs to be stored in server and do the validation when clicks on upload button whether the file is selected or not. Here we are allowing only excel file so verify the file extension while uploading file also check file size limitation if required. If everything is fine related with excel file then save into specified server location.

Then open connection with excel file using OleDbConnection and provider as Microsoft.Jet.OLEDB.4.0 for .xls files and Microsoft.ACE.OLEDB.12.0 for .xlsx files. Get the list of sheets available in excel file using GetOleDbSchemaTable then load list of sheets details into datatable and get first sheet name from datatable then Select all rows from sheet#1 and fill into dataset then finally bind the gridview with dataset. Here I’m reading the content from first sheet of excel, if you want to read content from all sheets in excel then get the sheet name from datatable using for loop one by one and read the content.

Prepare the excel sheet as like below to test this.

Once excel sheet is ready then run the project and upload this file using upload control and display the contents as like below.

I have attached the sample project here, download and see how it works. Thanks for reading; I hope you enjoyed this article.

User Responses | Post Article
Comment posted by Pieter Grobbelaar on 8/6/2013 11:55:56 PM | Points : 10
Excellent! Very clear and THANK YOU. The way you explain makes it super easy to understand.
  Most viewed Articles
 Submit feedback about this article
Please sign in to post feedback