DataTable To Excel File,Create Excel, Create Local Floder 从数据库中读取数据存到本地指定文件夹的Excel中

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;

public partial class Excel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
      
    }
    protected void BtnExport_Click(object sender, EventArgs e)
    {
        string Path = "E:\\DorFee"; //文件夹
        string SubPath = "KQ"; //子文件夹
        string ExcelName = "KQ" + DateTime.Now.ToString("yyyyMM") + ".xlsx"; //要创建的Excel的文件名
        string ExcelFullPath = Path + "\\" + SubPath + "\\" + ExcelName; //Excel的完整路径
        string str = "SELECT [classID],[className] FROM [SchoolNews].[dbo].[newsClass] ";
        CreateFolder(Path,SubPath,ExcelName);  //创建要保存Excel的文件夹
        Export(str,ExcelFullPath); //从数据库中读取数据写到Excel中
    }
    protected void Export(string str,string excelFullPath)
    {
        // sqlH = new SQLHelper();

        //string str = "SELECT [classID],[className] FROM [SchoolNews].[dbo].[newsClass] ";
        SqlConnection sqlCon = new SqlConnection("server=.;database=schoolnews;uid=sa;pwd=;");
        sqlCon.Open();
        SqlCommand sqlcom = new SqlCommand(str, sqlCon);
        System.Data.SqlClient.SqlDataReader sdr = sqlcom.ExecuteReader();
        System.Data.DataTable dt = new System.Data.DataTable();
        dt.Load(sdr);
        sdr.Close();
        sqlCon.Close();

        int[] index = { 0, 1 };
        string[] heads = { "classID", "className" };
        //string name = Server.MapPath("file/template.xls");
        string name = excelFullPath;
        ExportToExcel(dt, excelFullPath, index, heads);// Server.MapPath("file/template.xls")
        System.IO.FileInfo aFile = new System.IO.FileInfo(name);
        //页面弹出保存窗口，exe中不需要
        //string na = Path.GetFileName(name);
        //Response.Clear();
        //Response.ClearHeaders();
        //Response.BufferOutput = false;
        //Response.ContentType = "application/ms-excel";
        ////Response.AddHeader("Content-Disposition","attachment;filename="+na);  
        ////上面这条中文会乱码,应该下面这样写  
        //Response.AppendHeader("Content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(na, System.Text.Encoding.UTF8));
        //Response.AddHeader("Content-Length", aFile.Length.ToString());
        //Response.WriteFile(name);
        //Response.Flush();
        //Response.End();
    }
    // DataTable To Excel
    public static bool ExportToExcel(System.Data.DataTable table, string excelName, int[] columnIndexs, string[] columnHeads)
    {
        #region 将方法中用到的所有Excel变量声明在方法最开始，以便最后统一回收。
        object missing = System.Reflection.Missing.Value;

        Microsoft.Office.Interop.Excel.ApplicationClass oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
        Microsoft.Office.Interop.Excel.Workbook obook = null;
        Microsoft.Office.Interop.Excel.Worksheet oSheet = null;
        Microsoft.Office.Interop.Excel.Range range = null;
        #endregion
        try
        {
            obook = oExcel.Workbooks.Add("");

            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Worksheets[1];
            int rCount, cCount;
            rCount = table.Rows.Count;
            cCount = table.Columns.Count;
            object obj = System.Reflection.Missing.Value;

            if (cCount < columnIndexs.Length || cCount < columnHeads.Length)
            {
                throw new ArgumentOutOfRangeException("columnIndexs 与 columnHeads 长度必须一致。");
            }
            for (int i = 1; i <= columnIndexs.Length; i++)
            {
                //Excel.Range = (Excel.Range)oSheet.Columns.get_Item(i, obj); 
                range = (Microsoft.Office.Interop.Excel.Range)oSheet.Columns.get_Item(i, obj);
                range.NumberFormatLocal = "@";
            }
            for (int c = 0; c < columnIndexs.Length; c++)
            {
                oSheet.Cells[1, c + 1] = columnHeads[c];
                for (int r = 1; r <= rCount; r++)
                {
                    oSheet.Cells[r + 1, c + 1] = table.Rows[r - 1][columnIndexs[c]].ToString();
                }
            }
            obook.Saved = true;
            obook.SaveCopyAs(excelName);

            //必须调用 obook.Close()， 否则无法释放进程。
            obook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            return true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            // 调用System.Runtime.InteropServices.Marshal.ReleaseComObject（object）方法释放方法中
            //用到的所有的Excel 变量， 记住是所有的。 比如说此方法中的range 对象， 就容易被遗忘。

            System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obook);

            // 很多文章上都说必须调用此方法， 但是我试过没有调用oExcel.Quit() 的情况， 进程也能安全退出，
            //还是保留着吧。
            oExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
            // 垃圾回收是必须的。 测试如果不执行垃圾回收， 无法关闭Excel 进程。
            GC.Collect();
        }
    }

        protected void CreateFolder(string physicsPath, string toFindDirectoryName, string FileNameToCreate)
    {
        string path = "";
        string fullpath = physicsPath + "\\" + toFindDirectoryName + "\\" + FileNameToCreate; //要查找的文件的全部路径
        FindDirectory(physicsPath + "\\", toFindDirectoryName, out path);//用递归的方式去查找文件夹
        if (!string.IsNullOrEmpty(path)) //如果存在，在此文件夹下建立Excel
        {
            CreateExcel(fullpath);
        }
        else
        {
            //没有找到路径,创建新文件夹，在此文件夹下创建Excel
            Directory.CreateDirectory(physicsPath + "\\" + toFindDirectoryName);
            CreateExcel(fullpath);
        }
    }

    /// <summary>
    /// 在指定目录下递归查找子文件夹
    /// </summary>
    /// <param name="bootPath">根文件夹路径</param>
    /// <param name="directoryName">要查找的文件夹名</param>
    private void FindDirectory(string bootPath, string directoryName, out string filePath)
    {
        //在指定目录下递归查找子文件夹
        DirectoryInfo dir = new DirectoryInfo(bootPath);
        filePath = "";
        try
        {
            foreach (DirectoryInfo d in dir.GetDirectories()) //查找子文件夹
            {
                if (d.Name == directoryName) //找到,返回文件夹路径
                {
                    filePath = d.FullName;
                    break;
                }
                FindDirectory(bootPath + d.Name + "\\", directoryName, out filePath); //否则继续查找
            }
        }
        catch (Exception )
        {
            return;
        }

    }
    //在某个路径下生成 Excel
    public void CreateExcel(string fileName)
    {
        Object missing = System.Reflection.Missing.Value;
        //判断要创建的Excel文件是否已经存在
        if (File.Exists(fileName))
        {
            File.Delete(fileName);
        }

        Microsoft.Office.Interop.Excel.Application m_objExcel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbooks m_objWorkBooks = m_objExcel.Workbooks;
        Microsoft.Office.Interop.Excel.Workbook m_objWorkBook = m_objWorkBooks.Add(true);
        Microsoft.Office.Interop.Excel.Sheets m_objWorkSheets = m_objWorkBook.Sheets; ;
        Microsoft.Office.Interop.Excel.Worksheet m_objWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objWorkSheets[1];
        m_objWorkBook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                missing, missing, missing, missing, missing);
        m_objWorkBook.Close(false, missing, missing);
        m_objExcel.Quit();
        //创建一个新的Excel 不需要网页弹出保存
        //Response.ClearContent();
        //Response.ClearHeaders();
        //Response.AppendHeader("Content-Disposition", "attachment;filename=C:\\DorFee\\KQ\\KQ.xls");
        //Response.Buffer = true;
        //Response.ContentType = "application/ms-excel";
        //Response.Flush();
        //Response.Close();
        //System.IO.File.Delete("d:cms.xls");
    }
}