17380917348

Committed 01 Sep 2025 02:45PM UTC coverage: 95.269% (+0.1%) from 95.124%

Build # 17380917348

Build Type

Pull #4624

github

Committed by

web-flow

Commit Message

Merge 2803d9612 into ff7195c27

Pull Request Pull Request #4624: Minor Improvements to Calculation Coverage

Run Details

2 of 2 new or added lines in 2 files covered. (100.0%)

7 existing lines in 1 file now uncovered.

40251 of 42250 relevant lines covered (95.27%)

347.02 hits per line

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

95.68

/src/PhpSpreadsheet/Calculation/CalculationLocale.php

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation;

class CalculationLocale extends CalculationBase
{
    public const FORMULA_OPEN_FUNCTION_BRACE = '(';
    public const FORMULA_CLOSE_FUNCTION_BRACE = ')';
    public const FORMULA_OPEN_MATRIX_BRACE = '{';
    public const FORMULA_CLOSE_MATRIX_BRACE = '}';
    public const FORMULA_STRING_QUOTE = '"';

    //    Strip xlfn and xlws prefixes from function name
    public const CALCULATION_REGEXP_STRIP_XLFN_XLWS = '/(_xlfn[.])?(_xlws[.])?(?=[\p{L}][\p{L}\p{N}\.]*[\s]*[(])/';

    /**
     * The current locale setting.
     */
    protected static string $localeLanguage = 'en_us'; //    US English    (default locale)

    /**
     * List of available locale settings
     * Note that this is read for the locale subdirectory only when requested.
     *
     * @var string[]
     */
    protected static array $validLocaleLanguages = [
        'en', //    English        (default language)
    ];

    /**
     * Locale-specific argument separator for function arguments.
     */
    protected static string $localeArgumentSeparator = ',';

    /** @var string[] */
    protected static array $localeFunctions = [];

    /**
     * Locale-specific translations for Excel constants (True, False and Null).
     *
     * @var array<string, string>
     */
    protected static array $localeBoolean = [
        'TRUE' => 'TRUE',
        'FALSE' => 'FALSE',
        'NULL' => 'NULL',
    ];

    /** @var array<int, array<int, string>> */
    protected static array $falseTrueArray = [];

    public static function getLocaleBoolean(string $index): string
    {
        return self::$localeBoolean[$index];
    }

    protected static function loadLocales(): void
    {
        $localeFileDirectory = __DIR__ . '/locale/';
        $localeFileNames = glob($localeFileDirectory . '*', GLOB_ONLYDIR) ?: [];
        foreach ($localeFileNames as $filename) {
            $filename = substr($filename, strlen($localeFileDirectory));
            if ($filename != 'en') {
                self::$validLocaleLanguages[] = $filename;
            }
        }
    }

    /**
     * Return the locale-specific translation of TRUE.
     *
     * @return string locale-specific translation of TRUE
     */
    public static function getTRUE(): string
    {
        return self::$localeBoolean['TRUE'];
    }

    /**
     * Return the locale-specific translation of FALSE.
     *
     * @return string locale-specific translation of FALSE
     */
    public static function getFALSE(): string
    {
        return self::$localeBoolean['FALSE'];
    }

    /**
     * Get the currently defined locale code.
     */
    public function getLocale(): string
    {
        return self::$localeLanguage;
    }

    protected function getLocaleFile(string $localeDir, string $locale, string $language, string $file): string
    {
        $localeFileName = $localeDir . str_replace('_', DIRECTORY_SEPARATOR, $locale)
            . DIRECTORY_SEPARATOR . $file;
        if (!file_exists($localeFileName)) {
            //    If there isn't a locale specific file, look for a language specific file
            $localeFileName = $localeDir . $language . DIRECTORY_SEPARATOR . $file;
            if (!file_exists($localeFileName)) {
                throw new Exception('Locale file not found');
            }
        }

        return $localeFileName;
    }

