Create excel file from the ASP.NET datagrid control


This article provides the sample code for exporting data from the dataset to excel sheet with out altering the format of the dataset. The code is in C#.NET and it will run on .NET frame work 1.1 or 2.0.

Introduction


I developed this function to fulfill the export to excel functionality by using data grid as source. We got a requirement from the client saying that they need export to excel functionality should be implemented with out altering the format of the dataset.

Button click event


Here the button name which is used for export to excel is "ButtonExportToXL" and datagrid name is "dataGridSLAReport".

/// This function will create the xl file from the dataset
private void ButtonExportToXL_Click(object sender, System.EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
this.ClearControls(dataGridSLAReport);
dataGridSLAReport.RenderControl(htmlTextWriter);
stringWriter.Write("<table><tr style=\"background-color:White;font-size:12px;\"><td nowrap colspan=3>\t * Calculated field as not readily available from remedy</td></tr><tr><td colspan=3>\n</td></tr><tr><td colspan=3>\n</td></tr><tr style=\"background-color:White;font-size:14px;\"><td colspan=3>\tTotal number of tickets : \t" + ((DataSet)(Session["SLADataSet"])).Tables[0].Rows.Count.ToString() + "</td></tr></table>");
Response.Write(stringWriter.ToString());
Response.End();
}

ClearControls function


This function will create an HTML table from the dataset with including all formatting as well.

/// This user defined function will export the data grid control data to the Microsoft excel
private void ClearControls(Control sLAReportDataGrid)
{
for (int contolsCount = sLAReportDataGrid.Controls.Count -1; contolsCount >= 0; contolsCount--)
{
ClearControls(sLAReportDataGrid.Controls[contolsCount]);
}
if (!(sLAReportDataGrid is TableCell))
{
if (sLAReportDataGrid.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literalControl = new LiteralControl();
sLAReportDataGrid.Parent.Controls.Add(literalControl);
try
{
literalControl.Text = (string)sLAReportDataGrid.GetType().GetProperty("SelectedItem").GetValue(sLAReportDataGrid, null);
}
catch {}
sLAReportDataGrid.Parent.Controls.Remove(sLAReportDataGrid);
}
else
{
if (sLAReportDataGrid.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
sLAReportDataGrid.Parent.Controls.Add(literal);
literal.Text = (string)sLAReportDataGrid.GetType().GetProperty("Text").GetValue(sLAReportDataGrid, null);
sLAReportDataGrid.Parent.Controls.Remove(sLAReportDataGrid);
}
}
}
return;
}

Summary


This will provides the best solution to export to excel from the data grid. This code ensure the formatting of the excel sheet is same as formatting of the data grid.


Related Articles

Export Data to Excel in CSV format using .NET

Export Data to excel in CSV format. .NET code to export CSV data. Free .NET code to export data to excel. .NET way of exporting data to excel. Explanation of .NET code to export data to excel in CSV.

Client Side Script to Export to Excel 2007

This article explains the work around for Export to Excel 2007. The earlier vbScript we used for Exporting to Excel 2003 relied on HTMLProject which is no more supported in Excel 2007. Here is the work around.

More articles: Export to Excel

Comments

Author: siva kumari kankanala24 Oct 2006 Member Level: Silver   Points : 0

I am getting error in the below line

stringWriter.Write("

\t * Calculated field as not readily available from remedy
\n
\n
\tTotal number of tickets : \t" + ((DataSet)(Session["SLADataSet"])).Tables[0].Rows.Count.ToString() + "
");

please mail me sivakumari_idsi@hotmail.com

Author: Vinoth02 Apr 2008 Member Level: Bronze   Points : 0

Hi
I have the same error near the dataset can u tell why u use dataset in that place.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: