Examples with OpenXMLWorkbook org.olat.core.util.openxml.OpenXMLWorkbook used on opensource projects

Example 26 with OpenXMLWorkbook

use of org.olat.core.util.openxml.OpenXMLWorkbook in project openolat by klemens.

the class ScoreAccountingHelper method createCourseResultsOverviewXMLTable.

/**
 * The results from assessable nodes are written to one row per user into an excel-sheet. An
 * assessable node will only appear if it is producing at least one of the
 * following variables: score, passed, attempts, comments.
 *
 * @param identities The list of identities which results need to be archived.
 * @param myNodes The assessable nodes to archive.
 * @param course The course.
 * @param locale The locale.
 * @param bos The output stream (which will be closed at the end, if you use a zip stream don't forget to shield it).
 */
public static void createCourseResultsOverviewXMLTable(List<Identity> identities, List<AssessableCourseNode> myNodes, ICourse course, Locale locale, OutputStream bos) {
    OpenXMLWorkbook workbook = new OpenXMLWorkbook(bos, 1);
    OpenXMLWorksheet sheet = workbook.nextWorksheet();
    sheet.setHeaderRows(2);
    int headerColCnt = 0;
    Translator t = Util.createPackageTranslator(ScoreAccountingArchiveController.class, locale);
    String sequentialNumber = t.translate("column.header.seqnum");
    String login = t.translate("column.header.businesspath");
    // user properties are dynamic
    String sc = t.translate("column.header.score");
    String pa = t.translate("column.header.passed");
    String co = t.translate("column.header.comment");
    String cco = t.translate("column.header.coachcomment");
    String at = t.translate("column.header.attempts");
    String il = t.translate("column.header.initialLaunchDate");
    String slm = t.translate("column.header.scoreLastModified");
    String na = t.translate("column.field.notavailable");
    String mi = t.translate("column.field.missing");
    String yes = t.translate("column.field.yes");
    String no = t.translate("column.field.no");
    String submitted = t.translate("column.field.submitted");
    Row headerRow1 = sheet.newRow();
    headerRow1.addCell(headerColCnt++, sequentialNumber);
    headerRow1.addCell(headerColCnt++, login);
    // Initial launch date
    headerRow1.addCell(headerColCnt++, il);
    // get user property handlers for this export, translate using the fallback
    // translator configured in the property handler
    List<UserPropertyHandler> userPropertyHandlers = UserManager.getInstance().getUserPropertyHandlersFor(ScoreAccountingHelper.class.getCanonicalName(), true);
    t = UserManager.getInstance().getPropertyHandlerTranslator(t);
    for (UserPropertyHandler propertyHandler : userPropertyHandlers) {
        headerRow1.addCell(headerColCnt++, t.translate(propertyHandler.i18nColumnDescriptorLabelKey()));
    }
    int header1ColCnt = headerColCnt;
    for (AssessableCourseNode acNode : myNodes) {
        headerRow1.addCell(header1ColCnt++, acNode.getShortTitle());
        header1ColCnt += acNode.getType().equals("ita") ? 1 : 0;
        boolean scoreOk = acNode.hasScoreConfigured();
        boolean passedOk = acNode.hasPassedConfigured();
        boolean attemptsOk = acNode.hasAttemptsConfigured();
        boolean commentOk = acNode.hasCommentConfigured();
        if (scoreOk || passedOk || commentOk || attemptsOk) {
            header1ColCnt += scoreOk ? 1 : 0;
            header1ColCnt += passedOk ? 1 : 0;
            header1ColCnt += attemptsOk ? 1 : 0;
            // last modified
            header1ColCnt++;
            header1ColCnt += commentOk ? 1 : 0;
            // coach comment
            header1ColCnt++;
        }
        // column title
        header1ColCnt--;
    }
    int header2ColCnt = headerColCnt;
    Row headerRow2 = sheet.newRow();
    for (AssessableCourseNode acNode : myNodes) {
        if (acNode.getType().equals("ita")) {
            headerRow2.addCell(header2ColCnt++, submitted);
        }
        boolean scoreOk = acNode.hasScoreConfigured();
        boolean passedOk = acNode.hasPassedConfigured();
        boolean attemptsOk = acNode.hasAttemptsConfigured();
        boolean commentOk = acNode.hasCommentConfigured();
        if (scoreOk || passedOk || commentOk || attemptsOk) {
            if (scoreOk) {
                headerRow2.addCell(header2ColCnt++, sc);
            }
            if (passedOk) {
                headerRow2.addCell(header2ColCnt++, pa);
            }
            if (attemptsOk) {
                headerRow2.addCell(header2ColCnt++, at);
            }
            // last modified
            headerRow2.addCell(header2ColCnt++, slm);
            if (commentOk) {
                headerRow2.addCell(header2ColCnt++, co);
            }
            // coach comment
            headerRow2.addCell(header2ColCnt++, cco);
        }
    }
    // preload user properties cache
    CourseEnvironment courseEnvironment = course.getCourseEnvironment();
    int rowNumber = 0;
    DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm");
    UserCourseInformationsManager mgr = CoreSpringFactory.getImpl(UserCourseInformationsManager.class);
    OLATResource courseResource = courseEnvironment.getCourseGroupManager().getCourseResource();
    Map<Long, Date> firstTimes = mgr.getInitialLaunchDates(courseResource, identities);
    for (Identity identity : identities) {
        Row dataRow = sheet.newRow();
        int dataColCnt = 0;
        ContextEntry ce = BusinessControlFactory.getInstance().createContextEntry(identity);
        String uname = BusinessControlFactory.getInstance().getAsURIString(Collections.singletonList(ce), false);
        dataRow.addCell(dataColCnt++, ++rowNumber, null);
        dataRow.addCell(dataColCnt++, uname, null);
        if (firstTimes.containsKey(identity.getKey())) {
            dataRow.addCell(dataColCnt++, firstTimes.get(identity.getKey()), workbook.getStyles().getDateStyle());
        } else {
            dataRow.addCell(dataColCnt++, mi);
        }
        // add dynamic user properties
        for (UserPropertyHandler propertyHandler : userPropertyHandlers) {
            String value = propertyHandler.getUserProperty(identity.getUser(), t.getLocale());
            dataRow.addCell(dataColCnt++, (StringHelper.containsNonWhitespace(value) ? value : na));
        }
        // create a identenv with no roles, no attributes, no locale
        IdentityEnvironment ienv = new IdentityEnvironment();
        ienv.setIdentity(identity);
        UserCourseEnvironment uce = new UserCourseEnvironmentImpl(ienv, course.getCourseEnvironment());
        ScoreAccounting scoreAccount = uce.getScoreAccounting();
        scoreAccount.evaluateAll();
        AssessmentManager am = course.getCourseEnvironment().getAssessmentManager();
        for (AssessableCourseNode acnode : myNodes) {
            boolean scoreOk = acnode.hasScoreConfigured();
            boolean passedOk = acnode.hasPassedConfigured();
            boolean attemptsOk = acnode.hasAttemptsConfigured();
            boolean commentOk = acnode.hasCommentConfigured();
            if (acnode.getType().equals("ita")) {
                String log = acnode.getUserLog(uce);
                String date = null;
                Date lastUploaded = null;
                try {
                    log = log.toLowerCase();
                    log = log.substring(0, log.lastIndexOf("submit"));
                    log = log.substring(log.lastIndexOf("date:"));
                    date = log.split("\n")[0].substring(6);
                    lastUploaded = df.parse(date);
                } catch (Exception e) {
                // 
                }
                if (lastUploaded != null) {
                    dataRow.addCell(dataColCnt++, lastUploaded, workbook.getStyles().getDateStyle());
                } else {
                    // date == null
                    dataRow.addCell(dataColCnt++, mi);
                }
            }
            if (scoreOk || passedOk || commentOk || attemptsOk) {
                ScoreEvaluation se = scoreAccount.evalCourseNode(acnode);
                if (scoreOk) {
                    Float score = se.getScore();
                    if (score != null) {
                        dataRow.addCell(dataColCnt++, AssessmentHelper.getRoundedScore(score), null);
                    } else {
                        // score == null
                        dataRow.addCell(dataColCnt++, mi);
                    }
                }
                if (passedOk) {
                    Boolean passed = se.getPassed();
                    if (passed != null) {
                        String yesno;
                        if (passed.booleanValue()) {
                            yesno = yes;
                        } else {
                            yesno = no;
                        }
                        dataRow.addCell(dataColCnt++, yesno);
                    } else {
                        // passed == null
                        dataRow.addCell(dataColCnt++, mi);
                    }
                }
                if (attemptsOk) {
                    Integer attempts = am.getNodeAttempts(acnode, identity);
                    int a = attempts == null ? 0 : attempts.intValue();
                    dataRow.addCell(dataColCnt++, a, null);
                }
                Date lastModified = am.getScoreLastModifiedDate(acnode, identity);
                if (lastModified != null) {
                    dataRow.addCell(dataColCnt++, lastModified, workbook.getStyles().getDateStyle());
                } else {
                    dataRow.addCell(dataColCnt++, mi);
                }
                if (commentOk) {
                    // Comments for user
                    String comment = am.getNodeComment(acnode, identity);
                    if (comment != null) {
                        dataRow.addCell(dataColCnt++, comment);
                    } else {
                        dataRow.addCell(dataColCnt++, mi);
                    }
                }
                // Always export comments for tutors
                String coachComment = am.getNodeCoachComment(acnode, identity);
                if (coachComment != null) {
                    dataRow.addCell(dataColCnt++, coachComment);
                } else {
                    dataRow.addCell(dataColCnt++, mi);
                }
            }
        }
    }
    // min. max. informations
    boolean first = true;
    for (AssessableCourseNode acnode : myNodes) {
        if (!acnode.hasScoreConfigured()) {
            // only show min/max/cut legend when score configured
            continue;
        }
        if (first) {
            sheet.newRow().addCell(0, "");
            sheet.newRow().addCell(0, "");
            sheet.newRow().addCell(0, t.translate("legend"));
            sheet.newRow().addCell(0, "");
            first = false;
        }
        String minVal;
        String maxVal;
        String cutVal;
        if (acnode instanceof STCourseNode || !acnode.hasScoreConfigured()) {
            minVal = maxVal = cutVal = "-";
        } else {
            minVal = acnode.getMinScoreConfiguration() == null ? "-" : AssessmentHelper.getRoundedScore(acnode.getMinScoreConfiguration());
            maxVal = acnode.getMaxScoreConfiguration() == null ? "-" : AssessmentHelper.getRoundedScore(acnode.getMaxScoreConfiguration());
            if (acnode.hasPassedConfigured()) {
                cutVal = acnode.getCutValueConfiguration() == null ? "-" : AssessmentHelper.getRoundedScore(acnode.getCutValueConfiguration());
            } else {
                cutVal = "-";
            }
        }
        sheet.newRow().addCell(0, acnode.getShortTitle());
        Row minRow = sheet.newRow();
        minRow.addCell(2, "minValue");
        minRow.addCell(3, minVal);
        Row maxRow = sheet.newRow();
        maxRow.addCell(2, "maxValue");
        maxRow.addCell(3, maxVal);
        Row cutRow = sheet.newRow();
        cutRow.addCell(2, "cutValue");
        cutRow.addCell(3, cutVal);
    }
    IOUtils.closeQuietly(workbook);
}

