Export GridView to Excel

Question

using Matt util code

(bit edited for Unicode text)

public class GridViewExportUtil { /// <param name="fileName"></param> /// <param name="gv"></param> public static void Export(string fileName, GridView gv) { HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache); HttpContext.Current.Response.Charset = System.Text.Encoding.Unicode.EncodingName; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Unicode; HttpContext.Current.Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble()); HttpContext.Current.Response.AddHeader( "content-disposition", string.Format(//"content-disposition", "attachment; filename=Report.xml"));//, fileName)); // Need .XLS file using (StringWriter sw = new StringWriter()) { using (HtmlTextWriter htw = new HtmlTextWriter(sw)) { // Create a form to contain the grid Table table = new Table(); // add the header row to the table if (gv.HeaderRow != null) { GridViewExportUtil.PrepareControlForExport(gv.HeaderRow); table.Rows.Add(gv.HeaderRow); } // add each of the data rows to the table foreach (GridViewRow row in gv.Rows) { GridViewExportUtil.PrepareControlForExport(row); table.Rows.Add(row); } // add the footer row to the table if (gv.FooterRow != null) { GridViewExportUtil.PrepareControlForExport(gv.FooterRow); table.Rows.Add(gv.FooterRow); } // render the table into the htmlwriter table.RenderControl(htw); // render the htmlwriter into the response HttpContext.Current.Response.Write(sw.ToString()); HttpContext.Current.Response.End(); } } } /// <summary> /// Replace any of the contained controls with literals /// </summary> /// <param name="control"></param> private static void PrepareControlForExport(Control control) { for (int i = 0; i < control.Controls.Count; i++) { Control current = control.Controls[i]; if (current is LinkButton) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text)); } else if (current is ImageButton) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText)); } else if (current is HyperLink) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text)); } else if (current is DropDownList) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text)); } else if (current is CheckBox) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False")); } if (current.HasControls()) { GridViewExportUtil.PrepareControlForExport(current); } } } 

Question: How to make the downloaded file editable (not only for reading)

And ... XLS does not open in Unicode format. When I change the format to UTF8, I do not see Russian words: S

Second question: how to make Unicode for .xls

Third question: how to save table rows?

Thanks.

4 - how to create a table in an NPOI sheet

  int i =0; if (gv.HeaderRow != null) { GridViewExportUtil.PrepareControlForExport(gv.HeaderRow); sheet1.CreateRow(1).CreateCell(i).SetCellValue(gv.HeaderRow.ToString()); //table.Rows.Add(gv.HeaderRow); i++; } 

like this?

Answer 1

HTML is not the same as XML.

The SpreadsheetML language used by Excel 2002/2003 is a completely different language. Even if you get Excel to open the file, it is only because it tends to recognize HTML files regardless of the file extension.

Regarding the Unicode problem, I think the problem is setting the StringWriter encoding when calling ToString ().

I recommend looking into the NPOI project, which contains everything you need to create files with binary / BIFF (Excel 97/2000), SpreadsheetML (Excel 2002/2003) and OOXML (Excel 2007) from .NET:

http://npoi.codeplex.com/