/**
* Setzt this->sheet auf das gerade erstellte Sheet
* und die Row auf Zeile 1
*/
protected function createSheet($name)
{
$name = \Psc\PHPExcel\Helper::sanitizeSheetTitle($name);
$this->excel->addSheet(new PHPExcel_Worksheet($this->excel, $name));
$this->excel->setActiveSheetIndexByName($name);
$this->sheet = $this->excel->getActiveSheet();
$this->setRow(1);
}
protected function readRowsWithMapping(ExcelSheet $excelSheet, array $mapping, $minRow = 1, $maxRow = NULL)
{
$nullValue = NULL;
$emptyRow = array_combine(array_values($mapping), array_fill(0, count($mapping), $nullValue));
$rows = array();
foreach ($excelSheet->getRowIterator($minRow) as $rowNum => $wsRow) {
if ($maxRow !== NULL && $rowNum > $maxRow) {
break;
}
$row = array();
foreach ($wsRow->getCellIterator() as $cell) {
$cellIndex = h::getColumnIndex($cell->getColumn());
$isEmpty = TRUE;
if (array_key_exists($cellIndex, $mapping)) {
$row[$mapping[$cellIndex]] = $this->readValue($cell, $isEmpty, $nullValue);
}
}
$rows[$rowNum] = array_replace($emptyRow, $row);
}
return $rows;
}
/**
* Konvertiert ein Excel in einen Array
*
* Erwartet excelFile als Uploaded File
* der zurückgegebene Array hat jedoch keine Zahlen Columns sondern auch einen 0 basierten index
* auch die Rows sind 0 basierend
*
* @controller-api
* @return array
*/
public function convert(\stdClass $table, $excelFile = NULL, $removeEmpty = TRUE)
{
$excelFile = $excelFile ?: $this->v->validateUploadedFile('excelFile');
$reader = PHPExcel_IOFactory::createReaderForFile((string) $excelFile);
$excel = $reader->load((string) $excelFile);
$sheet = $excel->getSheet(0);
$columns = isset($table->columns) ? (array) $table->columns : array();
$nullValue = '';
// sadly json macht aus NULL den string "null" in js
$data = array();
$mCell = 0;
foreach ($sheet->getRowIterator() as $wsRow) {
$row = array();
$empty = TRUE;
foreach ($wsRow->getCellIterator() as $cell) {
// $cell->getDataType() gibts sogar auch
$cellIndex = h::getColumnIndex($cell->getColumn());
$mCell = max($mCell, $cellIndex + 1);
$row[$cellIndex] = $value = $this->convertValue($cell->getValue(), isset($columns[$cellIndex]) && isset($columns[$cellIndex]->type) ? $columns[$cellIndex]->type : NULL);
if ($value != '') {
$empty = FALSE;
}
}
if (!$removeEmpty || !$empty) {
ksort($row);
$data[] = array_replace(array_fill(0, $mCell, $nullValue), $row);
}
}
return new ServiceResponse(Service::OK, $data, ServiceResponse::JSON_UPLOAD_RESPONSE);
}
