"""Build a formatted Arabic (RTL) Excel report from books.json produced by raqib.py. python3 report.py ../data/books.json ../data/raqib-report.xlsx The summary sheet uses live formulas (COUNTIFS / AVERAGEIFS / SUMIFS) over the data table, so the client can paste a newer crawl into «البيانات» and every figure and chart updates. """ import json import sys from collections import Counter from openpyxl import Workbook from openpyxl.chart import BarChart, DoughnutChart, Reference from openpyxl.chart.label import DataLabelList from openpyxl.formatting.rule import ColorScaleRule, DataBarRule from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.utils import get_column_letter from openpyxl.worksheet.table import Table, TableStyleInfo NAVY, TEAL, SAND, WHITE, GREY = "0F2A3D", "0E7C7B", "F3EEE3", "FFFFFF", "6B7A8C" F = "IBM Plex Sans Arabic" thin = Side(style="thin", color="E2E8F0") def style_range(ws, ref, **kw): for row in ws[ref]: for c in row: for k, v in kw.items(): setattr(c, k, v) def build(src, dst): data = json.load(open(src, encoding="utf-8")) books, meta = data["books"], data["meta"] wb = Workbook() # ---------------- data sheet ---------------- ds = wb.active ds.title = "البيانات" ds.sheet_view.rightToLeft = True cols = [("upc", "الرمز UPC", 20), ("title", "العنوان", 52), ("category", "التصنيف", 20), ("price_gbp", "السعر (£)", 12), ("rating", "التقييم", 10), ("stars", "النجوم", 12), ("in_stock", "المخزون", 11), ("description_words", "كلمات الوصف", 13), ("url", "الرابط", 40)] ds.append([c[1] for c in cols]) for i, b in enumerate(books, start=2): ds.append([b["upc"], b["title"], b["category"], b["price_gbp"], b["rating"], f'=REPT("★",E{i})&REPT("☆",5-E{i})', b["in_stock"], b["description_words"], b["url"]]) n = len(books) + 1 for j, (_, _, w) in enumerate(cols, 1): ds.column_dimensions[get_column_letter(j)].width = w tbl = Table(displayName="Books", ref=f"A1:I{n}") tbl.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True) ds.add_table(tbl) style_range(ds, f"A1:I{n}", font=Font(name=F, size=10)) style_range(ds, "A1:I1", font=Font(name=F, size=10, bold=True, color=WHITE)) for c in ds[f"D2:D{n}"]: c[0].number_format = "£#,##0.00" ds[f"F2"].font = Font(name=F, color="D97706") style_range(ds, f"F2:F{n}", font=Font(name=F, color="D97706")) ds.conditional_formatting.add(f"D2:D{n}", ColorScaleRule(start_type="min", start_color="E6F4F1", end_type="max", end_color="0E7C7B")) ds.conditional_formatting.add(f"G2:G{n}", DataBarRule(start_type="min", end_type="max", color="2547D0")) ds.freeze_panes = "A2" # ---------------- summary sheet ---------------- sm = wb.create_sheet("لوحة الملخص", 0) sm.sheet_view.rightToLeft = True sm.sheet_view.showGridLines = False for col, w in zip("ABCDEFGHIJKL", [3, 26, 14, 14, 14, 16, 3, 18, 14, 14, 14, 3]): sm.column_dimensions[col].width = w sm.merge_cells("B2:K2") sm["B2"] = "تقرير رقيب — كتالوج المنتجات المجمّع آليًا" sm["B2"].font = Font(name=F, size=20, bold=True, color=WHITE) sm["B2"].fill = PatternFill("solid", fgColor=NAVY) sm["B2"].alignment = Alignment(horizontal="right", vertical="center", indent=1) sm.row_dimensions[2].height = 42 sm.merge_cells("B3:K3") sm["B3"] = f"المصدر: {meta['source']} — تاريخ الجمع: {meta['collected_at'][:16].replace('T', ' ')} UTC — {meta['pages']} صفحة في {meta['seconds']} ثانية — مثال تعليمي على موقع مخصص للتدريب" sm["B3"].font = Font(name=F, size=9, color=GREY) # KPI cards (formulas over the table) kpis = [("عدد المنتجات", "=COUNTA(Books[الرمز UPC])", "#,##0"), ("متوسط السعر", "=AVERAGE(Books[السعر (£)])", "£0.00"), ("متوسط التقييم", "=AVERAGE(Books[التقييم])", '0.00" / 5"'), ("وحدات في المخزون", "=SUM(Books[المخزون])", "#,##0"), ("قيمة المخزون", "=SUMPRODUCT(Books[السعر (£)],Books[المخزون])", "£#,##0")] for k, (label, f, fmt) in enumerate(kpis): col = ["B", "C", "E", "H", "J"][k] end = {"B": "B", "C": "D", "E": "F", "H": "I", "J": "K"}[col] sm.merge_cells(f"{col}5:{end}5"); sm.merge_cells(f"{col}6:{end}6") sm[f"{col}5"] = label; sm[f"{col}6"] = f; sm[f"{col}6"].number_format = fmt sm[f"{col}5"].font = Font(name=F, size=9, color=GREY); sm[f"{col}6"].font = Font(name=F, size=18, bold=True, color=TEAL) for r in (5, 6): for cc in sm[f"{col}{r}:{end}{r}"][0]: cc.fill = PatternFill("solid", fgColor=SAND); cc.alignment = Alignment(horizontal="center", vertical="center") sm.row_dimensions[6].height = 34 # category table cats = [c for c, _ in Counter(b["category"] for b in books).most_common(15)] hdr = ["التصنيف", "عدد الكتب", "متوسط السعر", "أعلى سعر", "متوسط التقييم"] for j, h in enumerate(hdr): c = sm.cell(row=8, column=2 + j, value=h) c.font = Font(name=F, bold=True, color=WHITE, size=10); c.fill = PatternFill("solid", fgColor=TEAL); c.alignment = Alignment(horizontal="center") for i, cat in enumerate(cats, start=9): sm.cell(row=i, column=2, value=cat) sm.cell(row=i, column=3, value=f'=COUNTIFS(Books[التصنيف],B{i})') sm.cell(row=i, column=4, value=f'=AVERAGEIFS(Books[السعر (£)],Books[التصنيف],B{i})').number_format = "£0.00" sm.cell(row=i, column=5, value=f'=_xlfn.MAXIFS(Books[السعر (£)],Books[التصنيف],B{i})').number_format = "£0.00" sm.cell(row=i, column=6, value=f'=AVERAGEIFS(Books[التقييم],Books[التصنيف],B{i})').number_format = "0.00" for j in range(2, 7): c = sm.cell(row=i, column=j); c.font = Font(name=F, size=10); c.border = Border(bottom=thin) if i % 2: c.fill = PatternFill("solid", fgColor="F8FAFC") last = 8 + len(cats) sm.conditional_formatting.add(f"C9:C{last}", DataBarRule(start_type="num", start_value=0, end_type="max", color="0E7C7B")) # rating distribution (formulas) sm["H8"], sm["I8"] = "التقييم", "عدد الكتب" for c in (sm["H8"], sm["I8"]): c.font = Font(name=F, bold=True, color=WHITE, size=10); c.fill = PatternFill("solid", fgColor=NAVY); c.alignment = Alignment(horizontal="center") for r in range(1, 6): sm.cell(row=8 + r, column=8, value=f"{r} ★").font = Font(name=F, size=10) sm.cell(row=8 + r, column=9, value=f"=COUNTIFS(Books[التقييم],{r})").font = Font(name=F, size=10) # charts bar = BarChart(); bar.type = "bar"; bar.style = 10; bar.title = "متوسط السعر حسب التصنيف (£)" bar.add_data(Reference(sm, min_col=4, min_row=8, max_row=last), titles_from_data=True) bar.set_categories(Reference(sm, min_col=2, min_row=9, max_row=last)) bar.legend = None; bar.height, bar.width = 9.5, 16; bar.y_axis.majorGridlines = None bar.series[0].graphicalProperties.solidFill = TEAL sm.add_chart(bar, f"B{last + 3}") dn = DoughnutChart(); dn.title = "توزيع التقييمات"; dn.holeSize = 55 dn.add_data(Reference(sm, min_col=9, min_row=8, max_row=13), titles_from_data=True) dn.set_categories(Reference(sm, min_col=8, min_row=9, max_row=13)); dn.height, dn.width = 9.5, 11 dn.dataLabels = DataLabelList(); dn.dataLabels.showPercent = True sm.add_chart(dn, f"H{last + 3}") # ---------------- quality sheet ---------------- q = wb.create_sheet("جودة البيانات") q.sheet_view.rightToLeft = True rows = [("البند", "القيمة"), ("الصفحات المزارة", meta["pages"]), ("السجلات المقبولة", meta["records"]), ("السجلات المرفوضة", meta["rejected"]), ("أخطاء الجلب", meta["fetch_errors"]), ("قواعد التحقق", "UPC من 16 خانة سداسية، بدون تكرار، سعر بين 0 و1000، تقييم من 1 إلى 5"), ("مدة التشغيل (ثانية)", meta["seconds"]), ("أسلوب الجمع", "4 طلبات متزامنة كحد أقصى، تأخير بين الطلبات، إعادة المحاولة مع انتظار متزايد، وكيل مستخدم واضح")] for r in rows: q.append(r) q.column_dimensions["A"].width = 24; q.column_dimensions["B"].width = 90 style_range(q, f"A1:B{len(rows)}", font=Font(name=F, size=10), alignment=Alignment(wrap_text=True, vertical="top", horizontal="right")) style_range(q, "A1:B1", font=Font(name=F, size=10, bold=True, color=WHITE), fill=PatternFill("solid", fgColor=NAVY)) wb.properties.title = "Raqib catalogue report (demo)"; wb.properties.creator = "Cadrien Studio" wb.save(dst) print("saved", dst, len(books), "rows") if __name__ == "__main__": build(sys.argv[1], sys.argv[2])