fhanyuh
caf8e98378
No content changes: git diff --ignore-all-space over these files is empty. The churn came from editing on Windows against a repo checked out with LF.
207 lines
8.5 KiB
Python
207 lines
8.5 KiB
Python
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()
|