ValueError: Cannot convert 'column' to Excel when converting pandas dataframe [sqlalchemy related]
Created originally on Bitbucket by mkupfer (Maxim Kupfer)
I couldn' (closed);t for the life of me figure out why I couldn' (closed);t write a dataframe to an excel workbook via the openpyxl dataframe_to_row
utility function. All I kept getting was the following error
ValueError: Cannot convert 'column_name' to Excel
I figured out the problem was that when pandas reads an sql table into memory, the columns are not of type str, rather they are stored as a low-level sqlalchemy element sqlalchemy.sql.elements.quoted_name
. Since this is a subclass of string, it was hard to tell that it wasn’t pure string since the repr and print all return just like a string does.
This is an easy fix, all you need to do is add a condition for the type being a subclass of string somewhere in [cell.py](https://bitbucket.org/openpyxl/openpyxl/src/d090796b358b0238cce5e9c71fa2dd1ccc37ccbc/openpyxl/cell/cell.py#lines-190:220) and then coerce it into a string.
[ins] In [1]: from sqlalchemy import create_engine
[ins] In [2]: eng = create_engine('sqlite://')
[ins] In [3]: df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})
[ins] In [4]: df.to_sql('users', eng)
[ins] In [5]: df = pd.read_sql('users', eng)
[ins] In [6]: cols = list(df.columns)
[ins] In [7]: cols[0]
Out[7]: 'index'
[ins] In [8]: type(cols[0])
Out[8]: sqlalchemy.sql.elements.quoted_name
[ins] In [11]: from openpyxl import Workbook
[ins] In [12]: wb = Workbook()
[ins] In [13]: from openpyxl.utils.dataframe import dataframe_to_rows
[nav] In [15]: rows = dataframe_to_rows(df)
[ins] In [16]: for row in rows:
...: wb.active.append(row)
...:
---------------------------------------------------------------------------
ValueError Traceback (most recent call last)
<ipython-input-16-4d3709b887b3> in <module>
1 for row in rows:
----> 2 wb.active.append(row)
3
~/temp/pandas-bug/venv/lib/python3.7/site-packages/openpyxl/worksheet/worksheet.py in append(self, iterable)
648 cell.row = row_idx
649 else:
--> 650 cell = Cell(self, row=row_idx, column=col_idx, value=content)
651 self._cells[(row_idx, col_idx)] = cell
652
~/temp/pandas-bug/venv/lib/python3.7/site-packages/openpyxl/cell/cell.py in __init__(self, worksheet, row, column, value, style_array)
118 self.data_type = 'n'
119 if value is not None:
--> 120 self.value = value
121 self._comment = None
122
~/temp/pandas-bug/venv/lib/python3.7/site-packages/openpyxl/cell/cell.py in value(self, value)
250 def value(self, value):
251 """Set the value and infer type and display options."""
--> 252 self._bind_value(value)
253
254 @property
~/temp/pandas-bug/venv/lib/python3.7/site-packages/openpyxl/cell/cell.py in _bind_value(self, value)
216
217 elif value is not None:
--> 218 raise ValueError("Cannot convert {0!r} to Excel".format(value))
219
220 self._value = value
ValueError: Cannot convert 'index' to Excel