ExcelHelper

最新推荐文章于 2021-07-17 22:21:20 发布
最新推荐文章于 2021-07-17 22:21:20 发布
阅读量977 收藏 1
点赞数
文章标签： exception string excel 工作 textbox null

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模板中设置好
 /// 作    者：colin
 /// 创建日期：2009－07－03
 /// </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
      
   }
  }


  /// <summary>
  /// 将DataTable数据写入Excel文件（不分页）
  /// </summary>
  /// <param name="dt">DataTable</param>
  /// <param name="top">表格数据起始行索引</param>
  /// <param name="left">表格数据起始列索引</param>
  public void DataTableToExcel(DataTable dt,int top,int left)
  {
   int rowCount = dt.Rows.Count;  //DataTable行数
   int colCount = dt.Columns.Count; //DataTable列数

   //利用二维数组批量写入
   string[,] arr = new string[rowCount,colCount];

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

   range = (Excel.Range)workSheet.Cells[top,left];
   range = range.get_Resize(rowCount,colCount);
   range.Value = arr;
  }


  /// <summary>
  /// 将DataTable数据写入Excel文件（自动分页，并指定要合并的列索引）
  /// </summary>
  /// <param name="dt">DataTable</param>
  /// <param name="rows">每个WorkSheet写入多少行数据</param>
  /// <param name="top">表格数据起始行索引</param>
  /// <param name="left">表格数据起始列索引</param>
  /// <param name="mergeColumnIndex">DataTable中要合并相同行的列索引，从0开始</param>
  public void DataTableToExcel(DataTable dt,int rows,int top,int left,int mergeColumnIndex)
  {
   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;

    //合并相同行
    this.MergeRows(workSheet,left+mergeColumnIndex,top,rows);
      
   }
  }


  /// <summary>
  /// 将二维数组数据写入Excel文件（自动分页）
  /// </summary>
  /// <param name="arr">二维数组</param>
  /// <param name="rows">每个WorkSheet写入多少行数据</param>
  /// <param name="top">行索引</param>
  /// <param name="left">列索引</param>
  public void ArrayToExcel(string[,] arr,int rows,int top,int left)
  {
   int rowCount = arr.GetLength(0);  //二维数组行数（一维长度）
   int colCount = arr.GetLength(1); //二维数据列数（二维长度）
   sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数

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

   //将二维数组数据写入Excel
   for(int i=sheetCount;i>=1;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();

    //将二维数组中的数据写入WorkSheet
//    for(int j=0;j<endRow-startRow;j++)
//    {
//     for(int k=0;k<colCount;k++)
//     {
//      workSheet.Cells[top + j,left + k] = arr[startRow + j,k];
//     }
//    }

    //利用二维数组批量写入
    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] = arr[startRow + j,k];
     }
    }

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


  /// <summary>
  /// 将二维数组数据写入Excel文件（不分页）
  /// </summary>
  /// <param name="arr">二维数组</param>
  /// <param name="top">行索引</param>
  /// <param name="left">列索引</param>
  public void ArrayToExcel(string[,] arr,int top,int left)
  {
   int rowCount = arr.GetLength(0);  //二维数组行数（一维长度）
   int colCount = arr.GetLength(1); //二维数据列数（二维长度）

   range = (Excel.Range)workSheet.Cells[top,left];
   range = range.get_Resize(rowCount,colCount);
   range.FormulaArray = arr;
   
  }//end ArrayToExcel

  /// <summary>
  /// 将二维数组数据写入Excel文件（不分页）
  /// </summary>
  /// <param name="arr">二维数组</param>
  /// <param name="top">行索引</param>
  /// <param name="left">列索引</param>
  /// <param name="isFormula">填充的数据是否需要计算</param>
  public void ArrayToExcel(string[,] arr,int top,int left,bool isFormula)
  {
   int rowCount = arr.GetLength(0);  //二维数组行数（一维长度）
   int colCount = arr.GetLength(1); //二维数据列数（二维长度）

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

   //注意：使用range.FormulaArray写合并的单元格会出问题
   if(isFormula)
    range.FormulaArray = arr;
   else
    range.Value = arr;
   
  }//end ArrayToExcel

  /// <summary>
  /// 将二维数组数据写入Excel文件（不分页），合并指定列的相同行
  /// </summary>
  /// <param name="arr">二维数组</param>
  /// <param name="top">行索引</param>
  /// <param name="left">列索引</param>
  /// <param name="isFormula">填充的数据是否需要计算</param>
  /// <param name="mergeColumnIndex">需要合并行的列索引</param>
  public void ArrayToExcel(string[,] arr,int top,int left,bool isFormula,int mergeColumnIndex)
  {
   int rowCount = arr.GetLength(0);  //二维数组行数（一维长度）
   int colCount = arr.GetLength(1); //二维数据列数（二维长度）

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

   //注意：使用range.FormulaArray写合并的单元格会出问题
   if(isFormula)
    range.FormulaArray = arr;
   else
    range.Value = arr;

   this.MergeRows(workSheet,mergeColumnIndex,top,rowCount);
   
  }//end ArrayToExcel

  /// <summary>
  /// 将二维数组数据写入Excel文件（不分页）
  /// </summary>
  /// <param name="sheetIndex">工作表索引</param>
  /// <param name="arr">二维数组</param>
  /// <param name="top">行索引</param>
  /// <param name="left">列索引</param>
  public void ArrayToExcel(int sheetIndex,string[,] arr,int top,int left)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   // 改变当前工作表
   this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);

   int rowCount = arr.GetLength(0);  //二维数组行数（一维长度）
   int colCount = arr.GetLength(1); //二维数据列数（二维长度）

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

   range.Value2 = arr;
   
  }//end ArrayToExcel

  /// <summary>
  /// 将二维数组数据写入Excel文件（自动分页，并指定要合并的列索引）
  /// </summary>
  /// <param name="arr">二维数组</param>
  /// <param name="rows">每个WorkSheet写入多少行数据</param>
  /// <param name="top">行索引</param>
  /// <param name="left">列索引</param>
  /// <param name="mergeColumnIndex">数组的二维索引，相当于DataTable的列索引，索引从0开始</param>
  public void ArrayToExcel(string[,] arr,int rows,int top,int left,int mergeColumnIndex)
  {
   int rowCount = arr.GetLength(0);  //二维数组行数（一维长度）
   int colCount = arr.GetLength(1); //二维数据列数（二维长度）
   sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数

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

   //将二维数组数据写入Excel
   for(int i=sheetCount;i>=1;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();

    //将二维数组中的数据写入WorkSheet
    for(int j=0;j<endRow-startRow;j++)
    {
     for(int k=0;k<colCount;k++)
     {
      workSheet.Cells[top + j,left + k] = arr[startRow + j,k];
     }
    }

    //利用二维数组批量写入
    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] = arr[startRow + j,k];
     }
    }

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

    //合并相同行
    this.MergeRows(workSheet,left+mergeColumnIndex,top,rows);
   }
   
  }//end ArrayToExcel
  #endregion

  #region WorkSheet Methods

  /// <summary>
  /// 改变当前工作表
  /// </summary>
  /// <param name="sheetIndex">工作表索引</param>
  public void ChangeCurrentWorkSheet(int sheetIndex)
  {
   //若指定工作表索引超出范围，则不改变当前工作表
   if(sheetIndex < 1)
    return;

   if(sheetIndex > this.WorkSheetCount)
    return;

   this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex);
  }
  /// <summary>
  /// 隐藏指定名称的工作表
  /// </summary>
  /// <param name="sheetName">工作表名称</param>
  public void HiddenWorkSheet(string sheetName)
  {
   try
   {
    Excel.Worksheet sheet = null;

    for(int i=1;i<=this.WorkSheetCount;i++)
    {
     workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
     
     if(workSheet.Name == sheetName)
      sheet = workSheet;
    }

    if(sheet != null)
     sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
    else
    {
     this.KillExcelProcess();
     throw new Exception("名称为/"" + sheetName + "/"的工作表不存在");
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 隐藏指定索引的工作表
  /// </summary>
  /// <param name="sheetIndex"></param>
  public void HiddenWorkSheet(int sheetIndex)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   try
   {
    Excel.Worksheet sheet = null;
    sheet = (Excel.Worksheet)workBook.Sheets.get_Item(sheetIndex);

    sheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }


  /// <summary>
  /// 在指定名称的工作表后面拷贝指定个数的该工作表的副本，并重命名
  /// </summary>
  /// <param name="sheetName">工作表名称</param>
  /// <param name="sheetCount">工作表个数</param>
  public void CopyWorkSheets(string sheetName,int sheetCount)
  {
   try
   {
    Excel.Worksheet sheet = null;
    int sheetIndex = 0;

    for(int i=1;i<=this.WorkSheetCount;i++)
    {
     workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
     
     if(workSheet.Name == sheetName)
     {
      sheet = workSheet;
      sheetIndex = workSheet.Index;
     }
    }

    if(sheet != null)
    {
     for(int i=sheetCount;i>=1;i--)
     {
      sheet.Copy(this.missing,sheet);
     }

     //重命名
     for(int i=sheetIndex;i<=sheetIndex+sheetCount;i++)
     {
      workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
      workSheet.Name = sheetName + "-" + Convert.ToString(i - sheetIndex + 1);
     }
    }
    else
    {
     this.KillExcelProcess();
     throw new Exception("名称为/"" + sheetName + "/"的工作表不存在");
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 将一个工作表拷贝到另一个工作表后面，并重命名
  /// </summary>
  /// <param name="srcSheetIndex">拷贝源工作表索引</param>
  /// <param name="aimSheetIndex">参照位置工作表索引，新工作表拷贝在该工作表后面</param>
  /// <param name="newSheetName"></param>
  public void CopyWorkSheet(int srcSheetIndex,int aimSheetIndex,string newSheetName)
  {
   if(srcSheetIndex > this.WorkSheetCount || aimSheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   try
   {
    Excel.Worksheet srcSheet = (Excel.Worksheet)workBook.Sheets.get_Item(srcSheetIndex);
    Excel.Worksheet aimSheet = (Excel.Worksheet)workBook.Sheets.get_Item(aimSheetIndex);
    
    srcSheet.Copy(this.missing,aimSheet);
     
    //重命名
    workSheet = (Excel.Worksheet)aimSheet.Next;  //获取新拷贝的工作表
    workSheet.Name = newSheetName;
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }


  /// <summary>
  /// 根据名称删除工作表
  /// </summary>
  /// <param name="sheetName"></param>
  public void DeleteWorkSheet(string sheetName)
  {
   try
   {
    Excel.Worksheet sheet = null;

    //找到名称位sheetName的工作表
    for(int i=1;i<=this.WorkSheetCount;i++)
    {
     workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
     
     if(workSheet.Name == sheetName)
     {
      sheet = workSheet;
     }
    }

    if(sheet != null)
    {
     sheet.Delete();
    }
    else
    {
     this.KillExcelProcess();
     throw new Exception("名称为/"" + sheetName + "/"的工作表不存在");
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 根据索引删除工作表
  /// </summary>
  /// <param name="sheetIndex"></param>
  public void DeleteWorkSheet(int sheetIndex)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   try
   {
    Excel.Worksheet sheet = null;
    sheet = (Excel.Worksheet)workBook.Sheets.get_Item(sheetIndex);

    sheet.Delete();
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  #endregion

  #region TextBox Methods
  /// <summary>
  /// 向指定文本框写入数据，对每个WorkSheet操作
  /// </summary>
  /// <param name="textboxName">文本框名称</param>
  /// <param name="text">要写入的文本</param>
  public void SetTextBox(string textboxName,string text)
  {
   for(int i=1;i<=this.WorkSheetCount;i++)
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);


    try
    {
     textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
     textBox.Text = text;
    }
    catch
    {
     this.KillExcelProcess();
     throw new Exception("不存在ID为/"" + textboxName + "/"的文本框！");
    }
   }
  }

  /// <summary>
  /// 向指定文本框写入数据，对指定WorkSheet操作
  /// </summary>
  /// <param name="sheetIndex">工作表索引</param>
  /// <param name="textboxName">文本框名称</param>
  /// <param name="text">要写入的文本</param>
  public void SetTextBox(int sheetIndex,string textboxName,string text)
  {
   workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);

   try
   {
    textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName);
    textBox.Text = text;
   }
   catch
   {
    this.KillExcelProcess();
    throw new Exception("不存在ID为/"" + textboxName + "/"的文本框！");
   }
  }

  /// <summary>
  /// 向文本框写入数据，对每个WorkSheet操作
  /// </summary>
  /// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param>
  public void SetTextBoxes(Hashtable ht)
  {
   if(ht.Count == 0) return;

   for(int i=1;i<=this.WorkSheetCount;i++)
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

    foreach(DictionaryEntry dic in ht)
    {
     try
     {
      textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
      textBox.Text = dic.Value.ToString();
     }
     catch
     {
      this.KillExcelProcess();
      throw new Exception("不存在ID为/"" + dic.Key.ToString() + "/"的文本框！");
     }
    }
   }
  }

  /// <summary>
  /// 向文本框写入数据，对指定WorkSheet操作
  /// </summary>
  /// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param>
  public void SetTextBoxes(int sheetIndex,Hashtable ht)
  {
   if(ht.Count == 0) return;

   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);

   foreach(DictionaryEntry dic in ht)
   {
    try
    {
     textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key);
     textBox.Text = dic.Value.ToString();
    }
    catch
    {
     this.KillExcelProcess();
     throw new Exception("不存在ID为/"" + dic.Key.ToString() + "/"的文本框！");
    }
   }
  }
  #endregion

  #region Cell Methods
  /// <summary>
  /// 向单元格写入数据，对当前WorkSheet操作
  /// </summary>
  /// <param name="rowIndex">行索引</param>
  /// <param name="columnIndex">列索引</param>
  /// <param name="text">要写入的文本值</param>
  public void SetCells(int rowIndex,int columnIndex,string text)
  {
   try
   {
    workSheet.Cells[rowIndex,columnIndex] = text;
   }
   catch
   {
    this.KillExcelProcess();
    throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！");
   }
  }

  /// <summary>
  /// 向单元格写入数据，对指定WorkSheet操作
  /// </summary>
  /// <param name="sheetIndex">工作表索引</param>
  /// <param name="rowIndex">行索引</param>
  /// <param name="columnIndex">列索引</param>
  /// <param name="text">要写入的文本值</param>
  public void SetCells(int sheetIndex,int rowIndex,int columnIndex,string text)
  {
   try
   {
    this.ChangeCurrentWorkSheet(sheetIndex); //改变当前工作表为指定工作表
    workSheet.Cells[rowIndex,columnIndex] = text;
   }
   catch
   {
    this.KillExcelProcess();
    throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！");
   }
  }

  /// <summary>
  /// 向单元格写入数据，对每个WorkSheet操作
  /// </summary>
  /// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
  public void SetCells(Hashtable ht)
  {
   int rowIndex;
   int columnIndex;
   string position;

   if(ht.Count == 0) return;

   for(int i=1;i<=this.WorkSheetCount;i++)
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

    foreach(DictionaryEntry dic in ht)
    {
     try
     {
      position = dic.Key.ToString();
      rowIndex = Convert.ToInt32(position.Split(',')[0]);
      columnIndex = Convert.ToInt32(position.Split(',')[1]);
      
      workSheet.Cells[rowIndex,columnIndex] = dic.Value;
     }
     catch
     {
      this.KillExcelProcess();
      throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
     }
    }
   }
  }

  /// <summary>
  /// 向单元格写入数据，对指定WorkSheet操作
  /// </summary>
  /// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
  public void SetCells(int sheetIndex,Hashtable ht)
  {
   int rowIndex;
   int columnIndex;
   string position;

   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   if(ht.Count == 0) return;

   workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);

   foreach(DictionaryEntry dic in ht)
   {
    try
    {
     position = dic.Key.ToString();
     rowIndex = Convert.ToInt32(position.Split(',')[0]);
     columnIndex = Convert.ToInt32(position.Split(',')[1]);
      
     workSheet.Cells[rowIndex,columnIndex] = dic.Value;
    }
    catch
    {
     this.KillExcelProcess();
     throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
    }
   }
  }

  /// <summary>
  /// 设置单元格为可计算的
  /// </summary>
  /// <remarks>
  /// 如果Excel的单元格格式设置为数字，日期或者其他类型时，需要设置这些单元格的FormulaR1C1属性，
  /// 否则写到这些单元格的数据将不会按照预先设定的格式显示
  /// </remarks>
  /// <param name="arr">保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
  public void SetCells(int sheetIndex,string[] arr)
  {
   int rowIndex;
   int columnIndex;
   string position;

   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   if(arr.Length == 0) return;

   workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);

   for(int i=0;i<arr.Length;i++)
   {
    try
    {
     position = arr[i];
     rowIndex = Convert.ToInt32(position.Split(',')[0]);
     columnIndex = Convert.ToInt32(position.Split(',')[1]);

     Excel.Range cell = (Excel.Range)workSheet.Cells[rowIndex,columnIndex];
     cell.FormulaR1C1 = cell.Text;
    }
    catch
    {
     this.KillExcelProcess();
     throw new Exception(string.Format("计算单元格{0}出错！",arr[i]));
    }
   }
  }

  /// <summary>
  /// 向单元格写入数据，对指定WorkSheet操作
  /// </summary>
  /// <param name="ht">Hashtable的键值对保存单元格的位置索引（行索引和列索引用“,”隔开）和数据</param>
  public void SetCells(string sheetName,Hashtable ht)
  {
   int rowIndex;
   int columnIndex;
   string position;
   Excel.Worksheet sheet = null;
   int sheetIndex = 0;

   if(ht.Count == 0) return;

   try
   {
    for(int i=1;i<=this.WorkSheetCount;i++)
    {
     workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
     
     if(workSheet.Name == sheetName)
     {
      sheet = workSheet;
      sheetIndex = workSheet.Index;
     }
    }

    if(sheet != null)
    {
     foreach(DictionaryEntry dic in ht)
     {
      try
      {
       position = dic.Key.ToString();
       rowIndex = Convert.ToInt32(position.Split(',')[0]);
       columnIndex = Convert.ToInt32(position.Split(',')[1]);
      
       sheet.Cells[rowIndex,columnIndex] = dic.Value;
      }
      catch
      {
       this.KillExcelProcess();
       throw new Exception("向单元格[" + dic.Key + "]写数据出错！");
      }
     }
    }
    else
    {
     this.KillExcelProcess();
     throw new Exception("名称为/"" + sheetName + "/"的工作表不存在");
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }


  /// <summary>
  /// 合并单元格，并赋值，对每个WorkSheet操作
  /// </summary>
  /// <param name="beginRowIndex">开始行索引</param>
  /// <param name="beginColumnIndex">开始列索引</param>
  /// <param name="endRowIndex">结束行索引</param>
  /// <param name="endColumnIndex">结束列索引</param>
  /// <param name="text">合并后Range的值</param>
  public void MergeCells(int beginRowIndex,int beginColumnIndex,int endRowIndex,int endColumnIndex,string text)
  {
   for(int i=1;i<=this.WorkSheetCount;i++)
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
    range = workSheet.get_Range(workSheet.Cells[beginRowIndex,beginColumnIndex],workSheet.Cells[endRowIndex,endColumnIndex]);
    
    range.ClearContents();  //先把Range内容清除，合并才不会出错
    range.MergeCells = true;
    range.Value = text;
    range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
    range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
   }
  }

  /// <summary>
  /// 合并单元格，并赋值，对指定WorkSheet操作
  /// </summary>
  /// <param name="sheetIndex">WorkSheet索引</param>
  /// <param name="beginRowIndex">开始行索引</param>
  /// <param name="beginColumnIndex">开始列索引</param>
  /// <param name="endRowIndex">结束行索引</param>
  /// <param name="endColumnIndex">结束列索引</param>
  /// <param name="text">合并后Range的值</param>
  public void MergeCells(int sheetIndex,int beginRowIndex,int beginColumnIndex,int endRowIndex,int endColumnIndex,string text)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
   range = workSheet.get_Range(workSheet.Cells[beginRowIndex,beginColumnIndex],workSheet.Cells[endRowIndex,endColumnIndex]);
    
   range.ClearContents();  //先把Range内容清除，合并才不会出错
   range.MergeCells = true;
   range.Value = text;
   range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
   range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
  }
  #endregion

  #region Row Methods
  /// <summary>
  /// 将指定索引列的数据相同的行合并，对每个WorkSheet操作
  /// </summary>
  /// <param name="columnIndex">列索引</param>
  /// <param name="beginRowIndex">开始行索引</param>
  /// <param name="endRowIndex">结束行索引</param>
  public void MergeRows(int columnIndex,int beginRowIndex,int endRowIndex)
  {
   if(endRowIndex - beginRowIndex < 1)
    return;

   for(int i=1;i<=this.WorkSheetCount;i++)
   {
    int beginIndex = beginRowIndex;
    int count = 0;
    string text1;
    string text2;
    workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);

    for(int j=beginRowIndex;j<=endRowIndex;j++)
    {
     range = (Excel.Range)workSheet.Cells[j,columnIndex];
     text1 = range.Text.ToString();

     range = (Excel.Range)workSheet.Cells[j+1,columnIndex];
     text2 = range.Text.ToString();

     if(text1 == text2)
     {
      ++count;
     }
     else
     {    
      if(count > 0)
      {
       this.MergeCells(workSheet,beginIndex,columnIndex,beginIndex+count,columnIndex,text1);
      }

      beginIndex = j + 1;  //设置开始合并行索引
      count = 0;  //计数器清0
     }

    }

   }
  }


  /// <summary>
  /// 将指定索引列的数据相同的行合并，对指定WorkSheet操作
  /// </summary>
  /// <param name="sheetIndex">WorkSheet索引</param>
  /// <param name="columnIndex">列索引</param>
  /// <param name="beginRowIndex">开始行索引</param>
  /// <param name="endRowIndex">结束行索引</param>
  public void MergeRows(int sheetIndex,int columnIndex,int beginRowIndex,int endRowIndex)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   if(endRowIndex - beginRowIndex < 1)
    return;

   int beginIndex = beginRowIndex;
   int count = 0;
   string text1;
   string text2;
   workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);

   for(int j=beginRowIndex;j<=endRowIndex;j++)
   {
    range = (Excel.Range)workSheet.Cells[j,columnIndex];
    text1 = range.Text.ToString();

    range = (Excel.Range)workSheet.Cells[j+1,columnIndex];
    text2 = range.Text.ToString();

    if(text1 == text2)
    {
     ++count;
    }
    else
    {    
     if(count > 0)
     {
      this.MergeCells(workSheet,beginIndex,columnIndex,beginIndex+count,columnIndex,text1);
     }

     beginIndex = j + 1;  //设置开始合并行索引
     count = 0;  //计数器清0
    }

   }

  }


  /// <summary>
  /// 插行（在指定行上面插入指定数量行）
  /// </summary>
  /// <param name="rowIndex"></param>
  /// <param name="count"></param>
  public void InsertRows(int rowIndex,int count)
  {
   try
   {
    for(int n=1;n<=this.WorkSheetCount;n++)
    {
     workSheet = (Excel.Worksheet)workBook.Worksheets[n];
     range = (Excel.Range)workSheet.Rows[rowIndex,this.missing];

     for(int i=0;i<count;i++)
     {
      range.Insert(Excel.XlDirection.xlDown);
     }
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 插行（在指定WorkSheet指定行上面插入指定数量行）
  /// </summary>
  /// <param name="sheetIndex"></param>
  /// <param name="rowIndex"></param>
  /// <param name="count"></param>
  public void InsertRows(int sheetIndex,int rowIndex,int count)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   try
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
    range = (Excel.Range)workSheet.Rows[rowIndex,this.missing];

    for(int i=0;i<count;i++)
    {
     range.Insert(Excel.XlDirection.xlDown);
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 复制行（在指定行下面复制指定数量行）
  /// </summary>
  /// <param name="rowIndex"></param>
  /// <param name="count"></param>
  public void CopyRows(int rowIndex,int count)
  {
   try
   {
    for(int n=1;n<=this.WorkSheetCount;n++)
    {
     workSheet = (Excel.Worksheet)workBook.Worksheets[n];
     range1 = (Excel.Range)workSheet.Rows[rowIndex,this.missing];

     for(int i=1;i<=count;i++)
     {
      range2 = (Excel.Range)workSheet.Rows[rowIndex + i,this.missing];
      range1.Copy(range2);
     }
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 复制行（在指定WorkSheet指定行下面复制指定数量行）
  /// </summary>
  /// <param name="sheetIndex"></param>
  /// <param name="rowIndex"></param>
  /// <param name="count"></param>
  public void CopyRows(int sheetIndex,int rowIndex,int count)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   try
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
    range1 = (Excel.Range)workSheet.Rows[rowIndex,this.missing];

    for(int i=1;i<=count;i++)
    {
     range2 = (Excel.Range)workSheet.Rows[rowIndex + i,this.missing];
     range1.Copy(range2);
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 删除行
  /// </summary>
  /// <param name="rowIndex"></param>
  /// <param name="count"></param>
  public void DeleteRows(int rowIndex,int count)
  {
   try
   {
    for(int n=1;n<=this.WorkSheetCount;n++)
    {
     workSheet = (Excel.Worksheet)workBook.Worksheets[n];
     range = (Excel.Range)workSheet.Rows[rowIndex,this.missing];

     for(int i=0;i<count;i++)
     {
      range.Delete(Excel.XlDirection.xlDown);
     }
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 删除行
  /// </summary>
  /// <param name="sheetIndex"></param>
  /// <param name="rowIndex"></param>
  /// <param name="count"></param>
  public void DeleteRows(int sheetIndex,int rowIndex,int count)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   try
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
    range = (Excel.Range)workSheet.Rows[rowIndex,this.missing];

    for(int i=0;i<count;i++)
    {
     range.Delete(Excel.XlDirection.xlDown);
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  #endregion

  #region Column Methods

  /// <summary>
  /// 插列（在指定列右边插入指定数量列）
  /// </summary>
  /// <param name="columnIndex"></param>
  /// <param name="count"></param>
  public void InsertColumns(int columnIndex,int count)
  {
   try
   {
    for(int n=1;n<=this.WorkSheetCount;n++)
    {
     workSheet = (Excel.Worksheet)workBook.Worksheets[n];
     range = (Excel.Range)workSheet.Columns[this.missing,columnIndex];

     for(int i=0;i<count;i++)
     {
      range.Insert(Excel.XlDirection.xlDown);
     }
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 插列（在指定WorkSheet指定列右边插入指定数量列）
  /// </summary>
  /// <param name="sheetIndex"></param>
  /// <param name="columnIndex"></param>
  /// <param name="count"></param>
  public void InsertColumns(int sheetIndex,int columnIndex,int count)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   try
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
    range = (Excel.Range)workSheet.Columns[this.missing,columnIndex];

    for(int i=0;i<count;i++)
    {
     range.Insert(Excel.XlDirection.xlDown);
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 复制列（在指定列右边复制指定数量列）
  /// </summary>
  /// <param name="columnIndex"></param>
  /// <param name="count"></param>
  public void CopyColumns(int columnIndex,int count)
  {
   try
   {
    for(int n=1;n<=this.WorkSheetCount;n++)
    {
     workSheet = (Excel.Worksheet)workBook.Worksheets[n];
//     range1 = (Excel.Range)workSheet.Columns[columnIndex,this.missing];
     range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex)+"1",this.IntToLetter(columnIndex)+"10000");

     for(int i=1;i<=count;i++)
     {
//      range2 = (Excel.Range)workSheet.Columns[this.missing,columnIndex + i];
      range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex+i)+"1",this.IntToLetter(columnIndex+i)+"10000");
      range1.Copy(range2);
     }
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 复制列（在指定WorkSheet指定列右边复制指定数量列）
  /// </summary>
  /// <param name="sheetIndex"></param>
  /// <param name="columnIndex"></param>
  /// <param name="count"></param>
  public void CopyColumns(int sheetIndex,int columnIndex,int count)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   try
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
//    range1 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex];
    range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex)+"1",this.IntToLetter(columnIndex)+"10000");

    for(int i=1;i<=count;i++)
    {
//     range2 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex + i];
     range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex+i)+"1",this.IntToLetter(columnIndex+i)+"10000");
     range1.Copy(range2);
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 删除列
  /// </summary>
  /// <param name="columnIndex"></param>
  /// <param name="count"></param>
  public void DeleteColumns(int columnIndex,int count)
  {
   try
   {
    for(int n=1;n<=this.WorkSheetCount;n++)
    {
     workSheet = (Excel.Worksheet)workBook.Worksheets[n];
     range = (Excel.Range)workSheet.Columns[this.missing,columnIndex];

     for(int i=0;i<count;i++)
     {
      range.Delete(Excel.XlDirection.xlDown);
     }
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 删除列
  /// </summary>
  /// <param name="sheetIndex"></param>
  /// <param name="columnIndex"></param>
  /// <param name="count"></param>
  public void DeleteColumns(int sheetIndex,int columnIndex,int count)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   try
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex];
    range = (Excel.Range)workSheet.Columns[this.missing,columnIndex];

    for(int i=0;i<count;i++)
    {
     range.Delete(Excel.XlDirection.xlDown);
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  #endregion

  #region Range Methods

  /// <summary>
  /// 将指定范围区域拷贝到目标区域
  /// </summary>
  /// <param name="sheetIndex">WorkSheet索引</param>
  /// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
  /// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
  /// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
  public void RangeCopy(int sheetIndex,string startCell,string endCell,string targetCell)
  {
   if(sheetIndex > this.WorkSheetCount)
   {
    this.KillExcelProcess();
    throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
   }

   try
   {
    workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
    range1 = workSheet.get_Range(startCell,endCell);
    range2 = workSheet.get_Range(targetCell,this.missing);

    range1.Copy(range2);
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 将指定范围区域拷贝到目标区域
  /// </summary>
  /// <param name="sheetName">WorkSheet名称</param>
  /// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>
  /// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>
  /// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>
  public void RangeCopy(string sheetName,string startCell,string endCell,string targetCell)
  {
   try
   {
    Excel.Worksheet sheet = null;

    for(int i=1;i<=this.WorkSheetCount;i++)
    {
     workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i);
     
     if(workSheet.Name == sheetName)
     {
      sheet = workSheet;
     }
    }

    if(sheet != null)
    {
     for(int i=sheetCount;i>=1;i--)
     {
      range1 = sheet.get_Range(startCell,endCell);
      range2 = sheet.get_Range(targetCell,this.missing);

      range1.Copy(range2);
     }
    }
    else
    {
     this.KillExcelProcess();
     throw new Exception("名称为/"" + sheetName + "/"的工作表不存在");
    }
   }
   catch(Exception e)
   {
    this.KillExcelProcess();
    throw e;
   }
  }

  /// <summary>
  /// 自动填充
  /// </summary>
  public void RangAutoFill()
  {
   Excel.Range rng = workSheet.get_Range("B4", Type.Missing);
   rng.Value2 = "星期一 ";
   rng.AutoFill(workSheet.get_Range("B4","B9"),
    Excel.XlAutoFillType.xlFillWeekdays);

   rng = workSheet.get_Range("C4", Type.Missing);
   rng.Value = "一月";
   rng.AutoFill(workSheet.get_Range("C4","C9"),
    Excel.XlAutoFillType.xlFillMonths);

   rng = workSheet.get_Range("D4",Type.Missing);
   rng.Value2 = "1";
   rng.AutoFill(workSheet.get_Range("D4","D9"),
    Excel.XlAutoFillType.xlFillSeries);

   rng = workSheet.get_Range("E4",Type.Missing);
   rng.Value2 = "3";
   rng = workSheet.get_Range("E5",Type.Missing);
   rng.Value2 = "6";
   rng = workSheet.get_Range("E4","E5");
   rng.AutoFill(workSheet.get_Range("E4","E9"),
    Excel.XlAutoFillType.xlFillSeries);

  }

  /// <summary>
  /// 应用样式
  /// </summary>
  public void ApplyStyle()
  {
   object missingValue = Type.Missing;
   Excel.Range rng = workSheet.get_Range("B3","L23");
   Excel.Style style;

   try
   {
    style = workBook.Styles["NewStyle"];
   }
    // Style doesn't exist yet.
   catch
   {
    style = workBook.Styles.Add("NewStyle", missingValue);
    style.Font.Name = "Verdana";
    style.Font.Size = 12;
    style.Font.Color = 255;
    style.Interior.Color = (200 << 16) | (200 << 8) | 200;
    style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
   }

   rng.Value2 = "'Style Test";
   rng.Style = "NewStyle";
   rng.Columns.AutoFit();
  }

  #endregion

  #region ExcelHelper Kit
  /// <summary>
  /// 将Excel列的字母索引值转换成整数索引值
  /// </summary>
  /// <param name="letter"></param>
  /// <returns></returns>
  public int LetterToInt(string letter)
  {
   int n = 0;
   
   if(letter.Trim().Length == 0)
    throw new Exception("不接受空字符串！");

   if(letter.Length >= 2)
   {
    char c1 = letter.ToCharArray(0,2)[0];
    char c2 = letter.ToCharArray(0,2)[1];

    if(!char.IsLetter(c1) || !char.IsLetter(c2))
    {
     throw new Exception("格式不正确，必须是字母！");
    }

    c1 = char.ToUpper(c1);
    c2 = char.ToUpper(c2);

    int i = Convert.ToInt32(c1) - 64;
    int j = Convert.ToInt32(c2) - 64;

    n = i * 26 + j;
   }
   
   if(letter.Length == 1)
   {
    char c1 = letter.ToCharArray()[0];

    if(!char.IsLetter(c1))
    {
     throw new Exception("格式不正确，必须是字母！");
    }

    c1 = char.ToUpper(c1);

    n = Convert.ToInt32(c1) - 64;
   }

   if(n > 256)
    throw new Exception("索引超出范围，Excel的列索引不能超过256！");
   
   return n;
  }

  /// <summary>
  /// 将Excel列的整数索引值转换为字符索引值
  /// </summary>
  /// <param name="n"></param>
  /// <returns></returns>
  public string IntToLetter(int n)
  {
   if(n > 256)
    throw new Exception("索引超出范围，Excel的列索引不能超过256！");
    
   int i = Convert.ToInt32(n / 26);
   int j = n % 26;

   char c1 = Convert.ToChar( i + 64 );
   char c2 = Convert.ToChar( j + 64 );

   if(n > 26)
    return c1.ToString() + c2.ToString();
   else if(n == 26)
    return "Z";
   else
    return c2.ToString();
  }

  #endregion

  #region Output File(注意：如果目标文件已存在的话会出错)
  /// <summary>
  /// 输出Excel文件并退出
  /// </summary>
  public void OutputExcelFile()
  {
   if(this.outputFile == null)
    throw new Exception("没有指定输出文件路径！");

   try
   {
    workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
   }
   catch(Exception e)
   {
    throw e;
   }
   finally
   {
    this.Dispose();
   }
  }

  /// <summary>
  /// 输出指定格式的文件（支持格式：HTML，CSV，TEXT，EXCEL）
  /// </summary>
  /// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
  public void OutputFile(string format)
  {
   if(this.outputFile == null)
    throw new Exception("没有指定输出文件路径！");

   try
   {
    switch(format)
    {
     case "HTML":
     {
      workBook.SaveAs(outputFile,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
     case "CSV":
     {
      workBook.SaveAs(outputFile,Excel.XlFileFormat.xlCSV,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
     case "TEXT":
     {
      workBook.SaveAs(outputFile,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
//     case "XML":
//     {
//      workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
//       Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
//       Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//      break;
//
//     }
     default:
     {
      workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
    }
   }
   catch(Exception e)
   {
    throw e;
   }
   finally
   {
    this.Dispose();
   }
  }

  /// <summary>
  /// 保存文件
  /// </summary>
  public void SaveFile()
  {
   try
   {
    workBook.Save();   
   }
   catch(Exception e)
   {
    throw e;
   }
   finally
   {
    this.Dispose();
   }
  }

  /// <summary>
  /// 另存文件
  /// </summary>
  public void SaveAsFile()
  {
   if(this.outputFile == null)
    throw new Exception("没有指定输出文件路径！");

   try
   {
    workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
   }
   catch(Exception e)
   {
    throw e;
   }
   finally
   {
    this.Dispose();
   }
  }

  /// <summary>
  /// 将Excel文件另存为指定格式
  /// </summary>
  /// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
  public void SaveAsFile(string format)
  {
   if(this.outputFile == null)
    throw new Exception("没有指定输出文件路径！");

   try
   {
    switch(format)
    {
     case "HTML":
     {
      workBook.SaveAs(outputFile,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
     case "CSV":
     {
      workBook.SaveAs(outputFile,Excel.XlFileFormat.xlCSV,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
     case "TEXT":
     {
      workBook.SaveAs(outputFile,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
//     case "XML":
//     {
//      workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
//       Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
//       Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//      break;
//     }
     default:
     {
      workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
    }
   }
   catch(Exception e)
   {
    throw e;
   }
   finally
   {
    this.Dispose();
   }
  }

  /// <summary>
  /// 另存文件
  /// </summary>
  /// <param name="fileName">文件名</param>
  public void SaveFile(string fileName)
  {
   try
   {
    workBook.SaveAs(fileName,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
   }
   catch(Exception e)
   {
    throw e;
   }
   finally
   {
    this.Dispose();
   }
  }

  /// <summary>
  /// 将Excel文件另存为指定格式
  /// </summary>
  /// <param name="fileName">文件名</param>
  /// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>
  public void SaveAsFile(string fileName,string format)
  {
   try
   {
    switch(format)
    {
     case "HTML":
     {
      workBook.SaveAs(fileName,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
     case "CSV":
     {
      workBook.SaveAs(fileName,Excel.XlFileFormat.xlCSV,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
     case "TEXT":
     {
      workBook.SaveAs(fileName,Excel.XlFileFormat.xlHtml,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
//     case "XML":
//     {
//      workBook.SaveAs(fileName,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
//       Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
//       Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//      break;
//     }
     default:
     {
      workBook.SaveAs(fileName,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);   
      break;
     }
    }
   }
   catch(Exception e)
   {
    throw e;
   }
   finally
   {
    this.Dispose();
   }
  }
  #endregion

  #endregion

  #region 私有方法

  /// <summary>
  /// 合并单元格，并赋值，对指定WorkSheet操作
  /// </summary>
  /// <param name="beginRowIndex">开始行索引</param>
  /// <param name="beginColumnIndex">开始列索引</param>
  /// <param name="endRowIndex">结束行索引</param>
  /// <param name="endColumnIndex">结束列索引</param>
  /// <param name="text">合并后Range的值</param>
  private void MergeCells(Excel.Worksheet sheet,int beginRowIndex,int beginColumnIndex,int endRowIndex,int endColumnIndex,string text)
  {
   if(sheet == null)
    return;

   range = sheet.get_Range(sheet.Cells[beginRowIndex,beginColumnIndex],sheet.Cells[endRowIndex,endColumnIndex]);
    
   range.ClearContents();  //先把Range内容清除，合并才不会出错
   range.MergeCells = true;
   range.Value = text;
   range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
   range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
  }

  /// <summary>
  /// 将指定索引列的数据相同的行合并，对指定WorkSheet操作
  /// </summary>
  /// <param name="columnIndex">要合并的列索引</param>
  /// <param name="beginRowIndex">合并开始行索引</param>
  /// <param name="rows">要合并的行数</param>
  private void MergeRows(Excel.Worksheet sheet,int columnIndex,int beginRowIndex,int rows)
  {
   int beginIndex = beginRowIndex;
   int count = 0;
   string text1;
   string text2;

   if(sheet == null)
    return;

   for(int j=beginRowIndex;j<beginRowIndex+rows;j++)
   {
    range1 = (Excel.Range)sheet.Cells[j,columnIndex];
    range2 = (Excel.Range)sheet.Cells[j+1,columnIndex];
    text1 = range1.Text.ToString();
    text2 = range2.Text.ToString();

    if(text1 == text2)
    {
     ++count;
    }
    else
    {    
     if(count > 0)
     {
      this.MergeCells(sheet,beginIndex,columnIndex,beginIndex+count,columnIndex,text1);
     }

     beginIndex = j + 1;  //设置开始合并行索引
     count = 0;  //计数器清0
    }

   }

  }


  /// <summary>
  /// 计算WorkSheet数量
  /// </summary>
  /// <param name="rowCount">记录总行数</param>
  /// <param name="rows">每WorkSheet行数</param>
  public 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>
  public void KillExcelProcess()
  {
   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();
    }
   }
  }


  private void Dispose()
  {
   workBook.Close(null,null,null);
   app.Workbooks.Close();
   app.Quit();

   if(range != null)
   {
    System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
    range = null;
   }
   if(range1 != null)
   {
    System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
    range1 = null;
   }
   if(range2 != null)
   {
    System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
    range2 = null;
   }
   if(textBox != null)
   {
    System.Runtime.InteropServices.Marshal.ReleaseComObject(textBox);
    textBox = null;
   }
   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();

   this.KillExcelProcess();
   
  }//end Dispose
  #endregion

  
 }//end class
}//end namespace

colinRao
关注
一个Excel的帮助类——ExcelHelper
weixin_33693070的博客
03-14 1020
近日突发奇想，封装一个Excel的帮助类，好让日后做一些Excel操作时方便一点，至少导入导出会方便点吧。不过在封装过程中发现自己太差劲了，问题多多，搞这么百来行代码花了很长时间，于是写篇日志，记录一下这个ExcelHelper，也顺便记录一下封装过程中遇到的一些问题。 整个Helper中包括了读和写两部分，读是利用ADO.NET的OleDB进行读，与查询SQL Server很相像，查询语句是这...
C# Excel辅助ExcelHelper类库
04-20
C# Excel辅助ExcelHelper类库
excelhelper
10-26
ExcelHelper 是一个用于在.NET环境中处理Excel数据的工具，它提供了方便的方法来导入和导出Excel数据，并且能够与DataTable对象进行交互。这个实例源码是开发者为了简化Excel操作而创建的一个小型库，旨在帮助程序员...
ExcelHelper导入导出Excel
08-29
该方法用到了NPOI插件，然后封装成一个只需要配置的Exel插件，便可实现导入导出方法
一个操作Excle非常方便的工具包（NPOI）及ExcelHelper.cs类
09-18
操作Excle非常方便的NPOI工具包。 附带ExcelHelper.cs操作类。 网上的很多类似的都有不少问题。 加载本工具包并配合附带的ExcelHelper.cs操作类，都经过本人亲自调试无误！
Excelhelper
weixin_30279671的博客
09-28 116
using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Data.OleDb;using System.Collections.Specialized;using System.IO;using NPOI.SS.UserModel...
C# ExcelHelper
qq_34677276的博客
07-17 1090
C# ExcelHelper
数据导入到EXCEL(EXCELHelper)
youwenyouwen的专栏
05-11 3955
using System; using System.Data; using System.IO; using System.Windows.Forms; using Microsoft.Office.Interop.Excel; namespace zzz.BusinessSystem {     public static class ExcelHelp     {
NPOI excelhelper
最新发布
07-28
2. 然后，使用ExcelHelper.WriteExcel方法将DataTable中的数据写入到一个Excel文件中。这个方法接受两个参数，第一个参数是要写入的DataTable对象，第二个参数是Excel文件的路径。 3. 接下来，创建一个FileInfo对象...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值