Load a worksheet with openpyxl and ignore other worksheet which contains pivot table

user_4122928 0
Viewed 1073

I have an .xlsx file which contains 2 worksheets. The first one contains regular data (nothing fancy), while the second one contains pivot tables. I need only the data form the first worksheet and I want to ignore the second one, but the pivot tables raise error: TypeError: expected <type 'basestring'> when openpyxl.load_workbook is called.

The error is raised in: openpyxl/reader/excel.py, in line: pivot_caches = parser.pivot_caches.

I tried with openpyxl versions 2.6.4 and 2.5.1. I'm using Python 2.7.

After deleting the 2nd worksheet, the error is gone and the data from the 1sth worksheet is read correctly. However, these files are uploaded by users and although I don't need the pivot tables, I would like to avoid forcing users to remove the unnecessary worksheet(s), if possible.

Sample code:

from io import BytesIO

import openpyxl

pivot = '~/Downloads/file_with_pivot_tables.xlsx'

with open(pivot) as fin:
    content = BytesIO(fin.read())
    wb = openpyxl.load_workbook(content)  # this line fails
    ws = wb.get_sheet_by_name('Sheet1')

Entire error trace:

  File "/Users/gi/lib/openpyxl/reader/excel.py", line 224, in load_workbook
    pivot_caches = parser.pivot_caches
  File "/Users/gi/lib/openpyxl/packaging/workbook.py", line 125, in pivot_caches
    cache = get_rel(self.archive, self.rels, id=c.id, cls=CacheDefinition)
  File "/Users/gi/lib/openpyxl/packaging/relationship.py", line 162, in get_rel
    obj.deps = get_dependents(archive, rels_path)
  File "/Users/gi/lib/openpyxl/packaging/relationship.py", line 130, in get_dependents
    rels = RelationshipList.from_tree(node)
  File "/Users/gi/lib/openpyxl/descriptors/serialisable.py", line 84, in from_tree
    obj = desc.expected_type.from_tree(el)
  File "/Users/gi/lib/openpyxl/descriptors/serialisable.py", line 100, in from_tree
    return cls(**attrib)
  File "/Users/gi/lib/openpyxl/packaging/relationship.py", line 50, in __init__
    self.Target = Target
  File "/Users/gi/lib/openpyxl/descriptors/base.py", line 44, in __set__
    raise TypeError('expected ' + str(self.expected_type))
TypeError: expected <type 'basestring'>
openpyxl xlsx python-2.7 excel python
1 Answers

You can specify the sheet that you want to manipulate:

wb = openpyxl.load_workbook('H:\\myfile.xlsx')
ws = wb['sheet1']
ws['E1'] = 'The sky is gray.'
wb.save('H:\\myfile.xlsx')
wb.close()

You can also get a list of all the sheet names if you need to check them first:

print(wb.sheetnames)
user_111040950
Related