作为文件存储迁移项目的一部分,我试图在某些Excel工作簿中更改一些Excel链接以反映新的文件存储位置。
我正在使用VS2017 RC中的Winforms和C#来开发我打算部署的解决方案。
在我的代码顶部,我有以下代码,以便关闭警报并自动更新链接。
excelApp.Visible = true;
excelApp.DisplayAlerts = false;
excelApp.AskToUpdateLinks = false;
在我的解决方案中;我在Excel Workbook对象上调用ChangElink方法,并传递旧链接,新链接和Excel链接类型。
如果我打开非密码受保护的工作簿,其中包含指向的其他不受密码保护的工作簿的链接根据要求成功更改链接。
如果我打开非密码受保护的工作簿,其中包含链接到的其他受密码保护的工作簿,Excel会发出提示,以输入该链接工作簿的密码。<<<<<<<<<<<<<<<<
有人知道抑制链接工作簿密码的次要提示吗?我的代码在下面,我等待您考虑的回复。
if (MsOfficeHelper.IsPasswordProtected(fileName))
{
while ((excelApp.Workbooks.Count == 0) && (!allPasswordUsed))
{
// Open workbook - trying each password from password list in turn
foreach (var excelPassword in excelPasswords)
{
try
{
excelWorkbook = excelApp.Workbooks.Open(Filename: fileName, UpdateLinks: Excel.XlUpdateLinks.xlUpdateLinksNever, Password: excelPassword);
allPasswordUsed = true;
resultsOut = resultsOut.AppendLine(fileName + " - Opened");
}
catch (Exception WTF)
{
//MessageBox.Show(WTF.Message);
}
}
// Open workbook - trying each password from password list in turn
foreach (var excelPassword in excelPasswords)
{
try
{
excelWorkbook = excelApp.Workbooks.Open(Filename: fileName, UpdateLinks: Excel.XlUpdateLinks.xlUpdateLinksNever, Password: excelPassword.ToLower());
allPasswordUsed = true;
resultsOut = resultsOut.AppendLine(fileName + " - Opened");
//
}
catch (Exception WTF)
{
//MessageBox.Show(WTF.Message);
}
}
allPasswordUsed = true;
resultsOut = resultsOut.AppendLine(fileName + " - All known passwords used - Unable to Open File");
}
}
else
{
// Open Workbook - no password required
excelWorkbook = excelApp.Workbooks.Open(Filename: fileName, UpdateLinks: Excel.XlUpdateLinks.xlUpdateLinksNever);
resultsOut = resultsOut.AppendLine(fileName + " - Opened");
}
好。我无法找到有关解决此问题的任何信息,因此我别无选择,只能使用对Windows API的呼叫开发解决方法。
这是我开发的解决方案或解决方法。
在我的winform上,我在Windows API中添加了以下声明。
[DllImport("user32.dll", EntryPoint = "FindWindow", SetLastError = true)]
private static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
[DllImport("user32.dll", CharSet = CharSet.Auto)]
static extern IntPtr SendMessage(IntPtr hWnd, UInt32 Msg, IntPtr wParam, IntPtr lParam);
[DllImport("USER32.DLL")]
public static extern bool SetForegroundWindow(IntPtr hWnd);
我还将以下内容添加到我的Winform的顶部。
public bool fileOpenInProgress = false;
在我的winform中,我添加了一个背景工作者控件。在此背景工作人员控制上,我将WorkerSupportSclation属性设置为true。
在背景工作人员控件的Dowork事件处理程序中,我指定了以下方法。
private void workerXLPwdDialogCheck_DoWork(object sender, DoWorkEventArgs e)
{
while (fileOpenInProgress)
{
IntPtr hwndExcel = FindWindow(lpClassName: "XLMain", lpWindowName: null);
SetForegroundWindow(hwndExcel);
try
{
IntPtr hwndPasswordDialog = FindWindow(lpClassName: null, lpWindowName: "Password");
if (hwndPasswordDialog != IntPtr.Zero)
{
// Make the Password Dialog the active window
SetForegroundWindow(hwndPasswordDialog);
SendMessage(hwndPasswordDialog, WM_CLOSE, IntPtr.Zero, IntPtr.Zero);
}
IntPtr hwndSelectSheetDialog = FindWindow(lpClassName: null, lpWindowName: "Select Sheet");
if (hwndSelectSheetDialog != IntPtr.Zero)
{
// Make the Password Dialog the active window
SetForegroundWindow(hwndSelectSheetDialog);
SendMessage(hwndSelectSheetDialog, WM_CLOSE, IntPtr.Zero, IntPtr.Zero);
}
}
catch (Exception WTF)
{
MessageBox.Show(WTF.Message);
}
}
}
在我的其余代码中,我进行了Excel文件的打开和更改链接,我有以下代码
fileOpenInProgress = true;
workerXLPwdDialogCheck.RunWorkerAsync();
StringBuilder resultsOut = new StringBuilder();
if (MsOfficeHelper.IsPasswordProtected(fileName))
{
while ((excelApp.Workbooks.Count == 0) && (!allPasswordUsed))
{
// Open workbook - trying each password from password list in turn
foreach (var excelPassword in excelPasswords)
{
try
{
excelWorkbook = excelApp.Workbooks.Open(Filename: fileName, UpdateLinks: Excel.XlUpdateLinks.xlUpdateLinksNever, Password: excelPassword);
allPasswordUsed = true;
resultsOut = resultsOut.AppendLine(fileName + " - Opened");
}
catch (Exception WTF)
{
//MessageBox.Show(WTF.Message);
}
}
// Open workbook - trying each password from password list in turn
foreach (var excelPassword in excelPasswords)
{
try
{
excelWorkbook = excelApp.Workbooks.Open(Filename: fileName, UpdateLinks: Excel.XlUpdateLinks.xlUpdateLinksNever, Password: excelPassword.ToLower());
allPasswordUsed = true;
resultsOut = resultsOut.AppendLine(fileName + " - Opened");
//
}
catch (Exception WTF)
{
//MessageBox.Show(WTF.Message);
}
}
allPasswordUsed = true;
resultsOut = resultsOut.AppendLine(fileName + " - All known passwords used - Unable to Open File");
}
}
else
{
// Open Workbook - no password required
excelWorkbook = excelApp.Workbooks.Open(Filename: fileName, UpdateLinks: Excel.XlUpdateLinks.xlUpdateLinksNever);
resultsOut = resultsOut.AppendLine(fileName + " - Opened");
}
// Assuming there is an openwork book object
// check to see if it contains links and attempt to update them.
if (excelApp.Workbooks.Count > 0)
{
excelWorkbook = excelApp.ActiveWorkbook;
#pragma warning disable IDE0019 // Use pattern matching
Array olinks = excelWorkbook.LinkSources(Excel.XlLink.xlExcelLinks) as Array;
#pragma warning restore IDE0019 // Use pattern matching
if (olinks != null)
{
if (olinks.Length > 0)
{
resultsOut = resultsOut.AppendLine(" " + fileName + " - " + olinks.Length.ToString() + " links.");
foreach (var olink in olinks)
{
oldLink = olink.ToString();
// Search through list of linked files to find the oldLink
foreach (LinkedFile linkedFile in linkedFiles)
{
if (oldLink == linkedFile.OldLink)
{
newLink = linkedFile.NewLink;
break;
}
}
try
{
excelWorkbook.ChangeLink(Name: oldLink, NewName: newLink, Type: Excel.XlLinkType.xlLinkTypeExcelLinks);
resultsOut = resultsOut.AppendLine(" SUCCESS - ChangeLink from " + oldLink + " to " + newLink);
Application.DoEvents();
}
catch (Exception whoopsy)
{
resultsOut = resultsOut.AppendLine(" FAILURE - ChangeLink from " + oldLink + " to " + newLink);
Application.DoEvents();
}
//resultsOut = resultsOut.AppendLine(" " + oldLink);
} // End For loop
}
else
{
resultsOut = resultsOut.AppendLine(" No links.");
}
}
excelWorkbook.Close(SaveChanges: true);
resultsOut = resultsOut.AppendLine(fileName + " - Closed");
resultsOut = resultsOut.AppendLine(" ");
}
// Stop the background worker that checks for the existence of a
// Excel Password Dialog
fileOpenInProgress = false;
workerXLPwdDialogCheck.CancelAsync();
return resultsOut.ToString();
这具有单击显示的任何"密码"或"选择表"对话框上的取消按钮。
它可能不是最漂亮的解决方案或解决方案,但功能性。