PHP MoodleExcelWorkbook::close方法代码示例

function user_download_xls($fields) {

    global $CFG, $DB;
    require_once("$CFG->libdir/excellib.class.php");
    $filename = clean_filename(get_string('department', 'local_departments') . '.xls');
    $workbook = new MoodleExcelWorkbook('-');
    $workbook->send($filename);
    $worksheet = array();
    $worksheet[0] = $workbook->add_worksheet('');
    $col = 0;
    foreach ($fields as $fieldname) {
        $worksheet[0]->write(0, $col, $fieldname);
        $col++;
    }

    $hierarchy = new hierarchy();
    /*   Bug report #260 
     * Edited by hemalatha c arun <hemalatha@eabyas.in>
     * resolved- If loggedin user is admin, downloading all the department   
     */
    if (is_siteadmin()) {
        $sql = "SELECT distinct(s.id),s.* FROM {local_school} s ORDER BY s.sortorder";
        $schoollist = $DB->get_records_sql($sql);
    } else
        $schoollist = $hierarchy->get_assignedschools();

    $sheetrow = 1;
    foreach ($schoollist as $school) {
        $departments = $DB->get_records('local_department', array('schoolid' => $school->id));
        foreach ($departments as $department) {
            $post = new stdclass();
            $post->shortname = $department->shortname;
            $post->fullname = $department->fullname;
            $schoolname = $DB->get_field('local_school', 'fullname', array('id' => $department->schoolid));
            $post->schoolname = $schoolname;
            $post->summary = $department->description;
            $col = 0;
            foreach ($fields as $fieldname) {
                $worksheet[0]->write($sheetrow, $col, $post->$fieldname);
                $col++;
            }
            $sheetrow++;
        }
    }
    $workbook->close();
    die;
}

/** Configurable Reports
 * A Moodle block for creating customizable reports
 * @package blocks
 * @author: Juan leyva <http://www.twitter.com/jleyvadelgado>
 * @date: 2009
 */
function export_report($report) {
    global $DB, $CFG;
    require_once($CFG->dirroot . '/lib/excellib.class.php');

    $table = $report->table;
    $matrix = array();
    $filename = 'report_' . (time()) . '.xls';

    if (!empty($table->head)) {
        $countcols = count($table->head);
        $keys = array_keys($table->head);
        $lastkey = end($keys);
        foreach ($table->head as $key => $heading) {
            $matrix[0][$key] = str_replace("\n", ' ', htmlspecialchars_decode(strip_tags(nl2br($heading))));
        }
    }

    if (!empty($table->data)) {
        foreach ($table->data as $rkey => $row) {
            foreach ($row as $key => $item) {
                $matrix[$rkey + 1][$key] = str_replace("\n", ' ', htmlspecialchars_decode(strip_tags(nl2br($item))));
            }
        }
    }

    $downloadfilename = clean_filename($filename);
    /// Creating a workbook
    $workbook = new MoodleExcelWorkbook("-");
    /// Sending HTTP headers
    $workbook->send($downloadfilename);
    /// Adding the worksheet
    $myxls = $workbook->add_worksheet($filename);

    foreach ($matrix as $ri => $col) {
        foreach ($col as $ci => $cv) {
            $myxls->write_string($ri, $ci, $cv);
        }
    }

    $workbook->close();
    exit;
}

