Empty second row when using dataframe_to_rows()
When I try to write a simple DataFrame to an openpyxl sheet, an empty second row is inserted.
from pprint import pprint
import numpy as np
import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
df = pd.DataFrame(np.random.randint(0, 100, size=(20, 4)), columns=list("ABCD"))
pprint(df)
openpyxl_wb = Workbook()
openpyxl_wb.remove(openpyxl_wb["Sheet"])
df_sheet = openpyxl_wb.create_sheet("DataFrame")
for r in dataframe_to_rows(df):
df_sheet.append(r)
# df_sheet.delete_rows(2)
openpyxl_wb.save("empty_second_row.xlsx")
pprint(df.head())
I found both this mail conversation and Stackoverflow post regarding this issue:
- https://groups.google.com/g/openpyxl-users/c/N9QpvfzkJIM?pli=1
- https://stackoverflow.com/questions/51127382/empty-row-when-exporting-dataframe-with-openpyxl
There, you said
I think this depends a bit upon the version you're using and also whether you have indices visible or not. When adding support for multiple indices I went for an explicit structure that matches what df.head() will show as opposed to trying to "get it right".
df.head() doesn't show an empty row and I think it'd be more intuitive if, at least for the case of a simple DataFrame with indices and headers, there was no empty row when converting it to rows.
Edited by Alexander Wilms