Search code examples
pythonpandas.excelwriter

ExcelWriter save function not saving immediately to disc

the following code executes in Jupyter successfully, however I am still waiting for the output.xlsx to arrive on my desktop.

import pandas as pd
import xlsxwriter

# Create a DataFrame with the column values
data = {'Values': [397, 358, 412]}
df = pd.DataFrame(data)

# Create a writer using ExcelWriter
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')

# Write the DataFrame to Excel
df.to_excel(writer, sheet_name='Sheet1', index=False)

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

# Define the formats for conditional formatting
green_format = workbook.add_format({'bg_color': '#00B050'})
yellow_format = workbook.add_format({'bg_color': '#FFFF00'})
orange_format = workbook.add_format({'bg_color': '#FFC000'})
red_format = workbook.add_format({'bg_color': '#FF0000'})

# Apply conditional formatting based on cell values
worksheet.conditional_format('A2:A4', {'type': 'cell','criteria': '>=','value': 391,'format': green_format})

worksheet.conditional_format('A2:A4', {'type': 'cell','criteria': 'between','minimum': 354,'maximum': 390,
                                       'format': yellow_format})

worksheet.conditional_format('A2:A4', {'type': 'cell','criteria': 'between', 'minimum': 293,'maximum': 353,
                                       'format': orange_format})

worksheet.conditional_format('A2:A4', {'type': 'cell','criteria': '<=','value': 292,'format': red_format})

# Save the workbook
writer.save()

The cell above was executed and the file was created at 9:35 today. It is currently 9:50 and I am still waiting for my file.

When I close the Jupyter app in Windows, the file is then accessible..

Any help would be appreciated.

enter image description here

Solution

  • Change the code to use ExcelWriter as a context manager instead. That's what the documentation examples show:

    with pd.ExcelWriter('output.xlsx', engine='xlsxwriter') as writer:

    # Write the DataFrame to Excel
    df.to_excel(writer, sheet_name='Sheet1', index=False)

    ...
    worksheet.conditional_format('A2:A4', {'type': 'cell','criteria': '<=','value': 292,'format': red_format})

    This ensures that the data is saved and the ExcelWriter object is closed when its block terminates