Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

encrypt excel documents

by Win (Novice)
on Aug 30, 2005 at 11:32 UTC ( #487707=perlquestion: print w/replies, xml ) Need Help??

Win has asked for the wisdom of the Perl Monks concerning the following question:

How do I get a remote user of an Excel spread sheet to generate an encrypted text file that can be read and understood by a local Perl program? In other words, I want the excel encryption and the Perl de-coding to be talking the same language. ie. I want to encrypt excel documents, in such a way that you can read/write it with Perl. Any suggestions?

Replies are listed 'Best First'.
Re: encrypt excel documents
by Taulmarill (Deacon) on Aug 30, 2005 at 11:41 UTC

    Excel has a proper encryption (not very strong) build in, but i couldn't find a method to read "password-protected" Excel files from Perl. maybe you have more luck, since that would be the easiest way.

    on the other hand, if you have realy strong nerves, you can code some VBA snippet, which encodes the value of a cell with a common encryption, you can also use from within Perl.

    i'm afraid, there is no Perl interpreter included in Excel.

Re: encrypt excel documents
by InfiniteSilence (Curate) on Aug 30, 2005 at 15:26 UTC
    This is the week for Excel questions, isn't it?

    At any rate, to do this you will need OpenSSL for Win32 (the binaries). Note: You can use whatever encryption tools you want. This example uses OpenSSL.

    You will need to learn how to encrypt something with it unless you already know how or are doing it some other way.

    I used:

    C:\Temp\openSSL\bin>openssl enc -aes-256-cbc -a -salt -in foo.txt -out + file.enc enter aes-256-cbc encryption password: Verifying - enter aes-256-cbc encryption password: *****
    And got an encrypted file:
    C:\Temp\openSSL\bin>type file.enc U2FsdGVkX19MZ5n8Rb41XIrAuhwtlBUPPWhbvSe4b0J3Wfj8QRRwqCxuqTG69Ta9
    If you read the tutorial it will show you how to decrypt the file, provided the system decrypting has both the same cipher and the password (which I assume you will share your recipient via some more secure means, like the telephone).

    Now, your only 'problem' is convincing Excel to spit out a text file with the data:

    use Win32::OLE; my ($filename) = "F:\\Incidents.xls"; my ($nextStep) = q|mysystem.cgi?f=incidents_show|; my ($csv) = ".\\data\\_csv\\incidents.csv"; if(-f $filename) { my(@fnStat) = stat($filename); my(@csvStat)= stat($csv); if($fnStat[9] > $csvStat[9]) { my($obj) = Win32::OLE->new("Excel.Application"); $obj->Workbooks->Open (q|F:\Incidents.xls|); if (-f q|c:\data\_csv\incidents.csv|) {unlink(q|c:\data\_csv\i +ncidents.csv|) or die "Cannot unlink the incidents.csv file!"}; $obj->ActiveWorkbook->SaveAs(q|C:\data\_csv\incidents.csv|, 6) +; $obj->ActiveWorkbook->Close(0); $obj->Quit(); "The Incident.xls file has been recently modified and the exc +el file has been created! <a href=\"$nextStep\">Click</a>"; } else { "File is okay. Proceed to CSV. <a href=\"$nextStep\">Click</a +>"; } } else { "Could not find Incidents.xls!"};

    The above code checks to see whether the file on the local disk is older than the spreadsheet's last modification time. If so, it initiates the download.

    Now you can shell out to OpenSSL to encrypt that file. A possible vulnerability with this method -- having a Perl script encrypt a separate file -- is that you will have to maintain the password inside of your perl script somewhere. Perhaps there is an encryption method where you wouldn't need a shared password, like PGP? I'm not sure. Try Google. Update: Here is some more on passwords with OpenSSL

    Celebrate Intellectual Diversity

      There are some nice ideas in this post. However, the user of the Excel spread sheet does not necessarily have Perl on their PC and they won't want to be downloading encryption software to their PC. I think that the solution lies in using VB script. I will need to think about it a little bit more. Does anyone know of a bit of Perl code that encrypts and reads back the encrypted text. I could use that as a basis for producing the VB script and corresponding Perl script.
        There are various ways to make a perl script a binary executable on a windoze PC (link to get you started). If you take advantage of one of those options, the person would not need to install perl, nor encryption software other than the one you provide.


Re: encrypt excel documents
by eXile (Priest) on Aug 30, 2005 at 14:11 UTC
    I'd go for an external program that does the encryption. On most unices there is openssl which can do stuff like that from the commandline, but i guess your users aren't on unix (or 'kings of the commandline').

    If you use email as your way of transportation of files, you could think of using PGP or GPG as a way to do public-private key encryption-type things.

Re: encrypt excel documents
by 5mi11er (Deacon) on Aug 30, 2005 at 14:13 UTC
    It sounds like no matter what, you're going to be relying on the remote user. To make things most simple, you could supply the remote user with a perl script that will extract the data needed, and then encrypt it to send to you. You'd be in control of the encryption algorithm used at both ends this way.

    Another option is to have excel spew its data into a cvs, then encrypt that file using whatever method you wanted so long as it was reversable.

    And finally, a bit of VBA like Taulmarill wrote above could be used to export the file in an encrypted way, but this is probably the most complicated and slowest option to code. (meaning getting the code working will take the longest, I'm not attempting to make predictions of which option is going to be the fastest once written).


    Update: The Email encryption eXile has suggested above is also a good option I hadn't thought of. It reminded me of yet another option: Is the remote user using VPN to connect back to you? If so, the network is already encrypted, and you wouldn't need to encrypt that file before transmitting.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://487707]
Approved by Taulmarill
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2021-01-17 03:26 GMT
Find Nodes?
    Voting Booth?