C#开发的Excel操作类

using System;
using System.Diagnostics;


namespace Prolink.EHU.WebGui.classes
{
/// <summary>
/// ExcelOperation 的摘要说明。
/// </summary>
public class ExcelOperation
{
   private object missing = System.Reflection.Missing.Value; // 在引用excel时，有些参数为空，就用它替换
   private Excel.Application excel = new Excel.ApplicationClass(); // 引用excel组件
   private Excel.Workbook workbook    = null; // 工作薄
   private Excel.Worksheet worksheet = null; // 工作表

   /// <summary>
   /// 初始化,并打开工作薄和工作表
   /// </summary>
   /// <param name="fileName">文件名包含路径</param>
   /// <param name="worksheetName">字符型--工作表</param>
   public ExcelOperation(string fileName)
   {
    //
    // TODO: 在此处添加构造函数逻辑
    excel.Visible = false;
    excel.DisplayAlerts = false;
    workbook = excel.Workbooks.Open(fileName, missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);
   }

   /// <summary>
   /// 得到工作表
   /// </summary>
   /// <param name="worksheetName"></param>
   /// <returns></returns>
   public void GetWorkSheet(string worksheetName)
   {
    worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(worksheetName);
   }

   /// <summary>
   /// 得到工作表
   /// </summary>
   /// <param name="worksheetName"></param>
   /// <returns></returns>
   public void GetWorkSheet(int worksheetIndex)
   {
    worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(worksheetIndex);
   }

   public void Close()
   {
    workbook.Close(false, null, null);
    //退出Excel，并且释放调用的COM资源
    excel.Quit();

    //  
    //    System.Runtime.InteropServices.Marshal.ReleaseComObject(Range);   
    //    Range = null;
    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);   
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);   
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);   
    
    worksheet=null;   
    workbook=null;   
    excel=null;   
    
    GC.Collect();
   }

   /// <summary>
   /// 取得工作表名的集合
   /// </summary>
   /// <returns></returns>
   public string[] GetSheetNames()
   {
    string[] names = new string[workbook.Worksheets.Count];
    for (int i = 0; i < names.Length; i++)
    {
     Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item(i+1);
     names[i] = worksheet.Name;
    }
    return names;
   }

   /// <summary>
   /// 根据行号和列号取值
   /// </summary>
   /// <param name="position">如:B6</param>
   /// <returns></returns>
   public object GetCellValue(string position)
   {
    object obj = null;
    try
    {
     obj = worksheet.get_Range(position, missing).Value2;
    }
    catch(Exception exp)
    {
     throw exp;
    }
    return obj;
   }

   /// <summary>
   /// 取出范围内的单元格值
   /// </summary>
   /// <param name="fromPosition"></param>
   /// <param name="toPosition"></param>
   /// <returns></returns>
   public object[] GetCellRangeValue(string fromPosition, string toPosition)
   {
    string[] ranges = GetRange(fromPosition, toPosition);
    object[] obj = new object[ranges.Length];
    for (int i = 0; i < obj.Length; i++)
    {
     obj[i] = GetCellValue(ranges[i]);
    }
    return obj;
   }

   /// <summary>
   /// 根据excel中的两点，取出这两点范围的位置。如"A1,A2"，以逗号分隔
   /// </summary>
   /// <param name="fromPosition"></param>
   /// <param name="toPosition"></param>
   /// <returns></returns>
   private string[] GetRange(string fromPosition, string toPosition)
   {
    int[] _formPosition = GetPosition(fromPosition);
    int[] _toPosition = GetPosition(toPosition);
    if (_formPosition[0] > _toPosition[0])
    {
     int temp = _toPosition[0];
     _toPosition[0] = _formPosition[0];
     _formPosition[0] = temp;
    }
    if (_formPosition[1] > _toPosition[1])
    {
     int temp = _toPosition[1];
     _toPosition[1] = _formPosition[1];
     _formPosition[1] = temp;
    }

    string result = "";
    for (int i = _formPosition[0]; i <= _toPosition[0]; i++)
    {
     for (int j = _formPosition[1]; j <= _toPosition[1]; j++)
     {
      result += ReplaceNumber(i) + j.ToString() + ",";
     }
    }
    if (result.EndsWith(","))
     result = result.Substring(0, result.Length - 1);
    return result.Split(',');
   }

   /// <summary>
   /// 根据行数和列数（列数为字母，并且行号和列号放在一起）取出对应的数字数组
   /// </summary>
   /// <param name="str"></param>
   /// <returns></returns>
   private int[] GetPosition(string str)
   {
    char[] chars = str.ToUpper().ToCharArray();
    int[] position = new int[2];
    string column = "";
    string row = "";
    for (int i = 0; i < chars.Length; i++)
    {
     if ('A' <= chars[i] && chars[i] <= 'Z')
      column += chars[i].ToString();
     if ('0' <= chars[i] && chars[i] <= '9')
      row += chars[i].ToString();
    }
    position[0] = ReplaceString(column);
    position[1] = int.Parse(row);

    return position;
   }

   /// <summary>
   /// 把在excel中的字母列转换成相应的数字
   /// </summary>
   /// <param name="index"></param>
   /// <returns></returns>
   private string ReplaceNumber(int index)
   {
    int i = index;
    int j = 0;
    if (index > 26)
    {
     i = index % 26;
     j = index / 26;
    }
    if (j == 0)
     return ((char)((int)'A' + i - 1)).ToString();
    else
     return ((char)((int)'A' + j - 1)).ToString() + ((char)((int)'A' + i - 1)).ToString();
   }

   /// <summary>
   /// 与ReplaceNumber相反
   /// </summary>
   /// <param name="index"></param>
   /// <returns></returns>
   private int ReplaceString(string str)
   {
    str = str.ToUpper();
    char[] chars = str.ToCharArray();
    int index = 0;
    if (chars.Length == 1)
     index = (int)chars[0] - (int)'A' + 1;
    else if (chars.Length == 2)
    {
     index = (int)chars[1] - (int)'A' + 1;
     index += ((int)chars[0] - (int)'A' + 1) * 26;
    }
    return index;
   }
}
}