Example usage for org.apache.poi.ss.usermodel Workbook createSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createSheet

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook createSheet.

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.exporter.TimeseriesExcelTemplateGenerator.java

License:Open Source License

private void generateSheet(TimeseriesWorkbookContext wbContext, TypeOfBuildingBlock tobb, AttributeType at) {
    if (!hasPermissions(tobb, at)) {
        return;/*from   w  w  w. ja v a2s . co  m*/
    }

    Workbook workbook = wbContext.getWorkbook();

    String sheetName = MessageAccess.getStringOrNull(tobb.getAbbreviationValue()) + "-" + at.getName();
    sheetName = ExcelUtils.makeSheetNameValid(sheetName, workbook);
    Sheet sheet = workbook.createSheet(sheetName);

    TimeseriesSheetContext sheetContext = new TimeseriesSheetContext(sheet, tobb, at);
    wbContext.getSheetContexts().put(sheetName, sheetContext);

    preformatCells(sheet, wbContext.getCellStyles());

    addSheetTitle(sheetContext, wbContext.getCellStyles());

    addBuildingBlockType(sheetContext, wbContext.getCellStyles());

    addAttributeType(sheetContext, wbContext.getCellStyles());

    addDataHeader(sheetContext, wbContext.getCellStyles());
}

From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLSX.java

License:Open Source License

public void write() throws FileNotFoundException, IOException {
    FileOutputStream out = new FileOutputStream(new File(filename));
    Workbook wb;

    wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet(sheetName);

    //turn off gridlines
    sheet.setDisplayGridlines(false);/*from   w  ww.  ja v  a2 s.  com*/
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);

    int[][] width = new int[titles.length][titles.length];

    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell((short) i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));

        width[i][0] = titles[i].length();
    }

    Row row;
    Cell cell;
    int rownum = 1;

    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell((short) j);
            if (data[i][j] == null)
                data[i][j] = "";

            cell.setCellValue(data[i][j].toString());

            if (data[i][j].toString().length() > width[j][0])
                width[j][0] = data[i][j].toString().length();
        }
    }

    for (short i = 0; i < titles.length; i++) {
        short widthShort = (short) (256 * (width[i][0] + 3));

        sheet.setColumnWidth(i, widthShort);
    }

    int position = (titles.length / 2) - 1;

    row = sheet.createRow(rownum + 3);
    cell = row.createCell((short) position);

    if (footName == null) {
        SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm");
        cell.setCellValue("Export MaklerPoint vom " + df.format(new Date(System.currentTimeMillis()))
                + " - www.maklerpoint.de");
    } else {
        cell.setCellValue(footName);
    }

    sheet.setZoom(3, 4);

    wb.write(out);
    out.close();
}

From source file:de.quamoco.qm.editor.excelexport.HumanReadableExcelExporter.java

License:Apache License

