C#操作Excel  Excel对象简介

Excel对象模型  （.Net Perspective）

本文主要针对在Visual Studio中使用C# 开发关于Excel的应用程序

本文的PDF下载地址：C#操作Excel2007.pdf

来源：Understandingthe Excel Object Model from a .NET Developer's Perspective

Excel对象模型中的四大主要对象：

Application    Excel应用程序本身

Workbook     工作表Sheet的集合

Worksheet     一个工作表Sheet

Range             表示一块区域

所有Excel对象请参考MSDN:

ExcelObject Model Reference

----------------------------------------------华丽分割-------------------------------------------------

1.       Application 

首先讨论Application对象,Application对象的成员大概可以分成5大类

 

• l  控制Excel的状态与显示
• l  返回对象
• l  执行Action
• l  控制文件操作(handle file manipulation)
• l  其他

 

1.1 Application成员之：控制Excel的状态与显示

Property	Type	Description
Cursor	XlMousePointer (xlDefault, xlIBeam, xlNorthwestArrow, xlWait)	设置/取得 鼠标指针的Appearence
EditDirectlyInCell	Boolean	Gets or sets the ability to edit cells directly in place. If False, you can only edit cells in the formula bar.
FixedDecimal	Boolean	If True, all numeric values use the FixedDecimalPlacesproperty to determine the number of decimal places; otherwise, FixedDecimalPlaces property is ignored (the default value is False).
FixedDecimalPlaces	Long	Determines the number of decimal places to be used for numeric data if the FixedDecimal property is True.
Interactive	Boolean	Gets or sets the ability of the user to interact with Excel via the keyboard and mouse; if you set this property to False, make absolutely sure you set it back to True in your exception handler. Excel won't reset it for you.
MoveAfterReturn	Boolean	如果为真，表示按下回车键后移动到下一个单元格
MoveAfterReturnDirection	xlDirection (xlDown, xlToLeft, xlToRight, xlUp)	设置按下回车键后，单元格focus的移动方向，（ 仅在MoveAfterReturn 属性为真时有效） 默认移动方向：xlDown.
ScreenUpdating	Boolean	If True, Excel updates its screen after each method call. To save time, and to make your application look more professional,you can turn off the display while your code is running. Make sure you reset this property to True again once you're done. Excel won't reset it for you.
SheetsInNewWorkbook	Long	Gets or sets the number of sheets Excel automatically places in new workbooks
StandardFont	String	Gets or sets the name of the default font in Excel; doesn't take effect until you restart Excel.
StandardFontSize	Long	Gets or sets the size of the default font in Excel; doesn't take effect until you restart Excel.
StartupPath (read-only	String	Returns the complete path of the folder containing the Excel startup add-ins.
TemplatesPath (read-only)	String	Returns the complete path of the folder containing templates; this value represents one of the Windows special folders.

 

上面列出的属性中，比较重要的是：ScreenUpdating

它的含义是：是否在任一函数执行完成后更新Excel的界面显示。如果你在进行大数据量的计算，这一功能尽量关闭，因为这可以提高你的任务执行速度。

ScreenUpdating在C#中的使用方法是：(执行完任务记得重新开启该功能)

 

[csharp] view plain copy print ?

1. // C#   
2. try  
3. {  
4.    ThisApplication.ScreenUpdating = false;  
5.    // Do your work that updates the screen.   
6. }  
7.    
8. finally  
9. {  
10.    ThisApplication.ScreenUpdating = true;  
11. }  
12.    

// C#
try
{
   ThisApplication.ScreenUpdating = false;
   // Do your work that updates the screen.
}
 
finally
{
   ThisApplication.ScreenUpdating = true;
}
 

下面再介绍几个有关属性：

Property	Type	Description
DisplayAlerts	Boolean	If True (the default value), Excel displays warning messages while your code runs, as necessary--when deleting a sheet, for example. Set to False to bypass warnings. Excel acts as if you had selected the default value for each alert.
DisplayFormulaBar	Boolean	If True (the default value), Excel displays the standard formula bar for editing cells; set to False to hide the editing bar.
DisplayFullScreen	Boolean	If True, Excel runs in full-screen mode (which has a different effect from simply maximizing the Excel window); the default value is False.

 

1.2 Application成员之： who return objects

Excel库包含很多类，但是在Visual Studio中提供给开发人员的只有Excel.Application,Workbook等，那么我们怎么获得其他很多有用的类型呢？

      比如，我想获得当前活动的单元格(cell)，怎么办？不用担心，Application对象提供很多属性能够返回其他很多类型的对象的引用。下面具体来看一下：

Property	Type	Description
ActiveCell	Range	Returns a reference to the currently active cell in the active window (the window that's on top). If there's no active window, this property raises an error.
ActiveChart	Chart	Returns a reference to the currently active chart. An embedded chart is only considered active when it's selected or activated.
ActiveSheet	Object	Returns a reference to the active sheet in the active workbook.
ActiveWindow	Window	Returns a reference to the active window (the window that's on top); returns Nothing if there are no active windows.
Charts	Sheets	Returns a collection of Sheet objects (the parent for bothChart and Worksheet objects) containing references to each of the charts in the active workbook.
Selection	Object	Returns the selected object within the application. Might be a Range, a Worksheet, or any other object—also applies to the Window class, in which case the selection is generally a Range object. If no object is currently selected, returns Nothing.
Sheets	Sheets	Returns a collection of Sheet objects containing references to each of the sheets in the active workbook.
Workbooks	Workbooks	Returns a collection of Workbook objects containing references to all the open workbooks.

上面的属性中，Workbooks属性无疑是最常用的。通过这个属性，我们可以打开或者创建一个workbook，下面我们具体看一下该属性的一些行为。

1.    创建workbook

 

[csharp] view plain copy print ?

1. Excel.Workbook wb = ThisApplication.Workbooks.Add(Type.Missing);  

 Excel.Workbook wb = ThisApplication.Workbooks.Add(Type.Missing);

   

2.    关闭workbook

 

[csharp] view plain copy print ?

1. ThisApplication.Workbooks.Close();  

ThisApplication.Workbooks.Close();

3.    打开一个存在的workbook

 

[csharp] view plain copy print ?

1. Excel.Workbook wb = ThisApplication.Workbooks.Open(   
2.     "C:\\YourPath\\Yourworkbook.xls",   
3.     Type.Missing, Type.Missing, Type.Missing, Type.Missing,   
4.        Type.Missing, Type.Missing, Type.Missing, Type.Missing,   
5.     Type.Missing, Type.Missing, Type.Missing, Type.Missing,   
6. Type.Missing, Type.Missing);  

Excel.Workbook wb = ThisApplication.Workbooks.Open( 
    "C:\\YourPath\\Yourworkbook.xls", 
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
       Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
Type.Missing, Type.Missing);

4.    用Excel的方式打开XML文件，数据库文件或者TXT文件

OpenXML，OpenDatabase，OpenText

5.    通过名字或者索引引用workbook

 

[csharp] view plain copy print ?

1. Excel.Workbook wb = ThisApplication.Workbooks[1];  
2. wb = ThisApplication.Workbooks["Book1"];//在workbook未保存的情况下   
3. wb = ThisApplication.Workbooks["Book1.xls"];//在workbook已经保存的情况下  

Excel.Workbook wb = ThisApplication.Workbooks[1];
wb = ThisApplication.Workbooks["Book1"];//在workbook未保存的情况下
wb = ThisApplication.Workbooks["Book1.xls"];//在workbook已经保存的情况下

1.3 Application成员之： Execute Actions

这类成员包括Calculate，CheckSpelling，Evaluate，Sendmaid，Undo，Quit。。。实在是太多了，请参考原文。

1.4 Application成员之： Handles File Manipulation