CODEWITHSUNDEEP
python
Athena Brielle Mabuyo
Athena Brielle Mabuyo

Reputation: 1

Book has no extract_formulas attribute calling xlrd.open_workbook()

I have this code:

import xlrd

path = "C:\\Users\\m.macapanas\\Desktop\\OFCCP_Default_Values.xlsm"
excel_workbook = xlrd.open_workbook(path)
excel_worksheet = excel_workbook.sheet_by_index(0)


#Read from Excel Worksheet
print("Your Worksheet has " + str(excel_worksheet.ncols) + " columns")
print("Your Worksheet has " + str(excel_worksheet.nrows) + " rows")

for row in range (excel_worksheet.nrows):
    for col in range(excel_worksheet.ncols):
        print(excel_worksheet.cell_value(row, col), end='')
        print('\t', end='')
    print()

Then the result is error

Traceback (most recent call last): File "C:/Users/m.macapanas/IdeaProjects/OFCCP Tool/Read Excel File with Python/Pandas.py", line 4, in excel_workbook = xlrd.open_workbook(path) File "C:\Users\m.macapanas\AppData\Roaming\Python\Python36\site-packages\xlrd_init_.py", line 141, in open_workbook ragged_rows=ragged_rows, File "C:\Users\m.macapanas\AppData\Roaming\Python\Python36\site-packages\xlrd\xlsx.py", line 808, in open_workbook_2007_xml x12book.process_stream(zflo, 'Workbook') File "C:\Users\m.macapanas\AppData\Roaming\Python\Python36\site-packages\xlrd\xlsx.py", line 265, in process_stream meth(self, elem) File "C:\Users\m.macapanas\AppData\Roaming\Python\Python36\site-packages\xlrd\xlsx.py", line 392, in do_sheet sheet = Sheet(bk, position=None, name=name, number=sheetx) File "C:\Users\m.macapanas\AppData\Roaming\Python\Python36\site-packages\xlrd\sheet.py", line 326, in init self.extract_formulas = book.extract_formulas AttributeError: 'Book' object has no attribute 'extract_formulas'

Upvotes: 0

Views: 813

Answers (2)

hc_dev
hc_dev

Reputation: 9418

Issue

Analyze the error

line 4, in excel_workbook = xlrd.open_workbook(path)

Your script fails to open the workbook.

AttributeError: 'Book' object has no attribute 'extract_formulas'

The attribute-error states, it does not find extract_formulas as attribute of xlrd's Book object.

Caused by unsupported file-format .xlsx

As Nathaniel Ford's answer explained:

  • xlrd (as of current version 2.0.1) only supports older Excel file-format .xls

See also

Alternative solution

Research on Stackoverflow gave: How can I open an Excel file in Python?

Working with Excel Files in Python is a great resources-collection which lists popular libraries.

Ported to OpenPyXL

There on top: openpyxl

The recommended package for reading and writing Excel 2010 files (ie: .xlsx)

After installing using:

pip install openpyxl

Your code might be ported to this library like:

from openpyxl import load_workbook

path = "C:\\Users\\m.macapanas\\Desktop\\OFCCP_Default_Values.xlsm"

excel_workbook = load_workbook(filename = path)
excel_worksheet = excel_workbook. worksheets[0] # first worksheet


# Read from Excel Worksheet
print("Your Worksheet has " + str(excel_worksheet.ncols) + " columns")
print("Your Worksheet has " + str(excel_worksheet.nrows) + " rows")

for row in excel_worksheet.rows:
    for col in excel_worksheet.cols:
        print(excel_worksheet.cell(row, col), end='')
        print('\t', end='')
    print()

Upvotes: 1

Nathaniel Ford
Nathaniel Ford

Reputation: 21249

According to the xlrd documentation states in a warning:

This library will no longer read anything other than .xls files.

Your error is popping up when you attempt to open a workbook for the file "C:\\Users\\m.macapanas\\Desktop\\OFCCP_Default_Values.xlsm", which has a .xlsm extension.

The xlrd library explicitly doesn't support reading the newer file formats like .xlsm. So you'll either have to switch libraries or find a way to downgrade your input file to supported .xls format.

Upvotes: 2

Related Questions