private void createAllInOneSheet(Workbook workbook) {
    Sheet sheet = workbook.createSheet("All-In-One");
    sheet.setDefaultColumnWidth(30);/*from w ww  .j a v a 2 s.  com*/

    String[] names = new String[] { "Measure Name", "Tool", "Normalization", "Measure Description",
            "Measure Weight", "Influenced Factor", "Factor weight", "Impacted Quality Aspect",
            "Effect of the Impact", "Rationale for the Impact", "Description of the Quality Aspect" };

    Row header = sheet.createRow(0);
    for (int col = 0; col < names.length; col++) {
        header.createCell(col).setCellValue(names[col]);
    }

    int row = 1;
    for (Measure measure : measures) {
        for (Factor factor : measure.getMeasuresDirect()) {

            for (Impact imp : factor.getInfluences()) {
                Factor qa = imp.getTarget();
                Evaluation qaeval = qa.getActualEvaluation(context);

                WeightedSumFactorAggregation wsf = null;
                if (!(qaeval instanceof WeightedSumFactorAggregation)) {
                    System.err.println("WARNING: factor '" + factor + "' qa '" + qa
                            + "' does not have a WeightedSumFactorAggregation");
                } else {
                    wsf = (WeightedSumFactorAggregation) qaeval;
                }

                double qaweight = Double.NaN;
                if (wsf != null) {
                    for (Ranking ranking : wsf.getRankings()) {
                        if (ranking.getRankedElement() == factor) {
                            qaweight = ranking.getWeight();
                        }
                    }
                }

                Evaluation factoreval = factor.getActualEvaluation(context);
                WeightedSumMultiMeasureEvaluation mme = null;
                if (factoreval == null) {
                    System.err.println("WARNING: measure '" + measure.getQualifiedName() + "' factor '"
                            + factor.getQualifiedName() + "' does not have an evaluation at all.");
                } else if (!(factoreval instanceof WeightedSumMultiMeasureEvaluation)) {
                    System.err.println("WARNING: measure '" + measure.getQualifiedName() + "' factor '"
                            + factor.getQualifiedName() + "' does not have a multiMeasureEvaluation, but a "
                            + factoreval.getClass());
                } else {
                    mme = (WeightedSumMultiMeasureEvaluation) factoreval;
                }

                String norm = "NO NORMALIZATION";
                double weight = Double.NaN;
                if (mme != null) {
                    for (Ranking ranking : mme.getRankings()) {
                        if (ranking.getRankedElement() == measure) {
                            weight = ranking.getWeight();
                            if (ranking instanceof MeasureRanking) {
                                MeasureRanking mr = (MeasureRanking) ranking;
                                Function f = mr.getFunction();

                                norm = mr.getRange() + printFunction(f)
                                        + (mr.getNormlizationMeasure() != null
                                                ? mr.getNormlizationMeasure().getQualifiedName()
                                                : "");
                            }
                        }
                    }
                }

                Row r = sheet.createRow(row++);

                //               "Measure Name", 
                //               "Tool",
                //               "Normalization", 
                //               "Measure Description", 
                //               "Measure Weight",
                //               "Influenced Factor", 
                //               "Factor weight",
                //               "Impacted Quality Aspect", 
                //               "Effect of the Impact",
                //               "Rationale for the Impact", 
                //               "Description of the Quality Aspect"

                int cell = 0;
                r.createCell(cell++).setCellValue(measure.getQualifiedName());
                r.createCell(cell++).setCellValue(getToolName(measure));
                r.createCell(cell++).setCellValue(norm);
                r.createCell(cell++).setCellValue(measure.getDescription());
                if (!Double.isNaN(weight)) {
                    r.createCell(cell++).setCellValue(weight);
                } else {
                    r.createCell(cell++).setCellValue("NO WEIGHT GIVEN.");
                }
                r.createCell(cell++).setCellValue(factor.getQualifiedName());
                if (!Double.isNaN(qaweight)) {
                    r.createCell(cell++).setCellValue(qaweight);
                } else {
                    r.createCell(cell++).setCellValue("NO WEIGHT GIVEN.");
                }
                r.createCell(cell++).setCellValue(qa.getQualifiedName());
                r.createCell(cell++).setCellValue(imp.getEffect().getLiteral());
                r.createCell(cell++).setCellValue(imp.getJustification());
                r.createCell(cell++).setCellValue(qa.getDescription());
            }
        }
    }
}

From source file:de.quamoco.qm.editor.excelexport.HumanReadableExcelExporter.java

License:Apache License

