导出到excel 实例

public class ExcelHelper
    {
        protected   string  templetFile  =   null ;
        protected   string  outputFile  =   null ;
        protected   object  missing  =  Missing.Value;

        ///   <summary>
        ///  构造函数，需指定模板文件和输出文件完整路径
        ///   </summary>
        ///   <param name="templetFilePath"> Excel模板文件路径 </param>
        ///   <param name="outputFilePath"> 输出Excel文件路径 </param>
        public ExcelHelper(string templetFilePath, string outputFilePath)
        {
            if (templetFilePath == null)
                throw new Exception(" Excel模板文件路径不能为空！ ");

            if (outputFilePath == null)
                throw new Exception(" 输出Excel文件路径不能为空！ ");

            if (!File.Exists(templetFilePath))
                throw new Exception(" 指定路径的Excel模板文件不存在！ ");

            this.templetFile = templetFilePath;
            this.outputFile = outputFilePath;

        }

        ///   <summary>
       ///  将DataTable数据写入Excel文件（套用模板并分页）
       ///   </summary>
       ///   <param name="dt"> DataTable </param>
       ///   <param name="rows"> 每个WorkSheet写入多少行数据 </param>
       ///   <param name="top"> 行索引 </param>
       ///   <param name="left"> 列索引 </param>
       ///   <param name="sheetPrefixName"> WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2” </param>
        public   void  DataTableToExcel(System.Data.DataTable dt)
        {
            int  rowCount  =  dt.Rows.Count;         // 源DataTable行数
            int  colCount  =  dt.Columns.Count;     // 源DataTable列数
            int  sheetCount  =   1;     // WorkSheet个数
            DateTime beforeTime;  
            DateTime afterTime;

            // 创建一个Application对象并使其可见
            beforeTime  =  DateTime.Now;
            Excel.Application app  =   new  Excel.ApplicationClass();
            app.Visible  =   false ;
            afterTime  =  DateTime.Now;
            // 打开模板文件，得到WorkBook对象
            Excel.Workbook workBook  =  app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
                missing, missing, missing, missing, missing, missing, missing, missing, missing);

            // 得到WorkSheet对象
            Excel.Worksheet workSheet  =  (Excel.Worksheet)workBook.Sheets.get_Item(1);

            #region  将源DataTable数据写入Excel

            for ( int  i = 1 ;i <= sheetCount;i ++ )
            {
               // 获取要写入数据的WorkSheet对象，并重命名
               Excel.Worksheet sheet  =  (Excel.Worksheet)workBook.Worksheets.get_Item(i);
               sheet.Name  =  "sheet"  +   i.ToString();

               sheet.Cells[1, 3] = DateTime.Now.ToString("yyyyMMdd");

               for (int j = 0; j < dt.Rows.Count; j++)
               {
                   sheet.Cells[3 + j, 2] = dt.Rows[j]["BankAccountName"].ToString();
                   sheet.Cells[3 + j, 3] = dt.Rows[j]["BankAccount"].ToString();
                   sheet.Cells[3 + j, 4] = dt.Rows[j]["exchange"].ToString();
                   sheet.Cells[3 + j, 5] = dt.Rows[j]["I64UserID"].ToString();
                   if (j < dt.Rows.Count-1)
                   {
                       Microsoft.Office.Interop.Excel.Range rang1 = sheet.get_Range("A" + 3, "F" + 3);
                       rang1.Copy(sheet.get_Range("A" + (4 + j), "F" + (4 + j)));                      
                   }
               }

            }
            #endregion

            // 输出Excel文件并退出
            try
            {
                workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
                workBook.Close( null , null , null );
                app.Workbooks.Close();
                app.Application.Quit();
                app.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                workSheet = null ;
                workBook = null ;
                app = null ;

                GC.Collect();
            }
            catch (Exception e)
            {
               throw  e;
            }
            finally
            {
              //Process[] myProcesses;
              //DateTime startTime;
              //myProcesses  =  Process.GetProcessesByName( "Excel" );

              // // 得不到Excel进程ID，暂时只能判断进程启动时间
              //  foreach (Process myProcess  in  myProcesses)
              //  {
              //    startTime  =  myProcess.StartTime;

              //     if (startTime  >  beforeTime  &&  startTime  <  afterTime)
              //      {
              //        myProcess.Kill();
              //    }
              //}

                if (workSheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                    workSheet = null;
                }
                if (workBook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                    workBook = null;
                }
                if (app != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                    app = null;
                }
                GC.Collect();
          }
              
      }
    }