导出到Excel在ASP.NET中，发出带有十进制格式的数大于1000 [英] Export to Excel in ASP.NET, issue with decimal formatting for numbers greater than 1000

问题描述

我导出数据使用以下code脱颖而出。

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
    {

    }
}

- 下面的结果后，出口到Excel（约格式化对不起，我不能上传图片文件）的一个子集

--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

- 问题，Excel将截断在小数零少于1000个号码，我没有看到数字大于1000这个问题，我想甚至可以看到decilam地方是值是0（比如0.00 ）。我不想文本样式应用到列。任何建议或帮助实现这一目标是大大AP preciated。先谢谢了。

--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.

推荐答案

如下解决了这个问题。我失去的轨道，建议这些步骤有用的文章。

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

1. 在GridView控件的RowDataBound，我加了class属性所需的列

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

保护无效gvExcel_RowDataBound（对象发件人，GridViewRowEventArgs E）
    {

protected void gvExcel_RowDataBound(object sender, GridViewRowEventArgs e) {

    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        e.Row.Cells[22].Attributes.Add("class", "cost");
    }
} 

和作出了code如下一些细微的变化。好了，问题解决了现在。

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
    {

    }

这篇关于导出到Excel在ASP.NET中，发出带有十进制格式的数大于1000的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！