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;
}
}
}
}
}
|
