mail@pastecode.io avatar
2 months ago
1.9 kB
from openpyxl import load_workbook
import pandas as pd
from docx import Document
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

# Load the new Excel file
new_file_path = '/mnt/data/January_Checkbook_2024_1704220683.xlsx'
new_workbook = load_workbook(filename=new_file_path)
new_sheet = new_workbook.active

# Convert the new sheet to a DataFrame
new_excel_data = pd.DataFrame(new_sheet.values)

# Create the Word document for the new Excel data
new_doc = Document()
new_doc.add_heading('January Checkbook 2024', level=1)

# Adding the table for the new data
new_headers = new_excel_data.iloc[2]
new_table = new_doc.add_table(rows=1, cols=len(new_headers))
new_table.style = 'Table Grid'

# Adding header row for the new table
new_hdr_cells = new_table.rows[0].cells
for i, column_name in enumerate(new_headers):
    new_hdr_cells[i].text = str(column_name)

# Adding the rest of the data with updated formatting for the new table
for index, row in new_excel_data.iloc[3:].iterrows():
    new_row_cells = new_table.add_row().cells
    for i, value in enumerate(row):
        # Format dates and currency properly
        if isinstance(value, datetime):
            new_row_cells[i].text = format_date(value)
        elif i == 2 and isinstance(value, (float, int)):  # Assuming the 3rd column is the amount
            new_row_cells[i].text = format_currency(value)
            new_row_cells[i].text = str(value)

# Save the new document
new_updated_output_path = '/mnt/data/Updated_January_Checkbook_2024_Document.docx'
Leave a Comment