21815518402

Committed 09 Feb 2026 07:02AM UTC coverage: 96.313% (+0.04%) from 96.273%

Build # 21815518402

Build Type

Pull #4799

github

Committed by

web-flow

Commit Message

Merge fb6e56a22 into 95b826a3c

Pull Request Pull Request #4799: Ods Writer Support Some Number Formats

Coverage Stats

660 of 664 new or added lines in 6 files covered. (99.4%)

30 existing lines in 3 files now uncovered.

47128 of 48932 relevant lines covered (96.31%)

383.08 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

91.89

/src/PhpSpreadsheet/Style/NumberFormat/DateFormatter.php

<?php

namespace PhpOffice\PhpSpreadsheet\Style\NumberFormat;

use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Throwable;

class DateFormatter
{
    /**
     * Search/replace values to convert Excel date/time format masks to PHP format masks.
     */
    private const DATE_FORMAT_REPLACEMENTS = [
        // first remove escapes related to non-format characters
        '\\' => '',
        //    12-hour suffix
        'am/pm' => 'A',
        //    4-digit year
        'e' => 'Y',
        'yyyy' => 'Y',
        //    2-digit year
        'yy' => 'y',
        //    first letter of month - no php equivalent
        'mmmmm' => 'M',
        //    full month name
        'mmmm' => 'F',
        //    short month name
        'mmm' => 'M',
        //    mm is minutes if time, but can also be month w/leading zero
        //    so we try to identify times be the inclusion of a : separator in the mask
        //    It isn't perfect, but the best way I know how
        ':mm' => ':i',
        'mm:' => 'i:',
        //    full day of week name
        'dddd' => 'l',
        //    short day of week name
        'ddd' => 'D',
        //    days leading zero
        'dd' => 'd',
        //    days no leading zero
        'd' => 'j',
        //    fractional seconds - no php equivalent
        '.s' => '',
    ];

    /**
     * Search/replace values to convert Excel date/time format masks hours to PHP format masks (24 hr clock).
     */
    private const DATE_FORMAT_REPLACEMENTS24 = [
        'hh' => 'H',
        'h' => 'G',
        //    month leading zero
        'mm' => 'm',
        //    month no leading zero
        'm' => 'n',
        //    seconds
        'ss' => 's',
    ];

    /**
     * Search/replace values to convert Excel date/time format masks hours to PHP format masks (12 hr clock).
     */
    private const DATE_FORMAT_REPLACEMENTS12 = [
        'hh' => 'h',
        'h' => 'g',
        //    month leading zero
        'mm' => 'm',
        //    month no leading zero
        'm' => 'n',
        //    seconds
        'ss' => 's',
    ];

    private const HOURS_IN_DAY = 24;
    private const MINUTES_IN_DAY = 60 * self::HOURS_IN_DAY;
    private const SECONDS_IN_DAY = 60 * self::MINUTES_IN_DAY;
    private const INTERVAL_PRECISION = 10;
    private const INTERVAL_LEADING_ZERO = [
        '[hh]',
        '[mm]',
        '[ss]',
    ];
    private const INTERVAL_ROUND_PRECISION = [
        // hours and minutes truncate
        '[h]' => self::INTERVAL_PRECISION,
        '[hh]' => self::INTERVAL_PRECISION,
        '[m]' => self::INTERVAL_PRECISION,
        '[mm]' => self::INTERVAL_PRECISION,
        // seconds round
        '[s]' => 0,
        '[ss]' => 0,
    ];
    private const INTERVAL_MULTIPLIER = [
        '[h]' => self::HOURS_IN_DAY,
        '[hh]' => self::HOURS_IN_DAY,
        '[m]' => self::MINUTES_IN_DAY,
        '[mm]' => self::MINUTES_IN_DAY,
        '[s]' => self::SECONDS_IN_DAY,
        '[ss]' => self::SECONDS_IN_DAY,
    ];

    /** @param float|int|numeric-string $value */
    private static function tryInterval(bool &$seekingBracket, string &$block, mixed $value, string $format): void
    {
        if ($seekingBracket) {
            if (str_contains($block, $format)) {
                $hours = (string) (int) round(
                    self::INTERVAL_MULTIPLIER[$format] * $value,
                    self::INTERVAL_ROUND_PRECISION[$format]
                );
                if (strlen($hours) === 1 && in_array($format, self::INTERVAL_LEADING_ZERO, true)) {
                    $hours = "0$hours";
                }
                $block = str_replace($format, $hours, $block);
                $seekingBracket = false;
            }
        }
    }

