3

I am using Windows 7, Python 2.7 and Microsoft Excel 2013.

I know from here that I can open and access a password protected Excel sheet using the below sample code:

import sys
import win32com.client
xlApp = win32com.client.Dispatch("Excel.Application")
print "Excel library version:", xlApp.Version
filename, password = sys.argv[1:3]
xlwb = xlApp.Workbooks.Open(filename, Password=password)
# xlwb = xlApp.Workbooks.Open(filename)
xlws = xlwb.Sheets(1) # counts from 1, not from 0
print xlws.Name
print xlws.Cells(1, 1) # that's A1

I would like to save an Excel worksheet from a password protected file as a Python object. Ideally it would be saved as a pandas dataframe but I would be OK to have it as a dictionary or any other object type.

I have the password. Is this possible?

Thanks!

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
BeeGee
  • 815
  • 2
  • 17
  • 33
  • 1
    How about writing it to a temporary file (with no password) and then reading it from pandas? You don't even need it to be a physical file, python can give you a file buffer object without actually writing to disk (see StringIO) – Shovalt Apr 25 '16 at 21:59

1 Answers1

4

Add the following lines to your existing code (where xlwb already exists):

import os
import pandas as pd
from tempfile import NamedTemporaryFile

# Create an accessible temporary file, and then delete it. We only need a valid path.
f = NamedTemporaryFile(delete=False, suffix='.csv')  
f.close()
os.unlink(f.name)  # Not deleting will result in a "File already exists" warning

xlCSVWindows = 0x17  # CSV file format, from enum XlFileFormat
xlwb.SaveAs(Filename=f.name, FileFormat=xlCSVWindows)  # Save the workbook as CSV
df = pd.read_csv(f.name)  # Read that CSV from Pandas
print df

Bear in mind that for me your code didn't fully work, and I got prompted for a password. But assuming you do manage to read a password protected file, the code above works.

Excel SaveAs reference: https://msdn.microsoft.com/en-us/library/bb214129(v=office.12).aspx

Shovalt
  • 6,407
  • 2
  • 36
  • 51
  • I tried your code, but he line : xlwb.SaveAs(Filename=f.name, FileFormat=xlCSVWindows) rise the error : '-2147417848, 'The object invoked has disconnected from its clients.' – Sylvain Sep 15 '16 at 03:11
  • @Sylvain Did you initialize `xlwb` as described in the question? Try printing the name and first cell as in the question. Also, did you make sure to use `delete=False`? I'm just throwing guesses here but let's see where we stand on those. – Shovalt Sep 15 '16 at 08:37
  • @Shovalt Hi!What if I want to do it with a xlsx file. What will change then? – DimKoim Sep 18 '17 at 12:43
  • @DimKoim I'll make a guess: Change the suffix in the temp file to '.xlsx'; define a variable `xlOpenXMLWorkbook = 0x51` (this is the xlsx file format constant, as documented in: https://msdn.microsoft.com/en-us/library/bb241279(v=office.12).aspx); change `FileFormat=xlOpenXMLWorkbook` in the `SaveAs` command. Let us know if it works, and I can add it to the answer. – Shovalt Sep 18 '17 at 13:22
  • @Shovalt I am having this error: com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'SaveAs method of Workbook class failed', 'xlmain11.chm', 0, -2146827284), None) and it is in the FileFormat parameter. – DimKoim Sep 18 '17 at 13:33
  • @DimKoim you can try other file formats listed in the link I gave in the previous comment. Other than that, I do not know why this error occurs as I have not tried the code with this modification. – Shovalt Sep 18 '17 at 13:42