Untitled

from docx import Document
from openpyxl import load_workbook
from datetime import datetime
import pandas as pd
from docx.oxml.ns import nsdecls
from docx.oxml import parse_xml

# Helper functions
def is_category_header(row):
    return row[0] and row[1] is None and row[2] is None

def is_sub_header(row):
    return row[0] == "Name" and row[1] == "Amount" and row[2] == "Date"

def is_sum_row(row):
    return isinstance(row[1], str) and row[1].startswith('=SUM')

def format_date(date):
    return date.strftime('%B %-d, %Y')

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

def apply_enhanced_cell_shading(cell, shade):
    shading_elm_2 = parse_xml(r'<w:shd {} w:fill="{}"/>'.format(nsdecls('w'), shade))
    cell._tc.get_or_add_tcPr().append(shading_elm_2)

# Load the new Excel data
new_excel_file_path = '/mnt/data/January_Payments_2024_1704226205.xlsx'
new_workbook = load_workbook(filename=new_excel_file_path)
new_sheet = new_workbook.active
new_excel_data = pd.DataFrame(new_sheet.values)

# Process the Excel data and create the Word document
new_doc = Document()
new_doc.add_heading('January Payments 2024', level=1)

# Add categories and entries to the Word document, handling "HOME DEPOT" separately
for index, row in new_excel_data.iterrows():
    if is_category_header(row) and not is_sum_row(row):
        category_name = row[0]
        new_doc.add_heading(category_name, level=2)
        category_table = new_doc.add_table(rows=1, cols=3)
        category_table.style = 'Table Grid'
        for i, header in enumerate(["Name", "Amount", "Date"]):
            category_table.cell(0, i).text = header
            apply_enhanced_cell_shading(category_table.cell(0, i), 'D9EAD3')
    elif any(row) and not is_sub_header(row) and not is_sum_row(row):
        cells = category_table.add_row().cells
        cells[0].text = str(row[0])
        cells[1].text = format_currency(row[1]) if isinstance(row[1], (int, float)) else str(row[1])
        if isinstance(row[2], datetime):
            cells[2].text = format_date(row[2])
        else:
            cells[2].text = str(row[2])

# Save the document
new_complete_doc_path = '/mnt/data/New_Complete_January_Payments_2024_Document.docx'
new_doc.save(new_complete_doc_path)