从受密码保护的Excel文件到pandas DataFrame

Question

从受密码保护的Excel文件到pandas DataFrame

19

我可以使用以下方法打开一个受密码保护的Excel文件：

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

我不确定如何将信息转换为 Pandas 数据框。我需要一个一个地读取单元格吗，还是有一种便捷的方法可以实现这一点？

- dmvianna

xlws有Row和RowCount(或者叫其他名字)吗？如果有，那么循环遍历行数并构建一个列表的列表...然后在此基础上使用pandas.DataFrame...(抱歉-我不用Windows-所以无法自己尝试) - Jon Clements

6个回答

7

以下内容来自David Hamann的网站(所有功劳归于他) https://davidhamann.de/2018/02/21/read-password-protected-excel-files-into-pandas-dataframe/

使用xlwings，打开文件会先启动Excel应用程序，以便您可以输入密码。

import pandas as pd
import xlwings as xw

PATH = '/Users/me/Desktop/xlwings_sample.xlsx'
wb = xw.Book(PATH)
sheet = wb.sheets['sample']

df = sheet['A1:C4'].options(pd.DataFrame, index=False, header=True).value
df

- Maurice

看起来 xw.Book() 中有一个 password 参数（也许自此答案发布以来，API 已经添加了此功能）。https://docs.xlwings.org/en/stable/api.html?highlight=password#book - Leo

6

假设起始单元格为（StartRow，StartCol），结束单元格为（EndRow，EndCol），我发现以下方法适用于我：

# Get the content in the rectangular selection region
# content is a tuple of tuples
content = xlws.Range(xlws.Cells(StartRow, StartCol), xlws.Cells(EndRow, EndCol)).Value 

# Transfer content to pandas dataframe
dataframe = pandas.DataFrame(list(content))

注意：在win32com中，Excel单元格B5被视为行5、列2。此外，我们需要使用list(...)将元组的元组转换为元组列表，因为没有适用于元组的pandas.DataFrame构造函数。

- ikeoddy

你还可以使用字母来表示范围，例如：xlws.Range("A1:H100").Value。 - SAKURA

3

根据@ikeoddy提供的建议，这将把所有部分组合起来：

如何使用Python打开受密码保护的Excel文件？

# Import modules
import pandas as pd
import win32com.client
import os
import getpass

# Name file variables
file_path = r'your_file_path'
file_name = r'your_file_name.extension'

full_name = os.path.join(file_path, file_name)
# print(full_name)

在Python中获取命令行密码输入

# You are prompted to provide the password to open the file
xl_app = win32com.client.Dispatch('Excel.Application')
pwd = getpass.getpass('Enter file password: ')

Workbooks.Open Method (Excel)

xl_wb = xl_app.Workbooks.Open(full_name, False, True, None, pwd)
xl_app.Visible = False
xl_sh = xl_wb.Worksheets('your_sheet_name')

# Get last_row
row_num = 0
cell_val = ''
while cell_val != None:
    row_num += 1
    cell_val = xl_sh.Cells(row_num, 1).Value
    # print(row_num, '|', cell_val, type(cell_val))
last_row = row_num - 1
# print(last_row)

# Get last_column
col_num = 0
cell_val = ''
while cell_val != None:
    col_num += 1
    cell_val = xl_sh.Cells(1, col_num).Value
    # print(col_num, '|', cell_val, type(cell_val))
last_col = col_num - 1
# print(last_col)

ikeoddy的回答：

content = xl_sh.Range(xl_sh.Cells(1, 1), xl_sh.Cells(last_row, last_col)).Value
# list(content)
df = pd.DataFrame(list(content[1:]), columns=content[0])
df.head()

Python Win32 COM关闭Excel工作簿

xl_wb.Close(False)

- datalifenyc

不要手动计算行数和列数，只需使用 xl_sh.UsedRange.Rows.Count 和 xl_sh.UsedRange.Columns.Count。 - mhc

2

假设您可以使用win32com API将加密文件保存回磁盘（我意识到这可能会破坏目的），然后立即调用顶层pandas函数read_excel。但首先您需要安装一些组合的软件包，如xlrd（用于Excel 2003），xlwt（也用于2003）和openpyxl（用于Excel 2007）。这里是读取Excel文件的文档。目前，pandas不支持使用win32com API读取Excel文件。如果您愿意，可以打开GitHub问题。

- Phillip Cloud

我无法再进行测试，因为我目前没有能够这样做的工作环境。但是，如果您提供示例代码并向我保证其有效性，我将标记此或任何其他答案为已接受。 :7) - dmvianna

我不知道如何使用win32com API，所以你需要自己弄清楚，但是如果你查看我提供链接的文档，它会给出做你想要的事情的指示。这里没有必要复制示例代码，因为你可以在那里阅读它。 - Phillip Cloud

2

在@Maurice的回答中添加，可以获取工作表中的所有单元格而无需指定范围

wb = xw.Book(PATH, password='somestring')
sheet = wb.sheets[0] #get first sheet

#sheet.used_range.address returns string of used range
df = sheet[sheet.used_range.address].options(pd.DataFrame, index=False, header=True).value

- LouF

属性错误：'<win32com.gen_py.Microsoft Excel 16.0 Object Library._Worksheet instance at 0x2501460928384>' 对象没有 'used_range' 属性。 - Mehdi

我最近遇到了类似的错误，但随后我不得不升级到Office 365，所以这可能是我的问题。这里的代码使用的是Office 2016。 - LouF

也许，我也在使用Office 365。 - Mehdi

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Suhas_Pote · Accepted Answer

简单解决方案

import io
import pandas as pd
import msoffcrypto

passwd = 'xyz'

decrypted_workbook = io.BytesIO()
with open(path_to_your_file, 'rb') as file:
    office_file = msoffcrypto.OfficeFile(file)
    office_file.load_key(password=passwd)
    office_file.decrypt(decrypted_workbook)

df = pd.read_excel(decrypted_workbook, sheet_name='abc')

pip install --user msoffcrypto-tool

将目录和子目录中每个Excel文件的所有工作表导出为单独的CSV文件

from glob import glob
PATH = "Active Cons data"

# Scaning all the excel files from directories and sub-directories
excel_files = [y for x in os.walk(PATH) for y in glob(os.path.join(x[0], '*.xlsx'))] 

for i in excel_files:
    print(str(i))
    decrypted_workbook = io.BytesIO()
    with open(i, 'rb') as file:
        office_file = msoffcrypto.OfficeFile(file)
        office_file.load_key(password=passwd)
        office_file.decrypt(decrypted_workbook)

    df = pd.read_excel(decrypted_workbook, sheet_name=None)
    sheets_count = len(df.keys())
    sheet_l = list(df.keys())  # list of sheet names
    print(sheet_l)
    for i in range(sheets_count):
        sheet = sheet_l[i]
        df = pd.read_excel(decrypted_workbook, sheet_name=sheet)
        new_file = f"D:\\all_csv\\{sheet}.csv"
        df.to_csv(new_file, index=False)