import json import os import pandas as pd from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter def main(): jsonl_file = "/tmp/test_images_results.jsonl" xlsx_file = "/tmp/test_images_report.xlsx" if not os.path.exists(jsonl_file): print(f"Error: JSONL file not found at {jsonl_file}") return documents = [] items = [] with open(jsonl_file, "r") as f: for idx, line in enumerate(f): if not line.strip(): continue try: data = json.loads(line) except Exception as e: print(f"Skipping line due to parse error: {e}") continue filename = data.get("filename", "N/A") status = data.get("status", "N/A") tilt = data.get("tilt", "N/A") unwarped = data.get("unwarped", "N/A") metadata = data.get("metadata", {}) no_po = metadata.get("noPO", "N/A") no_so = metadata.get("noSO", "N/A") no_do = metadata.get("noDO", "N/A") tanggal = metadata.get("tanggal", "N/A") customer = metadata.get("customerInfo", "N/A") store = metadata.get("orderUntuk", "N/A") alamat = metadata.get("alamat", "N/A") plat = metadata.get("platTruk", "N/A") items_list = data.get("items", []) # Add to document list documents.append({ "No": idx + 1, "Filename": filename, "Status": status, "Tilt (Degrees)": tilt, "Auto-Rotated/Unwarped": unwarped, "PO Number": no_po, "SO Number": no_so, "DO Number": no_do, "Date": tanggal, "Customer": customer, "Store Match": store, "Alamat": alamat, "Plat Nomor": plat, "Items Count": len(items_list) }) # Add items to items list for item in items_list: items.append({ "Filename": filename, "Kode Barang (SKU)": item.get("kodeBarang", "N/A"), "Nama Barang": item.get("namaBarang", "N/A"), "Banyak (Qty)": item.get("banyak", ""), "Jumlah (Unit)": item.get("jumlah", "") }) df_docs = pd.DataFrame(documents) df_items = pd.DataFrame(items) # Style definitions font_family = "Segoe UI" header_font = Font(name=font_family, size=11, bold=True, color="FFFFFF") regular_font = Font(name=font_family, size=10) bold_font = Font(name=font_family, size=10, bold=True) # Fill colors header_fill = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid") # Dark Blue zebra_fill = PatternFill(start_color="F2F5F8", end_color="F2F5F8", fill_type="solid") # Very light blue-gray success_fill = PatternFill(start_color="E2EFDA", end_color="E2EFDA", fill_type="solid") # Light green error_fill = PatternFill(start_color="FCE4D6", end_color="FCE4D6", fill_type="solid") # Light orange # Alignments center_align = Alignment(horizontal="center", vertical="center") left_align = Alignment(horizontal="left", vertical="center") right_align = Alignment(horizontal="right", vertical="center") # Borders thin_side = Side(border_style="thin", color="D9D9D9") thick_bottom = Side(border_style="medium", color="1F4E78") cell_border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side) with pd.ExcelWriter(xlsx_file, engine='openpyxl') as writer: df_docs.to_excel(writer, sheet_name='Document Summary', index=False) df_items.to_excel(writer, sheet_name='Parsed Items', index=False) workbook = writer.book # 1. Style Document Summary Sheet sheet1 = workbook['Document Summary'] sheet1.views.sheetView[0].showGridLines = True # Style Header Row for col_idx in range(1, len(df_docs.columns) + 1): cell = sheet1.cell(row=1, column=col_idx) cell.font = header_font cell.fill = header_fill cell.alignment = center_align cell.border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thick_bottom) # Style Data Rows for row_idx in range(2, len(df_docs) + 2): # Check status for color coding status_val = sheet1.cell(row=row_idx, column=3).value row_fill = success_fill if status_val == "Success" else (error_fill if status_val == "Failed" or status_val == "Error" else None) # Apply Zebra stripe if no status color if not row_fill and row_idx % 2 == 0: row_fill = zebra_fill for col_idx in range(1, len(df_docs.columns) + 1): cell = sheet1.cell(row=row_idx, column=col_idx) cell.font = regular_font cell.border = cell_border # Apply alignments based on column content if col_idx in [1, 3, 4, 5, 9, 13, 14]: # No, Status, Tilt, Auto-rotated, Date, Plat, Items Count cell.alignment = center_align else: cell.alignment = left_align if row_fill: cell.fill = row_fill # Format tilt with degree symbol if col_idx == 4 and cell.value != "N/A" and cell.value is not None: try: cell.value = float(cell.value) cell.number_format = '0.00"°"' except ValueError: pass # Auto-adjust column width for Sheet 1 for col in sheet1.columns: max_len = 0 for cell in col: val_str = str(cell.value or '') # Exclude long text like Alamat from width sizing if cell.column in [12]: # Alamat max_len = max(max_len, min(len(val_str), 30)) else: max_len = max(max_len, len(val_str)) col_letter = get_column_letter(col[0].column) sheet1.column_dimensions[col_letter].width = max(max_len + 3, 10) sheet1.row_dimensions[1].height = 25 for r in range(2, len(df_docs) + 2): sheet1.row_dimensions[r].height = 20 # 2. Style Parsed Items Sheet sheet2 = workbook['Parsed Items'] sheet2.views.sheetView[0].showGridLines = True # Style Header Row for col_idx in range(1, len(df_items.columns) + 1): cell = sheet2.cell(row=1, column=col_idx) cell.font = header_font cell.fill = header_fill cell.alignment = center_align cell.border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thick_bottom) # Style Data Rows for row_idx in range(2, len(df_items) + 2): row_fill = zebra_fill if row_idx % 2 == 0 else None for col_idx in range(1, len(df_items.columns) + 1): cell = sheet2.cell(row=row_idx, column=col_idx) cell.font = regular_font cell.border = cell_border # Alignments if col_idx in [2, 4, 5]: # SKU, Qty, Unit cell.alignment = center_align else: cell.alignment = left_align if row_fill: cell.fill = row_fill # Auto-adjust column width for Sheet 2 for col in sheet2.columns: max_len = max(len(str(cell.value or '')) for cell in col) col_letter = get_column_letter(col[0].column) sheet2.column_dimensions[col_letter].width = max(max_len + 3, 10) sheet2.row_dimensions[1].height = 25 for r in range(2, len(df_items) + 2): sheet2.row_dimensions[r].height = 20 print("Premium Excel report generated successfully!") if __name__ == "__main__": main()