private void createMeasureSheet(Workbook workbook) {
    Sheet sheet = workbook.createSheet("Measures");
    sheet.setDefaultColumnWidth(30);/*from ww w . ja  va 2  s. c  o m*/

    String[] names = new String[] { "Measure Name", "Tool", "Normalization", "Measure Description",
            "Measure Weight", "Influenced Factor" };

    Row header = sheet.createRow(0);
    for (int col = 0; col < names.length; col++) {
        header.createCell(col).setCellValue(names[col]);
    }

    int row = 1;
    for (Measure measure : measures) {
        for (Factor factor : measure.getMeasuresDirect()) {
            Evaluation eval = factor.getActualEvaluation(context);
            WeightedSumMultiMeasureEvaluation mme = null;
            if (eval == null) {
                System.err.println("WARNING: measure '" + measure.getQualifiedName() + "' factor '"
                        + factor.getQualifiedName() + "' does not have an evaluation at all.");
            } else if (!(eval instanceof WeightedSumMultiMeasureEvaluation)) {
                System.err.println("WARNING: measure '" + measure.getQualifiedName() + "' factor '"
                        + factor.getQualifiedName() + "' does not have a multiMeasureEvaluation, but a "
                        + eval.getClass());
            } else {
                mme = (WeightedSumMultiMeasureEvaluation) eval;
            }

            String norm = "NO NORMALIZATION";
            double weight = Double.NaN;
            if (mme != null) {
                for (Ranking ranking : mme.getRankings()) {
                    if (ranking.getRankedElement() == measure) {
                        weight = ranking.getWeight();
                        if (ranking instanceof MeasureRanking) {
                            MeasureRanking mr = (MeasureRanking) ranking;
                            Function f = mr.getFunction();

                            norm = mr.getRange() + printFunction(f)
                                    + (mr.getNormlizationMeasure() != null
                                            ? mr.getNormlizationMeasure().getQualifiedName()
                                            : "");
                        }
                    }
                }
            }

            Row r = sheet.createRow(row++);

            int cell = 0;
            r.createCell(cell++).setCellValue(measure.getQualifiedName());
            r.createCell(cell++).setCellValue(getToolName(measure));
            r.createCell(cell++).setCellValue(norm);
            r.createCell(cell++).setCellValue(measure.getDescription());
            if (!Double.isNaN(weight)) {
                r.createCell(cell++).setCellValue(weight);
            } else {
                r.createCell(cell++).setCellValue("NO WEIGHT GIVEN.");
            }
            r.createCell(cell++).setCellValue(factor.getQualifiedName());
        }
    }
}

From source file:de.quamoco.qm.editor.excelexport.HumanReadableExcelExporter.java

License:Apache License

private void createFactorSheet(Workbook workbook) {
    Sheet sheet = workbook.createSheet("Factors");
    sheet.setDefaultColumnWidth(30);/*from  w  w w . ja v a  2s.  c  o  m*/

    String[] names = new String[] { "Factor Name", "Factor Description", "Factor Weight",
            "Impacted Quality Aspect", "Effect of the Impact", "Rationale for the Impact" };

    Row header = sheet.createRow(0);
    for (int col = 0; col < names.length; col++) {
        header.createCell(col).setCellValue(names[col]);
    }

    int row = 1;
    for (Factor factor : factors) {

        if (factor.getCharacterizes() == null) {
            continue;
        }

        for (Impact imp : factor.getInfluences()) {
            Factor qa = imp.getTarget();
            Evaluation eval = qa.getActualEvaluation(context);

            WeightedSumFactorAggregation wsf = null;
            if (!(eval instanceof WeightedSumFactorAggregation)) {
                System.err.println("WARNING: factor '" + factor + "' qa '" + qa
                        + "' does not have a WeightedSumFactorAggregation");
            } else {
                wsf = (WeightedSumFactorAggregation) eval;
            }

            double weight = Double.NaN;
            if (wsf != null) {
                for (Ranking ranking : wsf.getRankings()) {
                    if (ranking.getRankedElement() == factor) {
                        weight = ranking.getWeight();
                    }
                }
            }

            Row r = sheet.createRow(row++);

            int cell = 0;
            r.createCell(cell++).setCellValue(factor.getQualifiedName());
            r.createCell(cell++).setCellValue(factor.getDescription());
            r.createCell(cell++).setCellValue(weight);
            r.createCell(cell++).setCellValue(qa.getQualifiedName());
            r.createCell(cell++).setCellValue(imp.getEffect().getLiteral());
            r.createCell(cell++).setCellValue(imp.getJustification());
        }
    }
}

From source file:de.quamoco.qm.editor.excelexport.HumanReadableExcelExporter.java

License:Apache License

private void createQASheet(Workbook workbook) {
    Sheet sheet = workbook.createSheet("QualityAspects");
    sheet.setDefaultColumnWidth(30);//w w  w .  j  a  va2 s  . co  m

    String[] names = new String[] { "QualityAspect Name", "Quality Aspect Description" };

    Row header = sheet.createRow(0);
    for (int col = 0; col < names.length; col++) {
        header.createCell(col).setCellValue(names[col]);
    }

    int row = 1;
    for (Factor factor : factors) {

        if (factor.getCharacterizes() != null) {
            continue;
        }

        Row r = sheet.createRow(row++);

        int cell = 0;
        r.createCell(cell++).setCellValue(factor.getQualifiedName());
        r.createCell(cell++).setCellValue(factor.getDescription());
    }
}

