DataSet导出到Excel

  //导出按钮事件触发 调用导出数据方法

protected void btn_export_Click(object sender, EventArgs e)
        {
            int count = 0;
            StringBuilder str_Where = new StringBuilder();
            str_Where.Append(" user_depid=" + user_depid.SelectedValue + " and user_powerid=" + user_powerid.SelectedValue);

            DataSet ds = userinfobll.ExportUserList(str_Where.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                string filecode = "userlist_" + DateTime.Now.ToString("yyyyMMdd");
                string filename = filecode + ".xls";
                string FileName = filename;
                CreateExcel(ds, FileName);
                count++;
            }

            if (count > 0)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "FF", "<script>alert('导出成功！');Istest1();</script>");
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "FF", "<script>alert('无数据导出失败！');Istest1();</script>");
            }
        }

        public void CreateExcel(DataSet ds, string FileName)
        {

            HttpResponse resp;

            resp = Page.Response;

            resp.ContentEncoding = Encoding.GetEncoding("GB2312");

            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            resp.ContentType = "application/ms-excel";
            string colHeaders = "", ls_item = "";
            int cl = ds.Tables[0].Columns.Count;
            int i = 0;

            //定义表对象与行对像，同时用DataSet对其值进行初始化

            DataTable dt = ds.Tables[0];

            DataRow[] myRow = dt.Select("");

 

            //取得数据表各列标题，各标题之间以"t分割，最后一个列标题后加回车符

            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";
                }

            //向HTTP输出流中写入取得的数据信息

            resp.Write(colHeaders);

            //逐行处理数据 

            foreach (DataRow row in myRow)
            {

                //在当前行中，逐列获得数据，数据之间以"t分割，结束时加回车符"n

                for (i = 0; i < cl; i++)
                    if (i == (cl - 1))
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }

                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据   

                resp.Write(ls_item);

                ls_item = "";

            }

 

            //写缓冲区中的数据到HTTP头文件中

            resp.End();

        }