getProperties()->setCreator("Maarten Balliauw") # ->setLastModifiedBy("Maarten Balliauw") # ->setTitle("Office 2007 XLSX Test Document") # ->setSubject("Office 2007 XLSX Test Document") # ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") # ->setKeywords("office 2007 openxml php") # ->setCategory("Test result file"); $objPHPExcel->getActiveSheet()->setCellValue('A' . 1, "Nachname"); $objPHPExcel->getActiveSheet()->setCellValue('B' . 1, "Vorname"); $objPHPExcel->getActiveSheet()->setCellValue('C' . 1, "Ab Datum"); $objPHPExcel->getActiveSheet()->setCellValue('D' . 1, "Ende"); $objPHPExcel->getActiveSheet()->setCellValue('E' . 1, "Jahresbetrag"); $objPHPExcel->getActiveSheet()->setCellValue('F' . 1, "Art"); $objPHPExcel->getActiveSheet()->freezePane('A2'); // Add some data $query3 = "SELECT distinct a.did FROM prog_tmp_lb_lz a, prog_tmp_professor b WHERE a.did=b.did AND date_format(monat,'%Y') = '$jahrgang' AND (wert > 0 or a.art ='O') GROUP BY a.did, lblzid ORDER BY b.art, doz_name asc, lblzid asc"; $result3 = $db->query($query3) or die("Cannot execute query3a"); $ln_zaehler = 1; while ($row3 = $result3->fetch_array()) { #echo "$row3[did]
"; $query4 = "SELECT a.did, doz_titel, doz_vorname, doz_name, lblzid, monat, sum(wert) wert, a.art, date_format(monat, '%Y-01-01') start_aussetz_fiktiv, date_format(monat, '%Y-12-31') ende_aussetz_fiktiv, b.art fikt_prof FROM prog_tmp_lb_lz a, prog_tmp_professor b WHERE a.did=b.did AND date_format(monat,'%Y') = '$jahrgang' AND a.did = $row3[did] AND (wert > 0 or a.art ='O') GROUP BY a.did, lblzid, art ORDER BY b.art, doz_name asc, lblzid asc"; $result4 = $db->query($query4) or die("Cannot execute query4"); while ($row4 = $result4->fetch_array()) { $ln_zaehler++; if ($row4['art'] == 'F') { $art = "Fiktiver Wert"; } if ($row4['art'] == 'B') { $art = 'Erfasster Wert'; } if ($row4['art'] == 'O') { $art = 'max. LBZ erreicht'; } $result_laufz = $db->query("SELECT date_format(min(monat), '%d.%m.%Y') min_monat_form, date_format(max(monat), '%d.%m.%Y') max_monat_form FROM prog_tmp_lb_lz WHERE lblzid='$row4[lblzid]' AND did= '$row4[did]' LIMIT 1"); $row_laufz = $result_laufz->fetch_array(); $startdat = "$row_laufz[min_monat_form]"; $enddat = "$row_laufz[max_monat_form]"; $objPHPExcel->getActiveSheet()->setCellValue('A' . $ln_zaehler, "$row4[doz_name]"); $objPHPExcel->getActiveSheet()->setCellValue('B' . $ln_zaehler, "$row4[doz_vorname]"); $objPHPExcel->getActiveSheet()->setCellValue('C' . $ln_zaehler, "$startdat"); $objPHPExcel->getActiveSheet()->setCellValue('D' . $ln_zaehler, "$enddat"); $objPHPExcel->getActiveSheet()->setCellValue('E' . $ln_zaehler, "$row4[wert]"); $objPHPExcel->getActiveSheet()->setCellValue('F' . $ln_zaehler, "$art"); } } const FORMAT_CURRENCY_EUR_SIMPLE = '#,##0.00 €_-'; $objPHPExcel->getActiveSheet() ->getStyle('E1:E65000') ->getNumberFormat() ->setFormatCode( FORMAT_CURRENCY_EUR_SIMPLE ); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('LBZ'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Summe drunterschreiben $ln_zaehler2 = $ln_zaehler+1; $objPHPExcel->getActiveSheet() ->setCellValue( 'E'.$ln_zaehler2, '=SUM(E2:E'.$ln_zaehler.')' ); // Auto size columns for each worksheet foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $objPHPExcel->setActiveSheetIndex($objPHPExcel->getIndex($worksheet)); $sheet = $objPHPExcel->getActiveSheet(); $cellIterator = $sheet->getRowIterator()->current()->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(true); /** @var PHPExcel_Cell $cell */ foreach ($cellIterator as $cell) { $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true); } } $objPHPExcel->getActiveSheet()->getStyle("A1:F1")->getFont()->setBold(true); // Redirect output to a client’s web browser (Excel2007) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="LBZ_'.$jahrgang.'.xlsx"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header ('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); ?>