Sign an Excel Workbook using COM object

PowerShell Help
1

Can anyone offer any tips on how I might digitally sign an excel workbook from a powershell script using the com object model? I have a powershell script that populates an excel workbook. At the end of the process, I would like to digitally sign the workbook so that I can validate later that the contents has not been altered. While there are lots of sample for working with excel workbooks from powershell, I have yet to find anything that references signing. Any suggestions are appreciated!

2

I’m not seeing anywhere in the object’s interface where it would do that. One of the many downsides of automating Excel through that centuries-old interface, unfortunately.

As a guess, I suspect the “Microsoft Way” to do what you’re after would be to put the data into an actual database. SQL Server, for example, supports Master Data, which allows you to “lock down” the data and prevent (and roll back) unwanted changes. I’m not saying that’s a practical approach for you, only that in most cases, if the “Microsoft Way” provides a way, they’re less likely to include alternatives elsewhere (like Excel).

3

To my knowledge, Excel only allows you to sign macros, not the whole spreadsheet. You can generate and distribute the signature yourself, separate from the file, but you’d also have to verify the signature yourself. (This isn’t something Excel would do for you automatically when double-clicking the workbook file.)

Here’s a quick example of how you could do this using an RSA certificate:

$spreadsheet = '.\TransactionLog.xlsx'

# This would be whatever certificate you're using to do the signing.  You need the
# private key in order to generate the signature.

$cert = Get-Item Cert:\CurrentUser\My\BEC9A4BCEFB61E8982B05CE18ECE527E45FA77A2

$spreadsheet = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($spreadsheet)
$spreadsheetBytes = [System.IO.File]::ReadAllBytes($spreadsheet)

# Here's the line that actually does the work of hashing and signing the data.  Everything else in the example
# is just the plumbing to load up or save the various byte arrays and certificates.
$signature = $cert.PrivateKey.SignData($spreadsheetBytes, 'SHA1')

# Saving the signature and the certificate needed to verify it later.

$folder = Split-Path $spreadsheet -Parent
$certFile = Join-Path $folder Signer.cer
$signatureFile = [System.IO.Path]::ChangeExtension($spreadsheet, '.signature')

[System.IO.File]::WriteAllBytes($certFile, $cert.RawData)
[System.IO.File]::WriteAllBytes($signatureFile, $signature)




# On the computer that needs to verify the signature.

# (Assuming that you've set up the path variables $signatureFile, $spreadsheet, and $certFile here;
# since the example just runs everything on the same computer, there's no need to show that part.)

$signatureToVerify = [System.IO.File]::ReadAllBytes($signatureFile)
$spreadsheetBytes = [System.IO.File]::ReadAllBytes($spreadsheet)
$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($certFile)

# Here's the line that hashes the data and verifies the signature of that hash.
$signatureIsValid = $cert.PublicKey.Key.VerifyData($spreadsheetBytes, 'SHA1', $signatureToVerify)

Write-Host "Signature is valid:  $signatureIsValid"

Write-Host "Changing something..."

$spreadsheetBytes[0] = ($spreadsheetBytes[0] + 10) % 256

$signatureIsValid = $cert.PublicKey.Key.VerifyData($spreadsheetBytes, 'SHA1', $signatureToVerify)

Write-Host "Signature is valid:  $signatureIsValid"