function user_download_xls($fields) {
    global $CFG, $DB;
    require_once("$CFG->libdir/excellib.class.php");
    $filename = clean_filename(get_string('program', 'local_programs') . '.xls');
    $workbook = new MoodleExcelWorkbook('-');
    $workbook->send($filename);
    $worksheet = array();
    $worksheet[0] = $workbook->add_worksheet('');
    $col = 0;
    foreach ($fields as $fieldname) {
        $worksheet[0]->write(0, $col, $fieldname);
        $col++;
    }

    $hierarchy = new hierarchy();
    $schoollist = $hierarchy->get_assignedschools();
    $sheetrow = 1;
    foreach ($schoollist as $school) {
        $programs = $DB->get_records('local_program', array('schoolid' => $school->id));
        foreach ($programs as $program) {
            $post = new stdclass();
            $post->shortname = $program->shortname;
            $post->fullname = $program->fullname;
            $departmentname = $DB->get_field('local_department', 'fullname', array('id' => $program->departmentid));
            $post->departmentname = $departmentname;
            $schoolname = $DB->get_field('local_school', 'fullname', array('id' => $program->schoolid));
            $post->schoolname = $schoolname;
            $post->summary = $program->description;
            $post->type = ($program->type == 0) ? 'UnderGraduate' : 'PostGraduate';
            $post->duration = $program->duration;
            $col = 0;
            foreach ($fields as $fieldname) {
                $worksheet[0]->write($sheetrow, $col, $post->$fieldname);
                $col++;
            }
            $sheetrow++;
        }
    }
    $workbook->close();
    die;
}

 function report_download_xls($fields, $data, $reportname = 'reportinxls')
 {
     $workbook = new MoodleExcelWorkbook('-');
     $workbook->send($reportname);
     $worksheet = array();
     $worksheet[0] = $workbook->add_worksheet('');
     $col = 0;
     foreach ($fields as $fieldname) {
         $worksheet[0]->write(0, $col, $fieldname);
         $col++;
     }
     $row = 1;
     foreach ($data as $eachrow) {
         $col = 0;
         foreach ($eachrow as $key => $value) {
             $worksheet[0]->write($row, $col, $value);
             $col++;
         }
         $row++;
     }
     $workbook->close();
     die;
 }

                if ($usergrps = groups_get_all_groups($course->id, $user->id)) {
                    foreach ($usergrps as $ug) {
                        $ug2 = $ug2 . $ug->name;
                    }
                }
                $myxls->write_string($row, 3, $ug2);
                if (isset($option_text)) {
                    $myxls->write_string($row, 4, format_string($option_text, true));
                }
                $row++;
            }
        }
        $pos = 4;
    }
    /// Close the workbook
    $workbook->close();
    exit;
}
// print text file
if ($download == "txt" && has_capability('mod/choice:downloadresponses', $context)) {
    $filename = clean_filename("{$course->shortname} " . strip_tags(format_string($choice->name, true))) . '.txt';
    header("Content-Type: application/download\n");
    header("Content-Disposition: attachment; filename=\"{$filename}\"");
    header("Expires: 0");
    header("Cache-Control: must-revalidate,post-check=0,pre-check=0");
    header("Pragma: public");
    /// Print names of all the fields
    echo get_string("lastname") . "\t" . get_string("firstname") . "\t" . get_string("idnumber") . "\t";
    echo get_string("group") . "\t";
    echo get_string("choice", "choice") . "\n";
    /// generate the data for the body of the spreadsheet

/**
 * @global object
 * @param array $export
 * @param string $dataname
 * @param int $count
 * @return string
 */
function data_export_xls($export, $dataname, $count) {
    global $CFG;
    require_once("$CFG->libdir/excellib.class.php");
    $filename = clean_filename("{$dataname}-{$count}_record");
    if ($count > 1) {
        $filename .= 's';
    }
    $filename .= clean_filename('-' . gmdate("Ymd_Hi"));
    $filename .= '.xls';

    $filearg = '-';
    $workbook = new MoodleExcelWorkbook($filearg);
    $workbook->send($filename);
    $worksheet = array();
    $worksheet[0] =& $workbook->add_worksheet('');
    $rowno = 0;
    foreach ($export as $row) {
        $colno = 0;
        foreach($row as $col) {
            $worksheet[0]->write($rowno, $colno, $col);
            $colno++;
        }
        $rowno++;
    }
    $workbook->close();
    return $filename;
}

function print_log_xls($course, $user, $date, $order = 'l.time DESC', $modname, $modid, $modaction, $groupid)
{
    global $CFG, $DB;
    require_once "{$CFG->libdir}/excellib.class.php";
    if (!($logs = build_logs_array($course, $user, $date, $order, '', '', $modname, $modid, $modaction, $groupid))) {
        return false;
    }
    $courses = array();
    if ($course->id == SITEID) {
        $courses[0] = '';
        if ($ccc = get_courses('all', 'c.id ASC', 'c.id,c.shortname')) {
            foreach ($ccc as $cc) {
                $courses[$cc->id] = $cc->shortname;
            }
        }
    } else {
        $courses[$course->id] = $course->shortname;
    }
    $count = 0;
    $ldcache = array();
    $tt = getdate(time());
    $today = mktime(0, 0, 0, $tt["mon"], $tt["mday"], $tt["year"]);
    $strftimedatetime = get_string("strftimedatetime");
    $nroPages = ceil(count($logs) / (EXCELROWS - FIRSTUSEDEXCELROW + 1));
    $filename = 'logs_' . userdate(time(), get_string('backupnameformat', 'langconfig'), 99, false);
    $filename .= '.xls';
    $workbook = new MoodleExcelWorkbook('-');
    $workbook->send($filename);
    $worksheet = array();
    $headers = array(get_string('course'), get_string('time'), get_string('ip_address'), get_string('fullnameuser'), get_string('action'), get_string('info'));
    // Creating worksheets
    for ($wsnumber = 1; $wsnumber <= $nroPages; $wsnumber++) {
        $sheettitle = get_string('logs') . ' ' . $wsnumber . '-' . $nroPages;
        $worksheet[$wsnumber] =& $workbook->add_worksheet($sheettitle);
        $worksheet[$wsnumber]->set_column(1, 1, 30);
        $worksheet[$wsnumber]->write_string(0, 0, get_string('savedat') . userdate(time(), $strftimedatetime));
        $col = 0;
        foreach ($headers as $item) {
            $worksheet[$wsnumber]->write(FIRSTUSEDEXCELROW - 1, $col, $item, '');
            $col++;
        }
    }
    if (empty($logs['logs'])) {
        $workbook->close();
        return true;
    }
    $formatDate =& $workbook->add_format();
    $formatDate->set_num_format(get_string('log_excel_date_format'));
    $row = FIRSTUSEDEXCELROW;
    $wsnumber = 1;
    $myxls =& $worksheet[$wsnumber];
    foreach ($logs['logs'] as $log) {
        if (isset($ldcache[$log->module][$log->action])) {
            $ld = $ldcache[$log->module][$log->action];
        } else {
            $ld = $DB->get_record('log_display', array('module' => $log->module, 'action' => $log->action));
            $ldcache[$log->module][$log->action] = $ld;
        }
        if ($ld && !empty($log->info)) {
            // ugly hack to make sure fullname is shown correctly
            if ($ld->mtable == 'user' and $ld->field == $DB->sql_concat('firstname', "' '", 'lastname')) {
                $log->info = fullname($DB->get_record($ld->mtable, array('id' => $log->info)), true);
            } else {
                $log->info = $DB->get_field($ld->mtable, $ld->field, array('id' => $log->info));
            }
        }
        // Filter log->info
        $log->info = format_string($log->info);
        $log->info = strip_tags(urldecode($log->info));
        // Some XSS protection
        if ($nroPages > 1) {
            if ($row > EXCELROWS) {
                $wsnumber++;
                $myxls =& $worksheet[$wsnumber];
                $row = FIRSTUSEDEXCELROW;
            }
        }
        $coursecontext = get_context_instance(CONTEXT_COURSE, $course->id);
        $myxls->write($row, 0, format_string($courses[$log->course], true, array('context' => $coursecontext)), '');
        $myxls->write_date($row, 1, $log->time, $formatDate);
        // write_date() does conversion/timezone support. MDL-14934
        $myxls->write($row, 2, $log->ip, '');
        $fullname = fullname($log, has_capability('moodle/site:viewfullnames', $coursecontext));
        $myxls->write($row, 3, $fullname, '');
        $myxls->write($row, 4, $log->module . ' ' . $log->action, '');
        $myxls->write($row, 5, $log->info, '');
        $row++;
    }
    $workbook->close();
    return true;
}

//.........这里部分代码省略.........
                                                $j++;
                                                $element = 'cmi.interactions_'.$i.'.correct_responses_'.$j.'.pattern';
                                            }
                                            $row[] = $rightans;
                                        } else {
                                            $row[] = ' ';
                                        }
                                    }
                                    $i++;
                                    $element = 'cmi.interactions_'.$i.'.id';
                                }
                            //---end of interaction data*/
                            } else {
                                // if we don't have track data, we haven't attempted yet
                                $strstatus = get_string('notattempted', 'scorm');
                                if (!$download) {
                                    $row[] = '<img src="'.$OUTPUT->pix_url('notattempted', 'scorm').'" alt="'.$strstatus.'" title="'.$strstatus.'" /><br/>'.$strstatus;
                                } else {
                                    $row[] = $strstatus;
                                }
                            }
                        }
                    }

                    if (!$download) {
                        $table->add_data($row);
                    } else if ($download == 'Excel' or $download == 'ODS') {
                        $colnum = 0;
                        foreach ($row as $item) {
                            $myxls->write($rownum, $colnum, $item, $format);
                            $colnum++;
                        }
                        $rownum++;
                    } else if ($download == 'CSV') {
                        $text = implode("\t", $row);
                        echo $text." \n";
                    }
                }
                if (!$download) {
                    $table->finish_output();
                    if ($candelete) {
                        echo '<table id="commands">';
                        echo '<tr><td>';
                        echo '<a href="javascript:select_all_in(\'DIV\', null, \'scormtablecontainer\');">'.
                             get_string('selectall', 'scorm').'</a> / ';
                        echo '<a href="javascript:deselect_all_in(\'DIV\', null, \'scormtablecontainer\');">'.
                             get_string('selectnone', 'scorm').'</a> ';
                        echo '&nbsp;&nbsp;';
                        echo '<input type="submit" value="'.get_string('deleteselected', 'quiz_overview').'"/>';
                        echo '</td></tr></table>';
                        // Close form
                        echo '</div>';
                        echo '</form>';
                    }
                    echo '</div>';
                    if (!empty($attempts)) {
                        echo '<table class="boxaligncenter"><tr>';
                        echo '<td>';
                        echo $OUTPUT->single_button(new moodle_url($PAGE->url,
                                                                   array('download'=>'ODS') + $displayoptions),
                                                                   get_string('downloadods'));
                        echo "</td>\n";
                        echo '<td>';
                        echo $OUTPUT->single_button(new moodle_url($PAGE->url,
                                                                   array('download'=>'Excel') + $displayoptions),
                                                                   get_string('downloadexcel'));
                        echo "</td>\n";
                        echo '<td>';
                        echo $OUTPUT->single_button(new moodle_url($PAGE->url,
                                                                   array('download'=>'CSV') + $displayoptions),
                                                                   get_string('downloadtext'));
                        echo "</td>\n";
                        echo "<td>";
                        echo "</td>\n";
                        echo '</tr></table>';
                    }
                }
            } else {
                if ($candelete && !$download) {
                    echo '</div>';
                    echo '</form>';
                    $table->finish_output();
                }
                echo '</div>';
            }
            // Show preferences form irrespective of attempts are there to report or not
            if (!$download) {
                $mform->set_data(compact('detailedrep', 'pagesize', 'attemptsmode'));
                $mform->display();
            }
            if ($download == 'Excel' or $download == 'ODS') {
                $workbook->close();
                exit;
            } else if ($download == 'CSV') {
                exit;
            }
        } else {
            echo $OUTPUT->notification(get_string('noactivity', 'scorm'));
        }
    }// function ends

