CalculationLocale::translateFormulaToLocale() - Code Metrics - Inspection of "Merge pull request #4398 from oleibman/localesplit..." - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Push — master ( 5868f8...b1beaf )

unknown

created 2025-03-12 07:40 UTC

CalculationLocale::translateFormulaToLocale() B

↳ Parent: CalculationLocale

Complexity

Conditions	7
Paths	4

Size

Total Lines	30
Code Lines	19

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	20
CRAP Score	7

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	19
c	1
b	0
f	0
dl	0
loc	30
ccs	20
cts	20
cp	1
rs	8.8333
cc	7
nc	4
nop	1
crap	7

<?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 = ',';

    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;
    }

    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;
    }

    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;
    }

    private static ?array $functionReplaceFromExcel;

    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
        );
    }

    protected static ?array $functionReplaceFromLocale;

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

PHPOffice / PhpSpreadsheet

Push — master ( 5868f8...b1beaf )

CalculationLocale::translateFormulaToLocale() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like