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.
I am getting error in the below line
\t * Calculated field as not readily available from remedy \n \n \tTotal number of tickets : \t" + ((DataSet)(Session["SLADataSet"])).Tables[0].Rows.Count.ToString() + "
");
stringWriter.Write("
please mail me sivakumari_idsi@hotmail.com