OwlCyberSecurity - MANAGER

Edit File: FormulaTranslator.php

<?php

namespace PhpOffice\PhpSpreadsheet\Reader\Ods;

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;

class FormulaTranslator
{
    public static function convertToExcelAddressValue(string $openOfficeAddress): string
    {
        $excelAddress = $openOfficeAddress;

// Cell range 3-d reference
        // As we don't support 3-d ranges, we're just going to take a quick and dirty approach
        //  and assume that the second worksheet reference is the same as the first
        $excelAddress = (string) preg_replace(
            [
                '/\$?([^\.]+)\.([^\.]+):\$?([^\.]+)\.([^\.]+)/miu',
                '/\$?([^\.]+)\.([^\.]+):\.([^\.]+)/miu', // Cell range reference in another sheet
                '/\$?([^\.]+)\.([^\.]+)/miu', // Cell reference in another sheet
                '/\.([^\.]+):\.([^\.]+)/miu', // Cell range reference
                '/\.([^\.]+)/miu', // Simple cell reference
            ],
            [
                '$1!$2:$4',
                '$1!$2:$3',
                '$1!$2',
                '$1:$2',
                '$1',
            ],
            $excelAddress
        );

return $excelAddress;
    }

public static function convertToExcelFormulaValue(string $openOfficeFormula): string
    {
        $temp = explode(Calculation::FORMULA_STRING_QUOTE, $openOfficeFormula);
        $tKey = false;
        $inMatrixBracesLevel = 0;
        $inFunctionBracesLevel = 0;
        foreach ($temp as &$value) {
            // @var string $value
            // Only replace in alternate array entries (i.e. non-quoted blocks)
            //      so that conversion isn't done in string values
            $tKey = $tKey === false;
            if ($tKey) {
                $value = (string) preg_replace(
                    [
                        '/\[\$?([^\.]+)\.([^\.]+):\.([^\.]+)\]/miu', // Cell range reference in another sheet
                        '/\[\$?([^\.]+)\.([^\.]+)\]/miu', // Cell reference in another sheet
                        '/\[\.([^\.]+):\.([^\.]+)\]/miu', // Cell range reference
                        '/\[\.([^\.]+)\]/miu', // Simple cell reference
                    ],
                    [
                        '$1!$2:$3',
                        '$1!$2',
                        '$1:$2',
                        '$1',
                    ],
                    $value
                );
                // Convert references to defined names/formulae
                $value = str_replace('$$', '', $value);

// Convert ODS function argument separators to Excel function argument separators
                $value = Calculation::translateSeparator(';', ',', $value, $inFunctionBracesLevel);

// Convert ODS matrix separators to Excel matrix separators
                $value = Calculation::translateSeparator(
                    ';',
                    ',',
                    $value,
                    $inMatrixBracesLevel,
                    Calculation::FORMULA_OPEN_MATRIX_BRACE,
                    Calculation::FORMULA_CLOSE_MATRIX_BRACE
                );
                $value = Calculation::translateSeparator(
                    '|',
                    ';',
                    $value,
                    $inMatrixBracesLevel,
                    Calculation::FORMULA_OPEN_MATRIX_BRACE,
                    Calculation::FORMULA_CLOSE_MATRIX_BRACE
                );

$value = (string) preg_replace('/COM\.MICROSOFT\./ui', '', $value);
            }
        }

// Then rebuild the formula string
        $excelFormula = implode('"', $temp);

return $excelFormula;
    }
}