本文主要针对在Visual Studio中使用C#
本文的PDF下载地址:C#操作Excel2007.pdf
来源:Understandingthe Excel Object Model from a .NET Developer's Perspective
所有Excel对象请参考MSDN:
----------------------------------------------华丽分割-------------------------------------------------
1.
首先讨论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 |
FixedDecimalPlaces |
Long |
Determines the number of decimal places to be used for numeric data if the |
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 |
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#中的使用方法是:(执行完任务记得重新开启该功能)
- //
C# - try
- {
-
ThisApplication.ScreenUpdating = false; -
// Do your work that updates the screen. - }
-
- finally
- {
-
ThisApplication.ScreenUpdating = true; - }
-
// 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
Excel库包含很多类,但是在Visual Studio中提供给开发人员的只有Excel.Application,Workbook等,那么我们怎么获得其他很多有用的类型呢?
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 |
Selection |
Object |
Returns the selected object within the application. Might be a |
Sheets |
Sheets |
Returns a collection of |
Workbooks |
Workbooks |
Returns a collection of |
上面的属性中,Workbooks属性无疑是最常用的。通过这个属性,我们可以打开或者创建一个workbook,下面我们具体看一下该属性的一些行为。
1.
- Excel.Workbook
wb = ThisApplication.Workbooks.Add(Type.Missing);
Excel.Workbook wb = ThisApplication.Workbooks.Add(Type.Missing);
2.
- ThisApplication.Workbooks.Close();
ThisApplication.Workbooks.Close();
3.
- 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);
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.
OpenXML,OpenDatabase,OpenText
5.
- Excel.Workbook
wb = ThisApplication.Workbooks[1]; - wb
= ThisApplication.Workbooks["Book1"];//在workbook未保存的情况下 - 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
这类成员包括Calculate,CheckSpelling,Evaluate,Sendmaid,Undo,Quit。。。实在是太多了,请参考原文。
1.4