我得到了一个功能，我需要保护在服务器上上传的Excel文件，在那里它不能被编辑甚至保存。为了实现这一点，我以编程方式保护文件，并添加了一些VBA代码限制用户可以使用它做什么。VBA代码函数假设有2个功能，它可以隐藏Ribbon和防止Save/ savea 函数。你可以参考我的代码，我有3个版本的VBA代码里面，但最希望的是没有。里面有3个VBA代码。这是我的函数，我将把文件位置以字符串形式传递给这个函数。我希望有人能帮助我如何让程序保存安全的Excel文件(使用VBA代码3号)。据我所知，由于 save (不是savea)部分的VBA代码不允许这行wbkExcel.Save()继续进行，我无法保存它。
- 您可以在Excel文件中测试VBA代码，看看它是否有效(测试为我工作到目前为止…)
提前感谢大家…
protected void ExcelEncryptor(string strExcelFile)
{
Microsoft.Office.Interop.Excel.Application wAppExcel = new Microsoft.Office.Interop.Excel.Application();
wAppExcel.Interactive = false;
wAppExcel.Visible = true;
wAppExcel.DisplayAlerts = false;
wAppExcel.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable;
Microsoft.Office.Interop.Excel.Workbook wbkExcel = wAppExcel.Workbooks.Open(strExcelFile.ToString(),System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value);
string strVBCode = string.Empty;
//To hide Ribbon only
/*strVBCode = "Private Sub Workbook_Open()rn" +
" msgbox "This document is protected!"rn" +
" application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"rn" +
"End Sub";*/
//To hide Ribbon + Disable SaveAs (F12 key) but still can Save (Ctrl+S key)
/*strVBCode = "Private Sub Workbook_Open()rn" +
" msgbox "This document is protected!"rn" +
" application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"rn" +
"End Subrn" +
"Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)rn" +
" If (SaveAsUI = True) Thenrn" +
" msgbox "You are not allowed to save this document!"rn" +
" Cancel = Truern" +
" End Ifrn" +
"End Sub";*/
//To hide Ribbon + Disable SaveAs (F12 key) and cannot Save (Ctrl+S key)
strVBCode = "Option Explicitrn" +
"Dim SaveByCode As Booleanrn" +
"Const msg As String = "You are not allowed to save this document!"rn" +
"Const ttl As String = "This document is protected!"rn" +
"Private Sub Workbook_Open()rn" +
" MsgBox msg, vbExclamation, ttlrn" +
" application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"rn" +
"End Subrn" +
"Private Sub Workook_BeforeClose(Cancel As Boolean)rn" +
" If Me.Saved = False And SaveByCode = False Thenrn" +
" MsgBox msg, vbExclamation, ttlrn" +
" Cancel = Truern" +
" End Ifrn" +
"End Subrn" +
"Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)rn" +
" Application.EnableEvents = Falsern" +
" If SaveByCode = True Thenrn" +
" SaveThisFilern" +
" Elsern" +
" MsgBox msg, vbExclamation, ttlrn" +
" Cancel = Truern" +
" End Ifrn" +
" Application.EnableEvents = Truern" +
"End Subrn" +
"Sub SaveThisFile()rn" +
" SaveByCode = Truern" +
" ThisWorkbook.Savern" +
"End Sub";
Microsoft.Vbe.Interop.VBProject vbMacro = wbkExcel.VBProject;
Microsoft.Vbe.Interop.VBComponent vbCode = vbMacro.VBComponents.Item("ThisWorkBook");
Microsoft.Vbe.Interop.CodeModule vbModule = vbCode.CodeModule;
vbModule.AddFromString(strVBCode.ToString());
wbkExcel.Protect("Pa$$w0rd!", true, false);
foreach (Microsoft.Office.Interop.Excel.Worksheet wstExcel in wAppExcel.Worksheets)
{
wstExcel.Protect("Pa$$w0rd!", true, true, true, true, false, false, false, false, false, false, false, false, true, true, false);
}
wbkExcel.Save();
wbkExcel.Close(System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value);
Marshal.ReleaseComObject(wbkExcel);
Marshal.ReleaseComObject(wAppExcel);
GC.Collect();
}
经过一番尝试&代码错误，我终于实现了我所需要的，Excel文件将受到保护+不能按F12键+不能按Ctrl+S键+不能按Ctrl+P键了，更新的VBA代码也可以阻止用户打开Visual Basic编辑器，如果他们希望禁用代码，但在我的情况下，我现在可以访问它。参考我更新的VBA代码如下:
//Excel file will be password protected+cannot press F12 key+cannot press Ctrl+S key+cannot press Ctrl+P key anymore
strVBCode = "Private Sub Workbook_Open()rn" +
" msgbox "This document is protected!"rn" +
" application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",false)"rn" +
" ThisWorkbook.Saved = Truern" +
" Application.OnKey "^s", ""rn" +
" Application.OnKey "^p", ""rn" +
//Enable this line if you wish to block Visual Basic Editor
//" Application.OnKey "%{F11}", ""rn" +
"End Subrn" +
"Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)rn" +
" If (SaveAsUI = true) Thenrn" +
" msgbox "You are not allowed to save this document!"rn" +
" Cancel = Truern" +
" Elsern" +
" ThisWorkbook.Saved = Truern" +
" Application.OnKey "^s", ""rn" +
" Application.OnKey "^p", ""rn" +
//Enable this line if you wish to block Visual Basic Editor
//" Application.OnKey "%{F11}", ""rn" +
" End Ifrn" +
"End Sub";
我从之前的代码中注意到，命令(VBA代码)Cancel=True中的Private Sub workbook_beforeave 将阻止命令(c#)wbkExcel.Save()运行，因为VBA代码将在保存过程中为c#代码返回false。我希望有人能帮助和纠正我，如果我错了。希望这篇文章也能对其他人有所帮助。由于StackOverflow.com。