PHP PHPExcel_Worksheet::rangeToArray方法代码示例

 /**
  * Returns row array with offsets as keys.
  * @return array integer => mixed
  */
 protected function getRow()
 {
     // Try to return cached value
     if ($this->position == $this->lastRowPosition) {
         return $this->lastRow;
     }
     $range = sprintf('A%d:%s%1$d', $this->position, \PHPExcel_Cell::stringFromColumnIndex($this->colsCount - 1));
     $rows = $this->sheet->rangeToArray($range);
     $row = array_pop($rows);
     // Cache value
     $this->lastRowPosition = $this->position;
     $this->lastRow = $row;
     return $row;
 }

 private function populateNamedRanges(\PHPExcel_Worksheet $templateSheet)
 {
     $template = $templateSheet->getParent();
     $excelNamedRanges = array_change_key_case($template->getNamedRanges(), CASE_UPPER);
     if (!isset($excelNamedRanges['ROOT'])) {
         throw new \InvalidArgumentException("Missing Named-Range: 'ROOT'");
     }
     $namedRanges = [];
     /** @var \PHPExcel_NamedRange $excelNamedRange */
     foreach ($excelNamedRanges as $name => $excelNamedRange) {
         $namedRanges[$name] = [];
         foreach ($templateSheet->rangeToArray($excelNamedRange->getRange(), null, false, true, true) as $rowNum => $row) {
             $namedRanges[$name][$rowNum - 1] = [];
             foreach ($row as $col => $cellValue) {
                 $colNum = \PHPExcel_Cell::columnIndexFromString($col);
                 $templateCor = $col . $rowNum;
                 $style = $this->getStyleOfCell($templateSheet, $templateCor);
                 $namedRanges[$name][$rowNum - 1][$colNum - 1] = ['value' => $cellValue, 'style' => $style];
             }
         }
     }
     $this->logger->debug('Populated named ranges', array_keys($namedRanges));
     return $namedRanges;
 }

 /**
  * Set header row number
  *
  * @param integer $rowNumber Number of the row that contains column header names
  */
 public function setHeaderRowNumber($rowNumber)
 {
     $rowNumber++;
     $this->headerRowNumber = $rowNumber;
     $res = $this->worksheet->rangeToArray(sprintf('A%d:%s%d', $rowNumber, $this->maxColumn, $rowNumber));
     $this->setColumnHeaders(current($res));
     $this->pointer = $rowNumber;
 }

 private function _calculateTopTenValue($columnID, $startRow, $endRow, $ruleType, $ruleValue)
 {
     $range = $columnID . $startRow . ':' . $columnID . $endRow;
     $dataValues = PHPExcel_Calculation_Functions::flattenArray($this->_workSheet->rangeToArray($range, NULL, TRUE, FALSE));
     $dataValues = array_filter($dataValues);
     if ($ruleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) {
         rsort($dataValues);
     } else {
         sort($dataValues);
     }
     return array_pop(array_slice($dataValues, 0, $ruleValue));
 }

/**
 * 
 * @param PHPExcel_Worksheet $hoja
 * @param int $pk representa el índice de la columna en el archivo de excel que está asociada con la llave primaria de la tabla
 * @return string rerpesenta el nombre la ruta del archivo creado. Si no se pudo crear el archivo se regresa otra cosa :p
 */
function prepararArchivo($hoja, $pk = false, $incluirPrimeraFila = false)
{
    $objetoExcel = new PHPExcel();
    $hojaInsertar = $objetoExcel->getSheet(0);
    $hojaInsertar->setTitle('Insertar');
    if ($objetoExcel->getSheetCount() > 1) {
        $hojaActualizar = $objetoExcel->getSheet(1);
        $hojaActualizar->setTitle('Actualizar');
    } else {
        $hojaActualizar = new PHPExcel_Worksheet();
        $hojaActualizar->setTitle('Actualizar');
        $objetoExcel->addSheet($hojaActualizar);
    }
    $rango = $hoja->calculateWorksheetDataDimension();
    if (!$incluirPrimeraFila) {
        $rango[1] = '2';
    }
    $contenidoExcel = $hoja->rangeToArray($rango);
    $datos = array();
    $datos['insertar'] = array();
    $datos['actualizar'] = array();
    if ($pk) {
        $db = new DbConnection();
        $db->abrirConexion();
        $llavePrimaria = $_SESSION['pk'];
        foreach ($contenidoExcel as $fila) {
            $existe = $db->existeRegistro($_SESSION['tabla'], $llavePrimaria, $fila[$pk]);
            if ($existe) {
                $datos['actualizar'][] = $fila;
            } else {
                $datos['insertar'][] = $fila;
            }
        }
        $db->cerrarConexion();
    } else {
        foreach ($contenidoExcel as $fila) {
            $datos['insertar'][] = $fila;
        }
    }
    $hojaInsertar->fromArray($datos['insertar'], null, 'A1', true);
    $hojaActualizar->fromArray($datos['actualizar'], null, 'A1', true);
    $escritorExcel = PHPExcel_IOFactory::createWriter($objetoExcel, 'Excel2007');
    $escritorExcel->save('excelTmp/tmp_import_upload.xlsx');
    return 'excelTmp/tmp_import_upload.xlsx';
}