//.........这里部分代码省略.........
             echo '<input type="hidden" name="id" value="' . $cm->id . '" />';
             echo '<input type="hidden" name="mode" value="overview" />';
             // Print table.
             $table->print_html();
             // Print "Select all" etc..
             if (!empty($attempts)) {
                 echo '<table id="commands">';
                 echo '<tr><td>';
                 echo '<a href="javascript:select_all_in(\'DIV\',null,\'tablecontainer\');">' . get_string('selectall', 'game') . '</a> / ';
                 echo '<a href="javascript:deselect_all_in(\'DIV\',null,\'tablecontainer\');">' . get_string('selectnone', 'game') . '</a> ';
                 echo '&nbsp;&nbsp;';
                 $options = array('delete' => get_string('delete'));
                 echo choose_from_menu($options, 'action', '', get_string('withselected', 'game'), 'if(this.selectedIndex > 0) submitFormById(\'attemptsform\');', '', true);
                 echo '<noscript id="noscriptmenuaction" style="display: inline;"><div>';
                 echo '<input type="submit" value="' . get_string('go') . '" /></div></noscript>';
                 echo '<script type="text/javascript">' . "\n<!--\n" . 'document.getElementById("noscriptmenuaction").style.display = "none";' . "\n-->\n" . '</script>';
                 echo '</td></tr></table>';
             }
             // Close form.
             echo '</div>';
             echo '</form></div>';
             if (!empty($attempts)) {
                 echo '<table class="boxaligncenter"><tr>';
                 $options = array();
                 $options["id"] = "{$cm->id}";
                 $options["q"] = "{$game->id}";
                 $options["mode"] = "overview";
                 $options['sesskey'] = sesskey();
                 $options["noheader"] = "yes";
                 $options['noattempts'] = $noattempts;
                 $options['detailedmarks'] = $detailedmarks;
                 echo '<td>';
                 $options["download"] = "ODS";
                 print_single_button("report.php", $options, get_string("downloadods", 'game'));
                 echo "</td>\n";
                 echo '<td>';
                 $options["download"] = "Excel";
                 print_single_button("report.php", $options, get_string("downloadexcel"));
                 echo "</td>\n";
                 echo '<td>';
                 $options["download"] = "CSV";
                 print_single_button('report.php', $options, get_string("downloadtext"));
                 echo "</td>\n";
                 echo "<td>";
                 helpbutton('overviewdownload', get_string('overviewdownload', 'quiz'), 'game');
                 echo "</td>\n";
                 echo '</tr></table>';
             }
         } else {
             if ($download == 'Excel' or $download == 'ODS') {
                 $workbook->close();
                 exit;
             } else {
                 if ($download == 'CSV') {
                     exit;
                 }
             }
         }
     } else {
         if (!$download) {
             $table->print_html();
         }
     }
     // Print display options.
     echo '<div class="controls">';
     echo '<form id="options" action="report.php" method="get">';
     echo '<div>';
     echo '<p>' . get_string('displayoptions', 'game') . ': </p>';
     echo '<input type="hidden" name="id" value="' . $cm->id . '" />';
     echo '<input type="hidden" name="q" value="' . $game->id . '" />';
     echo '<input type="hidden" name="mode" value="overview" />';
     echo '<input type="hidden" name="noattempts" value="0" />';
     echo '<input type="hidden" name="detailedmarks" value="0" />';
     echo '<table id="overview-options" class="boxaligncenter">';
     echo '<tr align="left">';
     echo '<td><label for="pagesize">' . get_string('pagesize', 'game') . '</label></td>';
     echo '<td><input type="text" id="pagesize" name="pagesize" size="3" value="' . $pagesize . '" /></td>';
     echo '</tr>';
     echo '<tr align="left">';
     echo '<td colspan="2">';
     $options = array(0 => get_string('attemptsonly', 'game', $course->students));
     if ($course->id != SITEID) {
         $options[1] = get_string('noattemptsonly', 'game', $course->students);
         $options[2] = get_string('allstudents', 'game', $course->students);
         $options[3] = get_string('allattempts', 'game');
     }
     choose_from_menu($options, 'noattempts', $noattempts, '');
     echo '</td></tr>';
     echo '<tr align="left">';
     echo '<td colspan="2"><input type="checkbox" id="checkdetailedmarks" name="detailedmarks" ' . ($detailedmarks ? 'checked="checked" ' : '') . 'value="1" /> <label for="checkdetailedmarks">' . get_string('showdetailedmarks', 'game') . '</label> ';
     echo '</td></tr>';
     echo '<tr><td colspan="2" align="center">';
     echo '<input type="submit" value="' . get_string('go') . '" />';
     echo '</td></tr></table>';
     echo '</div>';
     echo '</form>';
     echo '</div>';
     echo "\n";
     return true;
 }

