C#（com组件）操作Excel读写

昵称10504424 2013-06-13

展开全文

我们在项目中常常会涉及到对Excel的读写操作，而市面上也有很多的操作Excel方式。经过整理常用的是以下几种方式：

　　1. 　COM组件操作Excel读写

　　2. 　Ado.net方式操作Excel读写

　　3. 　开源的第三方组件NPOI

　　4.　Open XML 方式读写Excel

　　上述3种方式其实各有优缺点~，今天这里是要讲述的有关com组件的读写Excel操作。

　　这段时间准备用这3种方式分别体验下读写操作Excel的乐趣，要使用Com组件的方式操作Excel，我们要做的异步首先是要引入相应的Office的dll当然也可以默认从Office的安装目录下找到

添加之，因为所有的Com组件操作都需要添加该dll才能进行操作。要注意的一点如果你在开发过程中出现了下面的这种异常

　　此时你必须将点开刚刚引用的DLL属性，将其中的Embed Interop Types修改为False, 这样在创建ExcelApplication类的时候就不会编译不通过了。

　　具体原因可见http://blogs./b/mshneer/archive/2009/12/07/interop-type-xxx-cannot-be-embedded-use-the-applicable-interface-instead.aspx

　　进入正题，简单来说对于我们一般的操作Excel文档读写，我们只需要了解4个对象，Application, Workbook, Worksheet, Range

　　1. Application对象: 就是Excel实例，不仅仅是一个Excel文件，而是整体的Excel程序。

　　2. WorkBook就是实质意义上的某个Excel文件，你可以进行保存操作等等。

　　3. Worksheet是传统意义上的某个工作簿类型。

　　4. Range是我们使用的最多的，你可以理解成一个区域块，例如常见的"A2:B5"这种表示方式。

　　更详细的的概念可以参考微软的官方文档 http://msdn.microsoft.com/zh-cn/library/wss56bz7(v=vs.80).aspx

　　我们可以封装一个常用的ExcelUtil类来帮助我们通过Com组件的方式操作Excel。

　　首先，我们就可以动手来从一个Excel的sheet中读取我们需要的内容，我们可以把控件的引用名先弄短一点，例如这样：　　

using Excel = Microsoft.Office.Interop.Excel;

　　声明下常用的私有变量

private Excel.Application excelApplication = null;
private Excel.Workbooks excelWorkbooks;
private Excel.Workbook excelWorkbook;
private Excel.Worksheet excelWorksheet;
private Excel.Range excelRange;
private int activeSheetIndex;
private string saveFilePath = string.Empty;
private string openFilePath = string.Empty;

　常用的公共属性

/// <summary>
/// 获取或设置当前有效活动Sheet索引
/// </summary>
public int ActiveSheetIndex
{
    get { return activeSheetIndex; }
    set { activeSheetIndex = value; }
}

/// <summary>
/// 获取或设置
/// </summary>
public Excel.Workbook Workbook
{
    get { return excelWorkbook; }
    set { excelWorkbook = value; }
}

/// <summary>
/// 获取设置当前Excel含有的Worksheet数
/// </summary>
public int WorksheetsCount
{
    get
    {
        if (excelWorkbook == null) return 0;
        if (excelWorkbook.Worksheets == null) return 0;
        return excelWorkbook.Worksheets.Count;
    }
}

传入Excel文件路径打开一个Excel文件

public bool OpenExcelApplication(string path)
        {
            if (excelApplication != null) CloseExcelApplication();
            if (string.IsNullOrEmpty(path)) throw new Exception("请选择一个文件！");

            if (!File.Exists(path))
                throw new Exception(path + "文件不存在！");
            else
            {
                try
                {
                    //点击引用到的第三方组件然后属性中将Embed Interop Types置为False, ActiveSheet.UsedRange.Rows.Count
                    excelApplication = new Excel.ApplicationClass();
                    excelWorkbooks = excelApplication.Workbooks;
                    excelWorkbook = excelWorkbooks.Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Workbook;
                    excelWorksheet = excelWorkbook.Worksheets[activeSheetIndex] as Excel.Worksheet;
                    excelApplication.Visible = false;

                    return true;
                }
                catch (Exception ex)
                {
                    CloseExcelApplication();
                    throw new Exception(string.Format("（1）程序中没有安装Excel程序。（2）或没有安装Excel所需要支持的.NetFramework\n详细信息：{0}", ex.Message));
                }
            }
        }

每次操作完Excel都要对Excel中所使用到的对象进项释放资源的操作如下

