#php #excel #xlsx #phpspreadsheet
#php #excel #xlsx #phpspreadsheet
Вопрос:
Я создаю файл xlsx с помощью PHPspreadshet, и он всегда выдает мне пустой файл (0 КБ), если в нем более 16 строк. Я уже пытался искать ошибки и т.д. и ничего не нашел. Это просто зависит от строки. Электронная таблица (Html для тестирования): 
И когда я переключаю следующие строки:
$this->sheet->setCellValue("A16", "Besetzt");
Для
$this->sheet->setCellValue("A17", "Besetzt");
Генератор (execute() является точкой входа):
class ExcelOperation extends AbstractOperation
{
private function addWork($cell, $name = "", $phone = ""){
if($cell != null){
$richText = new PhpOfficePhpSpreadsheetRichTextRichText();
$boldText = $richText->createText($name);
//$boldText->getFont()->setBold(true);
$richText->createText("n".$phone);
$this->sheet->getCell($cell)->setValue($richText);
$this->sheet->getStyle($cell)->getAlignment()->setWrapText(true);
$this->sheet->getStyle($cell)->getAlignment()->setHorizontal(PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER);
}
}
public function execute(WebRequest $wreq, WebResponse $wres){
$eventId = $wreq->getParameter();
$userId = "1839357067871549236";
$calendar = $this->getCalendar($userId, $eventId);
$eventStatistics = $this->getEventStatistics($eventId);
//All Positions
$this->renderPositionData($calendar);
//$this->renderCurrentStatus($eventStatistics);
$this->formatColumn("A");
$this->formatColumn("B");
$this->formatColumn("C");
$this->formatColumn("D");
//$writer = new Xlsx($this->spreadsheet);
$writer = new Html($this->spreadsheet);
$wres->success(true);
$wres->set("filename", "Schichtplan.html");
$wres->set("file", $writer->save('php://output'));
}
private function addFullBorder($from, $to){
/*$styleArray = array(
'borders' => array(
'outline' => array(
'borderStyle' => PhpOfficePhpSpreadsheetStyleBorder::BORDER_THICK,
'color' => array('argb' => PhpOfficePhpSpreadsheetStyleColor::COLOR_BLACK),
),
),
);
$this->sheet ->getStyle($from.':'.$to)->applyFromArray($styleArray);*/
}
private function renderCurrentStatus($statistics){
$this->sheet->setCellValue("F2", "Stand");
$this->sheet->mergeCells('F2:G2');
$this->sheet->getStyle("F2")->getAlignment()->setHorizontal(PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER);
$this->sheet->getStyle("F2")->getFill()->setFillType(PhpOfficePhpSpreadsheetStyleFill::FILL_SOLID);
$this->sheet->getStyle("F2")->getFill()->getStartColor()->setRGB("A4DDF5");
$this->sheet->getStyle("F2")->getFont()->getColor()->setARGB(PhpOfficePhpSpreadsheetStyleColor::COLOR_WHITE);
$frei = $statistics["gesamt"]-($statistics["warten"] $statistics["besetzt"]);
$this->sheet->setCellValue("F2", "Frei");
$this->sheet->setCellValue("G2", $frei);
$this->sheet->setCellValue("F4", "Besetzt");
$this->sheet->setCellValue("G4", $statistics["besetzt"]);
$this->sheet->setCellValue("F6", "Wartend");
$this->sheet->setCellValue("G6", $statistics["warten"]);
$this->sheet->setCellValue("F8", "Gesamt");
$this->sheet->setCellValue("G8", $statistics["gesamt"]);
$this->addFullBorder("F2", "G8");
}
private function renderPositionData($calendar){
foreach($calendar as $positionId => $position){
$positionName = $position["position"]["alle_positionen_description"];
preg_match('/Pos. [0-9] /', $positionName, $matches);
$positionEndCell = null;
$actualCol = $this->positionCount%$this->positionsPerLine;
if(isset($matches[0])){
$shortPositionName = $matches[0];
}else{
$shortPositionName = $positionName;
}
if($actualCol <5){
$cell = $this->getCell($actualCol, $this->actualRowPerPosition);
//var_dump($cell);
//For Position header
$this->actualRowPerPosition[$actualCol] = 1;
$this->addPositionHeader($cell, $shortPositionName);
$positionStartCell = $cell;
//All shifts within this job
foreach($position["jobs"] as $job){
//For Each Shift in Position
$cell = $this->getCell($actualCol, $this->actualRowPerPosition);
//$cell = "A17";
//var_dump($cell);
$this->sheet->setCellValue("A17", "Besetzt");
$formattedDateFrom = $this->dbDateToTime($job["job"]["alle_schichten_start"]);
$formattedDateTo = $this->dbDateToTime($job["job"]["alle_schichten_ende"]);
$this->addShiftHeader($cell, $formattedDateFrom." - ".$formattedDateTo);
$this->actualRowPerPosition[$actualCol] = 18;
$positionEndCell = $cell;
foreach($job["workers"] as $worker){
$fullName = $worker["firstname"]." ".$worker["secondname"];
$phone = " 43";
$cell = $this->getCell($actualCol, $this->actualRowPerPosition);
$this->addWork($cell, $fullName, $phone);
//$this->actualRowPerPosition[$actualCol] = 1;
$positionEndCell = $cell;
}
}
if($positionEndCell == null){
$positionEndCell = $positionStartCell;
}
$this->addFullBorder($positionStartCell, $positionEndCell);
$this->positionCount ;
}
}
}
private function formatColumn($columnName){
$this->sheet->getColumnDimension($columnName)->setWidth(35);
}
private function dbDateToTime(string $dateTime){
$date = new DateTime($dateTime);
$formattedDateFrom = $date->format('H:i');
return $formattedDateFrom;
}
private function getCell($actualCol, $actualRowPerPosition){
$actualRow = $actualRowPerPosition[$actualCol] 1;
$actualColName = $this->getColName($actualCol);
$cell = $actualColName.$actualRow;
$this->sheet->getRowDimension($actualRow)->setRowHeight(30);
return $cell;
}
private function addPositionHeader($cell, $positionName){
$this->sheet->setCellValue($cell, $positionName);
//Font Color
$this->sheet->getStyle($cell)->getFont()->getColor()->setARGB(PhpOfficePhpSpreadsheetStyleColor::COLOR_WHITE);
$this->sheet->getStyle($cell)->getAlignment()->setHorizontal(PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER);
//Background Color
$this->sheet->getStyle($cell)->getFill()->setFillType(PhpOfficePhpSpreadsheetStyleFill::FILL_SOLID);
$this->sheet->getStyle($cell)->getFill()->getStartColor()->setRGB("A4DDF5");
}
private function addShiftHeader($cell, $shiftText){
$this->sheet->setCellValue($cell, $shiftText);
$this->sheet->getStyle($cell)->getAlignment()->setHorizontal(PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER);
}
private function getColName($colId){
$cols = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N"];
return $cols[$colId];
}
private function getCalendar($userId, $eventId){
$dbManager = new DbManager();
$jobs = $dbManager->getAdminCalendarForUser($eventId, $userId);
$calendar = $this->generateCalendarArray($jobs);
return $calendar;
}
private function getEventStatistics($eventId){
$dbManager = new DbManager();
$eventStatistics = $dbManager->getEventStatistics($eventId);
return $eventStatistics;
}
private function generateCalendarArray($jobs){
$dbManager = new DbManager();
$calendar = [];
foreach ($jobs as $job) {
if(!isset($calendar[$job["alle_positionen_id"]])){
$jobFieldsWithValues = $this->getJobFieldsWithValues($dbManager, $job["alle_positionen_id"]);
$job["needs_approval"] = $job["needs_approval"] ? true : false;
$calendar[$job["alle_positionen_id"]] =
[
"position" =>$job,
"jobFieldsWithValues"=> $jobFieldsWithValues,
"jobs" => [],
"positionPermission"=>$job["positionPermission"]
];
}
//Check if position has shift
if($job["alle_schichten_id"] != null){
//Check if job in this shift (falsely defined as job) exists
if(!isset($calendar[$job["alle_positionen_id"]]["jobs"][$job["alle_schichten_id"]])){
$calendar[$job["alle_positionen_id"]]["jobs"][$job["alle_schichten_id"]] = [
"job"=>$job,
"workers"=>[]
];
}
if($job["Email"] != null){
$jobDef = [
"email"=>$job["Email"],
"approved"=>$job["Approved"],
"userId"=>$job["id"]."",
"firstname"=>$job["Vorname"],
"secondname"=>$job["Nachname"]
];
array_push($calendar[$job["alle_positionen_id"]]["jobs"][$job["alle_schichten_id"]]["workers"], $jobDef);
}
}
}
return $calendar;
}
private function getJobFieldsWithValues(DbManager $dbManager, int $positionId) : array{
$jobFieldsWithValues = [];
$jobFields = $dbManager->getJobFieldsWithValues($positionId);
foreach ($jobFields as $jobField) {
if(isset($jobFieldsWithValues[$jobField["id"]])){
array_push($jobFieldsWithValues[$jobField["id"]]["values"], ["data-value"=>$jobField["value"], "value"=>$jobField["value"]]);
}else{
$jobFieldsWithValues[$jobField["id"]] = [
"id"=>$jobField["id"],
"fieldBaseType"=>$jobField["fieldDataType"],
"name"=>$jobField["Bezeichnung"],
"description"=>$jobField["description"],
"values"=>[]];
array_push($jobFieldsWithValues[$jobField["id"]]["values"], ["data-value"=>$jobField["value"], "value"=>$jobField["value"]]);
}
}
return $jobFieldsWithValues;
}
public function __construct() {
$this->spreadsheet = new Spreadsheet();
$this->sheet = $this->spreadsheet->getActiveSheet();
$this->positionsPerLine = 4;
$this->actualRowPerPosition = [0,0,0,0];
$this->positionCount = 0;
$this->colors = [
"black"=>PhpOfficePhpSpreadsheetStyleColor::COLOR_BLACK
];
}
}
Он генерирует пустой файл.
Комментарии:
1. Где ваш код? Покажите нам код и опишите ошибку, которую вы получаете, если используете отчет об ошибках. Как вы заполняете ячейки?
2. Я не получаю больше ошибок, чем показано на изображении и в этих предупреждениях. Я заполняю ячейки с помощью setCellValue(строка, string) в цикле, и когда он завершает строку 16, я получаю пустой файл.
3. Хорошо, вы можете показать свой код? Вы можете использовать
fromArray($array, NULL, 'A1');таким образом, вам не нужен цикл, и полный массив будет записан в файл Excel. Это встроенная функция в PhpSpreadsheet.4. Хорошо, я только что добавил код. Спасибо!