/** * Takes a 2D array and saves it as a nicely formatted Excel spreadsheet. * Metadata columns are preserved, multiple worksheets are used, when appropriate and headers are maintained. * * @param string $Test_name File name to be used. * @param unknown_type $instrument_table A 2D array of the data to be presented in Excel format * @param unknown_type $dataDir The output directory. */ function writeExcel($Test_name, $instrument_table, $dataDir) { //Modifiable parameters $maxColsPerWorksheet = 250; //leave a little bit of extra room // $metaCols = array("PSCID", "CandID", "Visit_label", "Examiner_name", "Data_entry_completion_status", "Date_taken"); //metadata columns $junkCols = array("CommentID", "UserID", "Examiner", "Testdate", "Data_entry_completion_status"); //columns to be removed // create empty Excel file to fill up $workbook = new Spreadsheet_Excel_Writer("{$dataDir}/{$Test_name}.xls"); //Excel has a 256 column limit per worksheet. If our instrument table/array is greater, split it into the needed number of worksheets for ($w = 1; $w <= ceil(count($instrument_table[0]) / $maxColsPerWorksheet); $w++) { $worksheets[] =& $workbook->addWorkSheet("Sheet{$w}"); } //ensure non-empty result set if (count($instrument_table) == 0) { //empty result set echo "Empty: {$Test_name} [Contains no data]\n"; return; // move on to the next instrument //nothing to save } //remove any undesired columns that came in from the DB query. for ($i = 0; $i < count($instrument_table); $i++) { $instrument_table[$i] = array_diff_key($instrument_table[$i], array_flip($junkCols)); } //Use Excel 97/2000 Binary File Format thereby allowing cells to contain more than 255 characters. $workbook->setVersion(8); // Use Excel97/2000 Format. // Formatting for the header row; bold and frozen $headerFormat =& $workbook->addFormat(); $headerFormat->setBold(); $headerFormat->setAlign('center'); // Formatting: Freeze only the first worksheet, at the metaCols and header intersection. // This is not used to be compatible with figs_year3_relatives and the candidate_info.csv files where there are non-standard numbers of columns // $worksheet =& $worksheets[0]; // $worksheet->freezePanes(array(1, count($metaCols), 1, count($metaCols))); //change after # of cols are decided. // add all header rows $headers = array_keys($instrument_table[0]); foreach ($headers as $headerNum => $header) { //figure out which sheet number the header belongs on $worksheetNum = intval($headerNum / $maxColsPerWorksheet); $worksheet =& $worksheets[$worksheetNum]; //figure out the column (only tricky if there is more than one worksheet. $col = $headerNum % $maxColsPerWorksheet; $worksheet->write(0, $col, $header, $headerFormat); } // add data to worksheet $rowCount = 1; //start right after the header foreach ($instrument_table as $row) { $dataRow = array_values($row); foreach ($dataRow as $valueNum => $value) { //figure out which sheet number the header belongs on $worksheetNum = intval($valueNum / $maxColsPerWorksheet); $worksheet =& $worksheets[$worksheetNum]; //figure out the column (only tricky if there is more than one worksheet) $col = $valueNum % $maxColsPerWorksheet; //Replace NULLs with . (dots) if (is_null($value)) { $value = "."; } $worksheet->write($rowCount, $col, $value); } $rowCount++; } // save file to disk if ($workbook->close() === true) { unset($worksheets); // need to unset for the next instrument echo "Success: {$Test_name}\n"; } else { echo "ERROR: Could not save {$Test_name} spreadsheet.\n"; } }
$workSheet->write(3, 1, _TOTALACCESSTIME, $fieldLeftFormat); $time = EfrontTimes::formatTimeForReporting($totalUserTime); $workSheet->write(3, 2, $time['time_string'], $fieldRightFormat); $workSheet->write(5, 1, _USERSINFO . " (" . formatTimestamp($from) . " - " . formatTimestamp($to) . ")", $headerFormat); $workSheet->mergeCells(5, 1, 5, 3); $workSheet->write(6, 1, _LOGIN, $titleLeftFormat); $workSheet->write(6, 2, _ACCESSNUMBER, $titleLeftFormat); $workSheet->write(6, 3, _TOTALTIME, $titleLeftFormat); $row = 7; foreach ($users as $login => $value) { $workSheet->write($row, 1, formatLogin($login), $fieldLeftFormat); $workSheet->write($row, 2, $value['accesses'], $fieldCenterFormat); $time = EfrontTimes::formatTimeForReporting($value['seconds']); $workSheet->write($row++, 3, $time['time_string'], $fieldCenterFormat); } $workSheet =& $workBook->addWorkSheet("Analytic log"); $workSheet->setInputEncoding('utf-8'); $workSheet->setColumn(0, 0, 5); $workSheet->write(1, 1, _ANALYTICLOG, $headerFormat); $workSheet->mergeCells(1, 1, 1, 7); $workSheet->setColumn(1, 6, 30); $workSheet->write(2, 1, _LOGIN, $fieldLeftFormat); $workSheet->write(2, 2, _LESSON, $fieldLeftFormat); $workSheet->write(2, 3, _UNIT, $fieldLeftFormat); $workSheet->write(2, 4, _ACTION, $fieldLeftFormat); $workSheet->write(2, 5, _TIME, $fieldLeftFormat); $workSheet->write(2, 6, _IPADDRESS, $fieldLeftFormat); $row = 3; foreach ($analytic_log as $value) { $workSheet->write($row, 1, formatLogin($value['users_LOGIN']), $fieldLeftFormat); $workSheet->write($row, 2, $value['lesson_name'], $fieldCenterFormat);
public function output($file = null) { ob_clean(); $spreadsheet = new Spreadsheet_Excel_Writer($file); if ($file == null) { $spreadsheet->send("report.xls"); } $worksheet =& $spreadsheet->addWorkSheet("Report"); $worksheet->setLandscape(); $worksheet->hideGridlines(); $worksheet->setPaper(9); $worksheet->setMargins(0.25); $worksheet->setFooter("Generated on " . date("jS F, Y @ g:i:s A") . " by " . $_SESSION["user_lastname"] . " " . $_SESSION["user_firstname"]); $row = 0; foreach ($this->contents as $content) { if (!is_object($content)) { continue; } switch ($content->getType()) { case "text": $format =& $spreadsheet->addFormat(); if ($row != 0) { $row++; } $style = "padding:0px;margin:0px;"; if (isset($content->style["font"])) { $format->setFontFamily($content->style["font"]); } if (isset($content->style["size"])) { $format->setSize($content->style["size"]); } if (isset($content->style["bold"])) { $format->setBold(700); } $worksheet->write($row, 0, $content->getText(), $format); break; case "table": if ($content->style["totalsBox"]) { $format =& $spreadsheet->addFormat(); $format->setFontFamily("Helvetica"); $format->setSize(12); $spreadsheet->setCustomColor(13, 180, 200, 180); $format->setBorderColor(13); $format->setBottom(2); $format->setBold(700); $totals = $content->getData(); for ($i = 0; $i < $this->numColumns; $i++) { $worksheet->write($row, $i, $totals[$i], $format); //,$format); } } else { if (!$this->widthsSet && isset($content->data_params["widths"])) { foreach ($content->data_params["widths"] as $i => $width) { $worksheet->setColumn($i, $i, $width * 1.5); } $this->widthsSet = true; } $headers = $content->getHeaders(); $format =& $spreadsheet->addFormat(); $format->setFontFamily("Helvetica"); $format->setSize(12); $spreadsheet->setCustomColor(12, 102, 128, 102); $format->setFgColor(12); $format->setColor("white"); $format->setBold(700); $this->numColumns = count($headers); foreach ($headers as $col => $header) { $worksheet->write($row, $col, str_replace("\\n", "\n", $header), $format); } $format =& $spreadsheet->addFormat(); $format->setFontFamily("Helvetica"); $format->setSize(12); $spreadsheet->setCustomColor(13, 180, 200, 180); $format->setBorderColor(13); $format->setBorder(1); foreach ($content->getData() as $rowData) { $row++; $col = 0; foreach ($rowData as $field) { $worksheet->write($row, $col, trim($field), $format); $col++; } } } break; } $row++; } $spreadsheet->close(); }