//.........这里部分代码省略.........
                                     }
                                     if ($displayoptions['objectivescore']) {
                                         if (isset($objectivescore[$name])) {
                                             $row[] = s($objectivescore[$name]);
                                         } else {
                                             $row[] = $emptycell;
                                         }
                                     }
                                 }
                             }
                             // End of interaction data.
                         } else {
                             // If we don't have track data, we haven't attempted yet.
                             $strstatus = get_string('notattempted', 'scorm');
                             if (!$download) {
                                 $row[] = \html_writer::img($OUTPUT->pix_url('notattempted', 'scorm'), $strstatus, array('title' => $strstatus)) . \html_writer::empty_tag('br') . $strstatus;
                             } else {
                                 $row[] = $strstatus;
                             }
                             // Complete the empty cells.
                             for ($i = 0; $i < count($columns) - $nbmaincolumns; $i++) {
                                 $row[] = $emptycell;
                             }
                         }
                     }
                 }
                 if (!$download) {
                     $table->add_data($row);
                 } else {
                     if ($download == 'Excel' or $download == 'ODS') {
                         $colnum = 0;
                         foreach ($row as $item) {
                             $myxls->write($rownum, $colnum, $item, $format);
                             $colnum++;
                         }
                         $rownum++;
                     } else {
                         if ($download == 'CSV') {
                             $csvexport->add_data($row);
                         }
                     }
                 }
             }
             if (!$download) {
                 $table->finish_output();
                 if ($candelete) {
                     echo \html_writer::start_tag('table', array('id' => 'commands'));
                     echo \html_writer::start_tag('tr') . \html_writer::start_tag('td');
                     echo \html_writer::link('javascript:select_all_in(\'DIV\', null, \'scormtablecontainer\');', get_string('selectall', 'scorm')) . ' / ';
                     echo \html_writer::link('javascript:deselect_all_in(\'DIV\', null, \'scormtablecontainer\');', get_string('selectnone', 'scorm'));
                     echo '&nbsp;&nbsp;';
                     echo \html_writer::empty_tag('input', array('type' => 'submit', 'value' => get_string('deleteselected', 'scorm'), 'class' => 'btn btn-secondary'));
                     echo \html_writer::end_tag('td') . \html_writer::end_tag('tr') . \html_writer::end_tag('table');
                     // Close form.
                     echo \html_writer::end_tag('div');
                     echo \html_writer::end_tag('form');
                 }
                 echo \html_writer::end_div();
                 if (!empty($attempts)) {
                     echo \html_writer::start_tag('table', array('class' => 'boxaligncenter')) . \html_writer::start_tag('tr');
                     echo \html_writer::start_tag('td');
                     echo $OUTPUT->single_button(new \moodle_url($PAGE->url, array('download' => 'ODS') + $displayoptions), get_string('downloadods'), 'post', ['class' => 'm-t-1']);
                     echo \html_writer::end_tag('td');
                     echo \html_writer::start_tag('td');
                     echo $OUTPUT->single_button(new \moodle_url($PAGE->url, array('download' => 'Excel') + $displayoptions), get_string('downloadexcel'), 'post', ['class' => 'm-t-1']);
                     echo \html_writer::end_tag('td');
                     echo \html_writer::start_tag('td');
                     echo $OUTPUT->single_button(new \moodle_url($PAGE->url, array('download' => 'CSV') + $displayoptions), get_string('downloadtext'), 'post', ['class' => 'm-t-1']);
                     echo \html_writer::end_tag('td');
                     echo \html_writer::start_tag('td');
                     echo \html_writer::end_tag('td');
                     echo \html_writer::end_tag('tr') . \html_writer::end_tag('table');
                 }
             }
         } else {
             if ($candelete && !$download) {
                 echo \html_writer::end_div();
                 echo \html_writer::end_tag('form');
                 $table->finish_output();
             }
             echo \html_writer::end_div();
         }
         // Show preferences form irrespective of attempts are there to report or not.
         if (!$download) {
             $mform->set_data(compact('detailedrep', 'pagesize', 'attemptsmode'));
             $mform->display();
         }
         if ($download == 'Excel' or $download == 'ODS') {
             $workbook->close();
             exit;
         } else {
             if ($download == 'CSV') {
                 $csvexport->download_file();
                 exit;
             }
         }
     } else {
         echo $OUTPUT->notification(get_string('noactivity', 'scorm'));
     }
 }

