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

Example 26 with OpenXMLWorksheet

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

the class XlsFlexiTableExporter method export.

@Override
public MediaResource export(FlexiTableComponent ftC, List<FlexiColumnModel> columns, Translator translator) {
    String label = "TableExport_" + Formatter.formatDatetimeFilesystemSave(new Date(System.currentTimeMillis())) + ".xlsx";
    return new OpenXMLWorkbookResource(label) {

        @Override
        protected void generate(OutputStream out) {
            try (OpenXMLWorkbook workbook = new OpenXMLWorkbook(out, 1)) {
                OpenXMLWorksheet sheet = workbook.nextWorksheet();
                createHeader(columns, translator, sheet, workbook);
                createData(ftC, columns, translator, sheet, workbook);
            } catch (IOException e) {
                log.error("", e);
            }
        }
    };
}

Example 27 with OpenXMLWorksheet

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

the class CheckListExcelExport method writeDataRow.

private void writeDataRow(AssessmentData data, AssessmentEntry entry, int num, OpenXMLWorksheet exportSheet) {
    int col = 0;
    Identity assessedIdentity = data.getIdentity();
    User assessedUser = assessedIdentity.getUser();
    Row dataRow = exportSheet.newRow();
    // sequence number
    dataRow.addCell(col++, num, null);
    for (UserPropertyHandler userPropertyHandler : userPropertyHandlers) {
        if (userPropertyHandler != null) {
            String property = userPropertyHandler.getUserProperty(assessedUser, translator.getLocale());
            dataRow.addCell(col++, property, null);
        }
    }
    // homepage
    ContextEntry ce = BusinessControlFactory.getInstance().createContextEntry(assessedIdentity);
    String homepage = BusinessControlFactory.getInstance().getAsURIString(Collections.singletonList(ce), false);
    dataRow.addCell(col++, homepage, null);
    // course node points and passed
    if (courseNode.hasScoreConfigured()) {
        if (entry != null && entry.getScore() != null) {
            dataRow.addCell(col++, entry.getScore(), null);
        } else {
            col++;
        }
    }
    if (courseNode.hasPassedConfigured()) {
        if (entry != null && entry.getPassed() != null) {
            dataRow.addCell(col++, entry.getPassed().toString(), null);
        } else {
            col++;
        }
    }
    ModuleConfiguration config = courseNode.getModuleConfiguration();
    CheckboxList list = (CheckboxList) config.get(CheckListCourseNode.CONFIG_KEY_CHECKBOX);
    if (list != null && list.getList() != null && data.getChecks() != null) {
        Map<String, DBCheck> checkMap = data.getChecks().stream().collect(Collectors.toMap(c -> c.getCheckbox().getCheckboxId(), c -> c));
        List<Checkbox> checkboxList = list.getList();
        for (Checkbox checkbox : checkboxList) {
            String boxId = checkbox.getCheckboxId();
            DBCheck check = checkMap.get(boxId);
            if (check != null && check.getChecked() != null && check.getChecked().booleanValue()) {
                dataRow.addCell(col++, "x", null);
            } else {
                col++;
            }
            if (courseNode.hasScoreConfigured() && checkbox.getPoints() != null) {
                if (check != null && check.getScore() != null) {
                    dataRow.addCell(col++, check.getScore(), null);
                } else {
                    col++;
                }
            }
        }
    }
}

Example 28 with OpenXMLWorksheet

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

the class CheckListExcelExport method exportWorkbook.

public void exportWorkbook(List<AssessmentData> dataList, OutputStream exportStream) {
    try (OpenXMLWorkbook workbook = new OpenXMLWorkbook(exportStream, 1)) {
        // headers
        OpenXMLWorksheet exportSheet = workbook.nextWorksheet();
        exportSheet.setHeaderRows(1);
        writeHeaders(exportSheet, workbook);
        writeData(dataList, exportSheet);
    } catch (Exception e) {
        log.error("", e);
    }
}

Example 29 with OpenXMLWorksheet

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

the class DefaultXlsTableExporter method export.

/**
 * @see org.olat.core.gui.components.table.TableExporter#export(org.olat.core.gui.components.table.Table)
 */
@Override
public MediaResource export(final Table table) {
    Translator translator = table.getTranslator();
    int cdcnt = table.getColumnCount();
    int rcnt = table.getRowCount();
    String label = "TableExport_" + Formatter.formatDatetimeFilesystemSave(new Date(System.currentTimeMillis())) + ".xlsx";
    return new OpenXMLWorkbookResource(label) {

        @Override
        protected void generate(OutputStream out) {
            try (OpenXMLWorkbook workbook = new OpenXMLWorkbook(out, 1)) {
                OpenXMLWorksheet sheet = workbook.nextWorksheet();
                createHeader(table, translator, cdcnt, sheet, workbook);
                createData(table, cdcnt, rcnt, sheet);
            } catch (IOException e) {
                log.error("", e);
            }
        }
    };
}

Example 30 with OpenXMLWorksheet

use of org.olat.core.util.openxml.OpenXMLWorksheet 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);
}