    /** @param float|int $value value to be formatted */
    public static function format(mixed $value, string $format): string
    {
        if ($value < 0 && $format === NumberFormat::FORMAT_DATE_TIME_INTERVAL) {
            $absVal = fmod(abs($value), 1.0);
            $hms = (int) round(86400 * $absVal);
            $hours = intdiv($hms, 3600);
            $hms -= $hours * 3600;
            $minutes = intdiv($hms, 60);
            $seconds = $hms % 60;

            return sprintf('-%02d:%02d:%02d', $hours, $minutes, $seconds);
        }
        // strip off first part containing e.g. [$-F800] or [$USD-409]
        // general syntax: [$<Currency string>-<language info>]
        // language info is in hexadecimal
        // strip off chinese part like [DBNum1][$-804]
        $format = (string) preg_replace('/^(\[DBNum\d\])*(\[\$[^\]]*\])/i', '', $format);

        // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case;
        //    but we don't want to change any quoted strings
        /** @var callable $callable */
        $callable = [self::class, 'setLowercaseCallback'];
        $format = (string) preg_replace_callback('/(?:^|")([^"]*)(?:$|")/', $callable, $format);

        // Only process the non-quoted blocks for date format characters

        $blocks = explode('"', $format);
        foreach ($blocks as $key => &$block) {
            if ($key % 2 == 0) {
                $block = strtr($block, self::DATE_FORMAT_REPLACEMENTS);
                if (!strpos($block, 'A')) {
                    // 24-hour time format
                    // when [h]:mm format, the [h] should replace to the hours of the value * 24
                    $seekingBracket = true;
                    self::tryInterval($seekingBracket, $block, $value, '[h]');
                    self::tryInterval($seekingBracket, $block, $value, '[hh]');
                    self::tryInterval($seekingBracket, $block, $value, '[mm]');
                    self::tryInterval($seekingBracket, $block, $value, '[m]');
                    self::tryInterval($seekingBracket, $block, $value, '[s]');
                    self::tryInterval($seekingBracket, $block, $value, '[ss]');
                    $block = strtr($block, self::DATE_FORMAT_REPLACEMENTS24);
                } else {
                    // 12-hour time format
                    $block = strtr($block, self::DATE_FORMAT_REPLACEMENTS12);
                }
            }
        }
        $format = implode('"', $blocks);

        // escape any quoted characters so that DateTime format() will render them correctly
        /** @var callable $callback */
        $callback = [self::class, 'escapeQuotesCallback'];
        $format = (string) preg_replace_callback('/"(.*)"/U', $callback, $format);

        try {
            $dateObj = Date::excelToDateTimeObject($value);
        } catch (Throwable) {
            return StringHelper::convertToString($value);
        }
        // If the colon preceding minute had been quoted, as happens in
        // Excel 2003 XML formats, m will not have been changed to i above.
        // Change it now.
        $format = (string) \preg_replace('/\\\:m/', ':i', $format);
        $microseconds = (int) $dateObj->format('u');
        if (str_contains($format, ':s.000')) {
            $milliseconds = (int) round($microseconds / 1000.0);
            if ($milliseconds === 1000) {
                $milliseconds = 0;
                $dateObj->modify('+1 second');
            }
            $dateObj->modify("-$microseconds microseconds");
            $format = str_replace(':s.000', ':s.' . sprintf('%03d', $milliseconds), $format);
        } elseif (str_contains($format, ':s.00')) {
            $centiseconds = (int) round($microseconds / 10000.0);
            if ($centiseconds === 100) {
                $centiseconds = 0;
                $dateObj->modify('+1 second');
            }
            $dateObj->modify("-$microseconds microseconds");
            $format = str_replace(':s.00', ':s.' . sprintf('%02d', $centiseconds), $format);
        } elseif (str_contains($format, ':s.0')) {
            $deciseconds = (int) round($microseconds / 100000.0);
            if ($deciseconds === 10) {
                $deciseconds = 0;
                $dateObj->modify('+1 second');
            }
            $dateObj->modify("-$microseconds microseconds");
            $format = str_replace(':s.0', ':s.' . sprintf('%1d', $deciseconds), $format);
        } else { // no fractional second
            if ($microseconds >= 500000) {
                $dateObj->modify('+1 second');
            }
            $dateObj->modify("-$microseconds microseconds");
        }

        return $dateObj->format($format);
    }

    /** @param string[] $matches */
    private static function setLowercaseCallback(array $matches): string
    {
        return mb_strtolower($matches[0]);
    }