From source file:de.quamoco.qm.editor.export.ResultCalibrationExporter.java

License:Apache License

/** Create the sheet to display the {@link MeasureEvaluation}s. */
private void createMeasureEvaluationSheet(Workbook workbook, IProgressMonitor monitor) throws IOException {
    checkNumberOfColumns(workbook, measureEvaluations.size() + 1);

    Sheet sheet = workbook.createSheet("Measures");
    sheet.setDefaultColumnWidth(30);/*w w w. j a v  a  2 s .c  om*/

    int header = createMeasureEvaluationSheetHeader(sheet);
    createMeasureEvaluationSheetBody(sheet, header, monitor);

    autoSizeColumns(sheet);
}

From source file:de.quamoco.qm.editor.export.ResultCalibrationExporter.java

License:Apache License

/** Create the sheet to display the {@link FactorAggregation}s. */
private void createFactorAggregationSheet(Workbook workbook, EAttribute attribute, String title,
        IProgressMonitor monitor) throws IOException {
    Sheet sheet = workbook.createSheet(title);

    int header = createFactorAggregationSheetHeader(sheet);
    createFactorAggregationSheetBody(sheet, attribute, header, monitor);

    autoSizeColumns(sheet);//from w  w w . j a v a  2 s . com
}

From source file:de.quamoco.qm.editor.export.ResultCalibrationExporter.java

License:Apache License

/**
 * Create the sheet to display {@link Impact}s and {@link FactorRefinement}s
 * to {@link FactorAggregation}s.//from  w  w w.j a v a 2s . com
 */
private void createFactorImpactSheet(Workbook workbook, IProgressMonitor monitor) throws IOException {
    Sheet sheet = workbook.createSheet("Impact");

    int header = createFactorAggregationSheetHeader(sheet);
    createFactorImpactSheetBody(sheet, header, monitor);

    autoSizeColumns(sheet);
}

From source file:de.thb.ue.backend.util.EvaluationExcelFileGenerator.java

License:Apache License

