从密码保护的Excel文件到大 pandas DataFrame [英] From password-protected Excel file to pandas DataFrame

问题描述

我可以通过以下方式打开受密码保护的Excel文件：

I can open a password-protected Excel file with this:

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'm not sure though how to transfer the information to a pandas dataframe. Do I need to read cells one by one and all, or is there a convenient method for this to happen?

推荐答案

假设起始单元格作为（StartRow，StartCol）给出，结尾单元格给出为（EndRow，EndCol），我发现以下内容对我有用：

Assuming the starting cell is given as (StartRow, StartCol) and the ending cell is given as (EndRow, EndCol), I found the following worked for me:

# 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))

注意：Excel单元格B5作为第5行，第2列在win32com中给出。另外，我们需要将列表（...）从元组元组转换为元组列表，因为元组元组没有pandas.DataFrame构造函数。

Note: Excel Cell B5 is given as row 5, col 2 in win32com. Also, we need list(...) to convert from tuple of tuples to list of tuples, since there is no pandas.DataFrame constructor for a tuple of tuples.

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