DokuWiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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/<name>.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:**
<code>
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
</code>

**Prompt B – new file, same style as before:**
<code>
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.
</code>

**Prompt C – fixes / iterations (one issue per message works best):**
<code>
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.
</code>

<code>
Change the accent color on the cover to #1a5d9e and shorten the subtitle text.
</code>

<code>
Add our company logo (attached) to the top-left of the cover page.
</code>

**Prompt D – cleanup after handover:**
<code>
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.
</code>

===== 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:
<code>
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.
</code>
* **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: <your name> · Version 1.0 · 2026-10-12 • Internal SOP – AI-generated PDF reports from Excel//