    /** @return array<int, array<int, string>> */
    public function getFalseTrueArray(): array
    {
        if (!empty(self::$falseTrueArray)) {
            return self::$falseTrueArray;
        }
        if (count(self::$validLocaleLanguages) == 1) {
            self::loadLocales();
        }
        $falseTrueArray = [['FALSE'], ['TRUE']];
        foreach (self::$validLocaleLanguages as $language) {
            if (str_starts_with($language, 'en')) {
                continue;
            }
            $locale = $language;
            if (str_contains($locale, '_')) {
                [$language] = explode('_', $locale);
            }
            $localeDir = implode(DIRECTORY_SEPARATOR, [__DIR__, 'locale', null]);

            try {
                $functionNamesFile = $this->getLocaleFile($localeDir, $locale, $language, 'functions');
            } catch (Exception $e) {
                continue;
            }
            //    Retrieve the list of locale or language specific function names
            $localeFunctions = file($functionNamesFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) ?: [];
            foreach ($localeFunctions as $localeFunction) {
                [$localeFunction] = explode('##', $localeFunction); //    Strip out comments
                if (str_contains($localeFunction, '=')) {
                    [$fName, $lfName] = array_map('trim', explode('=', $localeFunction));
                    if ($fName === 'FALSE') {
                        $falseTrueArray[0][] = $lfName;
                    } elseif ($fName === 'TRUE') {
                        $falseTrueArray[1][] = $lfName;
                    }
                }
            }
        }
        self::$falseTrueArray = $falseTrueArray;

        return $falseTrueArray;
    }

    /**
     * Set the locale code.
     *
     * @param string $locale The locale to use for formula translation, eg: 'en_us'
     */
    public function setLocale(string $locale): bool
    {
        //    Identify our locale and language
        $language = $locale = strtolower($locale);
        if (str_contains($locale, '_')) {
            [$language] = explode('_', $locale);
        }
        if (count(self::$validLocaleLanguages) == 1) {
            self::loadLocales();
        }

        //    Test whether we have any language data for this language (any locale)
        if (in_array($language, self::$validLocaleLanguages, true)) {
            //    initialise language/locale settings
            self::$localeFunctions = [];
            self::$localeArgumentSeparator = ',';
            self::$localeBoolean = ['TRUE' => 'TRUE', 'FALSE' => 'FALSE', 'NULL' => 'NULL'];

            //    Default is US English, if user isn't requesting US english, then read the necessary data from the locale files
            if ($locale !== 'en_us') {
                $localeDir = implode(DIRECTORY_SEPARATOR, [__DIR__, 'locale', null]);

                //    Search for a file with a list of function names for locale
                try {
                    $functionNamesFile = $this->getLocaleFile($localeDir, $locale, $language, 'functions');
                } catch (Exception $e) {
                    return false;
                }

                //    Retrieve the list of locale or language specific function names
                $localeFunctions = file($functionNamesFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) ?: [];
                $phpSpreadsheetFunctions = &self::getFunctionsAddress();
                foreach ($localeFunctions as $localeFunction) {
                    [$localeFunction] = explode('##', $localeFunction); //    Strip out comments
                    if (str_contains($localeFunction, '=')) {
                        [$fName, $lfName] = array_map('trim', explode('=', $localeFunction));
                        if ((str_starts_with($fName, '*') || isset($phpSpreadsheetFunctions[$fName])) && ($lfName != '') && ($fName != $lfName)) {
                            self::$localeFunctions[$fName] = $lfName;
                        }
                    }
                }
                //    Default the TRUE and FALSE constants to the locale names of the TRUE() and FALSE() functions
                if (isset(self::$localeFunctions['TRUE'])) {
                    self::$localeBoolean['TRUE'] = self::$localeFunctions['TRUE'];
                }
                if (isset(self::$localeFunctions['FALSE'])) {
                    self::$localeBoolean['FALSE'] = self::$localeFunctions['FALSE'];
                }

                try {
                    $configFile = $this->getLocaleFile($localeDir, $locale, $language, 'config');
                } catch (Exception) {
                    return false;
                }

                $localeSettings = file($configFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) ?: [];
                foreach ($localeSettings as $localeSetting) {
                    [$localeSetting] = explode('##', $localeSetting); //    Strip out comments
                    if (str_contains($localeSetting, '=')) {
                        [$settingName, $settingValue] = array_map('trim', explode('=', $localeSetting));
                        $settingName = strtoupper($settingName);
                        if ($settingValue !== '') {
                            switch ($settingName) {
                                case 'ARGUMENTSEPARATOR':
                                    self::$localeArgumentSeparator = $settingValue;

                                    break;
                            }
                        }
                    }
                }
            }

            self::$functionReplaceFromExcel = self::$functionReplaceToExcel
            = self::$functionReplaceFromLocale = self::$functionReplaceToLocale = null;
            self::$localeLanguage = $locale;

            return true;
        }

        return false;
    }

