/** */ private static function printData($objPHPExcel, $data, $type) { $row = 3; $lastColumn = 1; $colNumberOfPatients = ExcelExport::getColumnLetter(1); $indexes = PatientRaport::getIndexes($type); foreach ($data as $rowKey => $rowValue) { // 1 col: date $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, json_encode($rowKey)); $count = 1; // 2-5 cols: N1 N2 N3 N0 foreach ($indexes as $index) { $value = isset($rowValue[$index]) ? $rowValue[$index] : "0"; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($count++, $row, $value); } // 6 col: num of categorized (N = N1+N2+N3) $colHighestCategory = ExcelExport::getColumnLetter($count - 2); $colNumberOfPatients = ExcelExport::getColumnLetter($count); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($count++, $row, '=SUM(B' . $row . ':' . $colHighestCategory . $row . ')'); $tpb = PatientRaport::getTpb($type); // 7-11 tpb1, tpb2, tpb3, 0 (no-cat), 2 (add) foreach ($tpb as $tpbn) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($count++, $row, $tpbn); } // 9-11: Tpb1*N1,Tpb2*N2,Tpb3*N3+2xN $tpbSize = count($tpb); foreach ($tpb as $tpbn) { $col1Letter = ExcelExport::getColumnLetter($count - 2 * $tpbSize); $col2Letter = ExcelExport::getColumnLetter($count - $tpbSize); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($count++, $row, '=' . $col1Letter . $row . "*" . $col2Letter . $row); } // SUM Tpb $col3Letter = ExcelExport::getColumnLetter($count - $tpbSize); $col4Letter = ExcelExport::getColumnLetter($count - 1); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($count++, $row, '=SUM(' . $col3Letter . $row . ":" . $col4Letter . $row . ")"); $row++; $lastColumn = $count - 1; } // number of categorization days $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, '=COUNTIFS(' . $colNumberOfPatients . '3:' . $colNumberOfPatients . ($row - 1) . ',">0")'); // sum $col5Letter = ExcelExport::getColumnLetter($lastColumn); $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1, '=SUM(' . $col5Letter . '3:' . $col5Letter . ($row - 1) . ')/60'); return $row; }