    /** @param string[] $matches */
    private static function escapeQuotesCallback(array $matches): string
    {
        return '\\' . implode('\\', mb_str_split($matches[1], 1, 'UTF-8'));
    }
}

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Style\NumberFormat;
4
5	use PhpOffice\PhpSpreadsheet\Shared\Date;
6	use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
7	use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
8	use Throwable;
9
10	class DateFormatter
11	{
12	/**
13	* Search/replace values to convert Excel date/time format masks to PHP format masks.
14	*/
15	private const DATE_FORMAT_REPLACEMENTS = [
16	// first remove escapes related to non-format characters
17	'\\' => '',
18	// 12-hour suffix
19	'am/pm' => 'A',
20	// 4-digit year
21	'e' => 'Y',
22	'yyyy' => 'Y',
23	// 2-digit year
24	'yy' => 'y',
25	// first letter of month - no php equivalent
26	'mmmmm' => 'M',
27	// full month name
28	'mmmm' => 'F',
29	// short month name
30	'mmm' => 'M',
31	// mm is minutes if time, but can also be month w/leading zero
32	// so we try to identify times be the inclusion of a : separator in the mask
33	// It isn't perfect, but the best way I know how
34	':mm' => ':i',
35	'mm:' => 'i:',
36	// full day of week name
37	'dddd' => 'l',
38	// short day of week name
39	'ddd' => 'D',
40	// days leading zero
41	'dd' => 'd',
42	// days no leading zero
43	'd' => 'j',
44	// fractional seconds - no php equivalent
45	'.s' => '',
46	];
47
48	/**
49	* Search/replace values to convert Excel date/time format masks hours to PHP format masks (24 hr clock).
50	*/
51	private const DATE_FORMAT_REPLACEMENTS24 = [
52	'hh' => 'H',
53	'h' => 'G',
54	// month leading zero
55	'mm' => 'm',
56	// month no leading zero
57	'm' => 'n',
58	// seconds
59	'ss' => 's',
60	];
61
62	/**
63	* Search/replace values to convert Excel date/time format masks hours to PHP format masks (12 hr clock).
64	*/
65	private const DATE_FORMAT_REPLACEMENTS12 = [
66	'hh' => 'h',
67	'h' => 'g',
68	// month leading zero
69	'mm' => 'm',
70	// month no leading zero
71	'm' => 'n',
72	// seconds
73	'ss' => 's',
74	];
75
76	private const HOURS_IN_DAY = 24;
77	private const MINUTES_IN_DAY = 60 * self::HOURS_IN_DAY;
78	private const SECONDS_IN_DAY = 60 * self::MINUTES_IN_DAY;
79	private const INTERVAL_PRECISION = 10;
80	private const INTERVAL_LEADING_ZERO = [
81	'[hh]',
82	'[mm]',
83	'[ss]',
84	];
85	private const INTERVAL_ROUND_PRECISION = [
86	// hours and minutes truncate
87	'[h]' => self::INTERVAL_PRECISION,
88	'[hh]' => self::INTERVAL_PRECISION,
89	'[m]' => self::INTERVAL_PRECISION,
90	'[mm]' => self::INTERVAL_PRECISION,
91	// seconds round
92	'[s]' => 0,
93	'[ss]' => 0,
94	];
95	private const INTERVAL_MULTIPLIER = [
96	'[h]' => self::HOURS_IN_DAY,
97	'[hh]' => self::HOURS_IN_DAY,
98	'[m]' => self::MINUTES_IN_DAY,
99	'[mm]' => self::MINUTES_IN_DAY,
100	'[s]' => self::SECONDS_IN_DAY,
101	'[ss]' => self::SECONDS_IN_DAY,
102	];
103
104	/** @param float\|int\|numeric-string $value */
105	private static function tryInterval(bool &$seekingBracket, string &$block, mixed $value, string $format): void	186✔
106	{
107	if ($seekingBracket) {	186✔
108	if (str_contains($block, $format)) {	186✔
109	$hours = (string) (int) round(	36✔
110	self::INTERVAL_MULTIPLIER[$format] * $value,	36✔
111	self::INTERVAL_ROUND_PRECISION[$format]	36✔
112	);	36✔
113	if (strlen($hours) === 1 && in_array($format, self::INTERVAL_LEADING_ZERO, true)) {	36✔
114	$hours = "0$hours";	10✔
115	}
116	$block = str_replace($format, $hours, $block);	36✔
117	$seekingBracket = false;	36✔
118	}
119	}
120	}
121
122	/** @param float\|int $value value to be formatted */
123	public static function format(mixed $value, string $format): string	191✔
124	{
125	if ($value < 0 && $format === NumberFormat::FORMAT_DATE_TIME_INTERVAL) {	191✔
126	$absVal = fmod(abs($value), 1.0);	2✔
127	$hms = (int) round(86400 * $absVal);	2✔
128	$hours = intdiv($hms, 3600);	2✔
129	$hms -= $hours * 3600;	2✔
130	$minutes = intdiv($hms, 60);	2✔
131	$seconds = $hms % 60;	2✔
132
133	return sprintf('-%02d:%02d:%02d', $hours, $minutes, $seconds);	2✔
134	}
135	// strip off first part containing e.g. [$-F800] or [$USD-409]
136	// general syntax: [$<Currency string>-<language info>]
137	// language info is in hexadecimal
138	// strip off chinese part like [DBNum1][$-804]
139	$format = (string) preg_replace('/^(\[DBNum\d\])(\[\$[^\]]\])/i', '', $format);	191✔
140
141	// OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case;
142	// but we don't want to change any quoted strings
143	/** @var callable $callable */
144	$callable = [self::class, 'setLowercaseCallback'];	191✔
145	$format = (string) preg_replace_callback('/(?:^\|")([^"]*)(?:$\|")/', $callable, $format);	191✔
146
147	// Only process the non-quoted blocks for date format characters
148
149	$blocks = explode('"', $format);	191✔
150	foreach ($blocks as $key => &$block) {	191✔
151	if ($key % 2 == 0) {	191✔
152	$block = strtr($block, self::DATE_FORMAT_REPLACEMENTS);	191✔
153	if (!strpos($block, 'A')) {	191✔
154	// 24-hour time format
155	// when [h]:mm format, the [h] should replace to the hours of the value * 24
156	$seekingBracket = true;	186✔
157	self::tryInterval($seekingBracket, $block, $value, '[h]');	186✔
158	self::tryInterval($seekingBracket, $block, $value, '[hh]');	186✔
159	self::tryInterval($seekingBracket, $block, $value, '[mm]');	186✔
160	self::tryInterval($seekingBracket, $block, $value, '[m]');	186✔
161	self::tryInterval($seekingBracket, $block, $value, '[s]');	186✔
162	self::tryInterval($seekingBracket, $block, $value, '[ss]');	186✔
163	$block = strtr($block, self::DATE_FORMAT_REPLACEMENTS24);	186✔
164	} else {
165	// 12-hour time format
166	$block = strtr($block, self::DATE_FORMAT_REPLACEMENTS12);	9✔
167	}
168	}
169	}
170	$format = implode('"', $blocks);	191✔
171
172	// escape any quoted characters so that DateTime format() will render them correctly
173	/** @var callable $callback */
174	$callback = [self::class, 'escapeQuotesCallback'];	191✔
175	$format = (string) preg_replace_callback('/"(.*)"/U', $callback, $format);	191✔
176
177	try {
178	$dateObj = Date::excelToDateTimeObject($value);	191✔
179	} catch (Throwable) {	3✔
180	return StringHelper::convertToString($value);	3✔
181	}
182	// If the colon preceding minute had been quoted, as happens in
183	// Excel 2003 XML formats, m will not have been changed to i above.
184	// Change it now.
185	$format = (string) \preg_replace('/\\\:m/', ':i', $format);	188✔
186	$microseconds = (int) $dateObj->format('u');	188✔
187	if (str_contains($format, ':s.000')) {	188✔
188	$milliseconds = (int) round($microseconds / 1000.0);	9✔
189	if ($milliseconds === 1000) {	9✔
UNCOV 190	$milliseconds = 0;	×
UNCOV 191	$dateObj->modify('+1 second');	×
192	}
193	$dateObj->modify("-$microseconds microseconds");	9✔
194	$format = str_replace(':s.000', ':s.' . sprintf('%03d', $milliseconds), $format);	9✔
195	} elseif (str_contains($format, ':s.00')) {	179✔
196	$centiseconds = (int) round($microseconds / 10000.0);	2✔
197	if ($centiseconds === 100) {	2✔
UNCOV 198	$centiseconds = 0;	×
UNCOV 199	$dateObj->modify('+1 second');	×
200	}
201	$dateObj->modify("-$microseconds microseconds");	2✔
202	$format = str_replace(':s.00', ':s.' . sprintf('%02d', $centiseconds), $format);	2✔
203	} elseif (str_contains($format, ':s.0')) {	177✔
204	$deciseconds = (int) round($microseconds / 100000.0);	2✔
205	if ($deciseconds === 10) {	2✔
UNCOV 206	$deciseconds = 0;	×
UNCOV 207	$dateObj->modify('+1 second');	×
208	}
209	$dateObj->modify("-$microseconds microseconds");	2✔
210	$format = str_replace(':s.0', ':s.' . sprintf('%1d', $deciseconds), $format);	2✔
211	} else { // no fractional second
212	if ($microseconds >= 500000) {	175✔
213	$dateObj->modify('+1 second');	13✔
214	}
215	$dateObj->modify("-$microseconds microseconds");	175✔
216	}
217
218	return $dateObj->format($format);	188✔
219	}
220
221	/** @param string[] $matches */
222	private static function setLowercaseCallback(array $matches): string	191✔
223	{
224	return mb_strtolower($matches[0]);	191✔
225	}
226
227	/** @param string[] $matches */
228	private static function escapeQuotesCallback(array $matches): string	34✔
229	{
230	return '\\' . implode('\\', mb_str_split($matches[1], 1, 'UTF-8'));	34✔
231	}
232	}

PHPOffice / PhpSpreadsheet / 21815518402

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous