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

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.

Contents hide

Answers:

Method 1

Method 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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
    {

    }

Method 2

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/

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating