How can I open an Excel file in Python?
Edit:
In the newer version of pandas, you can pass the sheet name as a parameter.
file_name = # path to file + file namesheet = # sheet name or sheet number or list of sheet numbers and namesimport pandas as pddf = pd.read_excel(io=file_name, sheet_name=sheet)print(df.head(5)) # print first 5 rows of the dataframe
Check the docs for examples on how to pass sheet_name
:
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html
Old version:
you can use pandas
package as well....
When you are working with an excel file with multiple sheets, you can use:
import pandas as pdxl = pd.ExcelFile(path + filename)xl.sheet_names>>> [u'Sheet1', u'Sheet2', u'Sheet3']df = xl.parse("Sheet1")df.head()
df.head()
will print first 5 rows of your Excel file
If you're working with an Excel file with a single sheet, you can simply use:
import pandas as pddf = pd.read_excel(path + filename)print df.head()
Try the xlrd library.
[Edit] - from what I can see from your comment, something like the snippet below might do the trick. I'm assuming here that you're just searching one column for the word 'john', but you could add more or make this into a more generic function.
from xlrd import open_workbookbook = open_workbook('simple.xls',on_demand=True)for name in book.sheet_names(): if name.endswith('2'): sheet = book.sheet_by_name(name) # Attempt to find a matching row (search the first column for 'john') rowIndex = -1 for cell in sheet.col(0): # if 'john' in cell.value: break # If we found the row, print it if row != -1: cells = sheet.row(row) for cell in cells: print cell.value book.unload_sheet(name)
This isn't as straightforward as opening a plain text file and will require some sort of external module since nothing is built-in to do this. Here are some options:
If possible, you may want to consider exporting the excel spreadsheet as a CSV file and then using the built-in python csv module to read it: