How to export a Dataframe to Excel with divider lines using XlsxWriter

Question

I would like to export my df to excel using xlswriter but with format depending on the value of specific cells ie i have a df that look like that :

Date Data1 data2
01/01/1979 50.0. 10.0
01/01/1979 50.0. 11.0
02/01/1979 50.0. 11.0
02/01/1979 50.0. 11.0
02/01/1979 50.0. 11.0
03/01/1979 50.0. 11.0
03/01/1979 50.0. 11.0

From a df like that I would like to have an excel file that look like that :

Date Data1 data2
01/01/1979 50.0. 10.0
01/01/1979 50.0. 11.0
————————————————————-
02/01/1979 50.0. 11.0
02/01/1979 50.0. 11.0
02/01/1979 50.0. 11.0
————————————————————-
03/01/1979 50.0. 11.0
03/01/1979 50.0. 11.0
————————————————————-

That adds a line to all rows when the date change.

Where are your achievements? – Сергей Кох Aug 02 '22 at 10:54 — Сергей Кох, Aug 02 '22 at 10:54

Ibrahim Ayoup · Answer 1 · 2022-08-02T11:09:53.573

Below script adds line to each row when Date is changed :

Please change delimiter variable to whatever delimiter you desire.

delimiter = '-----'

def f(x):
    return x.append(pd.DataFrame(delimiter, columns=df.columns, index=[('')]))

df_updated = df.groupby('Date', sort=False, group_keys=False).apply(f)

Output:

    Date        Data1   data2
0   1979-01-01  50.0.   10
1   1979-01-01  50.0.   11
    -----       -----   -----
2   1979-01-02  50.0.   11
3   1979-01-02  50.0.   11
4   1979-01-02  50.0.   11
    -----       -----   -----
5   1979-01-03  50.0.   11
6   1979-01-03  50.0.   11
    -----       -----   -----

Thx but not exactly what I need… I would like to use xlswriter api to write in the excel file — Jacques Tebeka, Aug 02 '22 at 12:16

score 0 · Answer 2 · answered Aug 03 '22 at 11:18

One way to do it would be to use a conditional format in Excel via XlsxWriter. Something like this:

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Date': ['01/01/1979', '01/01/1979', '02/01/1979',
                            '02/01/1979', '02/01/1979', '03/01/1979',
                            '03/01/1979'],
                   'Data1': [50.0] * 7,
                   'Data2': [11.0] * 7})

df = df[['Date', 'Data1', 'Data2']]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1', index=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Make column A wider for clarity.
worksheet.set_column(0, 0, 12)

# Get the dimensions of the dataframe.
(max_row, max_col) = df.shape

# Add a format to use in the conditional format.
format1 = workbook.add_format({'bottom': 1})

# Apply a conditional format to the required cell range.
worksheet.conditional_format(1, 0, max_row, max_col -1,
                             {'type':     'formula',
                              'criteria': '=$A2<>$A3',
                              'format':   format1})


# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

Note, you may want to convert the data strings to actual DateTime objects.

How to export a Dataframe to Excel with divider lines using XlsxWriter

2 Answers2