Export Datatable or Dataset data to excel using EPPlus in asp.net and c#
This example shows you how to export datatable or dataset data to excel in asp.net and c#. By using EPPlus.dll we can easily export datatable or dataset data to excel. You can merge cells, give background color and can format the cell text in excel using EPPlus.dll in c# and asp.net
In the below example we have a dataset contains student details and product details. By using EPPlus.dll we will export the data to excel. Datatable1 (students) data will be exported to students sheet and Datatable2 (products) data will be exported to products sheet in the same excel. And we can dynamically merge the heading of the sheet and can give the border for dynamic data.
Example
Export Datatable or Dataset data to excel
C# code
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 OfficeOpenXml;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnExport_Click(object sender, EventArgs e)
{
CreateExcelFile();
}
DataSet GetData()
{
DataTable dt = new DataTable("Students");
dt.Columns.Add("StudentID", typeof(int));
dt.Columns.Add("StudentName", typeof(string));
dt.Columns.Add("RollNumber", typeof(int));
dt.Columns.Add("TotalMarks", typeof(int));
dt.Rows.Add(1, "Jame's", 101, 900);
dt.Rows.Add(2, "Steave, Smith", 105, 820);
dt.Rows.Add(3, "Mark\"Waugh", 109, 850);
dt.Rows.Add(4, "Steave,\"Waugh", 110, 950);
dt.Rows.Add(5, "Smith", 111, 910);
dt.Rows.Add(6, "Williams", 115, 864);
DataSet ds = new DataSet("Example-DotnetLearners");
ds.Tables.Add(dt);
dt = new DataTable("Prodcuts");
dt.Columns.Add("ProductID", typeof(int));
dt.Columns.Add("ProductName", typeof(string));
dt.Columns.Add("UnitPrice", typeof(decimal));
for (int i = 1; i <= 100; i++)
dt.Rows.Add(i, "Product - " + i.ToString(), i * 1.123);
ds.Tables.Add(dt);
return ds;
}
void CreateExcelFile()
{
try
{
using (DataSet ds = GetData())
{
if (ds != null && ds.Tables.Count > 0)
{
using (ExcelPackage xp = new ExcelPackage())
{
foreach (DataTable dt in ds.Tables)
{
ExcelWorksheet ws = xp.Workbook.Worksheets.Add(dt.TableName);
int rowstart = 2;
int colstart = 2;
int rowend = rowstart;
int colend = colstart + dt.Columns.Count;
ws.Cells[rowstart, colstart, rowend, colend].Merge = true;
ws.Cells[rowstart, colstart, rowend, colend].Value = dt.TableName;
ws.Cells[rowstart, colstart, rowend, colend].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells[rowstart, colstart, rowend, colend].Style.Font.Bold = true;
ws.Cells[rowstart, colstart, rowend, colend].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws.Cells[rowstart, colstart, rowend, colend].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
rowstart += 2;
rowend = rowstart + dt.Rows.Count;
ws.Cells[rowstart, colstart].LoadFromDataTable(dt, true);
int i = 1;
foreach (DataColumn dc in dt.Columns)
{
i++;
if (dc.DataType == typeof(decimal))
ws.Column(i).Style.Numberformat.Format = "#0.00";
}
ws.Cells[ws.Dimension.Address].AutoFitColumns();
ws.Cells[rowstart, colstart, rowend, colend].Style.Border.Top.Style =
ws.Cells[rowstart, colstart, rowend, colend].Style.Border.Bottom.Style =
ws.Cells[rowstart, colstart, rowend, colend].Style.Border.Left.Style =
ws.Cells[rowstart, colstart, rowend, colend].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
}
Response.AddHeader("content-disposition", "attachment;filename=" + ds.DataSetName + ".xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(xp.GetAsByteArray());
Response.End();
}
}
}
}
catch
{
throw;
}
}
}
ASPX Page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnExport" runat="server" Text="Export" OnClick="btnExport_Click" />
</div>
</form>
</body>
</html>
