Nick Green

NA
4
15.5k

export to excel - dynamic connection sources - not truncating 0s

Jul 23 2008 1:49 PM

I have searched the Internet high and low, but with no luck. Many people have posts about this but no real solution exists that I could find. I am exporting a GridView to Excel but I am having my leading 0s truncated. I am aware that you need to convert the column to a 'text' field but how do I go about doing that.

I keep my code on the backend and reference it frontend for portability.
NOTE: GridView ID: GridView1
DataSource = SqlDataSource1

[backend code - exportToExcel.cs]

using System;

using System.Configuration;

using System.Data;

using System.IO;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

/// <summary>

/// Summary description for exportToExcel

/// </summary>

public class exportToExcel

{

public static void export(GridView gridView)

{

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.AddHeader("content-disposition",

"attachment; filename=dummy.xls");

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htmlTW = new HtmlTextWriter(sw);

//create table to hold gridView

Table tbl = new Table();

//header

if (gridView.HeaderRow != null)

{

exportToExcel.currentRowStatus(gridView.HeaderRow);

tbl.Rows.Add(gridView.HeaderRow);

}

//write rows

foreach (GridViewRow rowX in gridView.Rows)

{

exportToExcel.currentRowStatus(rowX);

tbl.Rows.Add(rowX);

}

tbl.RenderControl(htmlTW);

//the CSS that should fix the truncation thanks to Excel >:| geh

////string style = @"<style .text { mso-number-format:\@; } </style>";

//writing the CSS to output

//works - change output format to txt and can see this!

////HttpContext.Current.Response.Write(style);

HttpContext.Current.Response.Write(sw.ToString());

HttpContext.Current.Response.End();

}

//this is not being fired

//if this would fire it would write the CSS to the third column in Excel

////protected void gridView_RowDataBound(object sender, GridViewRowEventArgs e)

////{

//// if (e.Row.RowType == DataControlRowType.DataRow)

//// {

//// e.Row.Cells[2].Attributes.Add("class", "text");

//// }

////}

//get values for various gridview options

private static void currentRowStatus(Control ctrl)

{

for (int i = 0; i < ctrl.Controls.Count; i++)

{

Control current = ctrl.Controls[i];

if (current is LinkButton)

{