Skip to main content

Import Data from Excel Sheet into Asp.Net Gridview using EPPlus.

Introduction:
Here I will explain how to import data from excel to gridview in asp.net using EPPlus in C#.
Description:
  • ·         Create a new C# console application project in Visual Studio. Download the EPPlus binaries from the Downloads section on the EPPlus CodePlex Site 
  • ·         Extract the files and add EPPlus.dll as a reference to your project.


Step 1:
Write the blow code to Default.aspx page.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     <asp:FileUpload ID="FileUploadToServer" Width="300px" runat="server" />
        <asp:Button ID="btnUpload" runat="server" Text="Upload File"
            ValidationGroup="vg" style="width: 99px" onclick="btnUpload_Click" />
        <br />
        <br />
        <asp:Label ID="lblMsg" runat="server" ForeColor="Green" Text=""></asp:Label>
        <br />
     <asp:GridView ID="gvRecord" 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>
    </form>
</body>
</html>
Step 2:
Write the blow code to Default.aspx.cs page.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using OfficeOpenXml;
using System.IO;

public partial class Epplus_New : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {
        ImportToDataTable();
    }
    public void ImportToDataTable()
    {
        DataTable dt = new DataTable();
        string filePath = Server.MapPath("Product1.xlsx");
        var existingFile = new FileInfo(filePath);
        using (var package = new ExcelPackage(existingFile))
        {
            ExcelWorkbook workBook = package.Workbook;
            if (workBook != null)
            {
                if (workBook.Worksheets.Count > 0)
                {
                    ExcelWorksheet worksheet = workBook.Worksheets.First();
                    ExcelCellAddress startCell = worksheet.Dimension.Start;
                    ExcelCellAddress endCell = worksheet.Dimension.End;


                    for (int col = startCell.Column; col <= endCell.Column; col++)
                    {
                        object col1Header1 = worksheet.Cells[1, col].Value;
                        dt.Columns.Add("" + col1Header1 + "");
                    }
                    for (int row = startCell.Row + 1; row <= endCell.Row + 1; row++)
                    {
                        DataRow dr = dt.NewRow();
                        int x = 0;
                        for (int col = startCell.Column; col <= endCell.Column; col++)
                        {
                            dr[x++] = worksheet.Cells[row, col].Value;
                        }
                        dt.Rows.Add(dr);
                        gvRecord.DataSource = dt;
                        gvRecord.DataBind();
                    }
                }

            }

        }
    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        string FilePath = ConfigurationManager.AppSettings["FilePath"].ToString();
        string filename = string.Empty;
        if (FileUploadToServer.HasFile)
        {

            string[] allowdFile = { ".xls", ".xlsx" };
            string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
            bool isValidFile = allowdFile.Contains(FileExt);
            if (!isValidFile)
            {
                lblMsg.ForeColor = System.Drawing.Color.Red;
                lblMsg.Text = "Please upload only Excel";
            }
            else
            {
                int FileSize = FileUploadToServer.PostedFile.ContentLength;
                if (FileSize <= 1048576)
                {
                    filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));
                    FileUploadToServer.SaveAs(Server.MapPath(FilePath) + filename);
                    string filePath = Server.MapPath(FilePath) + filename;

                }
            }
        }

    }
}




Comments

Post a Comment

Popular posts from this blog

Retrieve Gmail Inbox into Asp.Net using Pop3.

I ntroduction : In this article I will explain how to integrate gmail mail into asp.net using pop3 server. Pop3 Commands: The commands used to retrieve mail from a Pop3 server by an email client are a series of text commands with single line responses from the mail server. The commands and examples used here are defined by   RFC 1939- Post Office Protocol- Version 3. user: User is the first command after the connection is established. To connect, all you have to do is supply your e-mail user id. Example Usage: USER  john.smith PASS: After having supplied your user name, the next command is to supply your e-mail password. Example Usage: PASS  **** STAT: Returns the number of emails in the mailbox and the number of bytes all the emails are taking on the server. Example Usage: STAT Sample Response:  +OK 3 345910   TOP : Takes  two arguments i.e., the sort number of the email on the server and the number of lines of text to...
Post a Comment
Read more

How to Create ContactUs page using Rich TextBox in Asp.Net

I ntroduction : As we know, ASP.NET lacks a control that we need most,   RichText  editor. If you are trying to create an application or website with Admin Panel or Blog or Forum Website or some such project for Web in ASP.NET platform, we need RichText Editor. To overcome this, we can use many open source JavaScript based editors, like Tiny MCE, FCKEditor, etc. here we are using TinyMCE, which is one of the most popular and Open Source projects. Download the latest version of TinyMCE from  download page , and extract the zip file. Steps: Create a new WebPage  in  VS 2008. Browse the extracted location and go to  ..\tinymce_3.5.6\tinymce\jscripts   folder. Now copy the  tinymce  folder from the above location to your solution in Visual Studio. Step 1: Create a  WebPage  file named  ContactUs.aspx . Paste the code below into  ContactUs.aspx: < html   xmlns ="http:/...
Post a Comment
Read more

Insert, Edit, Update, Delete in Asp.Net Gridview

I ntroduction : In this article I will explain how to Insert, Edit, Update, Delete in Gridview in Asp.Net using C#. Description: First I will write code to Insert data into Gridview then I will perform Edit, Update and Delete operations on it. For achieving all these functionalities I have used following Gridview commands, they are: onrowediting onrowupdating onrowcancelingedit onPageIndexChanging Below is the code for aspx page: < html   xmlns ="http://www.w3.org/1999/xhtml"   > < head   id ="Head1"   runat ="server">      < title > Untitled Page </ title >      < style   type ="text/css"> .Gridview { font-family:Verdana; font-size:10pt; font-weight:normal; color:black; } </ style > < script   type ="text/javascript"> function  ConfirmationBox(username) { var  result = confirm( 'Are you sure you want to delete ' +user...
Post a Comment
Read more