Export Gridview data to Excel (.xlsx) without using HtmlTextWriter in Asp.NET

You can use export data by using EPPlus.dll.

Following is the sample code.

public void ExportToExcel()
{
DataView dv = new DataView();
System.Data.DataTable tbl = new System.Data.DataTable();
dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty); // To access the data associated with Gridview
tbl = dv.ToTable();

//Following code is copied from StackOverFlow

using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(“Demo”);

//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true);

//Format the header for column 1-3
using (ExcelRange rng = ws.Cells["A1:G1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;

//Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));

//Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
}

//Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
{
col.Style.Numberformat.Format = “#,##0.00″;
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}

//Write it back to the client
Response.Clear();
Response.AddHeader(“content-disposition”, “attachment; filename=file.xlsx”);
Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;
Response.BinaryWrite(pck.GetAsByteArray());
Response.End();
}
}

Leave a Comment