/**
* 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();
}