/// <summary>
/// 关闭Excel程序
/// </summary>
public void CloseExcelApplication()
{
    try
    {
        Save();
        excelWorksheet = null;
        excelWorkbook = null;
        excelWorkbooks = null;
        excelRange = null;
        if (excelApplication != null)
        {
            excelApplication.Workbooks.Close();
            excelApplication.Quit();
            excelApplication = null;
        }
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
    finally
    {
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}

读取一个Sheet的时候我们一般先要获取到相应的当前的Worksheet对象，然后从这个Worksheet对象中获取到相应的Range对象例如从A1:C7等

主要的代码如下，获取到当前有效的ActiveIndex，对应的worksheet对象

Excel.Worksheet activeWorksheet = excelWorkbook.Worksheets[activeIndex] as Excel.Worksheet;
if (activeWorksheet == null)
    activeWorksheet = new Excel.Worksheet();

//每次获取到最新的活动sheet时候必须激活
activeWorksheet.Activate();

//获取到当前你需要获取的数据的Range对象
Excel.Range excelRange = activeWorksheet.get_Range("A1", "C3");

这里可能有人会用到说想要知道哪些行列的区域边界里面是有数据的其实这里它自带提供了2种方式可以获得当前有数据的区块Range范围（默认从A1开始）

/*
    假设该表的左上角在单元格 a1,并且注意该表中间没有空行和空列,则: 
    sheets['sheet1'].range['a1'].CurrentRegion.Rows.Count
    sheets['sheet1'].range['a1'].CurrentRegion.Columns.Count
    返回该表的行数,Columns返回该表的列数 
*/
public ExcelRange GetCurrentRegion(string startRange, Excel.Worksheet activeWorksheet)
{
    return new ExcelRange()
    {
        Row = activeWorksheet.Range[startRange.ToUpper()].CurrentRegion.Rows.Count,
        Column = activeWorksheet.Range[startRange.ToUpper()].CurrentRegion.Columns.Count
    };
}

public class ExcelRange
{
    #region 构造方法

    public ExcelRange()
    {
        this.Row = 0;
        this.Column = 0;
    }

    #endregion

    #region 公共属性

    public int Row { get; set; }

    public int Column { get; set; }

    #endregion
}

此处的startRange对象就是默认以左上角的哪个Cell坐标当成起始位置，获得知道当前右下角的最后一个Cell的坐标例如此处的C3

另外一种方法

/*
    可以利用VBA中的SpecialCells函数，返回一个 Range 对象，此对象代表与指定类型及 
    值相匹配的所有单元格，下面为其可用的常数： 

    xlCellTypeAllFormatConditions 任意格式的单元格 
    xlCellTypeAllValidation 具有有效条件的单元格 
    xlCellTypeBlanks 空单元格 
    xlCellTypeComments 包含注释的单元格 
    xlCellTypeConstants 包含常量的单元格 
    xlCellTypeFormulas 包含公式的单元格 
    xlCellTypeLastCell 已用区域的最后一个单元格 /////××××//这个常数在确定范围最有用！ 
    xlCellTypeSameFormatConditions 具有相同格式的单元格 
    xlCellTypeSameValidation 具有相同有效条件的单元格 
    xlCellTypeVisible 所有可见单元格 

    比如我们可以利用这个函数确定最后一个包含数据的单元格： 
    ExcelApp.WorkBooks.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate; 
    lastRow := ExcelApp.ActiveCell.Row; 
    lastCol := ExcelApp.ActiveCell.Column;
 */
public ExcelRange GetCurrentRegion(Excel.Worksheet activeSheet)
{
    activeSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Missing.Value).Activate(); //此处的xlCellTypeLastCell是重点获取到最后一个有下角的坐标。
    Excel.Application excelApp = ((activeSheet.Parent as Excel.Workbook).Parent as Excel.Application).Application;
    return new ExcelRange()
    {
        Row = excelApp.ActiveCell.Row,
        Column = excelApp.ActiveCell.Row
    };
}

这里不建议使用他自带的UsedRange.Row和UsedRange.Column属性是因为它们十分坑爹的把你曾经使用过的元素所在的范围也包含进去了，也就是说如果你曾经在D3写过东西后来又擦掉使用空白的情况下UsedRange属性计算的是原来的范围，而这往往不是我们所要的

接下来接着上面我们获取到Range元素后最重要的一步就是对他进行操作了获取数据的操作了这是获取一个单元格的值的操作

//row,column分别是值cell所在的行和列，默认从1开始

string cellValue = (excelRange.Cells[row, col] as Excel.Range) == null ? string.Empty : (excelRange.Cells[row, col] as Excel.Range).Value;

如果要获取到当前这个Range范围内所有的元素呢我们就需要循环对应的行和咧，如下

string cellValue;
for (int row = 1; row <= excelRange.Rows.Count; row++)
{
    for (int col = 1; col <= excelRange.Columns.Count; col++)
    {
        cellValue = (excelRange.Cells[row, col] as Excel.Range) == null ? string.Empty : (excelRange.Cells[row, col] as Excel.Range).Value;
    }
}

而这里有个很大的弊端就是效率，看了网上很多用COM组件操作的方法说使用COM组件操作读写的效率很低，其实他可以一点也不低。主要因为我们再循环中一次次的操作了Excel的Range对象然后在获取他的Value对象这样十分耗损资源，后来在网上找到了解决方案一句话就疏通了整个案情啊~在很早的一篇小马哥的博客中找到了答案 http://www.cnblogs.com/maweifeng/archive/2005/06/28/182483.html

其实我们的Range对象可以和数组进行很好的互操作性，我们其实每次只需要把当前要获取的Range区域元素赋值给一个二维数组然后对二维数组在进行相应的循环读取或者写入操作，这速度的提升是飞一般的感觉啊~

我用Stopwatch监视了下，写入一个5000行10列的数据用Range的方式读取元素花了94880ms相当于1分30几秒，而转用二维数组的方式后只用4秒，这个差距其实是很大的

所以建议我们真的要使用COM来操作Excel的朋友可以用数组的方式来操作读写

object[,] dataValueRange = new object[rowCount, columnCount];
dataValueRange = excelRange.Value;
for (int row = 1; row <= rowCount; row++)
{
    for (int col = 1; col <= columnCount; col++)
    {
        cellValue = dataValueRange[row, col] == null ? string.Empty : dataValueRange[row, col].ToString();
    }
}