Export GridView to Excel without losing grid lines in Excel
Solution 1
The following Post gave me the answer. http://forums.asp.net/t/1074110.aspx I will summarize 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 *******
Solution 2
I have used the helper function below in the past. I just gave the user a checkbox that they could select to include grid lines or not. Obviously you could change this to always include the 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 how you would call it:
GridViewExportUtil.Export("QueryResults.xls", GridView1, includeGridLines);
Solution 3
I had the same problem not getting grid lines when I exported to Excel.
The way I solved it was by putting Gridlines="Both"
inside the <datagrid>
tag.
Justin C
Justin is the lead developer at SpedWare LLC. He oversees development of all databases and Web applications. While in college, Justin was recognized in national and international competitions for embedded software architecture and mathematical modeling. Justin is active in the community, serving as both a United Way Campaign Account Manager and a Healthy Community Council member since 2007. As part of JMU's College for Kids, Justin has taught Video Game Design courses for 10-13 years olds. When he's not programming, Justin enjoys listening to all forms of music and spending as much time outdoors as possible, an interest he developed while earning his Eagle Scout award.
Updated on June 14, 2022Comments
-
Justin C almost 2 years
I have a GridView that I want to export to Excel. When I use the sample code I find online, it exports the content to Excel just fine, but for some reason it also clears out all grid lines outside of my exported table.
For your average excel user this is easy enough to fix, but I need this solution to work for everyone.
So then is there a way to export the data in a GridView into an Excel Workbook so that it looks like it was just typed into Excel? I've pasted the code I am using below, assume that a GridView called 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: Found one partial solution. If I export as a comma-delimited list and set the header to be a CSV file, it opens fine and all grid lines (even those outside of the exported data) are showing. The only problem with this of course is having to strip out every comma and newline character from my values before exporting them.
-
Justin C about 14 yearsI put this code in to place but it's not working. Now nothing exports, the page just reloads like the button click event had no code in it.
-
Justin C about 14 yearsAlso, that code handles showing or not showing the gridlines in the grid of values being output to excel. It does nothing to the rest of the cells in the workbook.
-
s15199d almost 12 yearsDavid Glass...nice snippet. It's a keeper!
-
rDroid over 9 yearsthis still leaves rest of grid lines in excel (outside of data) blank