如何在SSIS脚本任务中使用C＃.net从excel工作簿中删除密码 [英] How to remove password from excel workbook using C#.net in SSIS script task

问题描述

我有一个受密码保护的excel工作簿，需要加载到表中。我从论坛了解到Excel源代码不会读取受密码保护的文件。因此，在Excel源代码中使用excel文件之前，我想过使用脚本
任务删除密码。

我在脚本任务中编写了以下代码，该脚本任务成功运行但密码不是从Excel文件中删除。保存之后它又要求输入密码。

 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application（）; 
 
 string Password = Dts.Variables [" Password"]。Value.ToString（）; 
 string filename = Dts.Variables [" File"]。Value.ToString（）; 
 
 xlApp.DisplayAlerts = false; 
 xlApp.Visible = false; 
 
 Excel.Workbook wbk = xlApp.Workbooks.Open（filename，0，false，5，Password，""，false，Excel.XlPlatform.xlWindows，""，true，false ，0，true，false，false）; 
 
 wbk.Unprotect（""）; 
 wbk.Save（）; 
 
 xlApp.Quit（）; 
 
 Marshal.ReleaseComObject（wbk）; 
 Marshal.ReleaseComObject（xlApp）; 

任何人都可以帮我弄清楚错误。如果此问题与C＃无关，请转到相应的类别。

谢谢。

解决方案

Hi Poornimadevi，

保护工作簿有两种不同的密码。

一个是密码保护工作簿的工作表不被添加，移动或删除。您可以使用以下代码将其删除。

 wbk.Unprotect（密码）; 

另一个是密码保护工作簿不被打开。您可以使用下面的代码将其删除。

 wbk.Password ="" ;; 

最好的问候，

特里

Hi,

I have a password protected excel workbook which needs to be loaded into a table. I understand from forums that Excel source will not read password protected files. So before using the excel file in Excel source I thought of removing password using a script task.

I have written below code inside a script task which ran successfully but password is not removed from Excel file. After saving also it is again asking for password.

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

string Password = Dts.Variables["Password"].Value.ToString(); 
string filename = Dts.Variables["File"].Value.ToString(); 
                
xlApp.DisplayAlerts = false;
xlApp.Visible = false;

Excel.Workbook wbk = xlApp.Workbooks.Open(filename, 0, false, 5, Password, "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

wbk.Unprotect("");
wbk.Save();

xlApp.Quit();

Marshal.ReleaseComObject(wbk);
Marshal.ReleaseComObject(xlApp);

Can anyone help me in figuring out the error. Please move to appropriate category if this question is not relevant to C#.

Thanks.

解决方案

Hi Poornimadevi,

There are two different password for protecting workbook.

One is the password protects the workbook's sheets from being added, moved or deleted. You could use below code to remove it.

wbk.Unprotect(Password);

Another is the password protects the workbook from being opened. You could use below code to remove it.

wbk.Password = "";

Best Regards,

Terry

这篇关于如何在SSIS脚本任务中使用C＃.net从excel工作簿中删除密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！