コード例 #1
0
 public function refresh(\PHPExcel\Worksheet $worksheet, $flatten = true)
 {
     if ($this->dataSource !== null) {
         $calcEngine = \PHPExcel\Calculation::getInstance($worksheet->getParent());
         $newDataValues = \PHPExcel\Calculation::unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->dataSource, null, $worksheet->getCell('A1')));
         if ($flatten) {
             $this->dataValues = \PHPExcel\Calculation\Functions::flattenArray($newDataValues);
             foreach ($this->dataValues as &$dataValue) {
                 if (!empty($dataValue) && $dataValue[0] == '#') {
                     $dataValue = 0.0;
                 }
             }
             unset($dataValue);
         } else {
             $cellRange = explode('!', $this->dataSource);
             if (count($cellRange) > 1) {
                 list(, $cellRange) = $cellRange;
             }
             $dimensions = \PHPExcel\Cell::rangeDimension(str_replace('$', '', $cellRange));
             if ($dimensions[0] == 1 || $dimensions[1] == 1) {
                 $this->dataValues = \PHPExcel\Calculation\Functions::flattenArray($newDataValues);
             } else {
                 $newArray = array_values(array_shift($newDataValues));
                 foreach ($newArray as $i => $newDataSet) {
                     $newArray[$i] = array($newDataSet);
                 }
                 foreach ($newDataValues as $newDataSet) {
                     $i = 0;
                     foreach ($newDataSet as $newDataVal) {
                         array_unshift($newArray[$i++], $newDataVal);
                     }
                 }
                 $this->dataValues = $newArray;
             }
         }
         $this->pointCount = count($this->dataValues);
     }
 }
