password "protecting" an xls file
python...@raf.org
two years ago, there were requests on this list for
password-protected spreadsheets to which the response
was that it's not documented and there are no open
source examples to copy from.
that's all changed now. microsoft has published all of
its format and protocol specifications and openoffice
lets you save an xls file with a password that needs to
be entered when subsequently opening it.
so, is there any chance that this feature might be added to
xlwt and time soon? i couldn't see any mention of it in the
current tutorial so i assume it hasn't already been done.
failing that, are there any python/win32/com experts who
could supply me with some code for getting excel to re-save
an xls file with a password?
cheers,
raf
Ervin Hegedüs
On Wed, Nov 17, 2010 at 4:17 AM, <python...@raf.org> wrote:
> two years ago, there were requests on this list for
> password-protected spreadsheets to which the response
> was that it's not documented and there are no open
> source examples to copy from.
[...]
> so, is there any chance that this feature might be added to
> xlwt and time soon? i couldn't see any mention of it in the
> current tutorial so i assume it hasn't already been done.
As I know there has been implemented this feature, but I don't know
which version from.
Example:
sheet.write(row, col, "content", xlwt.easyxf('protection: cell_locked True;'))
...
sheet.set_protect(True)
sheet.set_password('secret')
book.save('locked.xls')
As you can see, you need explicit to mark cell as locked.
Bye:
a.
John Machin
> Hello,
>
> As I know there has been implemented this feature, but I don't know
> which version from.
>
> Example:
>
> sheet.write(row, col, "content", xlwt.easyxf('protection: cell_locked True;'))
> ...
> sheet.set_protect(True)
> sheet.set_password('secret')
> book.save('locked.xls')
>
> As you can see, you need explicit to mark cell as locked.
Hi Ervin,
This is "write" protection, which allows the user to view all the data,
but prevents the user from changing the locked parts. A typical use is
in a "calculator"-type of worksheet to protect all the formulas and
constant data, and leave unprotected the cells which are input to the
calculation.
What raf is looking for is "open" or "read" protection, which actually
encrypts the content of almost all BIFF records in the Workbook stream.
Cheers,
John
John Machin
> hi,
>
> two years ago, there were requests on this list for
> password-protected spreadsheets to which the response
> was that it's not documented and there are no open
> source examples to copy from.
>
> that's all changed now. microsoft has published all of
> its format and protocol specifications and openoffice
> lets you save an xls file with a password that needs to
> be entered when subsequently opening it.
>
> so, is there any chance that this feature might be added to
> xlwt and time soon? i couldn't see any mention of it in the
> current tutorial so i assume it hasn't already been done.
Hi raf,
Sorry, but it's not planned. It'd be several truckloads of work for
little benefit.
There are 3 forms of "encryption" implemented in Excel 2003:
(a) XOR Obfuscation -- used up to and including Excel 5.0
(b) RC4 Encryption -- Excel 97 to 2000
(c) RC4 CryptoAPI Encryption -- introduced in Excel 2003, I think.
B is the default in Excel 2003. OOo implements only A and B; it won't
open a file saved with C. To get to C you need to lean on the Advanced
button in the Excel password dialogue and I believe most folk are
unaware of its existence. In any case Microsoft severely disses all 3
methods in their documentation.
This blog post by a Microsoft staffer gives the picture:
Main points:
"the older 40-bit RC4 encryption is the default for these documents.
There is no need to do complicated cryptanalysis on 40-bit encryption.
Just try all 2^40 keys, which can happen on a modern system in a matter
of hours."
"If you need to encrypt an Office document, then use the new file
format, and get real encryption as we've documented in more than one
place. If you need to encrypt an older file format, then use a 3rd party
tool that will do proper encryption. If you merely need obfuscation,
perhaps to keep your kids out of the Christmas list, it might suffice
for that, but not if you have a really bright kid."
Cheers,
John
Ervin Hegedüs
On Wed, Nov 17, 2010 at 12:09 PM, John Machin <sjma...@lexicon.net> wrote:
> On 17/11/2010 6:12 PM, Ervin Hegedüs wrote:
>> As I know there has been implemented this feature, but I don't know
>> which version from.
> Hi Ervin,
>
> This is "write" protection, which allows the user to view all the data, but
> prevents the user from changing the locked parts. A typical use is in a
> "calculator"-type of worksheet to protect all the formulas and constant
> data, and leave unprotected the cells which are input to the calculation.
>
> What raf is looking for is "open" or "read" protection, which actually
> encrypts the content of almost all BIFF records in the Workbook stream.
yes, sorry for my confusion, thanks for the correction,
bye:
a.
python...@raf.org
hi john,
thanks for that. i didn't really care whether it was meaningful
encryption or not (that's what gnupg is for in a sane world).
i'm just satisfying a bizarre client request. they refuse to
download the file securely via https and want it emailed to
them with password "protection" and i'm sure they couldn't
cope with gnupg.
and i can fully appreciate your lack of enthusiasm for doing
a large amount of pointless work...
so, in case anyone else seeing this in the archives has the
same need, i've attached a COM approach that tells Excel to
password "protect" an existing file but of course it only
works on windows (and, for some strange reason, only after
makepy.py has been run on the excel object library).
cheers,
raf
John Machin
>
> hi john,
>
> thanks for that. i didn't really care whether it was meaningful
> encryption or not (that's what gnupg is for in a sane world).
>
> i'm just satisfying a bizarre client request. they refuse to
> download the file securely via https and want it emailed to
> them with password "protection" and i'm sure they couldn't
> cope with gnupg.
What's wrong with zipping the XLS up with Win-ZIP (or better yet, 7Zip,
it's free) with AES-256 genuine meaningful encryption of the whole file?
That way you don't have to do any pointless work either :-)
> and i can fully appreciate your lack of enthusiasm for doing
> a large amount of pointless work...
>
> so, in case anyone else seeing this in the archives has the
> same need, i've attached a COM approach that tells Excel to
> password "protect" an existing file but of course it only
> works on windows (and, for some strange reason, only after
> makepy.py has been run on the excel object library).
Attachments don't. Your code can't be very long; just drop it in the
body of a message.
Cheers,
John
python...@raf.org
i didn't realise that. here's the code.
#!/usr/bin/env python
# win32only_password_protect_xls.py - Tell Excel to password protect a spreadsheet
#
# Copyright (C) 2010 raf <r...@raf.org>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
# or visit http://www.gnu.org/copyleft/gpl.html
#
# 20101118 raf <r...@raf.org>
def win32only_password_protect_xls(filename, password, orig_password=None, quit_excel_afterwards=False):
'''Tell Excel on Windows to save the spreadsheet that has the given absolute filename
with password protection using the given password. If the orig_password parameter is
supplied, it is used when opening the original spreadsheet. Doing so enables changing
an existing password. If quit_excel_afterwards is True, then Excel will be told to quit
afterwards. On platforms other than Windows, this function does nothing.
Warning: This should presumably work with either early or late COM binding but for reasons
I don't understand it only works with early binding. With late/dynamic binding, this runs
without error but the resulting spreadsheet is not password-protected. However, after
running makepy.py for the Microsoft Excel library, this code works correctly (only tested
on Windows 2000/XP). The easy way to run makepy.py is via the PythonWin aplication:
PythonWin app
-> "Tools" menu
-> "COM MakePy utility" menu item
-> "Microsoft Excel * Object Library (*)" list item
-> "OK" button
'''
# Import the necessary modules (this will fail on other platforms)
try: import os, win32com.client
except ImportError: return
# Convert / to \ in filename otherwise Open() doesn't work
filename = filename.replace('/', '\\')
# Create a temporary filename that isn't already in use
for i in xrange(10000):
dot = filename.rindex('.')
temp_filename = '%s-%s%s' % (filename[:dot], i, filename[dot:])
try: os.stat(temp_filename)
except OSError, e: break
# Tell Excel to SaveAs with password protection to a (hopefully) unused
# filename so as to avoid the confirmation dialog that would happen if
# the destination file already existed. Can that be automated too?
xl = win32com.client.Dispatch("Excel.Application")
# Tell Excel to be invisible if we are launching it but ignore any
# error that might result if Excel is already running. I haven't
# seen this error happen for that reason, though, but it was suggested
# as a possible reason on the web. I've only seen it happen when running
# the python script over an ssh/cygwin login to a remote Windows host
# and nonw of the COM calls worked in that context.
# When Excel is already running, this code makes its window disappear
# which might not be desirable but I'd prefer that to making Excel
# visible if it wasn't already running.
try: xl.Visible = 0
except AttributeError: pass
xl.Workbooks.Open(Filename=filename, Password=orig_password)
xl.ActiveWorkbook.SaveAs(Filename=temp_filename, Password=password)
xl.ActiveWorkbook.Close(SaveChanges=0)
if quit_excel_afterwards: xl.Quit()
# Finally replace the original file with the new password-protected file
os.unlink(filename)
os.rename(temp_filename, filename)
if __name__ == '__main__':
import sys
try:
win32only_password_protect_xls(sys.argv[1], sys.argv[2])
except IndexError, e:
print('usage: python %s absolute-filename.xls password' % sys.argv[0])
# vi:set ts=4 sw=4:
python...@raf.org
> What's wrong with zipping the XLS up with Win-ZIP (or better yet, 7Zip,
> it's free) with AES-256 genuine meaningful encryption of the whole file?
> That way you don't have to do any pointless work either :-)
i'd need to use python's zipfile module rather than windows
applications because this needs to be completely automated.
and i assumed that zip password "protection" was equally
pointless since it predates AES-256 by several decades.
the zipfile module's documentation doesn't mention what
kind of encryption is used but it does say:
ZIP supports a password-based form of encryption. Even though known
plaintext attacks have been found against it, it is still useful
to be able to get data out of such a file.
so it's not using AES-256.
so, given the choice between two pointless password "protection"
schemes i thought the best one was the one that required less
software on the part of the recipient.
i could probably use COM if 7Zip is up for it.
cheers,
raf
python...@raf.org
the zipfile module can only "decrypt" existing zip files.
it can't "encrypt" them. so unless 7Zip functionality is
accessible via COM or the command line, then zipfiles
aren't an option for me. ah, p7zip is the command line
version (or 7za.exe) so that would be fine.
cheers,
raf
Eduardo Silva
>
> so, given the choice between two pointless password "protection"
> schemes i thought the best one was the one that required less
> software on the part of the recipient.
>
> i could probably use COM if 7Zip is up for it.
>
> cheers,
> raf
>
[My comments] 7zip can be run from the command line.
Cheers,
Eduardo
Chris Withers
> i'm just satisfying a bizarre client request. they refuse to
> download the file securely via https and want it emailed to
> them with password "protection" and i'm sure they couldn't
> cope with gnupg.
What OS are you on?
For a similar situation, I've .zip'ed files by shelling out to the unix
zip command (using execute: http://packages.python.org/execute/use.html)
and used zip's password encrytion.
It's also weak and rubbish, but easier to do ;-)
cheers,
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk