Formatting Excel Export from GridView

[larrydavid]

Hello,

Using ASP.NET 4.0 with VS 2010 Ultimate and SQL Server 2005

I am able to export to a .csv file fine with this code, but am having a problem trying to pass a long numeric value into the first column without Excel trying to put a formula notation on it. Here is the code, output and what I am trying to get:

protected void BtnExportGrid_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition","attachment;filename=" + "_" + userID + "_" +

DateTime.Now.ToShortDateString() + "_" + ".csv");
Response.Charset = "";
Response.ContentType = "application/csv";
//Response.Write(this.MsoFormats);

StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
StringBuilder sb = new StringBuilder();

for (int k = 0; k < gvSubmissionData.Columns.Count; k++)
{
//add separator
sb.Append(gvSubmissionData.Columns[k].HeaderText + ',');
}

//append new line
sb.Append("\r\n");

for (int i = 0; i < gvSubmissionData.Rows.Count; i++)
{
for (int k = 0; k < gvSubmissionData.Columns.Count; k++)
{
//add separator
sb.Append(gvSubmissionData.Rows.Cells[k].Text + ',');
}
//append new line
sb.Append("\r\n");
}
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();
}

Here is the output:
Claim Type Amount Load Date
2.01014E+14 Test 350 12/15/2011
2.01014E+14 Test 909.25 12/15/2011
2.01009E+14 Test 638.68 12/15/2011
2.00911E+14 Test 660.25 12/15/2011

Here is what I want:
Claim Type Amount Load Date
201014479884700 Test 350 12/15/2011
201014479884800 Test 909.25 12/15/2011
201009179891500 Test 638.68 12/15/2011
200910679841500 Test 660.25 12/15/2011

Any help would be greatly appreciated.

Thanks,
Larry

 

[jbenson001]

There's nothing that can be done on the ASP.NET side. The formatting is an Excel issue. You can search and see if there is some way of formatting it in code so that Excel does not reformat it by default like it does.

 

[ggriffit]

sounds like you need to set the formatting in the XL file as per

http://www.greggriffiths.org/webdev/both/excel/activex.html

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[larrydavid]

I was able to do some formatting using the following:

protected void BtnExportGrid_Click(object sender, EventArgs e)
{

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition","attachment;filename=GridViewExport.xls");

Response.Charset = "";

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

StringWriter sw = new StringWriter();

HtmlTextWriter hw = new HtmlTextWriter(sw);

gvSubmissionView.AllowPaging = false;

gvSubmissionView.DataBind();

//Change the Header Row back to white color

gvSubmissionView.HeaderRow.Style.Add("background-color", "#FFFFFF");

//Apply style to Individual Cells

gvSubmissionView.HeaderRow.Cells[0].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[1].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[2].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[3].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[4].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[5].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[6].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[7].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[8].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[9].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[10].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[11].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[12].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[13].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[14].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[15].Style.Add("background-color", "green");

gvSubmissionView.HeaderRow.Cells[16].Style.Add("background-color", "green");

for (int i = 0; i < gvSubmissionView.Rows.Count; i++)
{

GridViewRow row = gvSubmissionView.Rows;

//Change Color back to white

row.BackColor = System.Drawing.Color.White;

//Apply text style to each Row

row.Attributes.Add("class", "textmode");

//Apply style to Individual Cells of Alternating Row

if (i % 2 != 0)
{

row.Cells[0].Style.Add("background-color", "#C2D69B");

row.Cells[1].Style.Add("background-color", "#C2D69B");

row.Cells[2].Style.Add("background-color", "#C2D69B");

row.Cells[3].Style.Add("background-color", "#C2D69B");

row.Cells[4].Style.Add("background-color", "#C2D69B");

row.Cells[5].Style.Add("background-color", "#C2D69B");

row.Cells[6].Style.Add("background-color", "#C2D69B");

row.Cells[7].Style.Add("background-color", "#C2D69B");

row.Cells[8].Style.Add("background-color", "#C2D69B");

row.Cells[9].Style.Add("background-color", "#C2D69B");

row.Cells[10].Style.Add("background-color", "#C2D69B");

row.Cells[11].Style.Add("background-color", "#C2D69B");

row.Cells[12].Style.Add("background-color", "#C2D69B");

row.Cells[13].Style.Add("background-color", "#C2D69B");

row.Cells[14].Style.Add("background-color", "#C2D69B");

row.Cells[15].Style.Add("background-color", "#C2D69B");

row.Cells[16].Style.Add("background-color", "#C2D69B");

}

}

gvSubmissionView.RenderControl(hw);

//style to format numbers to string

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

Response.Write(style);

Response.Output.Write(sw.ToString());

Response.Flush();

Response.End();

}

Thanks for the help.