[Summary view]

Differences Between: [Versions 401 and 402] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Reader\Ods;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  
   7  class FormulaTranslator
   8  {
   9      public static function convertToExcelAddressValue(string $openOfficeAddress): string
  10      {
  11          $excelAddress = $openOfficeAddress;
  12  
  13          // Cell range 3-d reference
  14          // As we don't support 3-d ranges, we're just going to take a quick and dirty approach
  15          //  and assume that the second worksheet reference is the same as the first
  16          $excelAddress = (string) preg_replace(
  17              [
  18                  '/\$?([^\.]+)\.([^\.]+):\$?([^\.]+)\.([^\.]+)/miu',
  19                  '/\$?([^\.]+)\.([^\.]+):\.([^\.]+)/miu', // Cell range reference in another sheet
  20                  '/\$?([^\.]+)\.([^\.]+)/miu', // Cell reference in another sheet
  21                  '/\.([^\.]+):\.([^\.]+)/miu', // Cell range reference
  22                  '/\.([^\.]+)/miu', // Simple cell reference
  23              ],
  24              [
  25                  '$1!$2:$4',
  26                  '$1!$2:$3',
  27                  '$1!$2',
  28                  '$1:$2',
  29                  '$1',
  30              ],
  31              $excelAddress
  32          );
  33  
  34          return $excelAddress;
  35      }
  36  
  37      public static function convertToExcelFormulaValue(string $openOfficeFormula): string
  38      {
  39          $temp = explode(Calculation::FORMULA_STRING_QUOTE, $openOfficeFormula);
  40          $tKey = false;
  41          $inMatrixBracesLevel = 0;
  42          $inFunctionBracesLevel = 0;
  43          foreach ($temp as &$value) {
  44              // @var string $value
  45              // Only replace in alternate array entries (i.e. non-quoted blocks)
  46              //      so that conversion isn't done in string values
  47              if ($tKey = !$tKey) {
  48                  $value = (string) preg_replace(
  49                      [
  50                          '/\[\$?([^\.]+)\.([^\.]+):\.([^\.]+)\]/miu', // Cell range reference in another sheet
  51                          '/\[\$?([^\.]+)\.([^\.]+)\]/miu', // Cell reference in another sheet
  52                          '/\[\.([^\.]+):\.([^\.]+)\]/miu', // Cell range reference
  53                          '/\[\.([^\.]+)\]/miu', // Simple cell reference
  54                      ],
  55                      [
  56                          '$1!$2:$3',
  57                          '$1!$2',
  58                          '$1:$2',
  59                          '$1',
  60                      ],
  61                      $value
  62                  );
  63                  // Convert references to defined names/formulae
  64                  $value = str_replace('$$', '', $value);
  65  
  66                  // Convert ODS function argument separators to Excel function argument separators
  67                  $value = Calculation::translateSeparator(';', ',', $value, $inFunctionBracesLevel);
  68  
  69                  // Convert ODS matrix separators to Excel matrix separators
  70                  $value = Calculation::translateSeparator(
  71                      ';',
  72                      ',',
  73                      $value,
  74                      $inMatrixBracesLevel,
  75                      Calculation::FORMULA_OPEN_MATRIX_BRACE,
  76                      Calculation::FORMULA_CLOSE_MATRIX_BRACE
  77                  );
  78                  $value = Calculation::translateSeparator(
  79                      '|',
  80                      ';',
  81                      $value,
  82                      $inMatrixBracesLevel,
  83                      Calculation::FORMULA_OPEN_MATRIX_BRACE,
  84                      Calculation::FORMULA_CLOSE_MATRIX_BRACE
  85                  );
  86  
  87                  $value = (string) preg_replace('/COM\.MICROSOFT\./ui', '', $value);
  88              }
  89          }
  90  
  91          // Then rebuild the formula string
  92          $excelFormula = implode('"', $temp);
  93  
  94          return $excelFormula;
  95      }
  96  }