从密码保护的Excel文件到Python对象 [英] From Password Protected Excel File to Python Object

问题描述

我正在使用Windows 7，Python 2.7和Microsoft Excel 2013。

我从这里，我可以打开并访问受密码保护的Excel表格以下示例代码：

  import sys 
 import win32com.client 
 xlApp = win32com.client.Dispatch （Excel.Application）
打印Excel库版本：，xlApp.Version 
文件名，密码= sys.argv [1：3] 
 xlwb = xlApp.Workbooks.Open文件名，密码=密码）
＃xlwb = xlApp.Workbooks.Open（filename）
 xlws = xlwb.Sheets（1）＃从1开始，不是从0 
打印xlws.Name 
打印xlws.Cells（1，1）＃这是A1 
  

我想保存Excel工作表从受密码保护的文件作为Python对象。理想情况下，它将被保存为一个大熊猫数据框，但我可以将其作为字典或任何其他对象类型。

我有密码。这可能吗？

谢谢！

解决方案

将以下行添加到现有代码中（其中xlwb已经存在）：

  import os 
导入pandas作为pd 
从tempfile导入NamedTemporaryFile 
 
＃创建一个可访问的临时文件，然后将其删除。我们只需要有效的路径。 
f = NamedTemporaryFile（delete = False，suffix ='。csv'）
 f.close（）
 os.unlink（f.name）＃不删除将导致文件已存在警告
 
 xlCSVWindows = 0x17＃CSV文件格式，从枚举XlFileFormat 
 xlwb.SaveAs（Filename = f.name，FileFormat = xlCSVWindows）＃将工作簿另存为CSV 
 df = pd.read_csv（f.name）＃从Pandas中读取CSV 
打印df 
  

请记住，对我来说，您的代码没有完全正常工作，并且我被提示输入密码。但是假设你设法读取受密码保护的文件，上面的代码可以工作。

Excel保存参考： https://msdn.microsoft.com/en-us/library/bb214129（v = office.12）.aspx

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!

解决方案

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

这篇关于从密码保护的Excel文件到Python对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！