Export to Excel in ASP.NET, issue with decimal formatting for numbers greater than 1000

Question

I am exporting dataset to excel using the following code.

 public void Export()
{
    string fileName = "Non-LaborActuals";
    string cmd = "";
    if (grid == "NLP")
    {
        fileName = wrnum + "_Paid Non-Labor.xls";
        cmd = "sp_Act_NonLabor_Paid_export";
    }
    if (grid == "NLC")
    {
        fileName = wrnum + "_Committed Non-Labor.xls";
        cmd = "sp_Act_NonLabor_Commit_export";
    }

    System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
    response.Clear();
    response.Charset = "";
    response.ContentType = "application/vnd.ms-excel";
    response.AddHeader("Content-Disposition", "attachment;filename=\"" + fileName);
    DataSet ds1;
    try
    {
        using (SqlCommand sqlCmd1 = new SqlCommand(cmd))
        {
            sqlCmd1.CommandType = CommandType.StoredProcedure;
            sqlCmd1.Parameters.Add(new SqlParameter("@c_service_req", SqlDbType.VarChar, 10));
            sqlCmd1.Parameters["@c_service_req"].Value = wrnum;
            ds1 = db.ExecuteDataSet(sqlCmd1);
        }

         using (System.IO.StringWriter sw = new System.IO.StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                DataGrid dg = new DataGrid();
                dg.DataSource = ds1.Tables[0];
                dg.DataBind();
                dg.RenderControl(htw);
                response.Write(sw.ToString());
                response.End();
            }
        }
    }
    catch
    {

    }
}

--Below a subset of result after export to excel (sorry about formatting, i cannot upload image file)

EXPENSE_TYPE_DESC   HOURS   COST_USD
Standard Hours  8   903.2
Standard Hours  16  1,172.80
Standard Hours  40  372
Standard Hours  32  297.6
Standard Hours  5   90
Standard Hours  71  1,278.00
Standard Hours  29  0

--The problem, Excel is truncating zeros in decimal places for numbers less than 1000 and i don't see this issue for numbers greater than 1000. I want to see the decilam places even is the value is 0 (like 0.00). I do not want to apply text style to the column. Any suggestions or help to achieve this is greatly appreciated. Thanks in advance.

score 3 · Accepted Answer · answered Nov 14 '11 at 21:47

Resolved this issue as follows. I lost track of an helpful article that suggested these steps.

on RowDataBound of GridView, I added Class attribute to the desired column

protected void gvExcel_RowDataBound(object sender, GridViewRowEventArgs e) {
```
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        e.Row.Cells[22].Attributes.Add("class", "cost");
    }
} 
```

And made some minor changes to the code as below. Well, the issue is resolved for now.

    try
    {
        DataSet ds = new DataSet();
        ds = businesscase.services.Actuals.GetActualsGridData(bcid, cmd);
        using (System.IO.StringWriter sw = new System.IO.StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                // instantiate a datagrid 
                GridView gvExcel = new GridView();
                gvExcel.RowDataBound += new GridViewRowEventHandler(this.gvExcel_RowDataBound);

                gvExcel.DataSource = ds.Tables[0];
                gvExcel.DataBind();
                gvExcel.RenderControl(htw);

                response.Write("<style> .cost{mso-number-format:\"\\#\\#0\\.00\";} </style>");

                //response.Write(style);
                response.Write(sw.ToString());
                response.End();
            }
        }
    }
    catch
    {

    }

Awesome. I used the same thing but a little differently. – sam Aug 29 '17 at 00:27 — sam, Aug 29 '17 at 00:27

Matt Varblow · Answer 2 · 2011-11-12T15:18:52.937

0

You might try adding some formatting to the DataGrid columns. For example, using the currency format string ("C") would likely fix the missing decimal problem. See here for an example:

http://dotnetguts.blogspot.com/2007/12/export-datagrid-to-excel-in-aspnet.html

The simple - send HTML and set the content type to ms-excel - approach doesn't give you much control over the formatting of the Excel spreadsheet. If you really want control then you're going to have to use another approach. Probably the best approach in an ASP.NET environment is to return an Excel XML document. Here are a few good resources to help you:

http://forums.asp.net/t/1038105.aspx

http://mikesnotebook.wordpress.com/2010/03/26/dynamically-generate-excel-files-from-asp-net/

http://meghainfotech.wordpress.com/2011/04/15/how-to-generate-excel-xl-document-from-asp-net-c/

edited Nov 12 '11 at 15:18

answered Nov 12 '11 at 15:10

Matt Varblow

7,651
3
34
44

Thanks Matt, i tried your first suggestion but was not helpful. DataGrid is getting correct value but it is the Excel behaviour which is truncating the trailing zeros. I did not dig deeper into your other suggestions but they seem to be worth trying and i will explore those options in future. – vindh123 Nov 14 '11 at 21:23
That's right. The problem is that Excel doesn't know how you want the cells formatted. I was guessing that if you put the $ in front of the number (using the "currency" format string) then Excel might interpret the data as a currency and keep the two decimal places. Again, your best bet is to actually output excel data instead of HTML. – Matt Varblow Nov 17 '11 at 22:19

Export to Excel in ASP.NET, issue with decimal formatting for numbers greater than 1000

2 Answers2

Linked