Untitled

from docx.shared import Pt
from docx.oxml import parse_xml
from docx.oxml.ns import nsdecls
from datetime import datetime

# Function to format the date in words
def format_date(date):
    return date.strftime('%B %-d, %Y')

# Function to format currency
def format_currency(amount):
    if isinstance(amount, float) or isinstance(amount, int):
        return "${:,.2f}".format(amount)
    return amount

# Re-create the Word document with the updated formatting
doc = Document()
doc.add_heading('Company Credit Card Payments', level=1)

# Adding the table
table = doc.add_table(rows=1, cols=len(excel_data.columns))
table.style = 'Table Grid'

# Adding header row
for i, column_name in enumerate(excel_data.iloc[1]):
    hdr_cells = table.rows[0].cells
    hdr_cells[i].text = str(column_name)
    apply_cell_shading(hdr_cells[i], 'D9EAD3')  # Light green color for headers

# Adding the rest of the data with updated formatting
for index, row in excel_data.iloc[2:].iterrows():
    row_cells = table.add_row().cells
    for i, value in enumerate(row):
        # Format dates and currency properly
        if isinstance(value, datetime):
            row_cells[i].text = format_date(value)
        elif (i == 1 or i == 3 or i == 4) and (isinstance(value, float) or isinstance(value, int)):
            row_cells[i].text = format_currency(value)
        else:
            row_cells[i].text = str(value)

        # Apply color coding based on conditions
        if i == 1 and isinstance(value, float) and value > 10000:  # High balance in red
            apply_cell_shading(row_cells[i], 'F4CCCC')
        elif i == 1 and isinstance(value, float):  # Lower balance in yellow
            apply_cell_shading(row_cells[i], 'FFF2CC')

# Save the document with the updated formatting
updated_output_path = '/mnt/data/Updated_Company_Credit_Card_Payments_Document.docx'
doc.save(updated_output_path)
updated_output_path