Upload Excel Spreadsheet File to Server, then Display Record(s) in Gridview

• Download source code - 12.88 KB
• Download Excel template - 4.3 KB

Introduction

A while ago, I had a task to develop a module to upload an Excel spreadsheet record(s) to database target table. Before saving it into such table, the requirement is first to display all record (rows) into grid view. Also there should a validation that the first column of such Excel file should not be included in Grid View if it is empty. The additional requirement is that should not encounters an error(s) such as security access rights if should be installed in a remote server. I got into searching in this site but could not find any with such related requirements, therefore I thought of sharing these.

Let us get started.

In Client Code

Create a division for Upload file button:

<div>
   <asp:Label ID="label1" runat="server" Text="File"></asp:Label>
      
     <asp:FileUpload ID="xlsUpload" runat="server" Font-Size="Small" />
</div>
<div>
    <table width="100%">
       <tr align="left">
          <td>
             <asp:Button ID="btnUpload" runat="server" Text="Upload" 
        OnClick="btnUpload_Click" />
           </td>
       </tr>
    </table>
</div>

Create division for label for message box:

<div>
   <table width="100%">
     <tr align="left">
        <td>
           <asp:Label ID="lblMessage" runat="server" Text="" ForeColor="Red"></asp:Label>
        </td>
     </tr>
   </table>
</div>

Create division for Grid view:

<div style="margin-top: 20px;">
    <table>
         <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
             CellPadding="4" ForeColor="#333333" >
             <PagerSettings FirstPageText="" LastPageText="" NextPageText="" />
             <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
             <Columns>
                 <asp:BoundField DataField="CardNo" HeaderText="Card No" />
                 <asp:BoundField DataField="MemberName" HeaderText="Member Name" />
             </Columns>
             <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
             <PagerStyle BackColor="#FFCC66" 
        ForeColor="#333333" HorizontalAlign="Center" />
             <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
             <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
             <AlternatingRowStyle BackColor="White" />
          </asp:GridView>
    </table>
</div>

In Code Behind

They are quite easy, in fact I think the comments code in the below code pretty much explains them in enough detail. So I won't bore you with any more explanatory words, as it's clear.

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

namespace ExcelToGridview
{
    public partial class ExcelToGrid : System.Web.UI.Page
    {
        DataTable dt = null;

        public System.Data.DataTable xlsInsert(string pth)
        {
            string strcon = string.Empty;
            if (Path.GetExtension(pth).ToLower().Equals(".xls") ||
                Path.GetExtension(pth).ToLower().Equals(".xlsx"))
            {
                strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                                + pth +
                                ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
            }
            else
            {
                strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                              + pth +
                              ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
            }
            string strselect = "Select * from [Sheet1$]";
            DataTable exDT = new DataTable();
            using (OleDbConnection excelCon = new OleDbConnection(strcon))
            {
                try
                {
                    excelCon.Open();
                    using (OleDbDataAdapter exDA = 
            new OleDbDataAdapter(strselect, excelCon))
                    {
                        exDA.Fill(exDT);
                    }
                }
                catch (OleDbException oledb)
                {
                    throw new Exception(oledb.Message.ToString());
                }
                finally
                {
                    excelCon.Close();
                }
                for (int i = 0; i < exDT.Rows.Count; i++)
                {
                    // Check if first column is empty
                    // If empty then delete such record
                    if (exDT.Rows[i]["CardNo"].ToString() == string.Empty)
                    {
                        exDT.Rows[i].Delete();
                    }
                }
                exDT.AcceptChanges();  // refresh rows changes
                if (exDT.Rows.Count == 0)
                {
                    throw new Exception("File uploaded has no record found.");
                }
                return exDT;
            }
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (xlsUpload.HasFile)
            {
                bool uplod = true;
                string fleUpload = Path.GetExtension(xlsUpload.FileName.ToString());
                if (fleUpload.Trim().ToLower() == ".xls" | 
            fleUpload.Trim().ToLower() == ".xlsx")
                {
                    // Save excel file into Server sub dir
                    // to catch excel file downloading permission
                    xlsUpload.SaveAs(Server.MapPath("~/XlsUploadFile/" +
                        xlsUpload.FileName.ToString()));
                    string uploadedFile = (Server.MapPath("~/XlsUploadFile/" +
                        xlsUpload.FileName.ToString()));
                    try
                    {
                        dt = xlsInsert(uploadedFile);
                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                    }
                    catch (Exception)
                    {
                        uplod = false;
                        this.lblMessage.Text = "System uploading Error";
                    }
                    File.Delete(uploadedFile); // Delete upload Excel
                            //file in sub dir 'lsUploadFile' no need to keep...
                }
                if (uplod)
                {
                    string mess1 = "File has successfully uploaded";
                    this.lblMessage.Text = mess1;
                }
            }
            else
            {
                this.lblMessage.Text = "Please select file to upload.";
            }

        }
        protected void Page_Load(object sender, EventArgs e)
        {

        }
    }
}

Points of Interest

The author in this article describes the technical usage on how to Upload Excel spreadsheet file to server, then display record(s) in Gridview.

I hope this article was useful and I thank you for viewing it.