从SQL导出数据到Excel

    /// <summary>

    /// 将DataTable中的数据导出到指定的Excel文件中

    /// </summary>

    /// <param name="page">Web页面对象</param>

    /// <param name="tab">包含被导出数据的DataTable对象</param>

    /// <param name="FileName">Excel文件的名称</param>

    public void Export(System.Web.UI.Page page, System.Data.DataTable tab, string FileName)

    {



        System.Web.HttpResponse httpResponse = page.Response;

        httpResponse.Clear();

        System.Web.UI.WebControls.DataGrid dataGrid = new System.Web.UI.WebControls.DataGrid();

        dataGrid.DataSource = tab.DefaultView;

        dataGrid.AllowPaging = false;

        dataGrid.HeaderStyle.BackColor = System.Drawing.Color.YellowGreen;

        dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;

        dataGrid.HeaderStyle.Font.Bold = true;

        dataGrid.DataBind();

        httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); //filename="*.xls";

        httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

        httpResponse.ContentType = "application/vnd.ms-excel";

        System.IO.StringWriter tw = new System.IO.StringWriter();

        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

        dataGrid.RenderControl(hw);

        string filePath = page.Request.PhysicalApplicationPath + "Exported//" + FileName;

        System.IO.StreamWriter sw = System.IO.File.CreateText(filePath);

        sw.Write(tw.ToString());

        sw.Close();

        DownFile(httpResponse, FileName, filePath);

        httpResponse.End();



    }



    private bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath)

    {

        try

        {

            //				Response.ContentType = "application/octet-stream";

            Response.ContentType = "application/vnd.ms-excel";



            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=GB2312");

            System.IO.FileStream fs = new System.IO.FileStream(fullPath, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read);



            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);//读入一个压缩块 

                Response.BinaryWrite(readData);

                fPos += size;

            }

            fs.Close();

            System.IO.File.Delete(fullPath);

            return true;

        }

        catch

        {

            return false;

        }

    }