.net 导出excel方法，内容自动识别科学计数法去除方式style= 'vnd.ms-excel.numberformat:@ '

最新推荐文章于 2022-10-18 11:28:01 发布

原创最新推荐文章于 2022-10-18 11:28:01 发布 · 5.6k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#excel #.net #string #csv #border #table

C# 专栏收录该内容

29 篇文章

订阅专栏

本文介绍了一种使用C#将DataTable数据转换为Excel文件的方法，包括设置响应头、编码、列标题和数据写入过程。

public void Export(string FilterName, DataTable dt, string[] headtxt)
        {
            HttpResponse resp;
         
            resp = System.Web.HttpContext.Current.Response;
           
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=oursjia." + FilterName);
            resp.AppendHeader("Content-Type", "application/ms-excel");


            string colHeaders = "",
            ls_item = "";
            DataRow[] myRow = dt.Select();
            int i = 0;
            int cl = dt.Columns.Count;


            colHeaders = colHeaders+" <html> <head> \n ";
           
            colHeaders = colHeaders + " <meta   http-equiv='Content-Type'   content='text/html; charset=gb2312' /> \n ";
            colHeaders = colHeaders+" </head> \n ";
            colHeaders = colHeaders+" <body>\n ";
            colHeaders = colHeaders+" <table border='1'> ";
            colHeaders = colHeaders + "<tr> ";
            for (i = 0; i < headtxt.Length; i++)
            {
                if (i == (headtxt.Length - 1))
                {
                    colHeaders = colHeaders +"<td style='background-color:#CCCCCC'>"+ headtxt[i] + "</td>\n";
                }
                else
                {
                    if (FilterName == "csv")
                    {
                        colHeaders += headtxt[i] + ",";
                    }
                    else
                    {
                        colHeaders = colHeaders + "<td style='background-color:#CCCCCC'>" + headtxt[i] + "</td>";
                    }
                }
            }
            colHeaders = colHeaders + "</tr> ";
            resp.Write(colHeaders);
            foreach (DataRow row in myRow)
            {
                ls_item = ls_item + "<tr>";
                for (i = 0; i < cl; i++)
                {
                    
                    if (i == (cl - 1))
                    {
                        ls_item = ls_item + "<td style= 'vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>" + "\n";
                    }
                    else
                    {
                        if (FilterName == "csv")
                        {
                            ls_item += row[i].ToString() + ",";
                        }
                        else
                        {
                            ls_item = ls_item + "<td style= 'vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>";
                        }
                    }
                    
                }
                ls_item = ls_item + "</tr>";                
            }
            ls_item += " </table> \n ";
            ls_item += " </body> \n ";
            ls_item += " </html>";
            resp.Write(ls_item);
            ls_item = "";
            resp.End();
        }