/** * 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'; }