Untitled
unknown
plain_text
2 years ago
2.4 kB
9
Indexable
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')
Editor is loading...
Leave a Comment