Example 27 with OpenXMLWorkbook

use of org.olat.core.util.openxml.OpenXMLWorkbook in project openolat by klemens.

the class QTI21ArchiveFormat method exportWorkbook.

public void exportWorkbook(OutputStream exportStream) {
    RepositoryEntry testEntry = searchParams.getTestEntry();
    FileResourceManager frm = FileResourceManager.getInstance();
    File unzippedDirRoot = frm.unzipFileResource(testEntry.getOlatResource());
    resolvedAssessmentTest = qtiService.loadAndResolveAssessmentTest(unzippedDirRoot, false, false);
    // content
    final List<AssessmentTestSession> sessions = testSessionDao.getTestSessionsOfResponse(searchParams);
    final List<AssessmentResponse> responses = responseDao.getResponse(searchParams);
    try (OpenXMLWorkbook workbook = new OpenXMLWorkbook(exportStream, 1)) {
        // headers
        OpenXMLWorksheet exportSheet = workbook.nextWorksheet();
        exportSheet.setHeaderRows(2);
        writeHeaders_1(exportSheet, workbook);
        writeHeaders_2(exportSheet, workbook);
        writeData(sessions, responses, exportSheet, workbook);
    } catch (Exception e) {
        log.error("", e);
    }
}

Example 28 with OpenXMLWorkbook

