$ticketId = $tickettyp['ticketId']; $ticketHead .= ", tickets.`{$ticketName}` \n"; $ticketQuery .= ", sum(if(ticketId = {$ticketId}, done_tickets, 0)) as `{$ticketName}` \n"; if ($tickettyp['counterSwitch'] > 1) { $counterName = filter_var($tickettyp['counterName'], FILTER_SANITIZE_STRING); $ticketHead .= ", tickets.`{$ticketName} - {$counterName}` \n"; $ticketQuery .= ", sum(if(ticketId = {$ticketId}, sum_counter, 0)) as `{$ticketName} - {$counterName}` \n"; } if ($tickettyp['durationSwitch'] > 1) { $ticketHead .= ", tickets.`{$ticketName} - Zeit`\n"; $ticketQuery .= ", sum(if(ticketId = {$ticketId}, sum_duration, 0)) as `{$ticketName} - Zeit`\n"; } } $query = "SELECT \n maId as `Kürzel`,\n sNachname AS Nachname,\n sVorname AS Vorname, \n sum_tickets.tickets as `Summe Tickets`\n {$ticketHead}\n FROM ma\n left join (\n SELECT \n userId,\n sum(done_tickets) as tickets\n FROM tickets_view \n WHERE `projektId` = ? and date = ?\n group by userId\n ) sum_tickets on (ma.userId = sum_tickets.userId)\n left join (\n SELECT \n userId\n {$ticketQuery}\n FROM tickets_view \n WHERE `projektId` = ? and date = ?\n group by userId\n ) tickets on (ma.userId = tickets.userId)\n WHERE deleted = 0 or deleted_date > ?\n order by sNachname"; $stmt = $tze->query($query, 'isiss', $projectId, $Date, $projectId, $Date, $Date); $headers = $tze->fetch_headers($stmt); $rows = $tze->fetch_assoc($stmt); //neues Blatt anlegen, Blatt benennen und Inhalt einfügen $worksheet = $objPHPExcel->createSheet()->setTitle($project["Projekt"])->fromArray($headers)->fromArray($rows, null, "A2"); $numRows = count($rows); $highestColumn = $worksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //e.g., 6 $firstRowStyle = ['font' => ['bold' => true], 'borders' => ['bottom' => ['style' => PHPExcel_Style_Border::BORDER_THIN]]]; $firstRowTicketStyle = ['font' => ['bold' => true], 'borders' => ['bottom' => ['style' => PHPExcel_Style_Border::BORDER_THIN]]]; $worksheet->getStyle('A1:' . $highestColumn . '1')->applyFromArray($firstRowStyle); $worksheet->getStyle('D1:' . $highestColumn . '1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setWrapText(true)->setTextRotation(90); $worksheet->getRowDimension('1')->setRowHeight("95"); $worksheet->getColumnDimension('A')->setAutoSize(true); $worksheet->getColumnDimension('B')->setAutoSize(true); $worksheet->getColumnDimension('C')->setAutoSize(true);