FPSMS-backend/docs/EXCEL_EXPORT_STANDARD.md

Excel export standard (FPSMS backend)

This document mirrors the visual and formatting rules used on the frontend for .xlsx exports.
It does not run automatically: backend code paths (JasperReports XLSX export, Apache POI, etc.) are separate from the Next.js xlsx-js-style pipeline. Apply these rules manually when implementing or changing server-side Excel so outputs stay consistent with the UI standard.

Frontend reference (implementation details):
FPSMS-frontend/src/app/(main)/chart/_components/EXCEL_EXPORT_STANDARD.md

---

When backend Excel is used

Typical cases:

• JasperReports templates configured for XLSX output (JRXlsxExporter or similar).
• Apache POI (XSSFWorkbook, SXSSFWorkbook) building sheets in Kotlin/Java services (e.g. production schedule export, BOM, item templates).

Reports that return JSON only and let the browser build the file (e.g. GRN rep-014) are not covered here — those follow the frontend doc.

---

Rules to mirror (parity with frontend)

Element	Target
Header row	Bold, black text; fill light grey #D9D9D9 (RGB); vertical center; for header text use horizontal center + wrap unless the column is numeric (see below).
Money / amount columns	Excel number format #,##0.00 (thousands separator, 2 decimals). Right-align header and data.
Quantity columns	Right-align header and data (no fixed decimal rule on frontend; match business need).
Column width	At least readable for bilingual headers; frontend uses max(12, headerLength + 4) character width — approximate in POI with setColumnWidth.

Column detection (naming)

Align with frontend header keywords so exports feel the same:

• Money: headers containing 金額, 單價, Amount, Unit Price, Total Amount (including bilingual labels like Amount / 金額).
• Quantity: headers containing Qty, 數量, Demand.

---

Apache POI (sketch)

• Create CellStyle for header: setFillForegroundColor (indexed or XSSFColor for #D9D9D9), setFillPattern, bold font, alignment.
• Data format for money: DataFormat → "#,##0.00" on numeric cells.
• setAlignment(HorizontalAlignment.RIGHT) for amount/qty columns (and header cells in those columns if desired).

---

JasperReports

• Apply styles in the report template (fonts, background, borders) and numeric pattern for amount fields equivalent to #,##0.00 where supported.
• Verify in Excel output: Jasper’s XLSX styling capabilities differ slightly from POI — test the generated file.

---

Checklist for new backend Excel features

1. Confirm whether the report is backend-only Excel or JSON + frontend Excel — only the latter uses the TypeScript standard automatically.
2. For backend-only, apply header fill, money format, and alignment per the table above.
3. Keep naming of column titles consistent with the keywords in Column detection when possible.

---

Aligned with frontend EXCEL_EXPORT_STANDARD.md (grey #D9D9D9, #,##0.00 for amounts, right-align numeric columns).