ExcelHelper（Excel和C#、asp.net匯入匯出，通用類）（一）

http://blog.163.com/it_software/blog/static/1238582962010518113421381/

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using System.Collections;

//using cfg = System.Configuration;

namespace GDBI.Common
{
 /// <summary>
 /// 說    明：Excel輸出列印模組
 ///     暫時不提供操作Excel對象樣式方法，樣式可以在Excel模板中設定好
 /// 作    者：lingyun_k
 /// 建立日期：2005－7－12
 /// </summary>
 public class ExcelHelper
 {
  #region 成員變數
  private string templetFile = null;
  private string outputFile = null;
  private object missing = Missing.Value;
  private DateTime beforeTime;   //Excel啟動之前時間
  private DateTime afterTime;    //Excel啟動之後時間
  Excel.Application app;
  Excel.Workbook workBook;
  Excel.Worksheet workSheet;
  Excel.Range range;
  Excel.Range range1;
  Excel.Range range2;
  Excel.TextBox textBox;
  private int sheetCount = 1;   //WorkSheet數量
  private string sheetPrefixName = "頁"; 
  #endregion

  #region 公用屬性
  /// <summary>
  /// WorkSheet首碼名，比如：首碼名為“頁”，那麼WorkSheet名稱依次為“頁-1，頁-2...”
  /// </summary>
  public string SheetPrefixName
  {
   set { this.sheetPrefixName = value; }
  }

  /// <summary>
  /// WorkSheet數量
  /// </summary>
  public int WorkSheetCount
  {
   get { return workBook.Sheets.Count; }
  }

  /// <summary>
  /// Excel模板檔案路徑
  /// </summary>
  public string TempletFilePath
  {
   set { this.templetFile = value; }
  }

  /// <summary>
  /// 輸出Excel檔案路徑
  /// </summary>
  public string OutputFilePath
  {
   set { this.outputFile = value; }
  }
  #endregion

  #region 公用方法

  #region ExcelHelper
  /// <summary>
  /// 建構函式，將一個已有Excel活頁簿作為模板，並指定輸出路徑
  /// </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;

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

   //開啟模板檔案，得到WorkBook對象
   workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
    missing,missing,missing,missing,missing,missing,missing);

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

  }

  /// <summary>
  /// 建構函式，開啟一個已有的活頁簿
  /// </summary>
  /// <param name="fileName">Excel檔案名稱</param>
  public ExcelHelper(string fileName)
  {
   if(!File.Exists(fileName))
    throw new Exception("指定路徑的Excel檔案不存在！");

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

   //開啟一個WorkBook
   workBook = app.Workbooks.Open(fileName,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing);

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

  }

  /// <summary>
  /// 建構函式，建立一個活頁簿
  /// </summary>
  public ExcelHelper()
  {
   //建立一個Application對象並使其可見
   beforeTime = DateTime.Now;
   app = new Excel.ApplicationClass();
   app.Visible = true;
   afterTime = DateTime.Now;

   //建立一個WorkBook
   workBook = app.Workbooks.Add(Type.Missing);

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

  }
  #endregion

  #region Data Export Methods

  /// <summary>
  /// 將DataTable資料寫入Excel檔案（自動分頁）
  /// </summary>
  /// <param name="dt">DataTable</param>
  /// <param name="rows">每個WorkSheet寫入多少行資料</param>
  /// <param name="top">表格式資料起始行索引</param>
  /// <param name="left">表格式資料起始列索引</param>
  public void DataTableToExcel(DataTable dt,int rows,int top,int left)
  {
   int rowCount = dt.Rows.Count;  //DataTable行數
   int colCount = dt.Columns.Count; //DataTable列數
   sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet個數
//   StringBuilder sb;
    
   //複製sheetCount-1個WorkSheet對象
   for(int i=1;i<sheetCount;i++)
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
    workSheet.Copy(missing,workBook.Worksheets[i]);
   }
  
   for(int i=1;i<=sheetCount;i++)
   {
    int startRow = (i - 1) * rows;  //記錄起始行索引
    int endRow = i * rows;   //記錄結束行索引
  
    //若是最後一個WorkSheet，那麼記錄結束行索引為源DataTable行數
    if(i == sheetCount)
     endRow = rowCount;
  
    //擷取要寫入資料的WorkSheet對象，並重新命名
    workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
    workSheet.Name = sheetPrefixName + "-" + i.ToString();
  
    //將dt中的資料寫入WorkSheet
//    for(int j=0;j<endRow-startRow;j++)
//    {
//     for(int k=0;k<colCount;k++)
//     {
//      workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
//     }
//    }

    //利用二維數組批量寫入
    int row = endRow-startRow;
    string[,] ss = new string[row,colCount];

    for(int j=0;j<row;j++)
    {
     for(int k=0;k<colCount;k++)
     {
      ss[j,k] = dt.Rows[startRow + j][k].ToString();
     }
    }

    range = (Excel.Range)workSheet.Cells[top,left];
    range = range.get_Resize(row,colCount);
    range.Value = ss;

    #region 利用Windwo粘貼板批量拷貝資料（在Web下面行不通）
    /*sb = new StringBuilder();

    for(int j=0;j<endRow-startRow;j++)
    {
     for(int k=0;k<colCount;k++)
     {
      sb.Append( dt.Rows[startRow + j][k].ToString() );
      sb.Append("\t");
     }

     sb.Append("\n");
    }

    System.Windows.Forms.Clipboard.SetDataObject(sb.ToString());

    range = (Excel.Range)workSheet.Cells[top,left];
    workSheet.Paste(range,false);*/
    #endregion
      
   }
  }