Untitled

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

# Helper functions
def is_profit_margins_header(row):
    return row[0] and 'Profit Margins' in row[0]

def is_detail_header(row):
    return row[0] == "Name" and row[1] == "Total" and row[2] == "Material" and row[3] == "Payroll" and row[4] == "Profit"

def format_as_currency(value):
    try:
        return "${:,.2f}".format(float(value))
    except ValueError:
        return value

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 = 'January_Profit_Margins_2024_1704228682.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 without the first title
profit_margins_doc = Document()

# Add sections and entries to the Word document
for index, row in new_excel_data.iterrows():
    if index > 1:  # Skip the first title row
        if is_profit_margins_header(row) and index > 2:  # Include the second title but skip the first
            section_name = row[0]
            profit_margins_doc.add_heading(section_name, level=2)
        elif is_detail_header(row):
            details_table = profit_margins_doc.add_table(rows=1, cols=5)
            details_table.style = 'Table Grid'
            for i, header in enumerate(["Name", "Total", "Material", "Payroll", "Profit"]):
                details_table.cell(0, i).text = header
                apply_enhanced_cell_shading(details_table.cell(0, i), 'D9EAD3')
        elif any(row) and not is_detail_header(row):
            cells = details_table.add_row().cells
            for i in range(5):
                if i > 0:  # Format numeric values as currency
                    cells[i].text = format_as_currency(row[i])
                else:  # The first column is not numeric
                    cells[i].text = str(row[i]) if row[i] is not None else ""

# Save the document
profit_margins_doc.save('Corrected_January_Profit_Margins_2024_Document.docx')