    public static function translateSeparator(
        string $fromSeparator,
        string $toSeparator,
        string $formula,
        int &$inBracesLevel,
        string $openBrace = self::FORMULA_OPEN_FUNCTION_BRACE,
        string $closeBrace = self::FORMULA_CLOSE_FUNCTION_BRACE
    ): string {
        $strlen = mb_strlen($formula);
        for ($i = 0; $i < $strlen; ++$i) {
            $chr = mb_substr($formula, $i, 1);
            switch ($chr) {
                case $openBrace:
                    ++$inBracesLevel;

                    break;
                case $closeBrace:
                    --$inBracesLevel;

                    break;
                case $fromSeparator:
                    if ($inBracesLevel > 0) {
                        $formula = mb_substr($formula, 0, $i) . $toSeparator . mb_substr($formula, $i + 1);
                    }
            }
        }

        return $formula;
    }

    /**
     * @param string[] $from
     * @param string[] $to
     */
    protected static function translateFormulaBlock(
        array $from,
        array $to,
        string $formula,
        int &$inFunctionBracesLevel,
        int &$inMatrixBracesLevel,
        string $fromSeparator,
        string $toSeparator
    ): string {
        // Function Names
        $formula = (string) preg_replace($from, $to, $formula);

        // Temporarily adjust matrix separators so that they won't be confused with function arguments
        $formula = self::translateSeparator(';', '|', $formula, $inMatrixBracesLevel, self::FORMULA_OPEN_MATRIX_BRACE, self::FORMULA_CLOSE_MATRIX_BRACE);
        $formula = self::translateSeparator(',', '!', $formula, $inMatrixBracesLevel, self::FORMULA_OPEN_MATRIX_BRACE, self::FORMULA_CLOSE_MATRIX_BRACE);
        // Function Argument Separators
        $formula = self::translateSeparator($fromSeparator, $toSeparator, $formula, $inFunctionBracesLevel);
        // Restore matrix separators
        $formula = self::translateSeparator('|', ';', $formula, $inMatrixBracesLevel, self::FORMULA_OPEN_MATRIX_BRACE, self::FORMULA_CLOSE_MATRIX_BRACE);
        $formula = self::translateSeparator('!', ',', $formula, $inMatrixBracesLevel, self::FORMULA_OPEN_MATRIX_BRACE, self::FORMULA_CLOSE_MATRIX_BRACE);

        return $formula;
    }

    /**
     * @param string[] $from
     * @param string[] $to
     */
    protected static function translateFormula(array $from, array $to, string $formula, string $fromSeparator, string $toSeparator): string
    {
        // Convert any Excel function names and constant names to the required language;
        //     and adjust function argument separators
        if (self::$localeLanguage !== 'en_us') {
            $inFunctionBracesLevel = 0;
            $inMatrixBracesLevel = 0;
            //    If there is the possibility of separators within a quoted string, then we treat them as literals
            if (str_contains($formula, self::FORMULA_STRING_QUOTE)) {
                //    So instead we skip replacing in any quoted strings by only replacing in every other array element
                //       after we've exploded the formula
                $temp = explode(self::FORMULA_STRING_QUOTE, $formula);
                $notWithinQuotes = false;
                foreach ($temp as &$value) {
                    //    Only adjust in alternating array entries
                    $notWithinQuotes = $notWithinQuotes === false;
                    if ($notWithinQuotes === true) {
                        $value = self::translateFormulaBlock($from, $to, $value, $inFunctionBracesLevel, $inMatrixBracesLevel, $fromSeparator, $toSeparator);
                    }
                }
                unset($value);
                //    Then rebuild the formula string
                $formula = implode(self::FORMULA_STRING_QUOTE, $temp);
            } else {
                //    If there's no quoted strings, then we do a simple count/replace
                $formula = self::translateFormulaBlock($from, $to, $formula, $inFunctionBracesLevel, $inMatrixBracesLevel, $fromSeparator, $toSeparator);
            }
        }

        return $formula;
    }

    /** @var null|string[] */
    private static ?array $functionReplaceFromExcel;

    /** @var null|string[] */
    private static ?array $functionReplaceToLocale;