public void generateExcelFile() {

    Row row;//www  .  jav a 2  s  .  c om
    Cell cell;
    int yOffset = 1;
    File workingDirectory = new File(
            (workingDirectoryPath.isEmpty() ? "" : (workingDirectoryPath + File.separatorChar))
                    + evaluationUID);
    if (!workingDirectory.exists()) {
        workingDirectory.mkdir();
    }
    File file = new File(workingDirectory, "auswertung.xls");

    try {
        FileOutputStream out = new FileOutputStream(file);
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("Evaluation");

        // configure cell styles
        configureCellStyles(wb);

        /*
        * **********************************
        * begin formatting document
        * **********************************
        * */

        //construct first row of infopanel
        yOffset = constructInfoPanelRow("Lehrveranstaltung", subject, numberStudentsAll, yOffset, wb, sheet,
                InfoPanelBorderStyles.topLeftCorner, InfoPanelBorderStyles.top,
                InfoPanelBorderStyles.topRightCorner);

        //construct second row of infopanel
        yOffset = constructInfoPanelRow("Semester", semesterType == SemesterType.WINTER ? "Winter" : "Sommer",
                numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none,
                InfoPanelBorderStyles.right);

        //construct third row of infopanel
        StringBuilder tutors = new StringBuilder();
        for (int i = 0; i < this.tutors.size(); i++) {
            if (i + 1 < this.tutors.size()) {
                tutors.append(this.tutors.get(i)).append(", ");
            } else {
                tutors.append(this.tutors.get(i));
            }
        }

        yOffset = constructInfoPanelRow("Lehrende(r)", tutors.toString(), numberStudentsAll, yOffset, wb, sheet,
                InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right);

        //construct fourth row of infopanel
        yOffset = constructInfoPanelRow("Datum der Befragung", dateOfEvaluation.toString("dd.MM.yy HH:mm"),
                numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none,
                InfoPanelBorderStyles.right);

        //construct fifth row of infopanel
        yOffset = constructInfoPanelRow("Anzahl der Teilnehmer", Integer.toString(numberStudentsAll),
                numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none,
                InfoPanelBorderStyles.right);

        //construct sixth row of infopanel () last
        yOffset = constructInfoPanelRow("Anzahl der ausgefllten Fragebgen",
                Integer.toString(numberStudentsVoted), numberStudentsAll, yOffset, wb, sheet,
                InfoPanelBorderStyles.bottomLeftCorner, InfoPanelBorderStyles.bottom,
                InfoPanelBorderStyles.bottomRightCorner);

        //begin construction of evaluationPanel
        yOffset++;
        row = sheet.createRow(yOffset);
        cell = row.createCell(1, Cell.CELL_TYPE_STRING);
        cell.setCellValue("Frage");
        cell.setCellStyle(headerStyle);

        cell = row.createCell(2, Cell.CELL_TYPE_STRING);
        cell.setCellValue("MW");
        cell.setCellStyle(headerStyle);

        cell = row.createCell(3, Cell.CELL_TYPE_STRING);
        cell.setCellValue("Ifd NR.");
        cell.setCellStyle(headerStyle);

        cell = row.createCell(4);
        cell.setCellStyle(commonStyle);

        // add count of valid evaluations (how many students voted) (horizontal)
        for (int i = 0; i < numberStudentsVoted; i++) {
            cell = row.createCell(i + 5);
            cell.setCellValue(i + 1);
            sheet.setColumnWidth(cell.getColumnIndex(), 4 * 256);
            cell.setCellStyle(headerStyle);
        }

        // get letter of last student column
        CellReference cellReference = new CellReference(cell.getRowIndex(), cell.getColumnIndex());
        String endCellName = cellReference.getCellRefParts()[2];

        Row headRow = row;

        for (int i = 1; i < mcQuestionTexts.size() + 1; i++) {
            //add number of questions
            row = sheet.createRow(i + yOffset);
            cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue(i);
            cell.setCellStyle(commonStyle);

            //add average formula
            cell = row.createCell(2, Cell.CELL_TYPE_FORMULA);
            //formlua works with blanks, empty strings and negative values
            String formula = "SUMPRODUCT(ABS(N(+F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1)
                    + ")))/COUNT(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")";
            //String averageFormula = "AVERAGE(IF(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + "<>\"\", ABS(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")))";
            cell.setCellFormula(formula);
            cell.setCellStyle(commonStyle);

            //fill blank cells
            cell = row.createCell(3);
            cell.setCellStyle(commonStyle);

            //add question texts
            sheet.setColumnWidth(4, findLongestString(mcQuestionTexts) * 256
                    * (wb.getFontAt(questionStyle.getFontIndex()).getFontHeightInPoints()) / 10);
            cell = row.createCell(4, Cell.CELL_TYPE_STRING);
            cell.setCellValue(mcQuestionTexts.get(i - 1));
            cell.setCellStyle(questionStyle);
        }

        //add student votes
        for (int i = 0; i < studentVotes.size(); i++) {
            Vote vote = studentVotes.get(i);
            for (int k = 0; k < mcQuestionTexts.size(); k++) {
                row = sheet.getRow(headRow.getRowNum() + 1 + k);
                cell = row.createCell(5 + i);
                for (MCAnswer answer : vote.getMcAnswers()) {
                    //if question of inner loop equals question of outer loop we found
                    // the correct question for this cell
                    if (answer.getQuestion().getText().equals(mcQuestionTexts.get(k))) {
                        Choice choice = answer.getChoice();
                        if (choice != null && choice.getGrade() != 0) {
                            cell = colorizeCell(cell, wb, choice.getGrade());
                            cell.setCellValue(answer.getChoice().getGrade());
                        } else {
                            cell = colorizeCell(cell, wb, -1);
                            cell.setCellValue("");
                        }
                    }
                }
            }
        }

        // include textual answers
        createTextualAnswers(studentVotes, textualQuestionTexts, sheet, wb);
        wb.write(out);
        out.close();
    } catch (IOException e) {
        e.printStackTrace();
    }

}