Data Browser - Viewing Site  Sector 23 Code Bank Logged in as:  Guest  




           


.NET Export GridView to a CSV (that Excel can Actually Read)
To export a gridview to CSV.

Note: I am not so sure about the 1252 encoding.
This allows Excel to read the text properly with my test data, but for high-register chars, you may need to do an explicit convert on the text from .NET string to Code-1252.

1. Set correct response. Filename can be something.csv

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
// This is a little tricky. Would like to use utf-8 or unicode... but Excel on Windows uses 1252 by default so we need to keep the same so most users can read the file.
// At some point, we may need to actually convert our text from whatever .NET uses to 1252, but at the moment they seem similar enough that it is okay
HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding(1252);


2. Determine what columns to export. I have a custom field on my templatecolumns to choose whether it is exportable (since they tend to include complex controls). You can adapt this as needed

// determine which columns to export
List<int> colsToExport = new List<int>();
for (int colIx = 0; colIx < gv.Columns.Count; colIx++)
{
if ((gv.Columns[colIx].Visible || gv.Columns[colIx] is ExportField) && gv.Columns[colIx].HeaderText != string.Empty)
{
if (gv.Columns[colIx] is TemplateField && (gv.Columns[colIx] as TemplateField).AllowExport == false)
{
continue; // don't export this column
}

gv.Columns[colIx].Visible = true; // temporarily show column so that data is bound and can be exported

colsToExport.Add(colIx);
}
}

3. Loop over headers and add header text, separated by commas. Separate rows by carriage return.
bool firstCell;

// add the header row to the table
if (gv.HeaderRow != null)
{
firstCell = true;
foreach (int colIx in colsToExport)
{
if (firstCell)
{
firstCell = false;
}
else
{
sb.Append(",");
}
CSVExportUtility.AddHTMLFieldForCSV(gv.Columns[colIx].HeaderText, sb);
}
sb.AppendLine();
}

4. If your grid is paged, you need to show all rows so that the entire grid is rendered as CSV (unless you only want to render the current page)
CSVExportUtility class comes later.

if (gv.AllowPaging)
{
gv.PageSize = gv.PageSize * gv.PageCount; // make all records fit on ONE page
gv.PageIndex = 0;
gv.DataBind(); // load this page's data
}

5. Now loop over row cells and add data, separated by commas. Separate rows by carriage return.
CSVExportUtility class comes later.

// add each of the header fields
foreach (GridViewRow row in gv.Rows)
{
firstCell = true;
foreach (int colIx in colsToExport)
{
if (firstCell)
{
firstCell = false;
}
else
{
sb.Append(",");
}
CSVExportUtility.AddFieldForCSV(row.Cells[colIx], sb);
}
sb.AppendLine();
}

6. Close response

// render the response
HttpContext.Current.Response.Write(sb.ToString());
HttpContext.Current.Response.End();

7. The actual render of the content is a bit tricky. Adapt below functions as needed.
This converts HTML to text, strips out HTML tags, and moves text from controls to flat text,
so that the actual text is exported (instead of formatted HTML/controls).
There is also some hack code to force Excel to read leading zeros (such as zip codes).

