如何在导出前格式化GridView中的列以在Excel中显示所有数字？ [英] How can I format a column from a GridView before export to show all numbers in excel?

问题描述

我试图将一个GridView导出到Excel，我有一列有一系列数字，如1245333325364.当我运行查询的GridView时，我可以看到完整的数字，但是当我导出到Excel时，我所看到的全部是该列上的1.00133E + 12。我知道我可以让用户在Excel中更改它，但并不是所有文件在导出后都会打开，而是直接将其保存到目录中。我真的很想在导出过程中更改列的格式，而不是让用户在保存文件之前进行操作。我在C＃中执行导出任何帮助都将非常感激。

我用来导出GridView的代码如下所示：

  protected void exporttoexcel_Click（object sender，EventArgs e）
 {
 string date = DateTime.Now.ToString（MM-dd -YYYY）; 
 
 PrepareGridViewForExport（GridView1）; 
 Response.Clear（）; 
 Response.Buffer = true; 
 
 Response.AddHeader（content-disposition，attachment; filename =+ date +_+ CHROUT.Text +_Trailer_+ TRAILER.Text）; 
 Response.Charset =''; 
 Response.ContentType =application / vnd.ms-excel; 
 StringWriter sw = new StringWriter（）; 
 HtmlTextWriter hw = new HtmlTextWriter（sw）; 
 
 GridView1.AllowPaging = false; 
 GridView1.DataBind（）; 
 
 GridView1.HeaderRow.Style.Add（background-color，#FFFFFF）; 
 GridView1.HeaderRow.Cells [0] .Style.Add（width，105px）; 
 GridView1.HeaderRow.Cells [0] .Style.Add（background-color，#CCCCCC）; 
 GridView1.HeaderRow.Cells [1] .Style.Add（background-color，#CCCCCC）; 
 GridView1.HeaderRow.Cells [2] .Style.Add（background-color，#CCCCCC）; 
 GridView1.HeaderRow.Cells [3] .Style.Add（background-color，#CCCCCC）; 
 GridView1.HeaderRow.Cells [4] .Style.Add（background-color，#CCCCCC）; 
 GridView1.HeaderRow.Cells [5] .Style.Add（background-color，#CCCCCC）; 
 GridView1.HeaderRow.Cells [6] .Style.Add（background-color，#CCCCCC）; 
 GridView1.HeaderRow.Cells [7] .Style.Add（background-color，#CCCCCC）; 
 
 for（int i = 0; i< GridView1.Rows.Count; i ++）
 {
 GridViewRow row = GridView1.Rows [i]; 
 
 row.BackColor = System.Drawing.Color.White; 
 
 row.Attributes.Add（class，texmode）; 
 
 if（i％2！= 0）
 {
 row.Cells [0] .Style.Add（background-color，＃f0f0f0）; 
 row.Cells [1] .Style.Add（background-color，＃f0f0f0）; 
 row.Cells [2] .Style.Add（background-color，＃f0f0f0）; 
 row.Cells [3] .Style.Add（background-color，＃f0f0f0）; 
 row.Cells [4] .Style.Add（background-color，＃f0f0f0）; 
 row.Cells [5] .Style.Add（background-color，＃f0f0f0）; 
 row.Cells [6] .Style.Add（background-color，＃f0f0f0）; 
 row.Cells [7] .Style.Add（background-color，＃f0f0f0）; 
} 
} 
 GridView1.RenderControl（hw）; 
 //将数字格式化为字符串
 string style = @< style> .text {mso-number-format：\ @;}< / style>; 
 Response.Write（style）; 
 Response.Output.Write（sw.ToString（））; 
 Response.Flush（）; 
 Response.End（）; 
} 
  

解决方案

这个问题。我基本上只需要在导出之前将格式添加到GridView，并在DataBound上更具体。请看下面的代码：

首先为OnRowDataBound创建一个事件

  protected void GridView1_RowDataBound（object sender，GridViewRowEventArgs e）
 {
 if（e.RowType == DataControlRowType.DataRow）
 {
 e.Row.Cells [1] .Attributes.Add（class，text）; 
 e.Row.Cells [2] .Attributes.Add（class，text）; 
 
 
 
 
 
 
 $ b然后在GridView上引用这样的内容： p> 
 
 
 < asp：GridView ID =GridView1runat =serverOnRowDataBound =GridView1_RowDataBound> 
  
然后在导出GridView之前添加这行代码。
  Response.Write（style）; 
  
就是这样。
 
I'm trying to export a GridView to Excel and I have a column with a series of numbers like 1245333325364. When I run the query for the GridView I can see the complete number but when I export to excel all I see is 1.00133E+12 on that column. I know I can have the user change this in excel but not all files are being open after export they just save it straight into a directory. I will really like to change the column's format in the export process rather than having the user do it before they save the file. I'm performing the export in C# any help will be really appreciate.

The code I'm using to export the GridView is like so:
    protected void exporttoexcel_Click(object sender, EventArgs e)
    {
        string date = DateTime.Now.ToString("MM-dd-yyyy");

        PrepareGridViewForExport(GridView1);
        Response.Clear();
        Response.Buffer = true;

        Response.AddHeader("content-disposition", "attachment;filename=" + date + "_" + CHROUT.Text + "_Trailer_" + TRAILER.Text);
        Response.Charset = "''";
        Response.ContentType = "application/vnd.ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        GridView1.AllowPaging = false;
        GridView1.DataBind();

        GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
        GridView1.HeaderRow.Cells[0].Style.Add("width", "105px");
        GridView1.HeaderRow.Cells[0].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[1].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[2].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[3].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[4].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[5].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[6].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[7].Style.Add("background-color", "#CCCCCC");

        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            GridViewRow row = GridView1.Rows[i];

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

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

            if (i % 2 != 0)
            {
                row.Cells[0].Style.Add("background-color", "#f0f0f0");
                row.Cells[1].Style.Add("background-color", "#f0f0f0");
                row.Cells[2].Style.Add("background-color", "#f0f0f0");
                row.Cells[3].Style.Add("background-color", "#f0f0f0");
                row.Cells[4].Style.Add("background-color", "#f0f0f0");
                row.Cells[5].Style.Add("background-color", "#f0f0f0");
                row.Cells[6].Style.Add("background-color", "#f0f0f0");
                row.Cells[7].Style.Add("background-color", "#f0f0f0");
            }
        }
        GridView1.RenderControl(hw);
        //style to format numbers to string
        string style = @"<style> .text { mso-number-format:\@; } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }

解决方案
I finally got the answer for this question. I basically just need to add the format to the GridView before export and to be more specific on the DataBound. Take a look at the code below:

First create an event for the OnRowDataBound
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        e.Row.Cells[1].Attributes.Add("class", "text");
        e.Row.Cells[2].Attributes.Add("class", "text");
    }
}
Then reference this on the GridView like this:
<asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound">
Then just add this little line of code right before your export the GridView.
Response.Write(style);
And that's all.

                        
这篇关于如何在导出前格式化GridView中的列以在Excel中显示所有数字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！