//.........这里部分代码省略.........
    $name->set_bold(0);
    $name->set_size(10);
    $grey_code_f =& $workbook->add_format();
    $grey_code_f->set_bold(0);
    // Make it bold
    $grey_code_f->set_size(12);
    $grey_code_f->set_fg_color(22);
    $grey_code_f->set_align('center');
    //$formatbc->set_size(14);
    $myxls->write(1, 1, "ATTENDANCE SUMMARY", $header1);
    $myxls->write(2, 1, $reportname, $header2);
    if ($start) {
        $duration = 'Period : ( ' . date(" M jS, Y", $start) . ' - ' . date(" M jS, Y", $end) . ')';
        $myxls->write(4, 1, $duration, $formatbc);
    }
    //$myxls->write(4, 0, $duration);
    $myxls->set_column(3, 100, 15);
    //Added By Hina
    $i = 6;
    $j = 0;
    $a = 0;
    $a = 0;
    foreach ($data->head as $heading) {
        $heading = str_replace('<br/>', '', $heading);
        $heading = trim($heading);
        $myxls->write_string($i, $j, $heading, $header2);
        $col_size = strlen($heading);
        $col_size += 6;
        if (preg_match('/^NAME/i', $heading)) {
            $col_size = 25;
        }
        $myxls->set_column($j, $j, $col_size);
        //added By Hina Yousuf
        if ($data->headspan[$a] == 4) {
            $myxls->merge_cells($i, $j, $i, $j + 3);
            $j += 4;
        }
        if ($data->headspan[$a] == 3) {
            $myxls->merge_cells($i, $j, $i, $j + 2);
            $j += 3;
        }
        if ($data->headspan[$a] == 1) {
            $j++;
        }
        //$j++;
        $a++;
        //end
        //$j++;
    }
    //$myxls->merge_cells(1,0,1,$j-1);
    //$myxls->merge_cells(2,0,2,$j-1);
    //$myxls->merge_cells(4,1,4,3);
    $i = 7;
    $j = 0;
    ///Added By Hina Yousuf
    foreach ($data->data as $row) {
        foreach ($row as $cell) {
            foreach ($cell as $cel) {
                $myxls->write_string($i, $j, strip_tags($cel->text), $normal1);
                $j++;
            }
        }
    }
    ///end
    foreach ($data->data as $row) {
        foreach ($row as $cell) {
            //$myxls->write($i, $j++, $cell);
            if (is_numeric($cell)) {
                //if($cell>25.99){
                //	$myxls->write_number($i, $j++, $cell,$grey_code_f);
                //}
                //else{
                $myxls->write_number($i, $j++, $cell, $normal);
                //}
            } else {
                ///
                if (preg_match('/^!!r/', $cell)) {
                    $cell = str_replace("!!r", '', $cell);
                    $myxls->write_string($i, $j++, $cell, $red);
                } elseif (preg_match('/^!!g/', $cell)) {
                    $cell = str_replace("!!g", '', $cell);
                    $myxls->write_string($i, $j++, $cell, $green);
                } elseif (preg_match('/^!!o/', $cell)) {
                    $cell = str_replace("!!o", '', $cell);
                    $myxls->write_string($i, $j++, $cell, $orange);
                } else {
                    if ($j == 2) {
                        $myxls->write_string($i, $j++, $cell, $name);
                    } else {
                        $myxls->write_string($i, $j++, $cell, $normal);
                    }
                }
            }
        }
        $i++;
        $j = 0;
    }
    $workbook->close();
    exit;
}

