====== AI-Generated PDF Reports from Excel – How-To & SOP ====== > **TL;DR:** Upload the .xlsx (as attachment, or place it in ''/home/user/Documents/inbox'') → tell the AI in one clear message what to build and where to save it → let it read and summarize the file first → review the generated PDF page by page → iterate with short fix requests → clean up temp files when done. **One project per chat**, delete chats you no longer need. ===== 1. What is possible ===== The AI assistant runs in a Linux environment and can turn a raw Excel export into a finished, presentation-ready A4 PDF report without any manual layout work: cover page, KPI dashboard, charts, formatted tables, status badges, page footers with page numbers – in the language of the data (German or English). It works best when the Excel file contains structured lists with status columns, areas, quantities or references. ===== 2. How it works under the hood ===== ^ Component ^ Role ^ | ''openpyxl'' / ''pandas'' (Python) | Reads the .xlsx: values, sheet structure, cell colors/legend | | ''matplotlib'' | Builds the charts (donut, bar charts, timelines) as image files | | HTML/CSS + ''WeasyPrint'' | Lays out real A4 pages and renders them into the final PDF (cover, footers, page x of y) | | ''pdftoppm'' (poppler) | Renders PDF pages back to images so the AI can visually inspect its own output before handover | The standard pipeline: **read → understand → summarize → build charts → build HTML report → render PDF → verify pages → iterate on feedback.** ===== 3. Prerequisites ===== * The Excel file (.xlsx), ideally one sheet per topic * A fixed output folder and filename, e.g. ''/home/user/Documents/generatedpdf/.pdf'' * A decision: report language (data in German → report in German) * Optional: brand colors or a logo image you want on the cover ===== 4. Step-by-step workflow ===== - **Step 1 – Upload the file.** Attach the .xlsx to the chat. If the AI reports it cannot find the file on disk, copy it into ''/home/user/Documents/inbox'' (this actually happened in Case Study B and cost one round trip). - **Step 2 – Send the initial prompt** (Section 5, Prompt A or B). Always include: what to do + exact target folder + filename. - **Step 3 – Let it read first.** The AI dumps the workbook structure, identifies sections/statuses/counts and summarizes them back to you. Check that summary – correcting understanding here is much cheaper than correcting a finished PDF. - **Step 4 – Generation.** Charts are generated, the report is laid out (cover, dashboard, sections, detail tables, open items), and the PDF is rendered into your target folder. - **Step 5 – Page-by-page review.** The AI renders pages to images and shows you key pages (cover, summary, charts, tables, final page). Look at: totals, spellings, colors, missing rows. - **Step 6 – Iterate with short fix prompts** (Section 5, Prompt C). Typical fixes: wrong total in a chart, typo, extra section, color change, logo. - **Step 7 – Handover & cleanup.** Confirm the file location, then ask the AI to delete its temporary files (Section 5, Prompt D). ===== 5. Prompt library (copy & paste) ===== **Prompt A – initial request, new report:** Read the attached Excel file "NAME_OF_FILE.xlsx". It is written in German. Before building anything, summarize what you understand: sheets, key columns, statuses, and all relevant counts – I will confirm before you proceed. Then create a professional customer-presentation PDF based on it with: cover page with key numbers, summary dashboard with KPI cards, charts, clean detail tables, status badges, and a footer with page numbers. Save the result to: /home/user/Documents/generatedpdf/REPORTNAME.pdf **Prompt B – new file, same style as before:** I uploaded a new Excel file "NAME_OF_FILE.xlsx". Read it and understand the content first. Create a professional PDF report (same style as the last one: cover, KPI dashboard, charts, tables, open-items section with recommended actions). Save it to /home/user/Documents/generatedpdf/ with exactly the same name as the Excel file. **Prompt C – fixes / iterations (one issue per message works best):** Page 3, chart "Kameras nach Bereich": the numbers do not add up to the total (342). Fix the chart so all categories are included and re-render the PDF. Change the accent color on the cover to #1a5d9e and shorten the subtitle text. Add our company logo (attached) to the top-left of the cover page. **Prompt D – cleanup after handover:** The report is final. Delete all temporary files you created for it (chart images, HTML, scripts, page previews) and confirm that only the final PDF remains in /home/user/Documents/generatedpdf. ===== 6. Case studies ===== ==== 6.1 "AS von Status Quo.xlsx" → Status-Quo-Bericht ==== | ^^Item^^ | ^^Value^^ | Source file | ''AS von Status Quo.xlsx'' (sheet "Tabelle1", 90 columns, German) | Output | ''/home/user/Documents/generatedpdf/StatusQuo_Bericht_Kundenvorstellung.pdf'' – 16 pages | Content | 152 camera positions across Video 1 / Video 2 / Video 3 + parking items, halls 1–9 plus outdoor areas | Status mix | 106 in operation · 28 defects fixed (22.–29.07.2026) · 12 mounted but not in system · 4 open orders (AN 1940) · 2 missing vehicle recognition | Highlights | Color legend taken 1:1 from the sheet (RE 1309 yellow, AN 1940 green, RE 1296 orange, AN 1801 blue, Bestand grey, Parkraumüberwachung purple); "Behoben" dates extracted per position; contradictory notes (KFZ-Kennung fehlt vs. vorhanden) flagged for on-site clarification instead of silently dropped | Lessons | The AI caught two of its own typos and a misclassified status only because pages were visually checked; always review cover + dashboard + at least one detail page ==== 6.2 "Kamera Netzwerkliste.xlsx" → Kamera Netzwerkliste.pdf ==== | ^^Item^^ | ^^Value^^ | Source file | ''Kamera Netzwerkliste.xlsx'' (sheet "Kamera", 342 camera rows) | Output | ''/home/user/Documents/generatedpdf/Kamera Netzwerkliste.pdf'' – 22 pages, same name as the Excel file as requested | Content | 342 cameras on 7 NVRs across 16 areas; 338 online & recording (98.8 %), 4 offline (parking/outdoor cams); firmware mix: 250 on V5 recorders, 92 on older V4 units flagged for modernization | Highlights | Cover KPIs, status donut, area + model charts, full NVR infrastructure table with IPs, complete list of all 342 cameras, offline-cam action table with recommended fixes | Lessons | The first upload did **not** land on the server disk (only a preview was visible). Solution: asked to re-upload; the file then appeared in ''/home/user/Documents/inbox''. → Always verify with the AI that it can actually read the file before starting. ===== 7. Checklist for a professional look ===== * Cover page: title, subtitle, 3–4 KPI chips, date + document meta * Dashboard: big number cards + one status donut chart * Charts must sum to the total row count – **always verify** (real bug caught in Case Study B) * Status colors stay consistent with the source legend; use badges/pills for table statuses * Page footer with report name and "Seite x von y" * Source reference at the end: document name, sheet, export date/time, author * One coherent palette (navy + one accent color worked well here) ===== 8. Context management & cleanup – important ===== > **WARNING:** Every message, attachment and tool output inside a chat eats into the shared context window of that chat. A long-running chat with several big Excel files, many page previews and repeated regenerations will eventually hit the limit: responses get slower, older instructions are forgotten, and new uploads may not fit at all. Manage this **before** it happens: * **One project per chat.** Start a fresh chat for every new Excel/report. Do not continue an old 20-message chat with a completely new file. * **Delete chats you no longer need** (you do this in the app UI – the AI cannot delete your own chat history). Unused chats are a wasted resource and clutter. * **Clean up generated temp files after handover.** Ask the AI (Prompt D) to remove chart PNGs, HTML and page previews it created. Do not let 10+ preview images and intermediate files accumulate in the working folder or get re-read into the chat later. * **Attach each file only once per project.** Re-uploading a slightly edited version of the same big Excel is one of the fastest ways to blow up the context. If the data changed significantly, prefer a new chat. * **Keep the Excel slim:** delete unused sheets/hidden columns before uploading if you can – the AI reads the whole workbook. * **State your target once, precisely** (absolute folder + exact filename) in the first prompt; vague requests cause extra rounds of back-and-forth, and every round costs context. * **Overwrite the same output file** instead of producing v1/v2/v3 copies. * **If a chat starts to feel heavy** (slow answers, forgotten instructions), stop iterating and hand over: ask the AI for a short state summary, then continue in a new chat with something like: State summary for continuation: Project: camera network report. Source: Kamera Netzwerkliste.xlsx. Output: /home/user/Documents/generatedpdf/Kamera Netzwerkliste.pdf (22 pages, final except cover logo). Next step: add our company logo to the cover page only. * **Do not paste huge text dumps** (full console output, entire HTML) into the chat. If something went wrong, describe the symptom in 1–2 sentences and point at the file/page – let the AI read the file itself. ===== 9. Troubleshooting ===== | ^^Symptom^^ | ^^Fix^^ | AI cannot find the uploaded file on disk | Re-upload as attachment, or copy it into ''/home/user/Documents/inbox''; ask the AI to confirm it can read the file | Wrong totals / chart doesn't add up | Ask: "cross-check every chart total against the table row count and fix"; show the affected page | PDF looks plain for a customer meeting | Request: cover + KPI dashboard + donut/bar charts + status badges + footer with page numbers | Detail row missing or misread | Point at the exact row in the source Excel (row number + column) and describe what the AI must show | Chat becomes slow or forgets instructions | Fresh chat + state summary (Section 8); delete old unused chats | German characters wrong | Should work out of the box (UTF-8, DejaVu fonts); if not, report it together with a page screenshot ---- //Author: · Version 1.0 · 2026-10-12 • Internal SOP – AI-generated PDF reports from Excel//