public static class CSVExportUtility
{
const string HTML_TAG_PATTERN = "<.*?>";

/// <summary>
/// export html to a csv
/// </summary>
/// <param name="text"></param>
/// <param name="sbCSV"></param>
public static void AddHTMLFieldForCSV(string text, StringBuilder sbCSV)
{
// convert html to text
text = HttpUtility.HtmlDecode(text);
// convert <br/> to a space since it will be removed, and lines run together (we could use a newline, but that's more difficult to read in excel, etc.
text = text.Replace("<br/>", " ");
// string html
text = Regex.Replace(text, HTML_TAG_PATTERN, string.Empty);
AddFieldForCSV(text, sbCSV, false, false);
}

public static void AddFieldForCSV(TableCell tc, StringBuilder sbCSV)
{
PrepareControlForExport(tc);
CSVExportUtility.AddHTMLFieldForCSV(tc.Text, sbCSV);
}

/// <summary>
/// export text to a csv
/// </summary>
/// <param name="text"></param>
/// <param name="sbCSV"></param>
/// <param name="appendTrailingComma"></param>
/// <param name="endOfRow"></param>
public static void AddFieldForCSV(string text, StringBuilder sbCSV, bool appendTrailingComma, bool endOfRow)
{
// shouldn't start or end with whitespace, escape quotes
if(text != null)
text = text.Trim().Replace("\"", "\"\"");
// quote field
int testInt;
if (text != null && text.Trim().Length > 1 && text.Trim()[0] == '0' && int.TryParse(text.Trim(), out testInt))
{ // if text is numeric and starts with '0' tell excel to treat as string and not strip the zero. This ONLY works if it's numeric! Otherwise it fails, example ="a,b" will use 2 cells
text = "=\"" + text.Trim() + "\"";
}
else
{
text = "\"" + text + "\"";
}

sbCSV.Append(text);
if (appendTrailingComma)
sbCSV.Append(",");
if (endOfRow)
sbCSV.AppendLine();
}

/// <summary>
/// Replace any of the contained controls with literals so control can be exported as plain text
/// </summary>
/// <param name="control"></param>
private static void PrepareControlForExport(TableCell control)
{
if (control.Controls.Count == 0)
return; // nothing to change

string text = "";
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (!current.Visible)
continue; // not rendered on screen, so do not display in excel file!
if (current is LinkButton)
{
//control.Controls.Remove(current);
text += (current as LinkButton).Text;
}
else if (current is ImageButton)
{
//control.Controls.Remove(current);
text += (current as ImageButton).AlternateText;
}
else if (current is Image)
{
//control.Controls.Remove(current);
text += (current as Image).AlternateText;
}
else if (current is HyperLink)
{
//control.Controls.Remove(current);
text += (current as HyperLink).Text;
}
else if (current is DropDownList)
{
//control.Controls.Remove(current);
text += (current as DropDownList).SelectedItem.Text;
}
else if (current is CheckBox)
{
//control.Controls.Remove(current);
text += RmsUtilities.ShowBooleanText((current as CheckBox).Checked);
}
else if (current is LiteralControl)
{
//control.Controls.Remove(current);
text += (current as LiteralControl).Text;
}
else if (current is Label)
{
//control.Controls.Remove(current);
text += (current as Label).Text;
}
}
control.Text = text;
} // prepare control

}



HERE IS THE FULL CODE FOR THE GRID EXPORT:


/// <summary>
///
/// </summary>
public class GridViewExportUtil
{
/// <summary>
///
/// </summary>
/// <param name="fileName"></param>
/// <param name="gv"></param>
public static void Export(string fileName, GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
// This is a little tricky. Would like to use utf-8 or unicode... but Excel on Windows uses 1252 by default so we need to keep the same so most users can read the file.
// At some point, we may need to actually convert our text from whatever .NET uses to 1252, but at the moment they seem similar enough that it is okay
HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding(1252);

StringBuilder sb = new StringBuilder();


// determine which columns to export
List<int> colsToExport = new List<int>();
for (int colIx = 0; colIx < gv.Columns.Count; colIx++)
{
if ((gv.Columns[colIx].Visible || gv.Columns[colIx] is ExportField) && gv.Columns[colIx].HeaderText != string.Empty)
{
if (gv.Columns[colIx] is TemplateField && (gv.Columns[colIx] as TemplateField).AllowExport == false)
{
continue; // don't export this column
}

gv.Columns[colIx].Visible = true; // temporarily show column so that data is bound and can be exported

colsToExport.Add(colIx);
}
}
bool firstCell;

// add the header row to the table
if (gv.HeaderRow != null)
{
firstCell = true;
foreach (int colIx in colsToExport)
{
if (firstCell)
{
firstCell = false;
}
else
{
sb.Append(",");
}
CSVExportUtility.AddHTMLFieldForCSV(gv.Columns[colIx].HeaderText, sb);
}
sb.AppendLine();
}
if (gv.AllowPaging)
{
gv.PageSize = gv.PageSize * gv.PageCount; // make all records fit on ONE page
gv.PageIndex = 0;
gv.DataBind(); // load this page's data
}

// add each of the row fields
foreach (GridViewRow row in gv.Rows)
{
firstCell = true;
foreach (int colIx in colsToExport)
{
if (firstCell)
{
firstCell = false;
}
else
{
sb.Append(",");
}
CSVExportUtility.AddFieldForCSV(row.Cells[colIx], sb);
}
sb.AppendLine();
}

// render the htmlwriter into the response
HttpContext.Current.Response.Write(sb.ToString());
HttpContext.Current.Response.End();
}

}

Created By: amos 10/16/2014 12:27:28 PM
Updated: 10/21/2014 11:44:13 AM