use of org.olat.core.util.openxml.OpenXMLWorkbook in project openolat by klemens.

the class ChatLogHelper method logMediaResource.

public MediaResource logMediaResource(OLATResourceable ores, Locale locale) {
    Translator translator = Util.createPackageTranslator(ChatController.class, locale);
    String tableExportTitle = translator.translate("logChat.export.title");
    String label = tableExportTitle + Formatter.formatDatetimeFilesystemSave(new Date(System.currentTimeMillis())) + ".xlsx";
    return new OpenXMLWorkbookResource(label) {

        @Override
        protected void generate(OutputStream out) {
            try (OpenXMLWorkbook workbook = new OpenXMLWorkbook(out, 1)) {
                // headers
                OpenXMLWorksheet exportSheet = workbook.nextWorksheet();
                Row headerRow = exportSheet.newRow();
                headerRow.addCell(0, "User", workbook.getStyles().getHeaderStyle());
                headerRow.addCell(1, "Date", workbook.getStyles().getHeaderStyle());
                headerRow.addCell(2, "Content", workbook.getStyles().getHeaderStyle());
                // content
                List<InstantMessage> messages = imDao.getMessages(ores, null, 0, -1);
                for (InstantMessage message : messages) {
                    Row dataRow = exportSheet.newRow();
                    dataRow.addCell(0, message.getFromNickName(), null);
                    dataRow.addCell(1, message.getCreationDate(), workbook.getStyles().getDateStyle());
                    dataRow.addCell(2, message.getBody(), null);
                }
            } catch (IOException e) {
                log.error("", e);
            }
        }
    };
}