function ExportToExcel($data, $perd, $weeks)
{
    global $CFG, $USER;
    global $modules;
    //require_once("$CFG->libdir/excellib.class.php");/*
    require_once $CFG->dirroot . '/lib/excellib.class.php';
    $filename = "Missing Lectures Report.xls";
    $workbook = new MoodleExcelWorkbook("-");
    /// Sending HTTP headers
    ob_clean();
    $workbook->send($filename);
    /// Creating the first worksheet
    $myxls =& $workbook->add_worksheet('MCR');
    /// format types
    $green =& $workbook->add_format();
    $green->set_bold(0);
    // Make it bold
    $green->set_size(12);
    $green->set_fg_color("white");
    $green->set_align('center');
    $red =& $workbook->add_format();
    $red->set_bold(0);
    // Make it bold
    $red->set_size(12);
    $red->set_fg_color(10);
    $red->set_align('center');
    $orange =& $workbook->add_format();
    $orange->set_bold(0);
    // Make it bold
    $orange->set_size(12);
    $orange->set_fg_color("orange");
    $orange->set_align('center');
    $normal =& $workbook->add_format();
    $normal->set_bold(0);
    // Make it bold
    $normal->set_size(10);
    $normal->set_align('center');
    $formatbc =& $workbook->add_format();
    $formatbc->set_bold(1);
    $formatbc->set_align('center');
    //$formatbc->set_size(14);
    $myxls->write(0, 3, "Missing Lectures Record", $formatbc);
    $myxls->write(2, 2, "Period : ( " . $perd . " ) ", $formatbc);
    $myxls->write(3, 2, "Weeks: " . $weeks, $formatbc);
    $myxls->write(4, 2, "The Remarks are entered by " . $USER->firstname . " " . $USER->lastname, $formatbc);
    $i = 6;
    $j = 0;
    $a = 0;
    foreach ($data->head as $heading) {
        $myxls->write_string($i, $j, $heading, $formatbc);
        if ($data->headspan[$a] == 3) {
            $myxls->merge_cells($i, $j, $i, $j + 2);
            $j += 3;
        }
        if ($data->headspan[$a] == 1) {
            $j++;
        }
        $a++;
    }
    $myxls->set_column(0, 0, 6, $formatbc);
    $myxls->set_column(1, 1, 22, $formatbc);
    $myxls->set_column(2, 2, 12, $formatbc);
    $myxls->set_column(3, 3, 30, $formatbc);
    $myxls->set_column(4, 4, 15, $formatbc);
    $myxls->set_column(5, 5, 25, $formatbc);
    $myxls->set_column(6, 6, 12, $formatbc);
    $i = 7;
    $j = 0;
    foreach ($data->data as $row) {
        foreach ($row as $cell) {
            //$myxls->write($i, $j++, $cell);
            if (is_numeric($cell)) {
                $myxls->write_number($i, $j++, $cell, $normal);
            } else {
                if (preg_match('/^!!r/', $cell)) {
                    $cell = str_replace("!!r", '', $cell);
                    $myxls->write_string($i, $j++, $cell, $red);
                } elseif (preg_match('/^!!g/', $cell)) {
                    $cell = str_replace("!!g", '', $cell);
                    $myxls->write_string($i, $j++, $cell, $green);
                } elseif (preg_match('/^!!o/', $cell)) {
                    $cell = str_replace("!!o", '', $cell);
                    $myxls->write_string($i, $j++, $cell, $orange);
                } else {
                    if (strstr($cell, "<b>") == true) {
                        $myxls->write_string($i, $j++, strip_tags($cell), $formatbc);
                    } else {
                        $myxls->write_string($i, $j++, $cell, $normal);
                    }
                }
            }
        }
        $i++;
        $j = 0;
    }
    $workbook->close();
    exit;
}

/**
 * Exports all grades and scores in an exam in Excel format
 *
 * @param unknown $emarking
 */
