Unable to read excel - Key error in coordinate_to_tuple
openpyxl version: 3.1.2
When I try to import an excel generated by a web application I get a KeyError: ''
error, and I have isolated the problem in the openpyxl library that create a touple out of the coordinates.
import openpyxl
excelfile = openpyxl.load_workbook('CA0123.xlsx') # Error happens here
sheet = excelfile['Ordre - Med produktmetadata']
sheet.delete_rows(1, amount=2)
excelfile.save(filename.name)
Traceback
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
c:\Users\no284835\Documents\GitHub\team-product-analytics\analytics\unique sku count per variant\unique-sku-count.ipynb Cell 5 line 3
1 import openpyxl
2 print(openpyxl.__version__)
----> 3 excelfile = openpyxl.load_workbook('CA0223.xlsx')
4 sheet = excelfile['Ordre - Med produktmetadata']
5 sheet.delete_rows(1, amount=2)
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\openpyxl\reader\excel.py:346, in load_workbook(filename, read_only, keep_vba, data_only, keep_links, rich_text)
316 """Open the given filename and return the workbook
317
318 :param filename: the path to open or a file-like object
(...)
342
343 """
344 reader = ExcelReader(filename, read_only, keep_vba,
345 data_only, keep_links, rich_text)
--> 346 reader.read()
347 return reader.wb
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\openpyxl\reader\excel.py:301, in ExcelReader.read(self)
299 apply_stylesheet(self.archive, self.wb)
300 action = "read worksheets"
--> 301 self.read_worksheets()
302 action = "assign names"
303 self.parser.assign_names()
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\openpyxl\reader\excel.py:237, in ExcelReader.read_worksheets(self)
235 ws._rels = rels
236 ws_parser = WorksheetReader(ws, fh, self.shared_strings, self.data_only, self.rich_text)
--> 237 ws_parser.bind_all()
239 # assign any comments to cells
240 for r in rels.find(COMMENTS_NS):
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\openpyxl\worksheet\_reader.py:465, in WorksheetReader.bind_all(self)
464 def bind_all(self):
--> 465 self.bind_cells()
466 self.bind_merged_cells()
467 self.bind_hyperlinks()
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\openpyxl\worksheet\_reader.py:368, in WorksheetReader.bind_cells(self)
367 def bind_cells(self):
--> 368 for idx, row in self.parser.parse():
369 for cell in row:
370 style = self.ws.parent._cell_styles[cell['style_id']]
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\openpyxl\worksheet\_reader.py:167, in WorkSheetParser.parse(self)
165 element.clear()
166 elif tag_name == ROW_TAG:
--> 167 row = self.parse_row(element)
168 element.clear()
169 yield row
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\openpyxl\worksheet\_reader.py:303, in WorkSheetParser.parse_row(self, row)
299 if keys - {'r', 'spans'}:
300 # don't create dimension objects unless they have relevant information
301 self.row_dimensions[str(self.row_counter)] = attrs
--> 303 cells = [self.parse_cell(el) for el in row]
304 return self.row_counter, cells
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\openpyxl\worksheet\_reader.py:303, in <listcomp>(.0)
299 if keys - {'r', 'spans'}:
300 # don't create dimension objects unless they have relevant information
301 self.row_dimensions[str(self.row_counter)] = attrs
--> 303 cells = [self.parse_cell(el) for el in row]
304 return self.row_counter, cells
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\openpyxl\worksheet\_reader.py:202, in WorkSheetParser.parse_cell(self, element)
199 value = element.findtext(VALUE_TAG, None) or None
201 if coordinate:
--> 202 row, column = coordinate_to_tuple(coordinate)
203 self.col_counter = column
204 else:
File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\openpyxl\utils\cell.py:202, in coordinate_to_tuple(coordinate)
200 col = coordinate[:idx].upper()
201 row = coordinate[idx:]
--> 202 return int(row), _COL_STRING_CACHE[col]
KeyError: ''
I also get the warning regarding that the workbook contains no default style.
UserWarning: Workbook contains no default style, apply openpyxl's default warn("Workbook contains no default style, apply openpyxl's default")
I thought it could be a problem with the sheet name as it contains a -
, but since the error occurs before selecting the sheet I suspect it has nothing to do with it. However, I did rename the sheet manually and this worked. I renamed the sheet back to the original and it also worked after that.
Therefore I suspect its a problem with the excel file, something that happens on save that the web application does not apply to the excel when its generated.
Is there anything I can do to check what this might be? I am having difficulties troubleshooting this.
Unfortunately I am unable to share the excel sheet as it contains sensitive company data.