empty row when exporting dataframe with openpyxl

[pyb...@byom.de]

I try to export an existing DataFrame to an Excel file using openpyxl.

import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import Workbook

d = {'col1': [1, 2], 'col2': [3, 4]}
df = pd.DataFrame(data=d)

wb = Workbook()
ws = wb.active

for rows in dataframe_to_rows(df, index=True, header=True):
    ws.append(rows)

wb.save(filename = 'test.xlsx')

When doing so, I always get an empty row in the output file after the header row.

I know I can delete the 2nd row manually, but is there no other way?

I have already tried this here - with the same result:

import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import Workbook

d = {'col1': [1, 2], 'col2': [3, 4]}
df = pd.DataFrame(data=d)

wb = Workbook()
ws = wb.active

rows = dataframe_to_rows(df)

for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)

wb.save(filename = 'test.xlsx')

[Charlie Clark]

Am .07.2018, 14:08 Uhr, schrieb <pyb...@byom.de>:

> I know I can delete the 2nd row manually, but is there no other way?

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".

If you convert the rows to a list you can simply pop the row.

rows = list(rows)
rows.pop(1)

But as dataframe_to_rows() is a generator you can always use next() to
control flow. Or itertools.islice()

This is what I do in one of my own reports where I have to format the
header:

rows = dataframe_to_rows(values)
dates = next(rows)
for col_idx, col in enumerate(dates[1:], 2):
cell = ws.cell(row=1, column=col_idx, value=col)
cell.number_format = 'd-mmm-yy'

index_labels = next(rows)
for idx, row in enumerate(rows):
ws.append(row)

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226