Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

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

, 15 Jan 2012
Rate this:
Please Sign up or sign in to vote.
Upload Excel Spreadsheet File to Server, then Display Record(s) in Gridview
sample_screen_input_output_small.jpg

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Al Moje
Software Developer (Senior) ***
Philippines Philippines
MCTS - Microsoft Certified Technology Specialist.
An Accountant.
Had been developed Payroll Accounting System Application
Live in: Quezon City, Metro Manila Philippines
Could reached at email address: ag_mojedo@live.com

Comments and Discussions

 
QuestionSample Code Pinmemberreal makoy18-Nov-13 21:52 
QuestionNot able to upload other excel file .. PinmemberHkashyap26-Jun-13 20:06 
GeneralMy vote of 5 PinmemberMichaelrc81721-Aug-12 8:16 
QuestionAlternative PinmemberRobert Hutch29-Jan-12 21:48 
GeneralMy vote of 1 PinmemberCaptain Scarlet16-Jan-12 8:06 
AnswerRe: My vote of 1 [modified] PinmemberAl Moje16-Jan-12 14:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 16 Jan 2012
Article Copyright 2012 by Al Moje
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid