C#操作DateTable导入到Excel简单方法

1.引用这com组件

   Mircosoft.Excel.12.0 Object Libary(自动添加应用Microsoft.Office.Interop.Excel)

2.打开一个空excel 或者加载一个模板

    private Excel.Application m_objExcel = null;
    private Excel.Workbooks m_objBooks = null;
    private Excel._Workbook m_objBook = null;
    private Excel.Sheets m_objSheets = null;
    private Excel._Worksheet m_objSheet = null;
    private Excel.Range m_objRange = null;
    private object m_objOpt = System.Reflection.Missing.Value;

   /// <summary>
    /// 实现Excel应用程序的打开
    /// </summary>
    /// <param name="TemplateFilePath">模板文件物理路径</param> 

   public void OpenExcel(string TemplateFilePath)
    {  

       //创建一个Application对象并使其可见

        m_objExcel = new Excel.Application();
        m_objExcel.Visible = false;
        m_objExcel.DisplayAlerts = false;

         m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
        if (TemplateFilePath==null ||TemplateFilePath=="")
        {

          //新建一个WorkBook

            m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
        }
        else
        {

          //打开模板文件，得到WorkBook对象
            m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
             m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
        }
        m_objSheets = (Excel.Sheets)m_objBook.Worksheets;

     //得到WorkSheet对象
        m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

}

     /// <summary>
        /// 将传入的DataSet数据导出至Excel文件 （弹出保存提示框)
        /// </summary>
        /// <param name="ctl">DataGrid</param>
        ///
        public  void DataSetToExcel(System.Data.DataTable ds)
        {
            int maxRow = ds.Rows.Count;
            Random random = new Random();
            string fileName = DateTime.Now.ToString("yyyyMMddhhmmss")+random .Next (100).ToString () + ".xls";//设置导出文件的名称

            DataView dv = new DataView(ds);//将DataSet转换成DataView
            string fileURL = string.Empty;
            //调用方法将文件写入服务器,并获取全部路径
            fileURL = DataViewToExcelBySheet(dv, fileName);
            //获取路径后从服务器下载文件至本地
            HttpContext curContext = System.Web.HttpContext.Current;
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = System.Text.Encoding.Default;
            curContext.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + fileName));
            curContext.Response.Charset = "";

            curContext.Response.WriteFile(fileURL);
            curContext.Response.Flush();
            curContext.Response.End();
        }

  /// <summary>
        /// 分Sheet导出Excel文件
        /// </summary>
        /// <param name="dv">需导出的DataView</param>
        /// <returns>导出文件的路径</returns>
        private  string DataViewToExcelBySheet(DataView dv, string fileName)
        {
            int sheetRows = 65535;//设置Sheet的行数,此为最大上限,本来是65536,因表头要占去一行
            int sheetCount = (dv.Table.Rows.Count - 1) / sheetRows + 1;//计算Sheet数

            GC.Collect();//垃圾回收

            Application excel;
            _Workbook xBk;
            _Worksheet xSt = null;
            excel = new ApplicationClass();
            xBk = excel.Workbooks.Add(true);

            //定义循环中要使用的变量
            int dvRowStart;
            int dvRowEnd;
            int rowIndex = 0;
            int colIndex = 0;
            //对全部Sheet进行操作
            for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
            {
                //初始化Sheet中的变量
                rowIndex = 1;
                colIndex = 1;
                //计算起始行
                dvRowStart = sheetIndex * sheetRows;
                dvRowEnd = dvRowStart + sheetRows - 1;
                if (dvRowEnd > dv.Table.Rows.Count - 1)
                {
                    dvRowEnd = dv.Table.Rows.Count - 1;
                }
                //创建一个Sheet
                if (null == xSt)
                {
                    xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
                }
                else
                {
                    xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
                }
                //设置Sheet的名称
                xSt.Name = sheetPrefixName;
                if (sheetCount > 1)
                {
                    xSt.Name += ((int)(sheetIndex + 1)).ToString();
                }
                //取得标题
                foreach (DataColumn col in dv.Table.Columns)
                {
                    //设置标题格式
                    xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置标题居中对齐
                    xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;//设置标题为粗体
                    //填值，并进行下一列
                    excel.Cells[rowIndex, colIndex++] = col.ColumnName;
                }
                //取得表格中数量
                int drvIndex;
                for (drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
                {
                    DataRowView row = dv[drvIndex];
                    //新起一行，当前单元格移至行首
                    rowIndex++;
                    colIndex = 1;
                    foreach (DataColumn col in dv.Table.Columns)
                    {
                        if (col.DataType == System.Type.GetType("System.DateTime"))
                        {
                            excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        }
                        else if (col.DataType == System.Type.GetType("System.String"))
                        {
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                        }
                        else
                        {
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                        colIndex++;
                    }
                }
                //使用最佳宽度
                Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex - 1]);
                allDataWithTitleRange.Select();
                allDataWithTitleRange.Columns.AutoFit();
                allDataWithTitleRange.Borders.LineStyle = 1;//将导出Excel加上边框
            }
            //设置导出文件在服务器上的文件夹
            string exportDir = "~/ExcelFile/";//注意:该文件夹您须事先在服务器上建好才行
            //设置文件在服务器上的路径
            string absFileName = HttpContext.Current.Server.MapPath(System.IO.Path.Combine(exportDir, fileName));
            xBk.SaveCopyAs(absFileName);
            xBk.Close(false, null, null);
            excel.Quit();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);

            xBk = null;
            excel = null;
            xSt = null;
            GC.Collect();
            //返回写入服务器Excel文件的路径
            return absFileName;
        }

  /// <summary>
    /// 将图片插入到指定的单元格位置，并设置图片的宽度和高度。
    /// 注意：图片必须是绝对物理路径
    /// </summary>
    /// <param name="RangeName">单元格名称，例如：B4</param>
    /// <param name="PicturePath">要插入图片的绝对路径。</param>
    /// <param name="PictuteWidth">插入后，图片在Excel中显示的宽度。</param>
    /// <param name="PictureHeight">插入后，图片在Excel中显示的高度。</param>
    public void InsertPicture(string RangeName, string PicturePath, float PictuteWidth, float PictureHeight)
    {
        m_objRange = m_objSheet.get_Range(RangeName, m_objOpt);
        m_objRange.Select();
        float PicLeft, PicTop;
        PicLeft = Convert.ToSingle(m_objRange.Left);
        PicTop = Convert.ToSingle(m_objRange.Top);
        //参数含义：
        //图片路径
        //是否链接到文件
        //图片插入时是否随文档一起保存
        //图片在文档中的坐标位置（单位：points）
        //图片显示的宽度和高度（单位：points）
        m_objSheet.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse,
         Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight);
    }

    /// <summary>
    /// 将DataTable插入Excel中.
    /// </summary>
    /// <param name="dt">插入的表</param>
    public void DataTableToExcel( DataTable dt)
    {
        int rowIndex = 1;
        int colIndex = 0;

        foreach (DataColumn col in dt.Columns)
        {
            colIndex++;
            m_objExcel.Cells[rowIndex, colIndex] = col.ColumnName;
        }

        foreach (DataRow row in dt.Rows)
        {
            rowIndex++;
            for (int i = 1; i <= dt.Columns.Count; i++)
            {
                m_objExcel.Cells[rowIndex, i] = row[i - 1];
            }
        }
    }

