21771954329

Committed 07 Feb 2026 01:42AM UTC coverage: 96.31% (+0.04%) from 96.273%

Build # 21771954329

Build Type

Pull #4799

github

Committed by

web-flow

Commit Message

Merge 3029ead09 into 95b826a3c

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

Coverage Stats

604 of 614 new or added lines in 7 files covered. (98.37%)

47 existing lines in 3 files now uncovered.

46987 of 48787 relevant lines covered (96.31%)

384.08 hits per line

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

90.91

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

<?php

namespace PhpOffice\PhpSpreadsheet\Style\NumberFormat;

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

PHPOffice / PhpSpreadsheet / 21771954329

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