    public function translateFormulaToLocale(string $formula): string
    {
        $formula = preg_replace(self::CALCULATION_REGEXP_STRIP_XLFN_XLWS, '', $formula) ?? '';
        // Build list of function names and constants for translation
        if (self::$functionReplaceFromExcel === null) {
            self::$functionReplaceFromExcel = [];
            foreach (array_keys(self::$localeFunctions) as $excelFunctionName) {
                self::$functionReplaceFromExcel[] = '/(@?[^\w\.])' . preg_quote($excelFunctionName, '/') . '([\s]*\()/ui';
            }
            foreach (array_keys(self::$localeBoolean) as $excelBoolean) {
                self::$functionReplaceFromExcel[] = '/(@?[^\w\.])' . preg_quote($excelBoolean, '/') . '([^\w\.])/ui';
            }
        }

        if (self::$functionReplaceToLocale === null) {
            self::$functionReplaceToLocale = [];
            foreach (self::$localeFunctions as $localeFunctionName) {
                self::$functionReplaceToLocale[] = '$1' . trim($localeFunctionName) . '$2';
            }
            foreach (self::$localeBoolean as $localeBoolean) {
                self::$functionReplaceToLocale[] = '$1' . trim($localeBoolean) . '$2';
            }
        }

        return self::translateFormula(
            self::$functionReplaceFromExcel,
            self::$functionReplaceToLocale,
            $formula,
            ',',
            self::$localeArgumentSeparator
        );
    }

    /** @var null|string[] */
    protected static ?array $functionReplaceFromLocale;

    /** @var null|string[] */
    protected static ?array $functionReplaceToExcel;

    public function translateFormulaToEnglish(string $formula): string
    {
        if (self::$functionReplaceFromLocale === null) {
            self::$functionReplaceFromLocale = [];
            foreach (self::$localeFunctions as $localeFunctionName) {
                self::$functionReplaceFromLocale[] = '/(@?[^\w\.])' . preg_quote($localeFunctionName, '/') . '([\s]*\()/ui';
            }
            foreach (self::$localeBoolean as $excelBoolean) {
                self::$functionReplaceFromLocale[] = '/(@?[^\w\.])' . preg_quote($excelBoolean, '/') . '([^\w\.])/ui';
            }
        }

        if (self::$functionReplaceToExcel === null) {
            self::$functionReplaceToExcel = [];
            foreach (array_keys(self::$localeFunctions) as $excelFunctionName) {
                self::$functionReplaceToExcel[] = '$1' . trim($excelFunctionName) . '$2';
            }
            foreach (array_keys(self::$localeBoolean) as $excelBoolean) {
                self::$functionReplaceToExcel[] = '$1' . trim($excelBoolean) . '$2';
            }
        }

        return self::translateFormula(self::$functionReplaceFromLocale, self::$functionReplaceToExcel, $formula, self::$localeArgumentSeparator, ',');
    }

