public function simpleExportExcel($orgu_ref_id)
{
$nodes = $this->getStructure($orgu_ref_id);
include_once "./Services/Excel/classes/class.ilExcelUtils.php";
include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
$adapter = new ilExcelWriterAdapter("org_unit_export_" . $orgu_ref_id . ".xls", true);
$workbook = $adapter->getWorkbook();
/** @var Spreadsheet_Excel_Writer_Worksheet $worksheet */
$worksheet = $workbook->addWorksheet();
ob_start();
$worksheet->write(0, 0, "ou_id");
$worksheet->write(0, 1, "ou_id_type");
$worksheet->write(0, 2, "ou_parent_id");
$worksheet->write(0, 3, "ou_parent_id_type");
$worksheet->write(0, 4, "reference_id");
$worksheet->write(0, 5, "external_id");
$worksheet->write(0, 6, "title");
$worksheet->write(0, 7, "description");
$worksheet->write(0, 8, "action");
$row = 0;
foreach ($nodes as $node) {
$orgu = new ilObjOrgUnit($node);
if ($orgu->getRefId() == ilObjOrgUnit::getRootOrgRefId()) {
continue;
}
$row++;
$attrs = $this->getAttrForOrgu($orgu);
$worksheet->write($row, 0, $attrs["ou_id"]);
$worksheet->write($row, 1, $attrs["ou_id_type"]);
$worksheet->write($row, 2, $attrs["ou_parent_id"]);
$worksheet->write($row, 3, $attrs["ou_parent_id_type"]);
$worksheet->write($row, 4, $orgu->getRefId());
$worksheet->write($row, 5, $orgu->getImportId());
$worksheet->write($row, 6, $orgu->getTitle());
$worksheet->write($row, 7, $orgu->getDescription());
$worksheet->write($row, 8, "create");
}
ob_end_clean();
$workbook->close();
}
/**
* Exports the evaluation data to the Microsoft Excel file format
*
* @param bool $deliver
* @param string $filterby
* @param string $filtertext Filter text for the user data
* @param boolean $passedonly TRUE if only passed user datasets should be exported, FALSE otherwise
*
* @return string
*/
public function exportToExcel($deliver = TRUE, $filterby = "", $filtertext = "", $passedonly = FALSE)
{
if (strcmp($this->mode, "aggregated") == 0) {
return $this->aggregatedResultsToExcel($deliver);
}
require_once './Services/Excel/classes/class.ilExcelWriterAdapter.php';
$excelfile = ilUtil::ilTempnam();
$adapter = new ilExcelWriterAdapter($excelfile, FALSE);
$testname = $this->test_obj->getTitle();
switch ($this->mode) {
case 'results':
$testname .= '_results';
break;
}
$testname = ilUtil::getASCIIFilename(preg_replace("/\\s/", "_", $testname)) . ".xls";
$workbook = $adapter->getWorkbook();
$workbook->setVersion(8);
// Use Excel97/2000 Format
// Creating a worksheet
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_percent =& $workbook->addFormat();
$format_percent->setNumFormat("0.00%");
$format_datetime =& $workbook->addFormat();
$format_datetime->setNumFormat("DD/MM/YYYY hh:mm:ss");
$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setColor('black');
$format_title->setPattern(1);
$format_title->setFgColor('silver');
require_once './Services/Excel/classes/class.ilExcelUtils.php';
$worksheet =& $workbook->addWorksheet(ilExcelUtils::_convert_text($this->lng->txt("tst_results")));
$additionalFields = $this->test_obj->getEvaluationAdditionalFields();
$row = 0;
$col = 0;
if ($this->test_obj->getAnonymity()) {
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("counter")), $format_title);
} else {
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("name")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("login")), $format_title);
}
if (count($additionalFields)) {
foreach ($additionalFields as $fieldname) {
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt($fieldname)), $format_title);
}
}
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_resultspoints")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("maximum_points")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_resultsmarks")), $format_title);
if ($this->test_obj->getECTSOutput()) {
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("ects_grade")), $format_title);
}
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_qworkedthrough")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_qmax")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_pworkedthrough")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_timeofwork")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_atimeofwork")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_firstvisit")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_lastvisit")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_mark_median")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_rank_participant")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_rank_median")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_total_participants")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("tst_stat_result_median")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("scored_pass")), $format_title);
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("pass")), $format_title);
$counter = 1;
$data =& $this->test_obj->getCompleteEvaluationData(TRUE, $filterby, $filtertext);
$firstrowwritten = false;
foreach ($data->getParticipants() as $active_id => $userdata) {
$remove = FALSE;
if ($passedonly) {
if ($data->getParticipant($active_id)->getPassed() == FALSE) {
$remove = TRUE;
}
}
if (!$remove) {
$row++;
if ($this->test_obj->isRandomTest() || $this->test_obj->getShuffleQuestions()) {
$row++;
}
$col = 0;
if ($this->test_obj->getAnonymity()) {
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($counter));
} else {
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getName()));
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getLogin()));
}
if (count($additionalFields)) {
$userfields = ilObjUser::_lookupFields($userdata->getUserID());
foreach ($additionalFields as $fieldname) {
if (strcmp($fieldname, "gender") == 0) {
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt("gender_" . $userfields[$fieldname])));
} else {
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($userfields[$fieldname]));
}
}
}
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getReached()));
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getMaxpoints()));
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getMark()));
if ($this->test_obj->getECTSOutput()) {
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getECTSMark()));
}
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getQuestionsWorkedThrough()));
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getParticipant($active_id)->getNumberOfQuestions()));
$worksheet->write($row, $col++, $data->getParticipant($active_id)->getQuestionsWorkedThroughInPercent() / 100.0, $format_percent);
$time = $data->getParticipant($active_id)->getTimeOfWork();
$time_seconds = $time;
$time_hours = floor($time_seconds / 3600);
$time_seconds -= $time_hours * 3600;
$time_minutes = floor($time_seconds / 60);
$time_seconds -= $time_minutes * 60;
$worksheet->write($row, $col++, ilExcelUtils::_convert_text(sprintf("%02d:%02d:%02d", $time_hours, $time_minutes, $time_seconds)));
$time = $data->getParticipant($active_id)->getQuestionsWorkedThrough() ? $data->getParticipant($active_id)->getTimeOfWork() / $data->getParticipant($active_id)->getQuestionsWorkedThrough() : 0;
$time_seconds = $time;
$time_hours = floor($time_seconds / 3600);
$time_seconds -= $time_hours * 3600;
$time_minutes = floor($time_seconds / 60);
$time_seconds -= $time_minutes * 60;
$worksheet->write($row, $col++, ilExcelUtils::_convert_text(sprintf("%02d:%02d:%02d", $time_hours, $time_minutes, $time_seconds)));
$fv = getdate($data->getParticipant($active_id)->getFirstVisit());
$firstvisit = ilUtil::excelTime($fv["year"], $fv["mon"], $fv["mday"], $fv["hours"], $fv["minutes"], $fv["seconds"]);
$worksheet->write($row, $col++, $firstvisit, $format_datetime);
$lv = getdate($data->getParticipant($active_id)->getLastVisit());
$lastvisit = ilUtil::excelTime($lv["year"], $lv["mon"], $lv["mday"], $lv["hours"], $lv["minutes"], $lv["seconds"]);
$worksheet->write($row, $col++, $lastvisit, $format_datetime);
$median = $data->getStatistics()->getStatistics()->median();
$pct = $data->getParticipant($active_id)->getMaxpoints() ? $median / $data->getParticipant($active_id)->getMaxpoints() * 100.0 : 0;
$mark = $this->test_obj->mark_schema->getMatchingMark($pct);
$mark_short_name = "";
if (is_object($mark)) {
$mark_short_name = $mark->getShortName();
}
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($mark_short_name));
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getStatistics()->getStatistics()->rank($data->getParticipant($active_id)->getReached())));
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getStatistics()->getStatistics()->rank_median()));
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($data->getStatistics()->getStatistics()->count()));
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($median));
if ($this->test_obj->getPassScoring() == SCORE_BEST_PASS) {
$worksheet->write($row, $col++, $data->getParticipant($active_id)->getBestPass() + 1);
} else {
$worksheet->write($row, $col++, $data->getParticipant($active_id)->getLastPass() + 1);
}
$startcol = $col;
for ($pass = 0; $pass <= $data->getParticipant($active_id)->getLastPass(); $pass++) {
$col = $startcol;
$finishdate = $this->test_obj->getPassFinishDate($active_id, $pass);
if ($finishdate > 0) {
if ($pass > 0) {
$row++;
if ($this->test_obj->isRandomTest() || $this->test_obj->getShuffleQuestions()) {
$row++;
}
}
$worksheet->write($row, $col++, ilExcelUtils::_convert_text($pass + 1));
if (is_object($data->getParticipant($active_id)) && is_array($data->getParticipant($active_id)->getQuestions($pass))) {
foreach ($data->getParticipant($active_id)->getQuestions($pass) as $question) {
$question_data = $data->getParticipant($active_id)->getPass($pass)->getAnsweredQuestionByQuestionId($question["id"]);
$worksheet->write($row, $col, ilExcelUtils::_convert_text($question_data["reached"]));
if ($this->test_obj->isRandomTest() || $this->test_obj->getShuffleQuestions()) {
$worksheet->write($row - 1, $col, ilExcelUtils::_convert_text(preg_replace("/<.*?>/", "", $data->getQuestionTitle($question["id"]))), $format_title);
} else {
if ($pass == 0 && !$firstrowwritten) {
$worksheet->write(0, $col, ilExcelUtils::_convert_text(preg_replace("/<.*?>/", "", $data->getQuestionTitle($question["id"]))), $format_title);
}
}
$col++;
}
$firstrowwritten = true;
}
}
}
$counter++;
}
}
if ($this->test_obj->getExportSettingsSingleChoiceShort() && !$this->test_obj->isRandomTest() && $this->test_obj->hasSingleChoiceQuestions()) {
// special tab for single choice tests
$titles =& $this->test_obj->getQuestionTitlesAndIndexes();
$positions = array();
$pos = 0;
$row = 0;
foreach ($titles as $id => $title) {
$positions[$id] = $pos;
$pos++;
}
$usernames = array();
$participantcount = count($data->getParticipants());
$allusersheet = false;
$pages = 0;
$resultsheet =& $workbook->addWorksheet($this->lng->txt("eval_all_users"));
$col = 0;
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('name')), $format_title);
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('login')), $format_title);
if (count($additionalFields)) {
foreach ($additionalFields as $fieldname) {
if (strcmp($fieldname, "matriculation") == 0) {
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('matriculation')), $format_title);
}
}
}
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('test')), $format_title);
foreach ($titles as $title) {
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($title), $format_title);
}
$row++;
foreach ($data->getParticipants() as $active_id => $userdata) {
$username = !is_null($userdata) && ilExcelUtils::_convert_text($userdata->getName()) ? ilExcelUtils::_convert_text($userdata->getName()) : "ID {$active_id}";
if (array_key_exists($username, $usernames)) {
$usernames[$username]++;
$username .= " ({$i})";
} else {
$usernames[$username] = 1;
}
$col = 0;
$resultsheet->write($row, $col++, $username);
$resultsheet->write($row, $col++, $userdata->getLogin());
if (count($additionalFields)) {
$userfields = ilObjUser::_lookupFields($userdata->getUserID());
foreach ($additionalFields as $fieldname) {
if (strcmp($fieldname, "matriculation") == 0) {
if (strlen($userfields[$fieldname])) {
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($userfields[$fieldname]));
} else {
$col++;
}
}
}
}
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->test_obj->getTitle()));
$pass = $userdata->getScoredPass();
if (is_object($userdata) && is_array($userdata->getQuestions($pass))) {
foreach ($userdata->getQuestions($pass) as $question) {
$objQuestion =& $this->test_obj->_instanciateQuestion($question["aid"]);
if (is_object($objQuestion) && strcmp($objQuestion->getQuestionType(), 'assSingleChoice') == 0) {
$solution = $objQuestion->getSolutionValues($active_id, $pass);
$pos = $positions[$question["aid"]];
$selectedanswer = "x";
foreach ($objQuestion->getAnswers() as $id => $answer) {
if (strlen($solution[0]["value1"]) && $id == $solution[0]["value1"]) {
$selectedanswer = $answer->getAnswertext();
}
}
$resultsheet->write($row, $col + $pos, ilExcelUtils::_convert_text($selectedanswer));
}
}
}
$row++;
}
if ($this->test_obj->isSingleChoiceTestWithoutShuffle()) {
// special tab for single choice tests without shuffle option
$pos = 0;
$row = 0;
$usernames = array();
$allusersheet = false;
$pages = 0;
$resultsheet =& $workbook->addWorksheet($this->lng->txt("eval_all_users") . " (2)");
$col = 0;
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('name')), $format_title);
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('login')), $format_title);
if (count($additionalFields)) {
foreach ($additionalFields as $fieldname) {
if (strcmp($fieldname, "matriculation") == 0) {
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('matriculation')), $format_title);
}
}
}
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->lng->txt('test')), $format_title);
foreach ($titles as $title) {
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($title), $format_title);
}
$row++;
foreach ($data->getParticipants() as $active_id => $userdata) {
$username = !is_null($userdata) && ilExcelUtils::_convert_text($userdata->getName()) ? ilExcelUtils::_convert_text($userdata->getName()) : "ID {$active_id}";
if (array_key_exists($username, $usernames)) {
$usernames[$username]++;
$username .= " ({$i})";
} else {
$usernames[$username] = 1;
}
$col = 0;
$resultsheet->write($row, $col++, $username);
$resultsheet->write($row, $col++, $userdata->getLogin());
if (count($additionalFields)) {
$userfields = ilObjUser::_lookupFields($userdata->getUserID());
foreach ($additionalFields as $fieldname) {
if (strcmp($fieldname, "matriculation") == 0) {
if (strlen($userfields[$fieldname])) {
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($userfields[$fieldname]));
} else {
$col++;
}
}
}
}
$resultsheet->write($row, $col++, ilExcelUtils::_convert_text($this->test_obj->getTitle()));
$pass = $userdata->getScoredPass();
if (is_object($userdata) && is_array($userdata->getQuestions($pass))) {
foreach ($userdata->getQuestions($pass) as $question) {
$objQuestion =& $this->test_obj->_instanciateQuestion($question["aid"]);
if (is_object($objQuestion) && strcmp($objQuestion->getQuestionType(), 'assSingleChoice') == 0) {
$solution = $objQuestion->getSolutionValues($active_id, $pass);
$pos = $positions[$question["aid"]];
$selectedanswer = chr(65 + $solution[0]["value1"]);
$resultsheet->write($row, $col + $pos, ilExcelUtils::_convert_text($selectedanswer));
}
}
}
$row++;
}
}
} else {
// test participant result export
$usernames = array();
$participantcount = count($data->getParticipants());
$allusersheet = false;
$pages = 0;
$i = 0;
foreach ($data->getParticipants() as $active_id => $userdata) {
$i++;
$username = !is_null($userdata) && ilExcelUtils::_convert_text($userdata->getName()) ? ilExcelUtils::_convert_text($userdata->getName()) : "ID {$active_id}";
if (array_key_exists($username, $usernames)) {
$usernames[$username]++;
$username .= " ({$i})";
} else {
$usernames[$username] = 1;
}
if ($participantcount > 250) {
if (!$allusersheet || $pages - 1 < floor($row / 64000)) {
$resultsheet =& $workbook->addWorksheet($this->lng->txt("eval_all_users") . ($pages > 0 ? " (" . ($pages + 1) . ")" : ""));
$allusersheet = true;
$row = 0;
$pages++;
}
} else {
$resultsheet =& $workbook->addWorksheet($username);
}
if (method_exists($resultsheet, "writeString")) {
$pass = $userdata->getScoredPass();
$row = $allusersheet ? $row : 0;
$resultsheet->writeString($row, 0, ilExcelUtils::_convert_text(sprintf($this->lng->txt("tst_result_user_name_pass"), $pass + 1, $userdata->getName())), $format_bold);
$row += 2;
if (is_object($userdata) && is_array($userdata->getQuestions($pass))) {
foreach ($userdata->getQuestions($pass) as $question) {
require_once "./Modules/TestQuestionPool/classes/class.assQuestion.php";
$question = assQuestion::_instanciateQuestion($question["id"]);
if (is_object($question)) {
$row = $question->setExportDetailsXLS($resultsheet, $row, $active_id, $pass, $format_title, $format_bold);
}
}
}
}
}
}
$workbook->close();
if ($deliver) {
ilUtil::deliverFile($excelfile, $testname, "application/vnd.ms-excel", false, true);
exit;
} else {
return $excelfile;
}
}
/**
* Export the Data
*
* @param type $format
* @param type $send
*/
public function exportData($format, $send = false)
{
if ($this->dataExists()) {
// #9640: sort
if (!$this->getExternalSorting() && $this->enabled["sort"]) {
$this->determineOffsetAndOrder(true);
$this->row_data = ilUtil::sortArray($this->row_data, $this->getOrderField(), $this->getOrderDirection(), $this->numericOrdering($this->getOrderField()));
}
$filename = "export";
switch ($format) {
default:
case self::EXPORT_EXCEL:
include_once "./Services/Excel/classes/class.ilExcelUtils.php";
include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
$adapter = new ilExcelWriterAdapter($filename . ".xls", $send);
$workbook = $adapter->getWorkbook();
$worksheet = $workbook->addWorksheet();
$row = 0;
ob_start();
$this->fillMetaExcel($worksheet, $row);
$this->fillHeaderExcel($worksheet, $row);
foreach ($this->row_data as $set) {
$row++;
$this->fillRowExcel($worksheet, $row, $set);
}
ob_end_clean();
$workbook->close();
break;
case self::EXPORT_CSV:
include_once "./Services/Utilities/classes/class.ilCSVWriter.php";
$csv = new ilCSVWriter();
$csv->setSeparator(";");
ob_start();
$this->fillMetaCSV($csv);
$this->fillHeaderCSV($csv);
foreach ($this->row_data as $set) {
$this->fillRowCSV($csv, $set);
}
ob_end_clean();
if ($send) {
$filename .= ".csv";
header("Content-type: text/comma-separated-values");
header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
header("Pragma: public");
echo $csv->getCSVString();
} else {
file_put_contents($filename, $csv->getCSVString());
}
break;
case self::EXPORT_PDF:
include_once "Customizing/global/plugins/Services/Repository/RepositoryObject/RoomSharing/classes/appointments/bookings/class.ilRoomSharingBookingsExportTableGUI.php";
$exportTable = new ilRoomSharingBookingsExportTableGUI($this->parent_obj, 'showBookings', $this->ref_id);
include_once "Customizing/global/plugins/Services/Repository/RepositoryObject/RoomSharing/classes/export/class.ilRoomSharingPDFCreator.php";
$staff = $exportTable->getTableHTML();
ilRoomSharingPDFCreator::generatePDF($exportTable->getTableHTML(), 'D', 'file.pdf');
break;
}
if ($send) {
exit;
}
}
}
/**
* Export the user specific results for the survey
*
* Export the user specific results for the survey
*
* @access private
*/
function exportUserSpecificResults($export_format, $export_label, $finished_ids)
{
global $ilLog;
// #13620
ilDatePresentation::setUseRelativeDates(false);
$csvfile = array();
$csvrow = array();
$csvrow2 = array();
$questions = array();
$questions =& $this->object->getSurveyQuestions(true);
array_push($csvrow, $this->lng->txt("lastname"));
// #12756
array_push($csvrow, $this->lng->txt("firstname"));
array_push($csvrow, $this->lng->txt("login"));
array_push($csvrow, $this->lng->txt('workingtime'));
// #13622
array_push($csvrow, $this->lng->txt('survey_results_finished'));
array_push($csvrow2, "");
array_push($csvrow2, "");
array_push($csvrow2, "");
array_push($csvrow2, "");
array_push($csvrow2, "");
if ($this->object->canExportSurveyCode()) {
array_push($csvrow, $this->lng->txt("codes"));
array_push($csvrow2, "");
}
/* #8211
if ($this->object->getAnonymize() == ilObjSurvey::ANONYMIZE_OFF)
{
array_push($csvrow, $this->lng->txt("gender"));
}
*/
$cellcounter = 1;
foreach ($questions as $question_id => $question_data) {
include_once "./Modules/SurveyQuestionPool/classes/class.SurveyQuestion.php";
$question = SurveyQuestion::_instanciateQuestion($question_data["question_id"]);
switch ($export_label) {
case "label_only":
$question->addUserSpecificResultsExportTitles($csvrow, true);
break;
case "title_only":
$question->addUserSpecificResultsExportTitles($csvrow, false);
break;
default:
$question->addUserSpecificResultsExportTitles($csvrow, false);
$question->addUserSpecificResultsExportTitles($csvrow2, true, false);
break;
}
$questions[$question_data["question_id"]] = $question;
}
array_push($csvfile, $csvrow);
if (sizeof($csvrow2) && implode("", $csvrow2)) {
array_push($csvfile, $csvrow2);
}
if (!$finished_ids) {
$participants =& $this->object->getSurveyFinishedIds();
} else {
$participants = $finished_ids;
}
$finished_data = array();
foreach ($this->object->getSurveyParticipants($participants) as $item) {
$finished_data[$item["active_id"]] = $item;
}
foreach ($participants as $user_id) {
if ($user_id < 1) {
continue;
}
$resultset =& $this->object->getEvaluationByUser($questions, $user_id);
$csvrow = array();
// #12756
array_push($csvrow, trim($resultset["lastname"]) ? $resultset["lastname"] : $resultset["name"]);
// anonymous
array_push($csvrow, $resultset["firstname"]);
array_push($csvrow, $resultset["login"]);
// #10579
if ($this->object->canExportSurveyCode()) {
array_push($csvrow, $user_id);
}
/* #8211
if ($this->object->getAnonymize() == ilObjSurvey::ANONYMIZE_OFF)
{
array_push($csvrow, $resultset["gender"]);
}
*/
$wt = $this->object->getWorkingtimeForParticipant($user_id);
array_push($csvrow, $wt);
$finished = $finished_data[$user_id];
if ((bool) $finished["finished"]) {
array_push($csvrow, ilDatePresentation::formatDate(new ilDateTime($finished["finished_tstamp"], IL_CAL_UNIX)));
} else {
array_push($csvrow, "-");
}
foreach ($questions as $question_id => $question) {
$question->addUserSpecificResultsData($csvrow, $resultset);
}
array_push($csvfile, $csvrow);
}
// #11179
$surveyname = $this->object->getTitle() . " " . $this->lng->txt("svy_eval_user") . " " . date("Y-m-d");
$surveyname = preg_replace("/\\s/", "_", trim($surveyname));
$surveyname = ilUtil::getASCIIFilename($surveyname);
switch ($export_format) {
case self::TYPE_XLS:
include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
$excelfile = ilUtil::ilTempnam();
$adapter = new ilExcelWriterAdapter($excelfile, FALSE);
$workbook = $adapter->getWorkbook();
$workbook->setVersion(8);
// Use Excel97/2000 Format
// Creating a worksheet
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_percent =& $workbook->addFormat();
$format_percent->setNumFormat("0.00%");
$format_datetime =& $workbook->addFormat();
$format_datetime->setNumFormat("DD/MM/YYYY hh:mm:ss");
$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setColor('black');
$format_title->setPattern(1);
$format_title->setFgColor('silver');
$format_title_plain =& $workbook->addFormat();
$format_title_plain->setColor('black');
$format_title_plain->setPattern(1);
$format_title_plain->setFgColor('silver');
// Creating a worksheet
$pages = floor(count($csvfile[0]) / 250) + 1;
$worksheets = array();
for ($i = 0; $i < $pages; $i++) {
$worksheets[$i] =& $workbook->addWorksheet();
}
$row = 0;
include_once "./Services/Excel/classes/class.ilExcelUtils.php";
$contentstartrow = 0;
foreach ($csvfile as $csvrow) {
$col = 0;
if ($row == 0) {
$worksheet = 0;
$mainworksheet =& $worksheets[$worksheet];
foreach ($csvrow as $text) {
if (is_array($text)) {
$textcount = 0;
foreach ($text as $string) {
$mainworksheet->writeString($row + $textcount, $col, ilExcelUtils::_convert_text($string, $_POST["export_format"]), $format_title);
$textcount++;
$contentstartrow = max($contentstartrow, $textcount);
}
$col++;
} else {
$mainworksheet->writeString($row, $col++, ilExcelUtils::_convert_text($text, $_POST["export_format"]), $format_title);
}
if ($col % 251 == 0) {
$worksheet++;
$col = 1;
$mainworksheet =& $worksheets[$worksheet];
$mainworksheet->writeString($row, 0, ilExcelUtils::_convert_text($csvrow[0], $_POST["export_format"]), $format_title);
}
}
$row = $contentstartrow;
} else {
$worksheet = 0;
$mainworksheet =& $worksheets[$worksheet];
foreach ($csvrow as $text) {
if (is_numeric($text)) {
$mainworksheet->writeNumber($row, $col++, $text);
} else {
$mainworksheet->writeString($row, $col++, ilExcelUtils::_convert_text($text, $_POST["export_format"]));
}
if ($col % 251 == 0) {
$worksheet++;
$col = 1;
$mainworksheet =& $worksheets[$worksheet];
$mainworksheet->writeString($row, 0, ilExcelUtils::_convert_text($csvrow[0], $_POST["export_format"]));
}
}
}
$row++;
}
$workbook->close();
ilUtil::deliverFile($excelfile, "{$surveyname}.xls", "application/vnd.ms-excel");
exit;
break;
case self::TYPE_SPSS:
$csv = "";
$separator = ";";
foreach ($csvfile as $idx => $csvrow) {
$csvrow =& str_replace("\n", " ", $this->object->processCSVRow($csvrow, TRUE, $separator));
$csv .= join($csvrow, $separator) . "\n";
}
include_once "./Services/Utilities/classes/class.ilUtil.php";
ilUtil::deliverData($csv, "{$surveyname}.csv");
exit;
break;
}
}
function createExcelExport(&$settings, &$data, $filename, $a_mode)
{
include_once "./Services/Excel/classes/class.ilExcelUtils.php";
include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
$adapter = new ilExcelWriterAdapter($filename, FALSE);
$workbook = $adapter->getWorkbook();
// Creating a worksheet
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_percent =& $workbook->addFormat();
$format_percent->setNumFormat("0.00%");
$format_datetime =& $workbook->addFormat();
$format_datetime->setNumFormat("DD/MM/YYYY hh:mm:ss");
$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setColor('black');
$format_title->setPattern(1);
$format_title->setFgColor('silver');
$worksheet =& $workbook->addWorksheet();
$row = 0;
$col = 0;
$udf_ex_fields = $this->getUserDefinedExportFields();
// title row
foreach ($settings as $value) {
if ($value == 'ext_account') {
$value = 'user_ext_account';
}
$worksheet->write($row, $col, ilExcelUtils::_convert_text($this->lng->txt($value), $a_mode), $format_title);
$col++;
}
foreach ($udf_ex_fields as $f) {
$worksheet->write($row, $col, ilExcelUtils::_convert_text($f["name"], $a_mode), $format_title);
$col++;
}
$this->lng->loadLanguageModule("meta");
foreach ($data as $index => $rowdata) {
$row++;
$col = 0;
// standard fields
foreach ($settings as $fieldname) {
$value = $rowdata[$fieldname];
switch ($fieldname) {
case "language":
$worksheet->write($row, $col, ilExcelUtils::_convert_text($this->lng->txt("meta_l_" . $value), $a_mode));
break;
case "time_limit_from":
case "time_limit_until":
$date = strftime("%Y-%m-%d %H:%M:%S", $value);
if (preg_match("/(\\d{4})-(\\d{2})-(\\d{2}) (\\d{2}):(\\d{2}):(\\d{2})/", $date, $matches)) {
$worksheet->write($row, $col, ilUtil::excelTime($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6]), $format_datetime);
}
break;
case "last_login":
case "last_update":
case "create_date":
case "approve_date":
case "agree_date":
if (preg_match("/(\\d{4})-(\\d{2})-(\\d{2}) (\\d{2}):(\\d{2}):(\\d{2})/", $value, $matches)) {
$worksheet->write($row, $col, ilUtil::excelTime($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6]), $format_datetime);
}
break;
case "interests_general":
case "interests_help_offered":
case "interests_help_looking":
if (is_array($value) && sizeof($value)) {
$value = implode(", ", $value);
} else {
$value = null;
}
// fallthrough
// fallthrough
default:
$worksheet->write($row, $col, ilExcelUtils::_convert_text($value, $a_mode));
break;
}
$col++;
}
// custom fields
reset($udf_ex_fields);
if (count($udf_ex_fields) > 0) {
include_once "./Services/User/classes/class.ilUserDefinedData.php";
$udf = new ilUserDefinedData($rowdata["usr_id"]);
foreach ($udf_ex_fields as $f) {
$worksheet->write($row, $col, ilExcelUtils::_convert_text($udf->get("f_" . $f["id"]), $a_mode));
$col++;
}
}
}
$workbook->close();
}
/**
* Exports grades as excel
*/
function exportGradesExcel()
{
include_once "./Modules/Exercise/classes/class.ilExAssignment.php";
$ass_data = ilExAssignment::getAssignmentDataOfExercise($this->getId());
include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
$excelfile = ilUtil::ilTempnam();
$adapter = new ilExcelWriterAdapter($excelfile, FALSE);
$workbook = $adapter->getWorkbook();
$workbook->setVersion(8);
// Use Excel97/2000 Format
include_once "./Services/Excel/classes/class.ilExcelUtils.php";
//
// status
//
$mainworksheet = $workbook->addWorksheet();
// header row
$mainworksheet->writeString(0, 0, ilExcelUtils::_convert_text($this->lng->txt("name")));
$cnt = 1;
foreach ($ass_data as $ass) {
$mainworksheet->writeString(0, $cnt, $cnt);
$cnt++;
}
$mainworksheet->writeString(0, $cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_total_exc")));
// data rows
$this->mem_obj = new ilExerciseMembers($this);
$getmems = $this->mem_obj->getMembers();
$mems = array();
foreach ($getmems as $user_id) {
$mems[$user_id] = ilObjUser::_lookupName($user_id);
}
$mems = ilUtil::sortArray($mems, "lastname", "asc", false, true);
$data = array();
$row_cnt = 1;
foreach ($mems as $user_id => $d) {
$col_cnt = 1;
// name
$mainworksheet->writeString($row_cnt, 0, ilExcelUtils::_convert_text($d["lastname"] . ", " . $d["firstname"] . " [" . $d["login"] . "]"));
reset($ass_data);
foreach ($ass_data as $ass) {
$status = ilExAssignment::lookupStatusOfUser($ass["id"], $user_id);
$mainworksheet->writeString($row_cnt, $col_cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_" . $status)));
$col_cnt++;
}
// total status
$status = ilExerciseMembers::_lookupStatus($this->getId(), $user_id);
$mainworksheet->writeString($row_cnt, $col_cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_" . $status)));
$row_cnt++;
}
//
// mark
//
$worksheet2 = $workbook->addWorksheet();
// header row
$worksheet2->writeString(0, 0, ilExcelUtils::_convert_text($this->lng->txt("name")));
$cnt = 1;
foreach ($ass_data as $ass) {
$worksheet2->writeString(0, $cnt, $cnt);
$cnt++;
}
$worksheet2->writeString(0, $cnt, ilExcelUtils::_convert_text($this->lng->txt("exc_total_exc")));
// data rows
$data = array();
$row_cnt = 1;
reset($mems);
foreach ($mems as $user_id => $d) {
$col_cnt = 1;
$d = ilObjUser::_lookupName($user_id);
// name
$worksheet2->writeString($row_cnt, 0, ilExcelUtils::_convert_text($d["lastname"] . ", " . $d["firstname"] . " [" . $d["login"] . "]"));
reset($ass_data);
foreach ($ass_data as $ass) {
$worksheet2->writeString($row_cnt, $col_cnt, ilExcelUtils::_convert_text(ilExAssignment::lookupMarkOfUser($ass["id"], $user_id)));
$col_cnt++;
}
// total mark
include_once 'Services/Tracking/classes/class.ilLPMarks.php';
$worksheet2->writeString($row_cnt, $col_cnt, ilExcelUtils::_convert_text(ilLPMarks::_lookupMark($user_id, $this->getId())));
$row_cnt++;
}
$workbook->close();
$exc_name = ilUtil::getASCIIFilename(preg_replace("/\\s/", "_", $this->getTitle()));
ilUtil::deliverFile($excelfile, $exc_name . ".xls", "application/vnd.ms-excel");
}
/**
* build xml export file
*/
function buildExportFileXLS()
{
global $ilBench;
$ilBench->start("QuestionpoolExport", "buildExportFile");
include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
$adapter = new ilExcelWriterAdapter($this->export_dir . "/" . $this->filename, FALSE);
$workbook = $adapter->getWorkbook();
$workbook->setVersion(8);
// Use Excel97/2000 Format
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_percent =& $workbook->addFormat();
$format_percent->setNumFormat("0.00%");
$format_datetime =& $workbook->addFormat();
$format_datetime->setNumFormat("DD/MM/YYYY hh:mm:ss");
$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setColor('black');
$format_title->setPattern(1);
$format_title->setFgColor('silver');
$worksheet =& $workbook->addWorksheet();
$row = 0;
$col = 0;
// title row
include_once "./Services/Excel/classes/class.ilExcelUtils.php";
include_once "./Modules/TestQuestionPool/classes/class.assQuestion.php";
$worksheet->write($row, $col, ilExcelUtils::_convert_text($this->lng->txt("title"), "latin1"), $format_title);
$col++;
$worksheet->write($row, $col, ilExcelUtils::_convert_text($this->lng->txt("description"), "latin1"), $format_title);
$col++;
$worksheet->write($row, $col, ilExcelUtils::_convert_text($this->lng->txt("question_type"), "latin1"), $format_title);
$col++;
$worksheet->write($row, $col, ilExcelUtils::_convert_text($this->lng->txt("author"), "latin1"), $format_title);
$col++;
$worksheet->write($row, $col, ilExcelUtils::_convert_text($this->lng->txt("create_date"), "latin1"), $format_title);
$col++;
$worksheet->write($row, $col, ilExcelUtils::_convert_text($this->lng->txt("last_update"), "latin1"), $format_title);
$col = 0;
$row++;
$questions = $this->qpl_obj->getQuestionList();
foreach ($questions as $question) {
$worksheet->write($row, $col, ilExcelUtils::_convert_text($question["title"], "latin1"));
$col++;
$worksheet->write($row, $col, ilExcelUtils::_convert_text($question["description"], "latin1"));
$col++;
$worksheet->write($row, $col, ilExcelUtils::_convert_text($this->lng->txt($question["type_tag"]), "latin1"));
$col++;
$worksheet->write($row, $col, ilExcelUtils::_convert_text($question["author"], "latin1"));
$col++;
// ilDatePresentation::formatDate(new ilDateTime($question["created"],IL_CAL_UNIX))
$worksheet->write($row, $col, ilExcelUtils::_convert_text(ilFormat::formatDate(ilFormat::ftimestamp2dateDB($question["created"]), "date"), "latin1"));
$col++;
$worksheet->write($row, $col, ilExcelUtils::_convert_text(ilFormat::formatDate(ilFormat::ftimestamp2dateDB($question["tstamp"]), "date"), "latin1"));
$col = 0;
$row++;
}
$workbook->close();
ilUtil::zip($this->export_dir . "/" . $this->filename, $this->export_dir . "/" . $this->zipfilename);
if (@file_exists($this->export_dir . "/" . $this->filename)) {
@unlink($this->export_dir . "/" . $this->filename);
}
}
/**
*
* @return
*/
public function createExcel()
{
include_once "./Services/Excel/classes/class.ilExcelUtils.php";
include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
$adapter = new ilExcelWriterAdapter($this->getFilename(), false);
$workbook = $adapter->getWorkbook();
$this->worksheet = $workbook->addWorksheet();
$this->write();
$workbook->close();
}
/**
* Export and optionally send current table data
*
* @param int $format
*/
public function exportData($format, $send = false)
{
if ($this->dataExists()) {
// #9640: sort
if (!$this->getExternalSorting()) {
$this->determineOffsetAndOrder(true);
$this->row_data = ilUtil::sortArray($this->row_data, $this->getOrderField(), $this->getOrderDirection(), $this->numericOrdering($this->getOrderField()));
}
$filename = "export";
switch ($format) {
case self::EXPORT_EXCEL:
include_once "./Services/Excel/classes/class.ilExcelUtils.php";
include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
$adapter = new ilExcelWriterAdapter($filename . ".xls", $send);
$workbook = $adapter->getWorkbook();
$worksheet = $workbook->addWorksheet();
$row = 0;
ob_start();
$this->fillMetaExcel($worksheet, $row);
$this->fillHeaderExcel($worksheet, $row);
foreach ($this->row_data as $set) {
$row++;
$this->fillRowExcel($worksheet, $row, $set);
}
ob_end_clean();
$workbook->close();
break;
case self::EXPORT_CSV:
include_once "./Services/Utilities/classes/class.ilCSVWriter.php";
$csv = new ilCSVWriter();
$csv->setSeparator(";");
ob_start();
$this->fillMetaCSV($csv);
$this->fillHeaderCSV($csv);
foreach ($this->row_data as $set) {
$this->fillRowCSV($csv, $set);
}
ob_end_clean();
if ($send) {
$filename .= ".csv";
header("Content-type: text/comma-separated-values");
header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
header("Pragma: public");
echo $csv->getCSVString();
} else {
file_put_contents($filename, $csv->getCSVString());
}
break;
}
if ($send) {
exit;
}
}
}
function ilPaymentExcelWriterAdapter($a_filename, $a_send = true)
{
parent::ilExcelWriterAdapter($a_filename, $a_send);
}
protected function export()
{
global $ilCtrl;
$params = $this->viewToolbar();
if ($params) {
// data
$tfr = explode("-", (string) $params["month"]);
$day_from = date("Y-m-d", mktime(0, 0, 1, $tfr[1] - ($params["scope"] - 1), 1, $tfr[0]));
$day_to = date("Y-m-d", mktime(0, 0, 1, $tfr[1] + 1, 0, $tfr[0]));
unset($tfr);
$chart_data = $this->getChartData($params["figure"], $params["scope"], $day_from, $day_to);
// excel
$period = ilDatePresentation::formatPeriod(new ilDate($day_from, IL_CAL_DATE), new ilDate($day_to, IL_CAL_DATE));
$filename = ilObject::_lookupTitle($this->wiki_id);
if ($this->page_id) {
$filename .= " - " . ilWikiPage::lookupTitle($this->page_id);
}
$filename .= " - " . ilWikiStat::getFigureTitle($params["figure"]) . " - " . $period . ".xls";
include_once "./Services/Excel/classes/class.ilExcelUtils.php";
include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
$adapter = new ilExcelWriterAdapter($filename, true);
$workbook = $adapter->getWorkbook();
$worksheet = $workbook->addWorksheet();
// $worksheet->setLandscape();
/*
$worksheet->setColumn(0, 0, 20);
$worksheet->setColumn(1, 1, 40);
*/
$row = 0;
foreach ($chart_data as $day => $value) {
$row++;
$worksheet->writeString($row, 0, $day);
$worksheet->writeNumber($row, 1, $value);
}
$workbook->close();
exit;
}
$ilCtrl->redirect($this, "view");
}
/**
* Exports group members to Microsoft Excel file
*
* Exports group members to Microsoft Excel file
*
*/
function exportMembersObject()
{
$title = preg_replace("/\\s/", "_", $this->object->getTitle());
include_once "./Services/Excel/classes/class.ilExcelWriterAdapter.php";
$adapter = new ilExcelWriterAdapter("export_" . $title . ".xls");
$workbook = $adapter->getWorkbook();
// Creating a worksheet
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_percent =& $workbook->addFormat();
$format_percent->setNumFormat("0.00%");
$format_datetime =& $workbook->addFormat();
$format_datetime->setNumFormat("DD/MM/YYYY hh:mm:ss");
$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setColor('black');
$format_title->setPattern(1);
$format_title->setFgColor('silver');
$worksheet =& $workbook->addWorksheet();
$column = 0;
$profile_data = array("email", "gender", "firstname", "lastname", "person_title", "institution", "department", "street", "zipcode", "city", "country", "phone_office", "phone_home", "phone_mobile", "fax", "matriculation");
foreach ($profile_data as $data) {
$worksheet->writeString(0, $column++, $this->cleanString($this->lng->txt($data)), $format_title);
}
$member_ids = $this->object->getGroupMemberIds();
$row = 1;
foreach ($member_ids as $member_id) {
$column = 0;
$member =& $this->ilias->obj_factory->getInstanceByObjId($member_id);
if ($member->getPref("public_email") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getEmail()));
} else {
$column++;
}
$worksheet->writeString($row, $column++, $this->cleanString($this->lng->txt("gender_" . $member->getGender())));
$worksheet->writeString($row, $column++, $this->cleanString($member->getFirstname()));
$worksheet->writeString($row, $column++, $this->cleanString($member->getLastname()));
$worksheet->writeString($row, $column++, $this->cleanString($member->getUTitle()));
if ($member->getPref("public_institution") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getInstitution()));
} else {
$column++;
}
if ($member->getPref("public_department") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getDepartment()));
} else {
$column++;
}
if ($member->getPref("public_street") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getStreet()));
} else {
$column++;
}
if ($member->getPref("public_zip") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getZipcode()));
} else {
$column++;
}
if ($member->getPref("public_city") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getCity()));
} else {
$column++;
}
if ($member->getPref("public_country") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getCountry()));
} else {
$column++;
}
if ($member->getPref("public_phone_office") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getPhoneOffice()));
} else {
$column++;
}
if ($member->getPref("public_phone_home") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getPhoneHome()));
} else {
$column++;
}
if ($member->getPref("public_phone_mobile") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getPhoneMobile()));
} else {
$column++;
}
if ($member->getPref("public_fax") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getFax()));
} else {
$column++;
}
if ($member->getPref("public_matriculation") == "y") {
$worksheet->writeString($row, $column++, $this->cleanString($member->getMatriculation()));
} else {
$column++;
}
$row++;
}
$workbook->close();
}