/// <summary>
    /// 将Excel文件保存到指定的目录，目录必须事先存在，文件名称不一定要存在。
    /// </summary>
    /// <param name="OutputFilePath">要保存成的文件的全路径</param>
    public void SaveFile(string OutputFilePath)
    {
         m_objBook.SaveAs(OutputFilePath, m_objOpt, m_objOpt,
         m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
         m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
         m_objBook.Close(false, m_objOpt, m_objOpt);
         m_objExcel.Quit();
          }

    /// <summary>
    /// 释放所引用的COM对象。

     /// </summary>
    public void Dispose()
    {
        ReleaseObj(m_objSheets);
        ReleaseObj(m_objBook);
        ReleaseObj(m_objBooks);
        ReleaseObj(m_objExcel);
        System.GC.Collect();
        System.GC.WaitForPendingFinalizers();
    }

 /// <summary>
    /// 释放对象，内部调用
    /// </summary>
    /// <param name="o"></param>
    private void ReleaseObj(object o)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
        }
        catch { }
        finally { o = null; }
    }

    /// <summary>
    /// 下载页面调用的方法

    /// </summary>
    /// <param name="strFile">路径 </param>
    public void DownloadFile(string strFile)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ContentType = "application/octet-stream";
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode  (Path.GetFileName(strFile).Trim()) + "\"");
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.WriteFile(strFile);
        HttpContext.Current.Response.End();
    }