function emarking_download_excel($emarking)
{
    global $DB;
    $csvsql = "\n\t\tSELECT cc.fullname AS course,\n\t\t\te.name AS exam,\n\t\t\tu.id,\n\t\t\tu.idnumber,\n\t\t\tu.lastname,\n\t\t\tu.firstname,\n\t\t\tcr.description,\n\t\t\tIFNULL(l.score, 0) AS score,\n\t\t\tIFNULL(c.bonus, 0) AS bonus,\n\t\t\tIFNULL(l.score,0) + IFNULL(c.bonus,0) AS totalscore,\n\t\t\td.grade\n\t\tFROM {emarking} AS e\n\t\tINNER JOIN {emarking_submission} AS s ON (e.id = :emarkingid AND e.id = s.emarking)\n\t\tINNER JOIN {emarking_draft} AS d ON (d.submissionid = s.id AND d.qualitycontrol=0)\n        INNER JOIN {course} AS cc ON (cc.id = e.course)\n\t\tINNER JOIN {user} AS u ON (s.student = u.id)\n\t\tINNER JOIN {emarking_page} AS p ON (p.submission = s.id)\n\t\tLEFT JOIN {emarking_comment} AS c ON (c.page = p.id AND d.id = c.draft)\n\t\tLEFT JOIN {gradingform_rubric_levels} AS l ON (c.levelid = l.id)\n\t\tLEFT JOIN {gradingform_rubric_criteria} AS cr ON (cr.id = l.criterionid)\n\t\tORDER BY cc.fullname ASC, e.name ASC, u.lastname ASC, u.firstname ASC, cr.sortorder";
    // Get data and generate a list of questions
    $rows = $DB->get_recordset_sql($csvsql, array('emarkingid' => $emarking->id));
    $questions = array();
    foreach ($rows as $row) {
        if (array_search($row->description, $questions) === FALSE) {
            $questions[] = $row->description;
        }
    }
    $current = 0;
    $laststudent = 0;
    $headers = array('00course' => get_string('course'), '01exam' => get_string('exam', 'mod_emarking'), '02idnumber' => get_string('idnumber'), '03lastname' => get_string('lastname'), '04firstname' => get_string('firstname'));
    $tabledata = array();
    $data = null;
    $rows = $DB->get_recordset_sql($csvsql, array('emarkingid' => $emarking->id));
    $studentname = '';
    $lastrow = null;
    foreach ($rows as $row) {
        $index = 10 + array_search($row->description, $questions);
        $keyquestion = $index . "" . $row->description;
        if (!isset($headers[$keyquestion])) {
            $headers[$keyquestion] = $row->description;
        }
        if ($laststudent != $row->id) {
            if ($laststudent > 0) {
                $tabledata[$studentname] = $data;
                $current++;
            }
            $data = array('00course' => $row->course, '01exam' => $row->exam, '02idnumber' => $row->idnumber, '03lastname' => $row->lastname, '04firstname' => $row->firstname, $keyquestion => $row->totalscore, '99grade' => $row->grade);
            $laststudent = intval($row->id);
            $studentname = $row->lastname . ',' . $row->firstname;
        } else {
            $data[$keyquestion] = $row->totalscore;
        }
        $lastrow = $row;
    }
    $studentname = $lastrow->lastname . ',' . $lastrow->firstname;
    $tabledata[$studentname] = $data;
    $headers['99grade'] = get_string('grade');
    ksort($tabledata);
    $current = 0;
    $newtabledata = array();
    foreach ($tabledata as $data) {
        foreach ($questions as $q) {
            $index = 10 + array_search($q, $questions);
            if (!isset($data[$index . "" . $q])) {
                $data[$index . "" . $q] = '0.000';
            }
        }
        ksort($data);
        $current++;
        $newtabledata[] = $data;
    }
    $tabledata = $newtabledata;
    $downloadfilename = clean_filename("{$emarking->name}.xls");
    // Creating a workbook
    $workbook = new MoodleExcelWorkbook("-");
    // Sending HTTP headers
    $workbook->send($downloadfilename);
    // Adding the worksheet
    $myxls = $workbook->add_worksheet(get_string('emarking', 'mod_emarking'));
    // Writing the headers in the first row
    $row = 0;
    $col = 0;
    foreach (array_values($headers) as $d) {
        $myxls->write_string($row, $col, $d);
        $col++;
    }
    // Writing the data
    $row = 1;
    foreach ($tabledata as $data) {
        $col = 0;
        foreach (array_values($data) as $d) {
            if ($row > 0 && $col >= 5) {
                $myxls->write_number($row, $col, $d);
            } else {
                $myxls->write_string($row, $col, $d);
            }
            $col++;
        }
        $row++;
    }
    $workbook->close();
}

function ExportToExcel($data, $name, $type)
{
    global $CFG;
    global $headings;
    global $name, $type;
    global $department;
    //require_once("$CFG->libdir/excellib.class.php");/*
    require_once $CFG->dirroot . '/lib/excellib.class.php';
    $filename = "Faculty_Feedback_Report.xls";
    $workbook = new MoodleExcelWorkbook("-");
    /// Sending HTTP headers
    ob_clean();
    $workbook->send($filename);
    /// Creating the first worksheet
    $myxls =& $workbook->add_worksheet('Faculty Feedback Report');
    /// format types
    $formatbc =& $workbook->add_format();
    $formatbc1 =& $workbook->add_format();
    $formatbc->set_bold(1);
    $myxls->set_column(0, 0, 50);
    $myxls->set_column(1, 7, 20);
    $formatbc->set_align('center');
    $formatbc1->set_align('center');
    $xlsFormats = new stdClass();
    $xlsFormats->default = $workbook->add_format(array('width' => 40));
    //$formatbc->set_size(14);
    $myxls->write(0, 2, "FEEDBACK REPORT", $formatbc);
    $myxls->write(1, 2, $name, $formatbc);
    $myxls->write(2, 2, "Department: " . $department, $formatbc);
    foreach ($headings as $heading) {
        $myxls->write_string(4, $j++, strtoupper($heading), $formatbc);
    }
    $i = 5;
    $j = 0;
    foreach ($data->data as $row) {
        foreach ($row as $cell) {
            //$myxls->write($i, $j++, $cell);
            if (is_numeric($cell)) {
                $myxls->write_number($i, $j++, strip_tags($cell), $formatbc1);
            } else {
                $myxls->write_string($i, $j++, strip_tags($cell), $formatbc1);
            }
        }
        $i++;
        $j = 0;
    }
    $workbook->close();
    exit;
}

