.net中导出数据到Excel的方法

一、

    调用 dstoexcel(getds(), "people.xls");
        /// <param name="dtSource">参数是填充有数据表的DataSet， </param>
        /// <param name="strFileName">文件名是全名，包括后缀名，如Excel2006.xls</param>
        protected void dstoexcel(DataSet ds, string FileName)
        {
            HttpResponse resp;
            resp = Page.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            string colHeaders = "", ls_item = "";
            DataTable dt = ds.Tables[0];
            DataRow[] myRow = dt.Select();//查询
            int i = 0;
            int cl = dt.Columns.Count;
            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加\n
            for (i = 0; i < cl; i++)
            {
                if (i == cl-1)
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\n";
                }
                else
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\t";
                }
            }
         
           resp.Write(colHeaders);//向HTTP输出流中写入取得的数据信息
            //逐行处理数据  
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据    
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加\n
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }

                }
                resp.Write(ls_item);
                ls_item = "";

            }
           resp.End();
        }

 

二、

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Response.Clear();
                string content = "";
                string filename = "";
                sqlClass1 sc = new sqlClass1();
                DataTable dt = sc.datasourceds();//数据源 dt
                content = getExcelContent(dt);
                if (dt.Rows.Count > 0)
                {
                    filename = "信息统计表.xls";
                }
                string css = ".firstTR td{color:blue;width:100px;}.secondTR td{color:blue;width:100px;}";//可以设置复杂的样式
                CommonTool.ExportToExcel(filename, content, css);
            }    
        }
        //内容很好理解，只需当成Table来拼字符串即可
        private string getExcelContent(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<table borderColor='black' border='1' >");
            sb.Append("<thead><tr><th colSpan='5' bgColor='#ccfefe'>" + "信息表</th></tr>");
            sb.Append("<tr><th bgColor='#ccfefe' width='135'>编号</th><th bgColor='#ccfefe'>姓名</th><th bgColor='#ccfefe'>年龄</th><th bgColor='#ccfefe'>性别</th></tr></thead>");
            sb.Append("<tbody>");
            foreach (DataRow DR in dt.Rows)
            {
                sb.Append("<tr class='firstTR'><td>" + DR["ID"].ToString() + "</td>" +
                           "<td>" + DR["name"].ToString() + "</td>" +
                             "</tr>");
            }
            sb.Append("</tbody></table>");
            return sb.ToString();
        }
        public class CommonTool
        {
            /// <summary>
            /// 以流的形式，可以设置很丰富复杂的样式
            /// </summary>
            /// <param name="content">Excel中内容(Table格式)</param>
            /// <param name="filename">文件名</param>
            /// <param name="cssText">样式内容</param>
            public static void ExportToExcel(string filename, string content, string cssText)
            {
                var res = HttpContext.Current.Response;
                content = String.Format("<style type='text/css'>{0}</style>{1}", cssText, content);
                res.Clear();
                res.Buffer = true;
                res.Charset = "UTF-8";
                res.AddHeader("Content-Disposition", "attachment; filename=" + filename);
                res.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
                res.ContentType = "application/ms-excel;charset=UTF-8";
                res.Write(content);
                res.Flush();
                res.End();
            }
        }

转载于:https://www.cnblogs.com/jksun/p/8421965.html