private void ExportToExcel(DataTable dt, string fileName, string worksheetName)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename="+ fileName + "");
Response.ContentType = "application/vnd.ms-excel";
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
DataGrid dataExportExcel = new DataGrid();
dataExportExcel.ItemDataBound += new DataGridItemEventHandler(dataExportExcel_ItemDataBound);
dataExportExcel.DataSource = dt;
dataExportExcel.DataBind();
dataExportExcel.RenderControl(htmlWrite);
StringBuilder sbResponseString = new StringBuilder();
sbResponseString.Append("<html xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns=\"http://www.w3.org/TR/REC-html40/"> <head><meta http-equiv=\"Content-Type\" content=\"text/html;charset=windows-1252\"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>"+ worksheetName +"</x:Name><x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head> <body>");
sbResponseString.Append(stringWriter + "</body></html>");
Response.Write(sbResponseString.ToString());
Response.End();
}
void dataExportExcel_ItemDataBound(object sender, DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Header)
{
//Header Text Format can be done as follows
e.Item.Font.Bold = true;
//Adding Filter/Sorting functionality for the Excel
int cellIndex = 0;
while (cellIndex < e.Item.Cells.Count)
{
e.Item.Cells[cellIndex].Attributes.Add("x:autofilter", "all");
e.Item.Cells[cellIndex].Width = 200;
e.Item.Cells[cellIndex].HorizontalAlign = HorizontalAlign.Center;
cellIndex++;
}
}
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
int cellIndex = 0;
while (cellIndex < e.Item.Cells.Count)
{
//Any Cell specific formatting should be done here
e.Item.Cells[cellIndex].HorizontalAlign = HorizontalAlign.Left;
cellIndex++;
}
}
}
No comments:
Post a Comment