コード例 #2
0
ファイル: SYLK.php プロジェクト: kameshwariv/testexample
 /**
  * Loads PHPExcel from file into PHPExcel instance
  *
  * @param     string         $pFilename
  * @param     \PHPExcel\Spreadsheet    $objPHPExcel
  * @return    \PHPExcel\Spreadsheet
  * @throws    Exception
  */
 public function loadIntoExisting($pFilename, \PHPExcel\Spreadsheet $objPHPExcel)
 {
     // Open file
     $this->openFile($pFilename);
     if (!$this->isValidFormat()) {
         fclose($this->fileHandle);
         throw new Exception($pFilename . " is an Invalid Spreadsheet file.");
     }
     $fileHandle = $this->fileHandle;
     rewind($fileHandle);
     // Create new Worksheets
     while ($objPHPExcel->getSheetCount() <= $this->sheetIndex) {
         $objPHPExcel->createSheet();
     }
     $objPHPExcel->setActiveSheetIndex($this->sheetIndex);
     $fromFormats = array('\\-', '\\ ');
     $toFormats = array('-', ' ');
     // Loop through file
     $rowData = array();
     $column = $row = '';
     // loop through one row (line) at a time in the file
     while (($rowData = fgets($fileHandle)) !== false) {
         // convert SYLK encoded $rowData to UTF-8
         $rowData = \PHPExcel\Shared\StringHelper::SYLKtoUTF8($rowData);
         // explode each row at semicolons while taking into account that literal semicolon (;)
         // is escaped like this (;;)
         $rowData = explode("\t", str_replace('¤', ';', str_replace(';', "\t", str_replace(';;', '¤', rtrim($rowData)))));
         $dataType = array_shift($rowData);
         //    Read shared styles
         if ($dataType == 'P') {
             $formatArray = array();
             foreach ($rowData as $rowDatum) {
                 switch ($rowDatum[0]) {
                     case 'P':
                         $formatArray['numberformat']['code'] = str_replace($fromFormats, $toFormats, substr($rowDatum, 1));
                         break;
                     case 'E':
                     case 'F':
                         $formatArray['font']['name'] = substr($rowDatum, 1);
                         break;
                     case 'L':
                         $formatArray['font']['size'] = substr($rowDatum, 1);
                         break;
                     case 'S':
                         $styleSettings = substr($rowDatum, 1);
                         for ($i = 0; $i < strlen($styleSettings); ++$i) {
                             switch ($styleSettings[$i]) {
                                 case 'I':
                                     $formatArray['font']['italic'] = true;
                                     break;
                                 case 'D':
                                     $formatArray['font']['bold'] = true;
                                     break;
                                 case 'T':
                                     $formatArray['borders']['top']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
                                     break;
                                 case 'B':
                                     $formatArray['borders']['bottom']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
                                     break;
                                 case 'L':
                                     $formatArray['borders']['left']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
                                     break;
                                 case 'R':
                                     $formatArray['borders']['right']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
                                     break;
                             }
                         }
                         break;
                 }
             }
             $this->formats['P' . $this->format++] = $formatArray;
             //    Read cell value data
         } elseif ($dataType == 'C') {
             $hasCalculatedValue = false;
             $cellData = $cellDataFormula = '';
             foreach ($rowData as $rowDatum) {
                 switch ($rowDatum[0]) {
                     case 'C':
                     case 'X':
                         $column = substr($rowDatum, 1);
                         break;
                     case 'R':
                     case 'Y':
                         $row = substr($rowDatum, 1);
                         break;
                     case 'K':
                         $cellData = substr($rowDatum, 1);
                         break;
                     case 'E':
                         $cellDataFormula = '=' . substr($rowDatum, 1);
                         //    Convert R1C1 style references to A1 style references (but only when not quoted)
                         $temp = explode('"', $cellDataFormula);
                         $key = false;
                         foreach ($temp as &$value) {
                             //    Only count/replace in alternate array entries
                             if ($key = !$key) {
                                 preg_match_all('/(R(\\[?-?\\d*\\]?))(C(\\[?-?\\d*\\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
                                 //    Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
                                 //        through the formula from left to right. Reversing means that we work right to left.through
                                 //        the formula
                                 $cellReferences = array_reverse($cellReferences);
                                 //    Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
                                 //        then modify the formula to use that new reference
                                 foreach ($cellReferences as $cellReference) {
                                     $rowReference = $cellReference[2][0];
                                     //    Empty R reference is the current row
                                     if ($rowReference == '') {
                                         $rowReference = $row;
                                     }
                                     //    Bracketed R references are relative to the current row
                                     if ($rowReference[0] == '[') {
                                         $rowReference = $row + trim($rowReference, '[]');
                                     }
                                     $columnReference = $cellReference[4][0];
                                     //    Empty C reference is the current column
                                     if ($columnReference == '') {
                                         $columnReference = $column;
                                     }
                                     //    Bracketed C references are relative to the current column
                                     if ($columnReference[0] == '[') {
                                         $columnReference = $column + trim($columnReference, '[]');
                                     }
                                     $A1CellReference = \PHPExcel\Cell::stringFromColumnIndex($columnReference - 1) . $rowReference;
                                     $value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
                                 }
                             }
                         }
                         unset($value);
                         //    Then rebuild the formula string
                         $cellDataFormula = implode('"', $temp);
                         $hasCalculatedValue = true;
                         break;
                 }
             }
             $columnLetter = \PHPExcel\Cell::stringFromColumnIndex($column - 1);
             $cellData = \PHPExcel\Calculation::unwrapResult($cellData);
             // Set cell value
             $objPHPExcel->getActiveSheet()->getCell($columnLetter . $row)->setValue($hasCalculatedValue ? $cellDataFormula : $cellData);
             if ($hasCalculatedValue) {
                 $cellData = \PHPExcel\Calculation::unwrapResult($cellData);
                 $objPHPExcel->getActiveSheet()->getCell($columnLetter . $row)->setCalculatedValue($cellData);
             }
             //    Read cell formatting
         } elseif ($dataType == 'F') {
             $formatStyle = $columnWidth = $styleSettings = '';
             $styleData = array();
             foreach ($rowData as $rowDatum) {
                 switch ($rowDatum[0]) {
                     case 'C':
                     case 'X':
                         $column = substr($rowDatum, 1);
                         break;
                     case 'R':
                     case 'Y':
                         $row = substr($rowDatum, 1);
                         break;
                     case 'P':
                         $formatStyle = $rowDatum;
                         break;
                     case 'W':
                         list($startCol, $endCol, $columnWidth) = explode(' ', substr($rowDatum, 1));
                         break;
                     case 'S':
                         $styleSettings = substr($rowDatum, 1);
                         for ($i = 0; $i < strlen($styleSettings); ++$i) {
                             switch ($styleSettings[$i]) {
                                 case 'I':
                                     $styleData['font']['italic'] = true;
                                     break;
                                 case 'D':
                                     $styleData['font']['bold'] = true;
                                     break;
                                 case 'T':
                                     $styleData['borders']['top']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
                                     break;
                                 case 'B':
                                     $styleData['borders']['bottom']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
                                     break;
                                 case 'L':
                                     $styleData['borders']['left']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
                                     break;
                                 case 'R':
                                     $styleData['borders']['right']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
                                     break;
                             }
                         }
                         break;
                 }
             }
             if ($formatStyle > '' && $column > '' && $row > '') {
                 $columnLetter = \PHPExcel\Cell::stringFromColumnIndex($column - 1);
                 if (isset($this->formats[$formatStyle])) {
                     $objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($this->formats[$formatStyle]);
                 }
             }
             if (!empty($styleData) && $column > '' && $row > '') {
                 $columnLetter = \PHPExcel\Cell::stringFromColumnIndex($column - 1);
                 $objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($styleData);
             }
             if ($columnWidth > '') {
                 if ($startCol == $endCol) {
                     $startCol = \PHPExcel\Cell::stringFromColumnIndex($startCol - 1);
                     $objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
                 } else {
                     $startCol = \PHPExcel\Cell::stringFromColumnIndex($startCol - 1);
                     $endCol = \PHPExcel\Cell::stringFromColumnIndex($endCol - 1);
                     $objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
                     do {
                         $objPHPExcel->getActiveSheet()->getColumnDimension(++$startCol)->setWidth($columnWidth);
                     } while ($startCol != $endCol);
                 }
             }
         } else {
             foreach ($rowData as $rowDatum) {
                 switch ($rowDatum[0]) {
                     case 'C':
                     case 'X':
                         $column = substr($rowDatum, 1);
                         break;
                     case 'R':
                     case 'Y':
                         $row = substr($rowDatum, 1);
                         break;
                 }
             }
         }
     }
     // Close file
     fclose($fileHandle);
     // Return
     return $objPHPExcel;
 }