We were very close to a solution, but I just discovered a fairly serious problem.
This “solution” only works for LibreOffice, if I try to open it from Microsoft Word, or Word-online, it just doesn’t work.
I also tried this suggestion, it creates the file without password, no matter what.
Any help is very appreciated, I’m out of options
Why should MS run our code? They hardly support our document format.
Simply use an encryption program for encryption and use your office suite to generate office documents in the native file format of the program you are using. Generating MS Office documents with LibreOffice makes no sense.
I’ll try to explain the problem in more detail.
We’re generating “.xlsx” documents from C++ using a library called QXlsx.
The problem is, is that library doesn’t allow saving documents with passwords, I tried using Xlnt, but their encryption scheme isn’t supported by LibreOffice or MS office, meaning that neither of those program can decode the document after encrypting.
The only way I found to encrypt a document from C++ is to use this macro in LibreOffice, and call soffice with the “macro” arguments .
Now the problem is, is that macro does work, it generates encrypted documents, but only LibreOffice can decode them. I tried MS office, and word-online, this is where I am at right now
This means that your problem has absolutely nothing to do with LibreOffice.
Well, I am generating the encrypted documents through LibreOffice’s macros.
Which only LibreOffice can open(the documents), it might not be at fault, maybe my script is incorrect.
That’s the I need help with
Stroring to encrypted files by unexperienced users is a frequent cause of data loss. No remedy then!
If you feel sure to know what you are doing, you can use the code below to start. (LibreOffice Basic isn’t “VBA”.)
Sub saveEncrypted(Optional pPwd As String)
If IsMissing(pPwd) Then pPwd = "encrypted"
doc = ThisComponent
cURL = doc.URL
If (cURL="") OR NOT (FileExists(cURL)) Then
Print "A complete existing URL is needed in the example."
REM Of course, the URL to store to can be any URL the calling
REM user has the right to write to.
Exit Sub
End If
Dim saveEncryptedArgs(0) As New com.sun.star.beans.PropertyValue
saveEncryptedArgs(0).Name = "Password"
saveEncryptedArgs(0).Value = pPwd
doc.storeAsURL(cURL, saveEncryptedArgs)
doc.close(True)
End Sub
My extra problem is to understand for what reason this should be done by a “macro”.
This seems promising, one question.
If I save this file somewhere on disk, can I use LibreOffice’s CIL to run this script with arguments?
I’m fairly new to LibreOffice, maybe “Macro” isn’t the right term.
I need a script which can be called by LibreOffice or some other program and given the right arguments
It was not about terms. “Macro” or “script” both are good enough here. My idea was that somebody creating files can save them one or another way manually.
You surely can’t simply run “this script” to complete your task. It only demonstrates the API method and the encryption-related argument you need for every single case of application.
If your question is about a kind of batch processing like “Read all the files from a given folder, filter them basded on their names, and then conditionally store them to an encrypted version.” the requirements can be very different.
In principle a LibO document (spreadsheets most likely) can be used as such a “batch processor” when called with a command line giving the macro to start …
I would expect complications, but first of all I’m not the one with a lot of experience concerning the usage of soffice.exe with commandline options.
soffice.exe macro:///standard.module1.macro1 is the “template” for the case.
Read Andrew Pitonyak’s famous texts and you will find lots of information and examples…
Thanks for the suggestion, if you can actually run scripts from the terminal it’s a game changer.
Also, I discovered you can record macros, so I recorded the “save-as-with-password” macro, and got this.
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file://[location of output file]"
args1(1).Name = "FilterName"
args1(1).Value = "Calc MS Excel 2007 XML"
args1(2).Name = "EncryptionData"
args1(2).Value = Array(Array("OOXPassword","[password]"))
dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())
The problem is, is that it just doesn’t work. It creates the new document, but without password
You probably wanted to phrase it differently: It works great if used correctly. Unfortunately, to do this correctly, I’ll have to study a lot of material related to information encryption.
One single "OOXPassword" is not enough. Actually the macro should contain something like this:
args1(1).Name = "FilterName"
args1(1).Value = "Calc MS Excel 2007 XML"
args1(2).Name = "EncryptionData"
args1(2).Value = Array(Array("OOXPassword","Sophia981"), _
Array("CryptoType","Standard"), _
Array("PackageSHA256UTF8EncryptionKey", _
Array(-57,-100,-82,43,71,-90,-102,-87,-76,110,109,-17,38,-23,-102,39,-128,-67,-96,-20,-4,29,-108,54,86,61,-29,-89,-25,-33,-97,-28)), _
Array("PackageSHA1UTF8EncryptionKey", _
Array(-23,-76,75,-66,32,-92,14,104,72,63,109,2,-84,127,-74,-29,-63,122,119,124)), _
Array("PackageSHA1MS1252EncryptionKey", _
Array(-23,-76,75,-66,32,-92,14,104,72,63,109,2,-84,127,-74,-29,-63,122,119,124)), _
Array("PackageSHA1CorrectEncryptionKey", _
Array(-23,-76,75,-66,32,-92,14,104,72,63,109,2,-84,127,-74,-29,-63,122,119,124)))
LibreOffice perfectly generates all these obscure numbers on its own. It remains to understand HOW EXACTLY this happens and the macro will create Excel workbooks with password protection.
Thank you very much, I’ll definitely look it that. However, @Lupp’s answer sorta worked, I’m just trying to make it more general.
If you find a way to generate these four arrays of numbers - PackageSHA256UTF8EncryptionKey, PackageSHA1UTF8EncryptionKey, PackageSHA1MS1252EncryptionKey and PackageSHA1CorrectEncryptionKey - with a given password, and post the code here with explanations as an answer to your question, I’ll gladly upvote your answer.
@Lupp’s code should work well and do its job. But you need to check for what types of files it works. I have no doubt that for native Calc files this works well. But will it work for excel files? After all, your task is to generate xlsx, isn’t it?
I looked for the “call macro via command line” myself in AP’s texts and didn’t find much more as already posted - and by that much less than I had expected.
May be kind of a rarely used track.
You surely already looked at soffice.exe -? ?
If you find a way to get the complete commandline from inside any instance created by the call of soffice.exe, please tell me.
So this is my script so far
sub save(inputFile as String, outputFile as String, password as String)
dim doc as Object
dim inputFileURL as string
inputFileURL = "file://" & inputFile
dim outputFileURL as String
outputFileURL = "file://" & outputFile
Dim saveEncryptedArgs(1) As New com.sun.star.beans.PropertyValue
saveEncryptedArgs(0).Name = "Password"
saveEncryptedArgs(0).Value = password
doc = stardesktop.LoadComponentFromURL(inputFileURL, "_blank", 0, Array())
doc.storeAsURL(outputFileURL, saveEncryptedArgs)
doc.close(True)
end sub
It mostly works, I can call it (from the terminal/bash) like so
soffice "macro:///Standard.Module1.save(\"[path to input document]\", \"[path to output document]\", \"[password]\")"
-
Is there any way to call this macro using relative paths?
instead of using the fully qualified path, do something like “./document.xlsx”? -
Is there a way to load a document from disk, without having to bring it up on the screen?
When I callstardesktop.LoadComponentFromURLit opens the document in a new window, is there a way to load the document without opening it (visibly)?
You can use
dim loadComponentProperties(0) As New com.sun.star.beans.PropertyValue
PloadComponentPropertiesrops(0).Name= "Hidden"
loadComponentProperties(0).Value= True
doc = stardesktop.LoadComponentFromURL([path to input file], "_blank", 0, loadComponentProperties)
To “hide” the loaded document
I by no means have any idea what I’m really doing, I’ve only started using LibreOffice yesterday.
I don’t exactly understand what you mean by “complete commnadline”.
Seems there was a misunderstanding. Sorry.
I don’t know which article came first: this one, which is tagged 2019-10-24, or this November 15, 2021. But there is a link here and there to Pitonyak’s book - if you are just starting to teach LibreOffice to do useful things with macros, then I highly recommend downloading the book and having it handy. For example, Listing 5.111: Load hidden then setVisible answers your question “how to quietly open a document” - ah, I see, yuo already found this
If you want to do that with Ecel files, a license of MS Office should be affordable. Calc is an alternative to MS Excel but no replacement. This is not an alternative OOXML editor. As a matter of fact, OOXML has been introduced to fight Open Document Format (ODF).
The problem is, is that we have to use LibreOffice since were on Linux.
Using a VM is not an option so LibreOffice is out only option