Nick Green

NA
4
15.5k

Gridview >> Excel - dropping 0s ??

Jul 23 2008 3:23 PM

I have searched the net high and low for the solution to no avail. I am exporting GridView to Excel - that I have accomplished. One of my columns contains information with leading 0s. Excel is truncating them and I can't figure out how to apply a style sheet to keep it from happening. I am using a code-behind class for portability - I have to invoke this in several areas with only the Queries changing. - hence why I can't hard code a query in :X

NOTE:

GridView ID = GridView1

Query source: SqlDataSource1

I'm developing with a code-behind class named as: 'exportToExcel.cs' -- the code

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)

{