C#套用模板輸出Excel，并對數據進行分頁(收集)

文章分類:.net編程
using System;
using System.IO;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using cfg = System.Configuration;
using Excel;

namespace ExcelHelperTest
{
/**/ ///   <summary>
/// 功能說明：套用模板輸出Excel，并對數據進行分頁
/// 作    者：Lingyun_k
/// 創建日期：2005-7-12
///   </summary>
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(DataTable dt, int rows, int top, int left, string sheetPrefixName)
      {
        int rowCount = dt.Rows.Count;         // 源DataTable行數
         int colCount = dt.Columns.Count;     // 源DataTable列數
         int sheetCount =   this .GetSheetCount(rowCount,rows);     // WorkSheet個數
        DateTime beforeTime;  
       DateTime afterTime;
     
        if (sheetPrefixName ==   null   || sheetPrefixName.Trim() ==   "" )
           sheetPrefixName =   " Sheet " ;

        // 創建一個Application對象并使其可見
        beforeTime = DateTime.Now;
       Excel.Application app =   new Excel.ApplicationClass();
       app.Visible =   true ;
       afterTime = DateTime.Now;

        // 打開模板文件，得到WorkBook對象
        Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
                           missing,missing,missing,missing,missing,missing,missing);

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

        // 復制sheetCount-1個WorkSheet對象
         for ( int i = 1 ;i < sheetCount;i ++ )
          {
           ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
       }

         將源DataTable數據寫入Excel #region 將源DataTable數據寫入Excel
        for ( int i = 1 ;i <= sheetCount;i ++ )
          {
            int startRow = (i -   1 ) * rows;         // 記錄起始行索引
             int endRow = i * rows;             // 記錄結束行索引

            // 若是最后一個WorkSheet，那么記錄結束行索引為源DataTable行數
             if (i == sheetCount)
               endRow = rowCount;

            // 獲取要寫入數據的WorkSheet對象，并重命名
            Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
           sheet.Name = sheetPrefixName +   " - "   + i.ToString();

            // 將dt中的數據寫入WorkSheet
             for ( int j = 0 ;j < endRow - startRow;j ++ )
              {
                for ( int k = 0 ;k < colCount;k ++ )
                  {
                   sheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
               }
           }

            // 寫文本框數據
            Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes( " txtAuthor " );
           Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes( " txtDate " );
           Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes( " txtVersion " );

           txtAuthor.Text =   " KLY.NET的Blog " ;
           txtDate.Text = DateTime.Now.ToShortDateString();
           txtVersion.Text =   " 1.0.0.0 " ;
       }
        #endregion

        // 輸出Excel文件并退出
         try
          {
           workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,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();
               }
           }
       }
     
   }

 
     /**/ ///   <summary>
    /// 獲取WorkSheet數量
     ///   </summary>
    ///   <param name="rowCount"> 記錄總行數 </param>
    ///   <param name="rows"> 每WorkSheet行數 </param>
     private   int GetSheetCount( int rowCount, int rows)
      {
        int n = rowCount % rows;         // 余數

        if (n ==   0 )
            return rowCount / rows;
        else
            return Convert.ToInt32(rowCount / rows) +   1 ;
   }

     /**/ ///   <summary>
    /// 將二維數組數據寫入Excel文件（套用模板并分頁）
    ///   </summary>
    ///   <param name="arr"> 二維數組 </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 ArrayToExcel( string [,] arr, int rows, int top, int left, string sheetPrefixName)
      {
        int rowCount = arr.GetLength( 0 );         // 二維數組行數（一維長度）
         int colCount = arr.GetLength( 1 );     // 二維數據列數（二維長度）
         int sheetCount =   this .GetSheetCount(rowCount,rows);     // WorkSheet個數
        DateTime beforeTime;  
       DateTime afterTime;
     
        if (sheetPrefixName ==   null   || sheetPrefixName.Trim() ==   "" )
           sheetPrefixName =   " Sheet " ;

        // 創建一個Application對象并使其可見
        beforeTime = DateTime.Now;
       Excel.Application app =   new Excel.ApplicationClass();
       app.Visible =   true ;
       afterTime = DateTime.Now;

        // 打開模板文件，得到WorkBook對象
        Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
           missing,missing,missing,missing,missing,missing,missing);

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

        // 復制sheetCount-1個WorkSheet對象
         for ( int i = 1 ;i < sheetCount;i ++ )
          {
           ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
       }

         將二維數組數據寫入Excel #region 將二維數組數據寫入Excel
        for ( int i = 1 ;i <= sheetCount;i ++ )
          {
            int startRow = (i -   1 ) * rows;         // 記錄起始行索引
             int endRow = i * rows;             // 記錄結束行索引

            // 若是最后一個WorkSheet，那么記錄結束行索引為源DataTable行數
             if (i == sheetCount)
               endRow = rowCount;

            // 獲取要寫入數據的WorkSheet對象，并重命名
            Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
           sheet.Name = sheetPrefixName +   " - "   + i.ToString();

            // 將二維數組中的數據寫入WorkSheet
             for ( int j = 0 ;j < endRow - startRow;j ++ )
              {
                for ( int k = 0 ;k < colCount;k ++ )
                  {
                   sheet.Cells[top + j,left + k] = arr[startRow + j,k];
               }
           }

           Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes( " txtAuthor " );
           Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes( " txtDate " );
           Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes( " txtVersion " );

           txtAuthor.Text =   " KLY.NET的Blog " ;
           txtDate.Text = DateTime.Now.ToShortDateString();
           txtVersion.Text =   " 1.0.0.0 " ;
       }
        #endregion

        // 輸出Excel文件并退出
         try
          {
           workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,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();
              }
           }
       }
     
   }
}
}