Month: August 2012

今天在一个VB交流群上面见到人问怎么打开一个有写保护的Excel文档而不需要用户额外操作。其实这个问题自己查阅MSDN就完全可以解决的，但是最后不得已还是受人以鱼了，整理在此，也算是自己的回顾吧。

本次探讨的背景是微软dotNet框架，主要代码语言是C#/CSharp。编写环境Visual Studio 2010，测试环境Windows 7 64 bit & Office 2010 Pro Plus 64bit。理论上支持的Excel版本最低到Excel 2003，在低的版本就不敢保证了。

所有Excel相关操作使用微软Microsoft.Office.Interop.Excel命名空间，我们这里先做引用：

using Excel = Microsoft.Office.Interop.Excel;

言归正传。

取得与Excel程序的通信

Excel.Application excel = new Excel.Application();
excel.Visible = false;

声明Excel程序实例，并且不显示Excel的界面。

Workbook.Open()方法

打开Excel文档使用下列方法：

String filename="filename"; //文件路径
Excel.Workbook workbook = excel.Workbooks.Open(filename);

其中Workbook.Open的原型是

Workbook Open(
	string Filename,
	Object UpdateLinks,
	Object ReadOnly,
	Object Format,
	Object Password,
	Object WriteResPassword,
	Object IgnoreReadOnlyRecommended,
	Object Origin,
	Object Delimiter,
	Object Editable,
	Object Notify,
	Object Converter,
	Object AddToMru,
	Object Local,
	Object CorruptLoad
)

Filename
类型: System.String
必需字符串，给出所打开工作簿的路径。

UpdateLinks
类型: System.Object
可选对象。 Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.
If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the UpdateLinks argument is 2, Microsoft Excel generates charts from the graphs attached to the file. If the argument is 0, no charts are created.

ReadOnly
类型: System.Object
可选对象。设为true则以只读模式打开工作簿。

Format
类型: System.Object
可选对象。 If Microsoft Excel is opening a text file, this argument specifies the delimiter character, as shown in the following table. If this argument is omitted, the current delimiter is used.

Password
类型: System.Object
可选对象。 打开受保护工作簿所需的密码字符串。 如果该参数被忽略而工作簿是受保护的，用户将会被要求提供密码。

WriteResPassword
类型: System.Object
可选对象。 修改写保护工作簿所需的写保护密码字符串。 如果该参数被忽略而工作簿是写保护的，用户将会被要求提供写保护密码。

IgnoreReadOnlyRecommended
类型: System.Object
可选对象。设置为true来避免显示推荐只读模式打开消息。

Origin
类型: System.Object
可选对象。 If the file is a text file, this argument indicates where it originated (so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly). Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the current operating system is used.

Delimiter
类型: System.Object
可选对象。 如果目标文件是一个文本文件且Format参数设置为6, 则本参数是一个用来确定分隔符的String对象。 只有字符串首位的字符会被用作为分隔符。

Editable
类型: System.Object
可选对象。 If the file is a Microsoft Excel 4.0 add-in, this argument is True to open the add-in so that it’s a visible window. If this argument is False or omitted, the add-in is opened as hidden, and it cannot be unhidden. This option doesn’t apply to add-ins created in Microsoft Excel 5.0 or later. If the file is an Excel template, use True to open the specified template for editing or False to open a new workbook based on the specified template. The default value is False.

Notify
类型: System.Object
可选对象。 If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.

Converter
类型: System.Object
可选对象。 The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter doesn’t recognize the file, all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property.

AddToMru
类型: System.Object
可选对象。 True to add this workbook to the list of recently used files. The default value is False.

Local
类型: System.Object
可选对象。 True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically U.S. English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

CorruptLoad
类型: System.Object
可选对象。 Can be one of the following constants: xlNormalLoad, xlRepairFile, and xlExtractData. The default behavior if no value is specified is usually normal, but may be safe load or data recovery if Excel has already attempted to open the file. The first attempt is normal. If Excel stops operating while opening the file, the second attempt is safe load. If Excel again stops operating, the next attempt is data recovery.

可选参数的忽略
我们注意到，只有第一个参数filename是必须参数，其他的都是可选参数。 不想明确的可选参数可以使用Type.Missing（C#）或missing（VB）代替。

与Excel交互

我们这里只做最基础的交互演示，详情请查技术文档。

Excel.Worksheet worksheet = workbook.ActiveSheet;
Range range = worksheet.get_Range("A1",Type.Missing);
//get_Range(cell1,cell2)可以接受2个参数，可以是Excel A1 Style的字符串代表目标单元格区域的左上及右下单元格
//range.Value将设置或返回指定区域的值
//range.Value2将设置或返回指定单元格的值
</pre>
<h1>与Excel通信的结束</h1>
我们使用_Application接口下的Quit()方法，必须先调用Quit方法，Excel进程才能被释放：
<pre lang="csharp">
excel.Quit();
excel=null;

示例

我们现在有一个名为test.xlsx的Excel文档，分别有文档保护密码”pswd1″以及文档写保护密码”pswd2″。我们不希望修改Excel文档内容，只需要读取A1单元格的内容并且显示出来，并且不需要用户的额外操作。

    String passwd = "pswd1";
    String WRPasswd = "pswd2";
    Excel.Application excel = new Excel.Application();
    Excel.Workbook workbook = excel.Workbooks.Open(@"E:ShowCaseExcel1test.xlsx", Type.Missing, true, Type.Missing,passwd);
    Excel.Worksheet worksheet = workbook.ActiveSheet;
    String content=worksheet.get_Range("A1", Type.Missing).Value2;
    excel.Quit();
    excel = null;
    MessageBox.Show(content);