GridView中将数据下载到excel中如何显示18位长度已上的数值,excel默认的是15位

Question

请教一下大家,就是我从GridView中将数据给下载到excel中,但是GridView中有一列的长度是18位已上,而excel只支持15位的数值显示,下载之后excel中卡号显示的值其后就自动加0,只显示15位正确的数值,其后都是0,应该怎么处理

Answer 1

我写的代码为下,传入的值为DataGrid,是导出到excel 2007版的代码public class ExcelUtil
    {
        public static void ExportToExcel(DataGrid dtData, string footString, System.Web.UI.Page page)
        {
            if (dtData != null)
            {
                if (dtData.Items.Count == 0)
                {
                    Tools.Alert("当前无数据导出", page);
                    return;
                }
                dtData.AllowPaging = false;
                dtData.AutoGenerateColumns = false;                System.Web.HttpResponse httpResponse = page.Response;
                httpResponse.Clear();
                httpResponse.Buffer = true;
                httpResponse.Charset = "gb2312";
                string fileName = DateTime.Now.ToString("yyyyMMddHHmmssms") + ".xls";
                httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName,
                    System.Text.Encoding.UTF8));
                httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                httpResponse.ContentType = "application/ms-excel";
                System.IO.StringWriter tw = new System.IO.StringWriter();
                System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
                dtData.RenderControl(hw);
                hw.Write(footString);
                string directoryPath = page.Server.MapPath("~/") + "TempExcel";
                string filePath = page.Server.MapPath("~/") + "TempExcel\\" + fileName;
                if (!System.IO.Directory.Exists(directoryPath))
                {
                    System.IO.Directory.CreateDirectory(directoryPath);
                }
                System.IO.StreamWriter sw = System.IO.File.CreateText(filePath);
                sw.Write(tw.ToString());
                sw.Close();
                DownFile(httpResponse, fileName, filePath);
                httpResponse.End();
            }
        }        private static bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath)
        {
            System.IO.FileStream fs = System.IO.File.OpenRead(fullPath);
            try
            {
                Response.ContentType = "application/octet-stream";                Response.AppendHeader("Content-Disposition", "attachment;filename=" +
                HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=GB2312");
                long fLen = fs.Length;
                int size = 102400;//每100K同时下载数据 
                byte[] readData = new byte[size];//指定缓冲区的大小 
                if (size > fLen) size = Convert.ToInt32(fLen);
                long fPos = 0;
                bool isEnd = false;
                while (!isEnd)
                {
                    if ((fPos + size) > fLen)
                    {
                        size = Convert.ToInt32(fLen - fPos);
                        readData = new byte[size];
                        isEnd = true;
                    }
                    fs.Read(readData, 0, size);//读入一个压缩块
                    if (readData.Length > 0)
                        Response.BinaryWrite(readData);
                    fPos += size;
                }
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                fs.Close();
                System.IO.File.Delete(fullPath);
            }
        }
    }

Answer 2

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;public partial class Default2 : System.Web.UI.Page
{    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataBinder();
        }
    }    private void DataBinder()
    {
        SqlConnection con =new SqlConnection(ConfigurationManager.ConnectionStrings["ABCConnectionString"].ConnectionString);
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [table]", con);
        DataSet dt = new DataSet();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }  
    
    public override void VerifyRenderingInServerForm(Control control)
    {
       
    }    protected void Button1_Click(object sender, EventArgs e)
    {
        string style = @"<style> .text { mso-number-format:\@; } </style> ";
        Response.ClearContent();
        Response.Buffer = true;
        string strFileName = "Myfile.xls";
        Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8));
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 
        Response.ContentType = "application/excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        GridView1.RenderControl(htw);
        // Style is added dynamically
        Response.Write(style);
        Response.Write(sw.ToString());
        Response.End();
    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {            e.Row.Cells[1].Attributes.Add("class", "text");
        }
    }
  
}
你用这个试试看，我以前用过，可以的．

Answer 3

我在此中加了这段解决了,谢谢楼上void dtData_ItemDataBound(object sender, DataGridItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Item ||
                e.Item.ItemType == ListItemType.AlternatingItem)
            {
                //解决此问题的关键语句,将查询出来的值加个引号
                e.Item.Cells[2].Attributes.Add("style", "vnd.ms-excel.numberformat:@");                e.Item.Cells[6].Text = GetCZOrderStatus(e.Item.Cells[6].Text);
            }
        }