//.........这里部分代码省略.........
        $sheetrow++;
        $i++;
    }
    $grandtotal += $subtotal;





    //Group all Non-EU countries---------------
    $subtotal = 0;
    $satisfy3 = false;
    foreach ($ctrs as $key => $ctr) {
        $satisfy3 = true;
        $line = array();
        $line[] = $reports[$key]->studentname; //Student Name
        $line[] = $reports[$key]->amount;
        $line[] = date('d M, Y', $reports[$key]->timecreated);
        if ($reports[$key]->itemtype == 'classtype' && $reports[$key]->moduleid) {
            $line[] = $DB->get_field('local_clclasses', 'fullname', array('id' => $reports[$key]->moduleid));
        } else if ($reports[$key]->itemtype == 'mooctype' && $reports[$key]->online_courseid) {
            $line[] = $DB->get_field('course', 'fullname', array('id' => $reports[$key]->online_courseid));
        }
        //$items = $DB->get_records('local_item', array('orderid'=>$reports[$key]->orderid));
        //$modules = array();
        //foreach($items as $item){
        //    if($item->itemtype=='classtype' && $item->moduleid){
        //	$onlinecourseid = $DB->get_field('local_clclasses', 'onlinecourseid', array('id'=>$item->moduleid));;
        //    }
        //    if($item->itemtype=='mooctype' && $item->online_courseid){
        //	$onlinecourseid = $item->online_courseid;
        //    }
        //    $course = $DB->get_record('course', array('id'=>$onlinecourseid));
        //    $modules[] = $course->fullname;
        //}
        //$line[] = implode(', ', $modules);
        $line[] = $countries[$reports[$key]->country]; //Student country
        $no_vat = false;
        if ($reports[$key]->itemtype == 'classtype' && $reports[$key]->moduleid) {
            $local_user = $DB->get_record('local_users', array('userid' => $reports[$key]->userid));
            $user_class = $DB->get_record('local_user_clclasses', array('userid' => $reports[$key]->userid, 'classid' => $reports[$key]->moduleid));
            $exist = $local_user && $user_class;
            if ($exist && $local_user->fundsbygovt && !$user_class->fundbyuk) {
                $no_vat = true;
            }
        }
        if ($no_vat) {
            $line[] = 'No VAT applied';
            $line[] = '0';
            $subtotal += '0';
        } else
        if ($tax = $DB->get_record_select('local_tax_rate', "country = '" . $reports[$key]->country . "' AND typeid = 1 AND '" . date('d-m-Y', $reports[$key]->timecreated) . "' BETWEEN FROM_UNIXTIME(startdate, '%d-%m-%Y') AND FROM_UNIXTIME(enddate, '%d-%m-%Y')")) {
            $line[] = $tax->rate . ' %';
            $vatamount = ( $tax->rate / 100 ) * $reports[$key]->amount;
            $line[] = $vatamount;
            $subtotal += $vatamount;
        } else if ($tax = $DB->get_record_select('local_tax_rate', "country = 'all' AND typeid = 1 AND '" . date('d-m-Y', $reports[$key]->timecreated) . "' BETWEEN FROM_UNIXTIME(startdate, '%d-%m-%Y') AND FROM_UNIXTIME(enddate, '%d-%m-%Y')")) {
            $line[] = $tax->rate . ' %';
            $vatamount = ( $tax->rate / 100 ) * $reports[$key]->amount;
            $line[] = $vatamount;
            $subtotal += $vatamount;
        } else {
            $line[] = '0 %';
            $line[] = '0';
            $subtotal += '0';
        }
        $col = 0;
        foreach ($fields as $k => $v) {
            $worksheet[0]->write($sheetrow, $col, $line[$k]);
            $col++;
        }
        $sheetrow++;
        $i++;
        unset($ctrs[$key]);
    }
    if ($satisfy3) {
        $line = array('', '', '', '', '', 'Sub-Total', $subtotal);
        $col = 0;
        foreach ($fields as $k => $v) {
            $worksheet[0]->write($sheetrow, $col, $line[$k]);
            $col++;
        }
        $sheetrow++;
        $i++;
    }
    $grandtotal += $subtotal;

    if ($satisfy1 || $satisfy2 || $satisfy3) {
        $line = array('', '', '', '', '', 'Grand-Total', $grandtotal);
        $col = 0;
        foreach ($fields as $k => $v) {
            $worksheet[0]->write($sheetrow, $col, $line[$k]);
            $col++;
        }
        $sheetrow++;
        $i++;
    }
    $workbook->close();
    die;
}