如何使用PhpSpreadsheet正确设置德语日期格式（dd.mm.yyyy）？

Hey there! I’ve run into similar quirks with PhpSpreadsheet’s built-in date format constants before—they can be finicky because many are tied to Excel’s region-specific presets. Let’s get your desired dd.mm.yyyy format working reliably.

Why the Built-in Constants Aren’t Working #

• FORMAT_DATE_DDMMYYYY maps to Excel’s *dd/mm/yyyy format (note the asterisk). This is a "dynamic" format that adapts to the user’s system region settings, which explains the unexpected output in your first screenshot.
• FORMAT_DATE_YYYYMMDDSLASH is explicitly linked to the Afrikaans locale, so it’s not meant for German date display.

The Simple Fix: Use a Custom Format String #

Instead of relying on predefined constants, directly pass the exact Excel date format string you want. This bypasses region-dependent presets and locks in the format you need.

Here’s the code to implement this:

// Assume $cell is your target cell object, and $date is a valid date value
$cell->setValue($date); // Make sure $date is a DateTime object, Unix timestamp, or Excel serial date
$cell->getStyle()->getNumberFormat()->setFormatCode('dd.mm.yyyy');

Important Tips #

1. Use Valid Date Types: Always pass a DateTime object, Unix timestamp, or Excel serial date to the cell. If you pass a raw string like '01.01.2024', Excel might treat it as text instead of a date.
2. Skip the Asterisk: The format string 'dd.mm.yyyy' (no asterisk) tells Excel to use a fixed format, ignoring the user’s regional settings entirely.

This will make Excel display the date exactly as dd.mm.yyyy, matching the German locale format you’re aiming for.

内容的提问来源于stack exchange，提问作者awekas