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