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
      
   }
  }