Excel Interop防止显示密码对话框 [英] Excel interop prevent showing password dialog

问题描述

我正在编写一个程序来从空行和空列中清除excel文件，我从我自己的问题开始

I am writing a program to clean excel files from empty rows and columns, i started from my own question Fastest method to remove Empty rows and Columns From Excel Files using Interop and everything is going fine.

问题是当工作簿受密码保护时，我想阻止excel显示密码对话框，并抛出异常代替.

The problem is that i want to prevent excel from showing the password dialog when the workbook is password protected and to throw an exception instead of that.

我正在使用以下代码通过互操作打开excel文件:

i am using the following code to open excel files using interop:

 m_XlApp = New Excel.Application
 m_XlApp.visible = False
 m_XlApp.DisplayAlerts = False

 Dim m_xlWrkbs As Excel.Workbooks = m_XlApp.Workbooks
 Dim m_xlWrkb As Excel.Workbook
 m_xlWrkb = m_xlWrkbs.Open(strFile)

 m_xlWrkb.DoNotPromptForConvert = true          

我尝试通过一些链接建议输入空密码

i tried to pass an empty password as some links suggested

m_xlWrkb = m_xlWrkbs.Open(strFile, Password:="")

或使用

m_xlWrkb.Unprotect("")

但没有运气.

有什么建议吗?

推荐答案

我找到了解决方案，但我会接受其他可行的答案

当传递一个空字符串作为密码时，excel将其视为空.因此，它要求输入密码并显示对话框.

When passing an empty string as password the excel consider it as nothing. So it ask for a password and show the dialog.

解决方案是使用单引号作为密码，excel会将其视为空字符串.如果工作簿没有密码保护，它将打开，否则它将引发以下异常

The solution is to pass a single quotation as a password, excel will consider it as empty string. If workbook is not password protected it will open, else it will throw the following exception

您提供的密码不正确.确认CAPS LOCK键已关闭，并确保使用正确的大写字母

The password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization

代码为:

m_xlWrkb = m_xlWrkbs.Open(strFile, Password:="'")

注意

在Microsoft Excel中，值开头的单引号用于强制文本格式.

In microsoft excel, single quotation on the beginning of a value is used to force text formatting.

示例； '0被读取为值为0

example; '0 is readed as a text of value 0

这篇关于Excel Interop防止显示密码对话框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！