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