All geek questions in one place

Export GridView to Excel without losing grid lines in Excel

I have a GridView that I want to export to Excel. When I use the sample code that I found on the Internet, it exports the contents to Excel just fine, but for some reason, it also clears all the grid lines outside the exported table.

For your regular excel user, this is easy enough to fix, but I need this solution to work for everyone.

So, is there a way to export data in a GridView to an Excel workbook so that it looks like it was entered into Excel? I pasted the code that I use below, suppose a GridView with the name toPrint exists and has accurate data.

Response.Clear(); Response.AddHeader("content-disposition", "attachment; filename=" + name + "_Registration_Forms.xls"); Response.Charset = ""; Response.ContentType = "application/vnd.ms-excel"; Page.EnableViewState = false; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); toPrint.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End();

EDIT: one partial solution found. If I export as a comma-delimited list and set the title to a CSV file, it opens normally and all grid lines (even those that are outside the exported data) are displayed. The only problem with this, of course, is to cut out every comma and newline from my values ​​before exporting them.

+4
source share
4 answers

The following post gave me an answer. http://forums.asp.net/t/1074110.aspx I will describe what to do with the code.

 System.IO.StringWriter dvWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter OutputGenerator = new System.Web.UI.HtmlTextWriter(dvWriter); //To put in the excel gridlines dvWriter.Write(@"<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">"); dvWriter.Write(@"<head> <xml> <x:ExcelWorkbook> <x:ExcelWorksheets> <x:ExcelWorksheet> <x:WorksheetOptions> <x:Panes></x:Panes> <x:Print><x:Gridlines /></x:Print> </x:WorksheetOptions> </x:ExcelWorksheet> </x:ExcelWorksheets> </x:ExcelWorkbook> </xml> </head>"); //*******Put your Table Body here *******
+5
source

I have used a helper function below in the past. I just gave the user a checkbox that they could choose to include grid lines or not. Obviously, you can change this to always include grid lines.

 namespace Helpers { public class GridViewExportUtil { public static void Export(string fileName, GridView gv, bool includeGridLines) { HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AddHeader( "content-disposition", string.Format("attachment; filename={0}", fileName)); HttpContext.Current.Response.ContentType = "application/ms-excel"; using (StringWriter sw = new StringWriter()) { using (HtmlTextWriter htw = new HtmlTextWriter(sw)) { // Create a form to contain the grid Table table = new Table(); if (includeGridLines) { table.GridLines = gv.GridLines; } // 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); } } } } }

This is an example of what you call it:

 GridViewExportUtil.Export("QueryResults.xls", GridView1, includeGridLines);
+3
source

I had the same problem that the grid lines did not work when exporting to Excel.

The way I solved this is to put Gridlines="Both" inside the <datagrid> .

+3
source

B. B.

 Public Overrides Sub VerifyRenderingInServerForm(control As Control) 'base.VerifyRenderingInServerForm(control); 'This remains empty End Sub Protected Sub btnExcel_Click(sender As Object, e As ImageClickEventArgs) Handles btnExcel.Click Response.Clear() Response.AddHeader("content-disposition", "attachment;filename=FileName.xls") Response.Charset = "" Response.Cache.SetCacheability(HttpCacheability.NoCache) Response.ContentType = "application/vnd.ms-excel" Dim stringWrite As New System.IO.StringWriter() Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite) htmlWrite.Write("<html xmlns:o=""urn:schemas-microsoft-com:office:office"" ") htmlWrite.Write("xmlns:x=""urn:schemas-microsoft-com:office:excel"" ") htmlWrite.Write("xmlns=""http://www.w3.org/TR/REC-html40""> ") htmlWrite.Write("<head> ") htmlWrite.Write("<!--[if gte mso 9]><xml> ") htmlWrite.Write("<x:ExcelWorkbook> ") htmlWrite.Write("<x:ExcelWorksheets> ") htmlWrite.Write("<x:ExcelWorksheet> ") htmlWrite.Write("<x:Name>Sheet1</x:Name> ") htmlWrite.Write("<x:WorksheetOptions> ") htmlWrite.Write("<x:Selected/> ") htmlWrite.Write("<x:ProtectContents>False</x:ProtectContents> ") htmlWrite.Write("<x:ProtectObjects>False</x:ProtectObjects> ") htmlWrite.Write("<x:ProtectScenarios>False</x:ProtectScenarios> ") htmlWrite.Write("</x:WorksheetOptions> ") htmlWrite.Write("</x:ExcelWorksheet> ") htmlWrite.Write("</x:ExcelWorksheets> ") htmlWrite.Write("</x:ExcelWorkbook> ") htmlWrite.Write("</xml><![endif]--> ") htmlWrite.Write("</head>") htmlWrite.WriteLine("") gridView.HeaderStyle.Reset() gridView.FooterStyle.Reset() gridView.AlternatingRowStyle.Reset() gridView.RowStyle.Reset() gridView.BackColor = Color.Transparent gridView.GridLines = GridLines.None gridView.RenderControl(htmlWrite) Response.Write(stringWrite.ToString()) Response.[End]() End Sub

FROM#

 public override void VerifyRenderingInServerForm(Control control) { //base.VerifyRenderingInServerForm(control); //This remains empty } protected void btnExcel_Click(object sender, ImageClickEventArgs e) { Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=FileName.xls"); Response.Charset = ""; Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = "application/vnd.ms-excel"; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); htmlWrite.Write("<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" "); htmlWrite.Write("xmlns:x=\"urn:schemas-microsoft-com:office:excel\" "); htmlWrite.Write("xmlns=\"http://www.w3.org/TR/REC-html40\"> "); htmlWrite.Write("<head> "); htmlWrite.Write("<!--[if gte mso 9]><xml> "); htmlWrite.Write("<x:ExcelWorkbook> "); htmlWrite.Write("<x:ExcelWorksheets> "); htmlWrite.Write("<x:ExcelWorksheet> "); htmlWrite.Write("<x:Name>Sheet1</x:Name> "); htmlWrite.Write("<x:WorksheetOptions> "); htmlWrite.Write("<x:Selected/> "); htmlWrite.Write("<x:ProtectContents>False</x:ProtectContents> "); htmlWrite.Write("<x:ProtectObjects>False</x:ProtectObjects> "); htmlWrite.Write("<x:ProtectScenarios>False</x:ProtectScenarios> "); htmlWrite.Write("</x:WorksheetOptions> "); htmlWrite.Write("</x:ExcelWorksheet> "); htmlWrite.Write("</x:ExcelWorksheets> "); htmlWrite.Write("</x:ExcelWorkbook> "); htmlWrite.Write("</xml><![endif]--> "); htmlWrite.Write("</head>"); htmlWrite.WriteLine(""); gridView.HeaderStyle.Reset(); gridView.FooterStyle.Reset(); gridView.AlternatingRowStyle.Reset(); gridView.RowStyle.Reset(); gridView.BackColor = Color.Transparent; gridView.GridLines = GridLines.None; gridView.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End(); }
0
source

Source: https://habr.com/ru/post/1303070/

More articles:


All Articles