How to load win32com Excel worksheet to Pandas df?

user_14366102 0
Viewed 1174

I have the following code:

import pandas as pd
import win32com.client

excel_app = win32com.client.Dispatch("Excel.Application")
file_path = r"path to the file"
file_password = "file password"
workbook = excel_app.Workbooks.Open(file_path, Password=file_password)
sheet = workbook.Sheets("sheet name")

Now I'd like to take the sheet variable and load it into a Pandas df. I was trying to accomplish it via saving the sheet to a separate file and then reading it from Pandas, but it seems to be over-complicating the issue, as the file is both password protected and in .xlsm format, so re-opening it directly from Pandas isn't straightforward.

How do I do it?

pandas excel python
1 Answers

The UsedRange property of the sheet will return an array that encompasses all the cells in the worksheet that have data.

df = pd.DataFrame(sheet.UsedRange())

With the column headers as the column number, and the index as the row number. Both zero-based.

user_138129820
Related