本篇是 VBA / Excel VBA將檔案另存成PDF再使用自訂python程式加密PDF 的番外
在爬文的過程中，可能因為關鍵字的關係「 VBA PDF 加密」
所以跑出一篇在mobile01論壇的教學 使用vba + 開源函式庫，解決excel 另存pdf 無法加密問題(vba範例)
為了下載檔案而加入會員，還好檔案還在、還好不需要什麼鬼積分
依樣畫葫蘆可以得到相同結果
再次爬文之後，在這篇 VBA : encrypted / password protected PDF for MS Office
可以看到使用相同名稱的DLL
但是這篇文章的作者沒有提供ProtectPDF.dll檔案
下面有人問他 "Where is ProtectPDF.dll download site " 也沒有得到回應
所以嘗試用dnSpy看看能不能反編譯在mobile01論壇下載的DLL，看看兩篇文章的DLL是不是一樣的
根據dnSpy的編譯結果，可以確認至少功能是一樣的
這個DLL是用C#編寫的，主要是使用ITEXTSHARP.dll的功能
Rummaging through all the above to address the simple task of filling in a gap in the Word/Excel object model functionality to save an encrypted PDF and set different security options from VBA, I found that the simplest and quickest solution to make a custom COM wrapper around one of well-known .Net open source libraries. As an in house component, you shouldn’t have to worry about getting IT approval. If you not familiar with making COM visible .NET DLL, here is an attached Visual Studio 2013 .NET v 4.0 project, written in C# and wrapping a popular library ITEXTSHARP.dll which is an .NET PDF library ported from Java. For simplicity, ITEXTSHARP.dll is imbedded into single output resulting library ProtectPDF.dll with a single method GoPDF.ProtectPdfStandard(string passwordUser, string passwordOwner, string namepathPDFIn, string namepathPDFOUT )
所以嘗試改成用exe來執行程式碼
基本上就是把ProtectPdfStandard()抓出來，在main()接收參數後執行
流程：
1.在visual studio新增C#解決方案
2.修改預設的Program.cs程式碼，也可以修改名稱，visual studio會自動修改相關的登錄設定
#3 引用 iTextSharp.text.pdf#20-23 接收命令列傳來的參數
#29 將參數傳到 自訂函數ProtectPdfStandard
#39-81 ProtectPdfStandard的內容，都是從ProtectPdfStandard()抓來的
重點是在#61-70執行iTextSharp.text.pdf的PdfReader.Encrypt方法
Program.cs程式碼如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
using System;
using System.IO;
using iTextSharp.text.pdf;
namespace goPdf
{
internal class Program
{
static void Main(string[] args)
{
//Console.WriteLine($"parameter count = {args.Length}");
/*
for (int i = 0; i < args.Length; i++)
{
Console.WriteLine($"Arg[{i}] = [{args[i]}]");
}*/
if (args.Length > 0)
{
//
string passwordUser = args[0];
string passwordOwner = args[1];
string inputPath = args[2];
string outputPath = args[3];
//Console.WriteLine(passwordUser);
//Console.WriteLine(passwordOwner);
//Console.WriteLine(inputPath);
//Console.WriteLine(outputPath);
//
string errorText = ProtectPdfStandard(passwordUser, passwordOwner, inputPath, outputPath);
Console.WriteLine(errorText);
//
}
else
{
Console.WriteLine("沒有輸入參數");
}
}
public static string ProtectPdfStandard(string passwordUser, string passwordOwner, string namepathPDFIn, string namepathPDFOUT)
{
string text = string.Empty;
string result;
if (string.IsNullOrWhiteSpace(passwordUser))
{
result = "Error: User Password not set";
}
else if (string.IsNullOrWhiteSpace(passwordOwner))
{
result = "Error: Owner Password not set";
}
else if (string.IsNullOrWhiteSpace(namepathPDFIn))
{
result = "Error: User Password not set";
}
else if (string.IsNullOrWhiteSpace(namepathPDFOUT))
{
result = "Error: Owner Password not set";
}
else
{
try
{
using (Stream stream = new FileStream(namepathPDFIn, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (Stream stream2 = new FileStream(namepathPDFOUT, FileMode.Create, FileAccess.Write, FileShare.None))
{
PdfReader pdfReader = new PdfReader(stream);
PdfEncryptor.Encrypt(pdfReader, stream2, true, passwordUser, passwordOwner, 2564);
}
}
}
catch (Exception ex)
{
text = ex.Message;
}
result = text;
}
return result;
}
}
}
|
3.在參考引用ITEXTSHARP.dll
備註：ITEXTSHARP.dll是從dnSpy另存出來的
4.測試或者建置，在bin資料夾的Debug資料夾內會產生exe檔跟相關的檔案，包含參考的ITEXTSHARP.dll
5.在Excel VBA內執行
這邊的流程就大同小異，不過因為這個函數接收4個參數passwordUser 開啟密碼
passwordOwner 編輯權限密碼
inputPath 輸入檔案路徑
outputPath 輸出檔案路徑
所以程式碼稍微修改如下
#42-57 新增了一個陣列，用來存儲輸出的檔案路徑
#72-75 設定參數，密碼的部分之後可以再視情框修改
#78 執行命令列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
Public Sub tt3()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
ActiveWorkbook.Sheets(1).Activate
'目前檔案路徑
nPath = ActiveWorkbook.Path
'輸出資料夾名稱
strFolderName = "output"
'完整的輸出資料夾路徑
outputFolderPath = nPath & Application.PathSeparator & strFolderName & Application.PathSeparator
'判斷輸出資料夾是否存在
strFolderExists = Dir(nPath & Application.PathSeparator & strFolderName, vbDirectory)
If strFolderExists = "" Then
'不存在則新增
fso.CreateFolder nPath & Application.PathSeparator & strFolderName
Else
'存在則移除 再新增
fso.DeleteFolder nPath & Application.PathSeparator & strFolderName, True 'True 強制刪除
fso.CreateFolder nPath & Application.PathSeparator & strFolderName
End If
'將工作表轉存為PDF檔
For i = 2 To Sheets.Count
nName = Sheets(i).Name
Sheets(i).Copy
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=outputFolderPath & nName & ".pdf"
ActiveWorkbook.Close False
Next
'讀取output資料夾的PDF 如果存在 回傳檔案名稱.副檔名
pdfFile = Dir(outputFolderPath & "*.pdf")
Dim fileArr() As String
Dim outArr() As String
i = 0
Do While pdfFile <> ""
ReDim Preserve fileArr(i)
ReDim Preserve outArr(i)
fileArr(i) = outputFolderPath & pdfFile
'Debug.Print outputFolderPath & pdfFile
outArr(i) = outputFolderPath & Split(pdfFile, ".")(0) & "_PW.pdf"
'Debug.Print outputFolderPath & Split(pdfFile, ".")(0) & "_PW.pdf"
i = i + 1
pdfFile = Dir()
Loop
'Debug.Print fileArr(0)
'陣列上限(序位值)
num = UBound(fileArr)
Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 0
Dim errorCode As Long
For i = 0 To num
DoEvents
passwordUser = "123"
passwordOwner = "456"
inputPath = fileArr(i)
outputPath = outArr(i)
'透過WScript.Shell 執行pdf加密程式
s = Chr(34) & Excel.ActiveWorkbook.Path & "\C#\goPDF\" & "goPDF.exe" & Chr(34) & Chr(32) & Chr(34) & passwordUser & Chr(34) & Chr(32) & Chr(34) & passwordOwner & Chr(34) & Chr(32) & Chr(34) & inputPath & Chr(34) & Chr(32) & Chr(34) & outputPath & Chr(34)
' Debug.Print s
Shell s
errorCode = wsh.Run(s, windowStyle, waitOnReturn)
If errorCode = 0 Then
' MsgBox "Done! No error to report."
Debug.Print "輸出:" & fileArr(i) & Chr(32) & "到:" & outArr(i)
Else
MsgBox "Program exited with error code " & errorCode & "."
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
|
發表文章
