PHP Spreadsheet_Excel_Writer::addWorkSheet方法代码示例

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