/** * Create sheet from query and dump named file to browser. * @param $query the query. */ function writeXlsx($query) { PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); $objPHPExcel = new PHPExcel(); if (!isset($this->rowParser)) { $this->rowParser = new DefaultRowParser(); } $objPHPExcel->getProperties()->setCreator($this->creator); $objPHPExcel->getProperties()->setLastModifiedBy($this->author); $objPHPExcel->getProperties()->setTitle($this->title); $objPHPExcel->getProperties()->setSubject($this->subject); $objPHPExcel->getProperties()->setDescription($this->description); $objPHPExcel->getProperties()->setKeywords($this->keywords); $objPHPExcel->getProperties()->setCategory($this->catagory); global $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; $resultSet = $this->dbConn->Execute($query); if ($resultSet === false) { die("<br>Cannot get spreadsheet data with <pre>" . $query . "</pre> reason " . $this->dbConn->ErrorMsg() . "<br>"); } //echo $query; //$colcount = $resultSet->FieldCount(); // start writing in 3rd row, top isf for title and link. $row = 3; $this->tableHeader = $this->rowParser->parseToTableHeader($resultSet); $headCount = count($this->tableHeader); $headerStyles = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FFC0C0C0'))); for ($i = 0; $i < $headCount; $i++) { $name = $this->tableHeader[$i]; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $row, $name); $coor = XLSWriter::cellCoordinate($i, $row); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); } $row++; // get types $this->columnTypes = $this->rowParser->parseTypes($resultSet); $XlsTypes = array(); //error_log('there are ' . count($this->columnTypes) . ' types from db =' . print_r($this->columnTypes, true), 0); for ($i = 0; $i < count($this->columnTypes); $i++) { $ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC; //error_log("found type = {$this->columnTypes[$i]} for column {$i}", 0); switch ($this->columnTypes[$i]) { case 'char': case 'bpchar': case 'varchar': case 'text': case 'date': $ftype = PHPExcel_Cell_DataType::TYPE_STRING; break; case 'int2': case 'int4': case 'int8': case '_numeric': case 'numeric': case 'float8': $ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC; break; default: $ftype = PHPExcel_Cell_DataType::TYPE_STRING; break; } $XlsTypes[] = $ftype; } $cellStyleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FF0000'))); $oldValue = ''; if ($this->firstWeightColumn > 0) { // add weights row $this->weigthsRow = $row; $coor = XLSWriter::cellCoordinate($this->firstWeightColumn - 1, $row); $objPHPExcel->getActiveSheet()->setCellValue($coor, 'Weights', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $weightSum = 0; $w = 0; ${$weightLast} = count($this->weights) - 1; for (; $w < count($this->weights); $w++) { $coor = XLSWriter::cellCoordinate($this->firstWeightColumn + $w, $row); $weightSum += $this->weights[$w]; $objPHPExcel->getActiveSheet()->setCellValue($coor, $this->weights[$w], PHPExcel_Cell_DataType::TYPE_NUMERIC); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); } $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row); $wBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row); $wEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + ${$weightLast}, $row); $formula = "=SUM({$wBegin}:{$wEnd})"; $objPHPExcel->getActiveSheet()->setCellValue($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row - 1); $objPHPExcel->getActiveSheet()->setCellValue($coor, 'Total WT', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $row++; } while (!$resultSet->EOF) { $rowData = $this->rowParser->parse($resultSet); $headCount = count($this->tableHeader); //$resultSet->FieldCount(); $changeColor = false; if ($this->colorChangerColumn >= 0) { if ($oldValue != $rowData[$this->colorChangerColumn]) { $changeColor = true; $oldValue = $rowData[$this->colorChangerColumn]; } } else { if ($this->autoZebra) { $changeColor = true; } } if ($changeColor) { $cellStyleArray['fill']['color']['argb'] = $this->rainBow->getCurrentAsARGBString(); $this->rainBow->getNext(); } $i = 0; for (; $i < $headCount; $i++) { $value = $rowData[$i]; $coor = XLSWriter::cellCoordinate($i, $row); $xlstype = isset($XlsTypes[$i]) ? $XlsTypes[$i] : PHPExcel_Cell_DataType::TYPE_STRING; //error_log("writing cell type = {$xlstype} for column {$i}, value {$value}", 0); $objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $value, $xlstype); if ($this->columnTypes[$i] == 'date') { $objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); } else { if ($this->columnTypes[$i] == 'time') { $objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8); } } $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray); } if ($this->weightedSumsColumn >= 0) { $weightLast = count($this->weights) - 1; $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row); $wBegin = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn, $this->weigthsRow); $wEnd = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn + $weightLast, $this->weigthsRow); $rBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row); $rEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + $weightLast, $row); $wSumCoor = XLSWriter::cellCoordinateAbsolute($this->weightedSumsColumn, $this->weigthsRow); $formula = "=SUMPRODUCT({$wBegin}:{$wEnd},{$rBegin}:{$rEnd})/{$wSumCoor}"; $objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray); } $row++; $resultSet->moveNext(); } $row = 1; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->linkText); $objPHPExcel->getActiveSheet()->getCell('A' . $row)->getHyperlink()->setUrl($this->linkUrl); $row++; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->title); $objPHPExcel->getActiveSheet()->getStyle('A' . $row)->applyFromArray($headerStyles); $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($headerStyles); $rightCell1 = XLSWriter::cellCoordinate(min($headCount - 1, 10), $row); $objPHPExcel->getActiveSheet()->mergeCells('A' . $row . ':' . $rightCell1); $rightCell2 = XLSWriter::cellCoordinate(min($headCount - 1, 10), 1); $objPHPExcel->getActiveSheet()->mergeCells('A1:' . $rightCell2); // set format $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1); $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0); for ($i = 'A', $j = 0; $i <= 'Z' && $j < $headCount; $i++, $j++) { $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(true); // $objPHPExcel->getActiveSheet()->getStyle($i . '2')->applyFromArray($styleArray); } PHPExcel_Calculation::getInstance()->clearCalculationCache(); PHPExcel_Calculation::getInstance()->disableCalculationCache(); PHPExcel_Calculation::getInstance()->calculate(); switch ($this->excelFormat) { case 'Excel2007': $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $this->mimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'; break; case 'Excel5': $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $this->mimeType = 'application/vnd.ms-excel'; break; default: $objWriter = new PHPExcel_Writer_CSV($objPHPExcel); $this->mimeType = 'text/comma-separated-values'; break; } $tempFile = tempnam('/tmp/', 'PHPEXCEL'); // '/tmp/'.$filename; $objWriter->setPreCalculateFormulas(true); $objWriter->save($tempFile); $fp = @fopen($tempFile, 'r'); if ($fp != false) { header("Content-type: " . $this->mimeType); header("Pragma: public"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Length: " . filesize($tempFile)); header("Content-Disposition: attachment; filename=\"{$this->filename}\""); fpassthru($fp); fclose($fp); $objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); unlink($tempFile); exit(0); } else { echo "cannot copy file {$tempFile} to out stream\n"; } }
public function processRequest() { global $_SESSION; global $_REQUEST; $excelFormat = 'Excel2007'; $fileExtension = 'xlsx'; if (isset($_REQUEST[$this->name]) && isset($_REQUEST['filetype'])) { switch ($_REQUEST['filetype']) { case 'Excel2007': $fileExtension = 'xlsx'; $excelFormat = 'Excel2007'; break; case 'Excel2003': $fileExtension = 'xls'; $excelFormat = 'Excel5'; break; default: case 'csv': $fileExtension = 'csv'; $excelFormat = 'CSV'; // all work here $this->writeCSVFile($this->query, $this->filename . '.' . $fileExtension); break; case 'AMC-exam': $fileExtension = 'csv'; $excelFormat = 'CSV'; // all work here $this->writeAMCFile($this->query, $this->filename . '.' . $fileExtension); break; case 'PROGRESS-set': $fileExtension = 'txt'; $excelFormat = 'CSV'; // all work here $this->writeProgresFile($this->query, $this->filename . '.' . $fileExtension); break; } require_once 'queryToXlsx.php'; $student_encoding_relation = ' student_email s '; $xlsWriter = new XLSWriter($this->dbConn, $this->query); $xlsWriter->setFilename($this->filename . '.' . $fileExtension)->setTitle($this->title)->setLinkText($this->linkText)->setLinkUrl($this->url)->setAutoZebra($this->autoZebra)->setWeights($this->weights)->setWeightedSumsColumn($this->weightSumsColumn)->setFirstWeightColumn($this->firstWeightsColumn)->setExcelFormat($excelFormat); if (isset($this->rowparser)) { $xlsWriter->setRowParser($this->rowparser); } if (isset($this->rainBow)) { $xlsWriter->setRainBow($this->rainBow); } if ($this->colorChangerColumn >= 0) { $xlsWriter->setColorChangerColumn($this->colorChangerColumn); } $xlsWriter->writeXlsx($this->query); exit(0); } }