Pandas keep existing format of Excel

[plonkarchivist]

When using pandas to write data back to an Excel file, you can lose the formatting because the default behavior is to only write the data, not the formatting information. To preserve the formatting, you can use the openpyxl library in conjunction with pandas. This library allows you to load an existing workbook, modify its content, and save it, preserving the original formatting. doodle baseball

#!/usr/bin/env Python3

# Imports
import pandas as pd
import openpyxl
import random
import sys

def main():
    # Load Information From Excel
    df = pd.read_excel('config/Mappe1.xlsx', engine='openpyxl')

    try:
        # Create List
        random_names = []

        # Get Random Task Entries
        for i in [{"description": "Value Below 800", "start_number": 0, "end_number": 800, "amount": 2}, {"description": "Value Above 800", "start_number": 801, "end_number": 10000, "amount": 1}]:
            # Criteria Are Checked
            entries = df.query(f"`Category` == 'Tasks' and `Status` == 'Open' and {i['start_number']} <= `Value` <= {i['end_number']}")[['Name', 'Value']].values.tolist()

            # Select Random Names and Append to List
            selected_entries = random.sample(entries, i['amount'])
            random_names.extend(selected_entries)

        # Change Information in DataFrame
        for name, follower in random_names:
            df.loc[df['Name'] == name, 'Status'] = 'Done'

        # Save Information to Excel with openpyxl
        with pd.ExcelWriter('config/Mappe1.xlsx', engine='openpyxl') as writer:
            writer.book = openpyxl.load_workbook('config/Mappe1.xlsx')
            df.to_excel(writer, sheet_name='Sheet1', index=False)

    except Exception as e:
        print(e)

# Start Main Function
main()

# End
sys.exit()