Friday, March 11, 2005

Data Grid to Excel export error

After trying to export a datagrid to excel with the MIME modification, you get the error:
"The file you are trying to open .xls is in a different format than specified by the file extension. verify the file is not corrupted and is from trusted source before opening the file. Do you want to open the file now"

Fix:
Add the following registry entry:
start -> run -> regedit -> HKEY_CURRENT_USER\SOFTWARE\MICROSOFT\OFFICE\12.0\EXCEL\SECURITY -> in the right pane, rightclick -> New -> DWORD value -> name -> ExtensionHardening -> value -> '0'

Value: (DWORD)"ExtensionHardening" = [0 = Disable check; 1 = Enable check and prompt; 2 = Enable check, no prompt deny open]Default setting if value not present is 1 (enable and prompt).

If you do not know the code to export the grid results to excel, below is the code:

protected void btnExcel_Click(object sender, ImageClickEventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
// use the below code to open only .
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
this.ClearControls(grdResults);
grdResults.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();

}


Sorting/filtering works only when there are literal controls present in the data. For a single non literal control, it fails to perform the special opearations.
Below is the code to remove the non-teralcontrols from being exported:


private void ClearControls(Control control)
{
for (int i=control.Controls.Count-1; i>=0; i--)
{
ClearControls(control.Controls[i]);
}
if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control,null);

control.Parent.Controls.Remove(control);
}
else
if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control,null);
control.Parent.Controls.Remove(control);
}
}
}

No comments:

Post a Comment