    public static function localeFunc(string $function): string
    {
        if (self::$localeLanguage !== 'en_us') {
            $functionName = trim($function, '(');
            if (isset(self::$localeFunctions[$functionName])) {
                $brace = ($functionName != $function);
                $function = self::$localeFunctions[$functionName];
                if ($brace) {
                    $function .= '(';
                }
            }
        }

        return $function;
    }
}

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Calculation;
4
5	class CalculationLocale extends CalculationBase
6	{
7	public const FORMULA_OPEN_FUNCTION_BRACE = '(';
8	public const FORMULA_CLOSE_FUNCTION_BRACE = ')';
9	public const FORMULA_OPEN_MATRIX_BRACE = '{';
10	public const FORMULA_CLOSE_MATRIX_BRACE = '}';
11	public const FORMULA_STRING_QUOTE = '"';
12
13	// Strip xlfn and xlws prefixes from function name
14	public const CALCULATION_REGEXP_STRIP_XLFN_XLWS = '/(_xlfn[.])?(_xlws[.])?(?=[\p{L}][\p{L}\p{N}\.][\s][(])/';
15
16	/**
17	* The current locale setting.
18	*/
19	protected static string $localeLanguage = 'en_us'; // US English (default locale)
20
21	/**
22	* List of available locale settings
23	* Note that this is read for the locale subdirectory only when requested.
24	*
25	* @var string[]
26	*/
27	protected static array $validLocaleLanguages = [
28	'en', // English (default language)
29	];
30
31	/**
32	* Locale-specific argument separator for function arguments.
33	*/
34	protected static string $localeArgumentSeparator = ',';
35
36	/** @var string[] */
37	protected static array $localeFunctions = [];
38
39	/**
40	* Locale-specific translations for Excel constants (True, False and Null).
41	*
42	* @var array<string, string>
43	*/
44	protected static array $localeBoolean = [
45	'TRUE' => 'TRUE',
46	'FALSE' => 'FALSE',
47	'NULL' => 'NULL',
48	];
49
50	/** @var array<int, array<int, string>> */
51	protected static array $falseTrueArray = [];
52
53	public static function getLocaleBoolean(string $index): string
54	{
55	return self::$localeBoolean[$index];	7✔
56	}
57
58	protected static function loadLocales(): void
59	{
60	$localeFileDirectory = __DIR__ . '/locale/';	3✔
61	$localeFileNames = glob($localeFileDirectory . '*', GLOB_ONLYDIR) ?: [];	3✔
62	foreach ($localeFileNames as $filename) {	3✔
63	$filename = substr($filename, strlen($localeFileDirectory));	3✔
64	if ($filename != 'en') {	3✔
65	self::$validLocaleLanguages[] = $filename;	3✔
66	}
67	}
68	}
69
70	/**
71	* Return the locale-specific translation of TRUE.
72	*
73	* @return string locale-specific translation of TRUE
74	*/
75	public static function getTRUE(): string
76	{
77	return self::$localeBoolean['TRUE'];	1,003✔
78	}
79
80	/**
81	* Return the locale-specific translation of FALSE.
82	*
83	* @return string locale-specific translation of FALSE
84	*/
85	public static function getFALSE(): string
86	{
87	return self::$localeBoolean['FALSE'];	985✔
88	}
89
90	/**
91	* Get the currently defined locale code.
92	*/
93	public function getLocale(): string
94	{
95	return self::$localeLanguage;	784✔
96	}
97
98	protected function getLocaleFile(string $localeDir, string $locale, string $language, string $file): string
99	{
100	$localeFileName = $localeDir . str_replace('_', DIRECTORY_SEPARATOR, $locale)	120✔
101	. DIRECTORY_SEPARATOR . $file;	120✔
102	if (!file_exists($localeFileName)) {	120✔
103	// If there isn't a locale specific file, look for a language specific file
104	$localeFileName = $localeDir . $language . DIRECTORY_SEPARATOR . $file;	29✔
105	if (!file_exists($localeFileName)) {	29✔
106	throw new Exception('Locale file not found');	3✔
107	}
108	}
109
110	return $localeFileName;	117✔
111	}
112
113	/** @return array<int, array<int, string>> */
114	public function getFalseTrueArray(): array
115	{
116	if (!empty(self::$falseTrueArray)) {	1✔
UNCOV 117	return self::$falseTrueArray;	×
118	}
119	if (count(self::$validLocaleLanguages) == 1) {	1✔
UNCOV 120	self::loadLocales();	×
121	}
122	$falseTrueArray = [['FALSE'], ['TRUE']];	1✔
123	foreach (self::$validLocaleLanguages as $language) {	1✔
124	if (str_starts_with($language, 'en')) {	1✔
125	continue;	1✔
126	}
127	$locale = $language;	1✔
128	if (str_contains($locale, '_')) {	1✔
UNCOV 129	[$language] = explode('_', $locale);	×
130	}
131	$localeDir = implode(DIRECTORY_SEPARATOR, [__DIR__, 'locale', null]);	1✔
132
133	try {
134	$functionNamesFile = $this->getLocaleFile($localeDir, $locale, $language, 'functions');	1✔
UNCOV 135	} catch (Exception $e) {	×
UNCOV 136	continue;	×
137	}
138	// Retrieve the list of locale or language specific function names
139	$localeFunctions = file($functionNamesFile, FILE_IGNORE_NEW_LINES \| FILE_SKIP_EMPTY_LINES) ?: [];	1✔
140	foreach ($localeFunctions as $localeFunction) {	1✔
141	[$localeFunction] = explode('##', $localeFunction); // Strip out comments	1✔
142	if (str_contains($localeFunction, '=')) {	1✔
143	[$fName, $lfName] = array_map('trim', explode('=', $localeFunction));	1✔
144	if ($fName === 'FALSE') {	1✔
145	$falseTrueArray[0][] = $lfName;	1✔
146	} elseif ($fName === 'TRUE') {	1✔
147	$falseTrueArray[1][] = $lfName;	1✔
148	}
149	}
150	}
151	}
152	self::$falseTrueArray = $falseTrueArray;	1✔
153
154	return $falseTrueArray;	1✔
155	}
156
157	/**
158	* Set the locale code.
159	*
160	* @param string $locale The locale to use for formula translation, eg: 'en_us'
161	*/
162	public function setLocale(string $locale): bool
163	{
164	// Identify our locale and language
165	$language = $locale = strtolower($locale);	784✔
166	if (str_contains($locale, '_')) {	784✔
167	[$language] = explode('_', $locale);	784✔
168	}
169	if (count(self::$validLocaleLanguages) == 1) {	784✔
170	self::loadLocales();	3✔
171	}
172
173	// Test whether we have any language data for this language (any locale)
174	if (in_array($language, self::$validLocaleLanguages, true)) {	784✔
175	// initialise language/locale settings
176	self::$localeFunctions = [];	784✔
177	self::$localeArgumentSeparator = ',';	784✔
178	self::$localeBoolean = ['TRUE' => 'TRUE', 'FALSE' => 'FALSE', 'NULL' => 'NULL'];	784✔
179
180	// Default is US English, if user isn't requesting US english, then read the necessary data from the locale files
181	if ($locale !== 'en_us') {	784✔
182	$localeDir = implode(DIRECTORY_SEPARATOR, [__DIR__, 'locale', null]);	119✔
183
184	// Search for a file with a list of function names for locale
185	try {
186	$functionNamesFile = $this->getLocaleFile($localeDir, $locale, $language, 'functions');	119✔
187	} catch (Exception $e) {	3✔
188	return false;	3✔
189	}
190
191	// Retrieve the list of locale or language specific function names
192	$localeFunctions = file($functionNamesFile, FILE_IGNORE_NEW_LINES \| FILE_SKIP_EMPTY_LINES) ?: [];	116✔
193	$phpSpreadsheetFunctions = &self::getFunctionsAddress();	116✔
194	foreach ($localeFunctions as $localeFunction) {	116✔
195	[$localeFunction] = explode('##', $localeFunction); // Strip out comments	116✔
196	if (str_contains($localeFunction, '=')) {	116✔
197	[$fName, $lfName] = array_map('trim', explode('=', $localeFunction));	116✔
198	if ((str_starts_with($fName, '*') \|\| isset($phpSpreadsheetFunctions[$fName])) && ($lfName != '') && ($fName != $lfName)) {	116✔
199	self::$localeFunctions[$fName] = $lfName;	116✔
200	}
201	}
202	}
203	// Default the TRUE and FALSE constants to the locale names of the TRUE() and FALSE() functions
204	if (isset(self::$localeFunctions['TRUE'])) {	116✔
205	self::$localeBoolean['TRUE'] = self::$localeFunctions['TRUE'];	116✔
206	}
207	if (isset(self::$localeFunctions['FALSE'])) {	116✔
208	self::$localeBoolean['FALSE'] = self::$localeFunctions['FALSE'];	116✔
209	}
210
211	try {
212	$configFile = $this->getLocaleFile($localeDir, $locale, $language, 'config');	116✔
UNCOV 213	} catch (Exception) {	×
UNCOV 214	return false;	×
215	}
216
217	$localeSettings = file($configFile, FILE_IGNORE_NEW_LINES \| FILE_SKIP_EMPTY_LINES) ?: [];	116✔
218	foreach ($localeSettings as $localeSetting) {	116✔
219	[$localeSetting] = explode('##', $localeSetting); // Strip out comments	116✔
220	if (str_contains($localeSetting, '=')) {	116✔
221	[$settingName, $settingValue] = array_map('trim', explode('=', $localeSetting));	116✔
222	$settingName = strtoupper($settingName);	116✔
223	if ($settingValue !== '') {	116✔
224	switch ($settingName) {
225	case 'ARGUMENTSEPARATOR':	116✔
226	self::$localeArgumentSeparator = $settingValue;	116✔
227
228	break;	116✔
229	}
230	}
231	}
232	}
233	}
234
235	self::$functionReplaceFromExcel = self::$functionReplaceToExcel	784✔
236	= self::$functionReplaceFromLocale = self::$functionReplaceToLocale = null;	784✔
237	self::$localeLanguage = $locale;	784✔
238
239	return true;	784✔
240	}
241
242	return false;	3✔
243	}
244
245	public static function translateSeparator(
246	string $fromSeparator,
247	string $toSeparator,
248	string $formula,
249	int &$inBracesLevel,
250	string $openBrace = self::FORMULA_OPEN_FUNCTION_BRACE,
251	string $closeBrace = self::FORMULA_CLOSE_FUNCTION_BRACE
252	): string {
253	$strlen = mb_strlen($formula);	52✔
254	for ($i = 0; $i < $strlen; ++$i) {	52✔
255	$chr = mb_substr($formula, $i, 1);	52✔
256	switch ($chr) {
257	case $openBrace:	52✔
258	++$inBracesLevel;	46✔
259
260	break;	46✔
261	case $closeBrace:	52✔
262	--$inBracesLevel;	46✔
263
264	break;	46✔
265	case $fromSeparator:	52✔
266	if ($inBracesLevel > 0) {	31✔
267	$formula = mb_substr($formula, 0, $i) . $toSeparator . mb_substr($formula, $i + 1);	31✔
268	}
269	}
270	}
271
272	return $formula;	52✔
273	}
274
275	/**
276	* @param string[] $from
277	* @param string[] $to
278	*/
279	protected static function translateFormulaBlock(
280	array $from,
281	array $to,
282	string $formula,
283	int &$inFunctionBracesLevel,
284	int &$inMatrixBracesLevel,
285	string $fromSeparator,
286	string $toSeparator
287	): string {
288	// Function Names
289	$formula = (string) preg_replace($from, $to, $formula);	19✔
290
291	// Temporarily adjust matrix separators so that they won't be confused with function arguments
292	$formula = self::translateSeparator(';', '\|', $formula, $inMatrixBracesLevel, self::FORMULA_OPEN_MATRIX_BRACE, self::FORMULA_CLOSE_MATRIX_BRACE);	19✔
293	$formula = self::translateSeparator(',', '!', $formula, $inMatrixBracesLevel, self::FORMULA_OPEN_MATRIX_BRACE, self::FORMULA_CLOSE_MATRIX_BRACE);	19✔
294	// Function Argument Separators
295	$formula = self::translateSeparator($fromSeparator, $toSeparator, $formula, $inFunctionBracesLevel);	19✔
296	// Restore matrix separators
297	$formula = self::translateSeparator('\|', ';', $formula, $inMatrixBracesLevel, self::FORMULA_OPEN_MATRIX_BRACE, self::FORMULA_CLOSE_MATRIX_BRACE);	19✔
298	$formula = self::translateSeparator('!', ',', $formula, $inMatrixBracesLevel, self::FORMULA_OPEN_MATRIX_BRACE, self::FORMULA_CLOSE_MATRIX_BRACE);	19✔
299
300	return $formula;	19✔
301	}
302
303	/**
304	* @param string[] $from
305	* @param string[] $to
306	*/
307	protected static function translateFormula(array $from, array $to, string $formula, string $fromSeparator, string $toSeparator): string
308	{
309	// Convert any Excel function names and constant names to the required language;
310	// and adjust function argument separators
311	if (self::$localeLanguage !== 'en_us') {	19✔
312	$inFunctionBracesLevel = 0;	19✔
313	$inMatrixBracesLevel = 0;	19✔
314	// If there is the possibility of separators within a quoted string, then we treat them as literals
315	if (str_contains($formula, self::FORMULA_STRING_QUOTE)) {	19✔
316	// So instead we skip replacing in any quoted strings by only replacing in every other array element
317	// after we've exploded the formula
318	$temp = explode(self::FORMULA_STRING_QUOTE, $formula);	6✔
319	$notWithinQuotes = false;	6✔
320	foreach ($temp as &$value) {	6✔
321	// Only adjust in alternating array entries
322	$notWithinQuotes = $notWithinQuotes === false;	6✔
323	if ($notWithinQuotes === true) {	6✔
324	$value = self::translateFormulaBlock($from, $to, $value, $inFunctionBracesLevel, $inMatrixBracesLevel, $fromSeparator, $toSeparator);	6✔
325	}
326	}
327	unset($value);	6✔
328	// Then rebuild the formula string
329	$formula = implode(self::FORMULA_STRING_QUOTE, $temp);	6✔
330	} else {
331	// If there's no quoted strings, then we do a simple count/replace
332	$formula = self::translateFormulaBlock($from, $to, $formula, $inFunctionBracesLevel, $inMatrixBracesLevel, $fromSeparator, $toSeparator);	13✔
333	}
334	}
335
336	return $formula;	19✔
337	}
338
339	/** @var null\|string[] */
340	private static ?array $functionReplaceFromExcel;
341
342	/** @var null\|string[] */
343	private static ?array $functionReplaceToLocale;
344
345	public function translateFormulaToLocale(string $formula): string
346	{
347	$formula = preg_replace(self::CALCULATION_REGEXP_STRIP_XLFN_XLWS, '', $formula) ?? '';	19✔
348	// Build list of function names and constants for translation
349	if (self::$functionReplaceFromExcel === null) {	19✔
350	self::$functionReplaceFromExcel = [];	19✔
351	foreach (array_keys(self::$localeFunctions) as $excelFunctionName) {	19✔
352	self::$functionReplaceFromExcel[] = '/(@?[^\w\.])' . preg_quote($excelFunctionName, '/') . '([\s]*\()/ui';	19✔
353	}
354	foreach (array_keys(self::$localeBoolean) as $excelBoolean) {	19✔
355	self::$functionReplaceFromExcel[] = '/(@?[^\w\.])' . preg_quote($excelBoolean, '/') . '([^\w\.])/ui';	19✔
356	}
357	}
358
359	if (self::$functionReplaceToLocale === null) {	19✔
360	self::$functionReplaceToLocale = [];	19✔
361	foreach (self::$localeFunctions as $localeFunctionName) {	19✔
362	self::$functionReplaceToLocale[] = '$1' . trim($localeFunctionName) . '$2';	19✔
363	}
364	foreach (self::$localeBoolean as $localeBoolean) {	19✔
365	self::$functionReplaceToLocale[] = '$1' . trim($localeBoolean) . '$2';	19✔
366	}
367	}
368
369	return self::translateFormula(	19✔
370	self::$functionReplaceFromExcel,	19✔
371	self::$functionReplaceToLocale,	19✔
372	$formula,	19✔
373	',',	19✔
374	self::$localeArgumentSeparator	19✔
375	);	19✔
376	}
377
378	/** @var null\|string[] */
379	protected static ?array $functionReplaceFromLocale;
380
381	/** @var null\|string[] */
382	protected static ?array $functionReplaceToExcel;
383
384	public function translateFormulaToEnglish(string $formula): string
385	{
386	if (self::$functionReplaceFromLocale === null) {	19✔
387	self::$functionReplaceFromLocale = [];	19✔
388	foreach (self::$localeFunctions as $localeFunctionName) {	19✔
389	self::$functionReplaceFromLocale[] = '/(@?[^\w\.])' . preg_quote($localeFunctionName, '/') . '([\s]*\()/ui';	19✔
390	}
391	foreach (self::$localeBoolean as $excelBoolean) {	19✔
392	self::$functionReplaceFromLocale[] = '/(@?[^\w\.])' . preg_quote($excelBoolean, '/') . '([^\w\.])/ui';	19✔
393	}
394	}
395
396	if (self::$functionReplaceToExcel === null) {	19✔
397	self::$functionReplaceToExcel = [];	19✔
398	foreach (array_keys(self::$localeFunctions) as $excelFunctionName) {	19✔
399	self::$functionReplaceToExcel[] = '$1' . trim($excelFunctionName) . '$2';	19✔
400	}
401	foreach (array_keys(self::$localeBoolean) as $excelBoolean) {	19✔
402	self::$functionReplaceToExcel[] = '$1' . trim($excelBoolean) . '$2';	19✔
403	}
404	}
405
406	return self::translateFormula(self::$functionReplaceFromLocale, self::$functionReplaceToExcel, $formula, self::$localeArgumentSeparator, ',');	19✔
407	}
408
409	public static function localeFunc(string $function): string
410	{
411	if (self::$localeLanguage !== 'en_us') {	11,813✔
412	$functionName = trim($function, '(');	73✔
413	if (isset(self::$localeFunctions[$functionName])) {	73✔
414	$brace = ($functionName != $function);	71✔
415	$function = self::$localeFunctions[$functionName];	71✔
416	if ($brace) {	71✔
417	$function .= '(';	68✔
418	}
419	}
420	}
421
422	return $function;	11,813✔
423	}
424	}

PHPOffice / PhpSpreadsheet / 17380917348

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