Export datatable to excel

July 10, 2013

Using this code you can export data from datatable to excel

Create data table first then pass it to ExporttoExcel method   and you’ll get desired excel file for download.

protected void btnExport_Click(object sender, EventArgs e)
{
DataTable dt = CreateDataTable();
ExporttoExcel(dt);
}
private void ExporttoExcel(DataTable table)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=TestingReports.xls");
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
//sets font
HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
HttpContext.Current.Response.Write("<BR><BR><BR>");
//sets the table border, cell spacing, border color, font of the text, background, foreground, font height
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
//am getting my grid's column headers
//int columnscount = GridView_Result.Columns.Count;

//for (int j = 0; j < columnscount; j++)
//{      //write in new column
//    HttpContext.Current.Response.Write("<Td>");
//    //Get column headers  and make it as bold in excel columns
//    HttpContext.Current.Response.Write("<B>");
//    HttpContext.Current.Response.Write(GridView_Result.Columns[j].HeaderText.ToString());
//    HttpContext.Current.Response.Write("</B>");
//    HttpContext.Current.Response.Write("</Td>");
//}

for (int j = 0; j < table.Columns.Count; j++)
{      //write in new column
HttpContext.Current.Response.Write("<Td>");
//Get column headers  and make it as bold in excel columns
HttpContext.Current.Response.Write("<B>");
HttpContext.Current.Response.Write(table.Columns[j].ColumnName.ToString());
HttpContext.Current.Response.Write("</B>");
HttpContext.Current.Response.Write("</Td>");
}

HttpContext.Current.Response.Write("</TR>");
foreach (DataRow row in table.Rows)
{//write in new row
HttpContext.Current.Response.Write("<TR>");
for (int i = 0; i < table.Columns.Count; i++)
{
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[i].ToString());
HttpContext.Current.Response.Write("</Td>");
}

HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("</font>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}

private  DataTable CreateDataTable()
{
DataTable companies = new DataTable("Companies");
DataColumn column;

column = new DataColumn("CompanyName");
companies.Columns.Add(column);

column = new DataColumn("CompanyCountry");
companies.Columns.Add(column);

column = new DataColumn("YearSales");
companies.Columns.Add(column);

column = new DataColumn("GrossProfit");
companies.Columns.Add(column);

column = new DataColumn("CreationDate");
companies.Columns.Add(column);

companies.Rows.Add("Mexican Tequila", "Mexico", "10875600", "4975200", "03/24/1995 2:35:00PM");
companies.Rows.Add("Canadian Food", "Canada", "308450870", "103476200", "08/12/1983");
companies.Rows.Add("French Wines", "France", "285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("German Beer", "Germany", "285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Russian Vodka", "Russia", "285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Spanish Paella", "Spain", "285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Argentinian Beef", "Argentina", "285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Italian Pasta", "Italy", "285309567", "81650000", "11/21/1990 17:40:00");

return companies;
}

Leave a Comment » | Asp.net | Tagged: , , , | Permalink
Posted by Juhi Paunikar