Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

Source Link

Document

Creates new cell range.

Usage

From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.template.LogFrameExcelTemplate.java

License:Open Source License

private int putIndicators(final Set<Indicator> indicators, int rowIndex, boolean mergeCodeCells,
        int lineCount) {
    if (indicators.size() > 0) {
        int startIndex = rowIndex;
        int indiTextLinesSum = 0;
        int indiLineCount = 0;

        for (final Indicator indicator : indicators) {
            if (startIndex != rowIndex) {
                row = sheet.createRow(rowIndex);

            }/*from  w  w w . j a  v a 2  s .  com*/
            indiLineCount = 0;
            if (data.isIndicatorsSheetExist()) {
                utils.createLinkCell(row.createCell(5), indicator.getName(),
                        ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName(), true);
                indiLineCount = utils.calculateLineCount(indicator.getName(), colWidthIndicator);
            } else {
                String indiName = data.getDetailedIndicatorName(indicator.getId());
                utils.putBorderedBasicCell(sheet, rowIndex, 5, indiName);
                indiLineCount = utils.calculateLineCount(indiName, colWidthIndicator);
            }

            indiLineCount = Math.max(indiLineCount,
                    utils.calculateLineCount(indicator.getSourceOfVerification(), colWidthIndicator));
            utils.putBorderedBasicCell(sheet, rowIndex, 6, indicator.getSourceOfVerification());

            indiTextLinesSum += indiLineCount;
            row.setHeightInPoints(indiLineCount * defHeight);
            rowIndex++;
        }
        rowIndex--;

        if (indiTextLinesSum < lineCount) {
            indiLineCount += (lineCount - indiTextLinesSum);
            row.setHeightInPoints(indiLineCount * defHeight);
        }

        if (mergeCodeCells) {
            region = new CellRangeAddress(startIndex, rowIndex, 2, 3);
            sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));
        } else {
            region = new CellRangeAddress(startIndex, rowIndex, 2, 2);
            sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));

            region = new CellRangeAddress(startIndex, rowIndex, 3, 3);
            sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));
        }

        region = new CellRangeAddress(startIndex, rowIndex, 4, 4);
        sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));

        region = new CellRangeAddress(startIndex, rowIndex, 7, 7);
        sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));

    } else {
        utils.putBorderedBasicCell(sheet, rowIndex, 5, "");
        utils.putBorderedBasicCell(sheet, rowIndex, 6, "");
        if (mergeCodeCells) {
            region = new CellRangeAddress(rowIndex, rowIndex, 2, 3);
            sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));
        } else {
            region = new CellRangeAddress(rowIndex, rowIndex, 2, 2);
            sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));

            region = new CellRangeAddress(rowIndex, rowIndex, 3, 3);
            sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));
        }

    }

    return rowIndex;
}

From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.template.LogFrameExcelTemplate.java

License:Open Source License

private void putGroupCell(int rowIndex, String groupType, String code, String groupLabel) {
    cell = sheet.getRow(rowIndex).createCell(2);
    StringBuilder builder = new StringBuilder(groupType);
    builder.append(" (");
    builder.append(code);//w  w w  .j ava 2s. c o  m
    builder.append(") - ");
    builder.append(groupLabel);
    cell.setCellValue(builder.toString());
    CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 2, data.getNumbOfCols());
    sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb));
    cell.setCellStyle(utils.getGroupStyle(wb));
}

From source file:org.sigmah.server.report.renderer.excel.BaseExcelTableRenderer.java

License:Open Source License

protected void generateColumnHeaders(int firstCol, ColumnT root) {

    /* //from ww w. jav  a 2s . c  om
     * Now try building the column headers 
     */

    int depth = root.getDepth();
    colIndexMap = new HashMap<ColumnT, Integer>();

    int startLevel = depth == 0 ? 0 : 1;

    for (int level = startLevel; level <= depth; ++level) {

        Row row = sheet.createRow(rowIndex);

        int colIndex = firstCol;

        List<ColumnT> cols = root.getDescendantsAtDepth(level, true);

        for (ColumnT col : cols) {

            if (col == null) {
                colIndex++;
            } else {
                Cell cell = row.createCell(colIndex);
                cell.setCellValue(factory.createRichTextString(col.getLabel()));
                cell.setCellStyle(col.isLeaf() ? leafColHeaderStyle : colHeaderStyle);

                int span = col.getLeaves().size();

                if (span > 1) {
                    sheet.addMergedRegion(
                            new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + span - 1));
                }
                if (col.isLeaf()) {
                    colIndexMap.put(col, colIndex);
                }

                colIndex += span;
            }
        }

        rowIndex++;
    }

}

From source file:org.tiefaces.components.websheet.utility.CellUtility.java

License:MIT License

/**
 * Copy rows.//w  w w  .jav  a2s.  c  o m
 *
 * @param srcSheet
 *            the src sheet
 * @param destSheet
 *            the dest sheet
 * @param srcRowStart
 *            the src row start
 * @param srcRowEnd
 *            the src row end
 * @param destRow
 *            the dest row
 * @param checkLock
 *            the check lock
 * @param setHiddenColumn
 *            the set hidden column
 */
public static void copyRows(final Sheet srcSheet, final Sheet destSheet, final int srcRowStart,
        final int srcRowEnd, final int destRow, final boolean checkLock, final boolean setHiddenColumn) {

    int length = srcRowEnd - srcRowStart + 1;
    if (length <= 0) {
        return;
    }
    destSheet.shiftRows(destRow, destSheet.getLastRowNum(), length, true, false);
    for (int i = 0; i < length; i++) {
        copySingleRow(srcSheet, destSheet, srcRowStart + i, destRow + i, checkLock, setHiddenColumn);
    }
    // If there are are any merged regions in the source row, copy to new
    // row
    for (int i = 0; i < srcSheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = srcSheet.getMergedRegion(i);
        if ((cellRangeAddress.getFirstRow() >= srcRowStart) && (cellRangeAddress.getLastRow() <= srcRowEnd)) {
            int targetRowFrom = cellRangeAddress.getFirstRow() - srcRowStart + destRow;
            int targetRowTo = cellRangeAddress.getLastRow() - srcRowStart + destRow;

            CellRangeAddress newCellRangeAddress = new CellRangeAddress(targetRowFrom, targetRowTo,
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            destSheet.addMergedRegion(newCellRangeAddress);
        }
    }
}

From source file:org.waterforpeople.mapping.dataexport.GraphicalSurveySummaryExporter.java

License:Open Source License

/**
 * Writes the report as an XLS document/*from w w  w .  j  a  va 2  s  . co  m*/
 */
private void writeSummaryReport(Map<QuestionGroupDto, List<QuestionDto>> questionMap, SummaryModel summaryModel,
        String sector, Workbook wb) throws Exception {
    String title = sector == null ? SUMMARY_LABEL.get(locale) : sector;
    Sheet sheet = null;
    int sheetCount = 2;
    String curTitle = WorkbookUtil.createSafeSheetName(title);
    while (sheet == null) {
        sheet = wb.getSheet(curTitle);
        if (sheet == null) {
            sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(curTitle));
        } else {
            sheet = null;
            curTitle = title + " " + sheetCount;
            sheetCount++;
        }
    }
    CreationHelper creationHelper = wb.getCreationHelper();
    Drawing patriarch = sheet.createDrawingPatriarch();
    int curRow = 0;
    Row row = getRow(curRow++, sheet);
    if (sector == null) {
        createCell(row, 0, REPORT_HEADER.get(locale), headerStyle);
    } else {
        createCell(row, 0, sector + " " + REPORT_HEADER.get(locale), headerStyle);
    }
    for (QuestionGroupDto group : orderedGroupList) {
        if (questionMap.get(group) != null) {
            for (QuestionDto question : questionMap.get(group)) {
                if (!(QuestionType.OPTION == question.getType() || QuestionType.NUMBER == question.getType())) {
                    continue;
                } else {
                    if (summaryModel.getResponseCountsForQuestion(question.getKeyId(), sector).size() == 0) {
                        // if there is no data, skip the question
                        continue;
                    }
                }
                // for both options and numeric, we want a pie chart and
                // data table for numeric, we also want descriptive
                // statistics
                int tableTopRow = curRow++;
                int tableBottomRow = curRow;
                row = getRow(tableTopRow, sheet);
                // span the question heading over the data table
                sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 2));
                createCell(row, 0, getLocalizedText(question.getText(), question.getTranslationMap()),
                        headerStyle);
                DescriptiveStats stats = summaryModel.getDescriptiveStatsForQuestion(question.getKeyId(),
                        sector);
                if (stats != null && stats.getSampleCount() > 0) {
                    sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 4, 5));
                    createCell(row, 4, getLocalizedText(question.getText(), question.getTranslationMap()),
                            headerStyle);
                }
                row = getRow(curRow++, sheet);
                createCell(row, 1, FREQ_LABEL.get(locale), headerStyle);
                createCell(row, 2, PCT_LABEL.get(locale), headerStyle);

                // now create the data table for the option count
                Map<String, Long> counts = summaryModel.getResponseCountsForQuestion(question.getKeyId(),
                        sector);
                int sampleTotal = 0;
                List<String> labels = new ArrayList<String>();
                List<String> values = new ArrayList<String>();
                int firstOptRow = curRow;
                for (Entry<String, Long> count : counts.entrySet()) {
                    row = getRow(curRow++, sheet);
                    String labelText = count.getKey();
                    if (labelText == null) {
                        labelText = "";
                    }
                    StringBuilder builder = new StringBuilder();
                    if (QuestionType.OPTION == question.getType() && !DEFAULT_LOCALE.equals(locale)) {
                        String[] tokens = labelText.split("\\|");
                        // see if we have a translation for this option
                        for (int i = 0; i < tokens.length; i++) {
                            if (i > 0) {
                                builder.append("|");
                            }
                            if (question.getOptionContainerDto() != null
                                    && question.getOptionContainerDto().getOptionsList() != null) {
                                boolean found = false;
                                for (QuestionOptionDto opt : question.getOptionContainerDto()
                                        .getOptionsList()) {
                                    if (opt.getText() != null
                                            && opt.getText().trim().equalsIgnoreCase(tokens[i])) {
                                        builder.append(getLocalizedText(tokens[i], opt.getTranslationMap()));
                                        found = true;
                                        break;
                                    }
                                }
                                if (!found) {
                                    builder.append(tokens[i]);
                                }
                            }
                        }
                    } else {
                        builder.append(labelText);
                    }
                    createCell(row, 0, builder.toString(), null);
                    createCell(row, 1, count.getValue().toString(), null);

                    labels.add(builder.toString());
                    values.add(count.getValue().toString());
                    sampleTotal += count.getValue();
                }
                row = getRow(curRow++, sheet);
                createCell(row, 0, TOTAL_LABEL.get(locale), null);
                createCell(row, 1, sampleTotal + "", null);
                for (int i = 0; i < values.size(); i++) {
                    row = getRow(firstOptRow + i, sheet);
                    if (sampleTotal > 0) {
                        createCell(row, 2, PCT_FMT.format((Double.parseDouble(values.get(i)) / sampleTotal)),
                                null);
                    } else {
                        createCell(row, 2, PCT_FMT.format(0), null);
                    }
                }

                tableBottomRow = curRow;

                if (stats != null && stats.getSampleCount() > 0) {
                    int tempRow = tableTopRow + 1;
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, "N", null);
                    createCell(row, 5, sampleTotal + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, MEAN_LABEL.get(locale), null);
                    createCell(row, 5, stats.getMean() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, STD_E_LABEL.get(locale), null);
                    createCell(row, 5, stats.getStandardError() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, MEDIAN_LABEL.get(locale), null);
                    createCell(row, 5, stats.getMedian() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, MODE_LABEL.get(locale), null);
                    createCell(row, 5, stats.getMode() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, STD_D_LABEL.get(locale), null);
                    createCell(row, 5, stats.getStandardDeviation() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, VAR_LABEL.get(locale), null);
                    createCell(row, 5, stats.getVariance() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, RANGE_LABEL.get(locale), null);
                    createCell(row, 5, stats.getRange() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, MIN_LABEL.get(locale), null);
                    createCell(row, 5, stats.getMin() + "", null);
                    row = getRow(tempRow++, sheet);
                    createCell(row, 4, MAX_LABEL.get(locale), null);
                    createCell(row, 5, stats.getMax() + "", null);
                    if (tableBottomRow < tempRow) {
                        tableBottomRow = tempRow;
                    }
                }
                curRow = tableBottomRow;
                if (labels.size() > 0) {
                    boolean hasVals = false;
                    if (values != null) {
                        for (String val : values) {
                            try {
                                if (val != null && new Double(val.trim()) > 0D) {
                                    hasVals = true;
                                    break;
                                }
                            } catch (Exception e) {
                                // no-op
                            }
                        }
                    }
                    // only insert the image if we have at least 1 non-zero
                    // value
                    if (hasVals && generateCharts) {
                        // now insert the graph
                        int indx = wb.addPicture(JFreechartChartUtil.getPieChart(labels, values,
                                getLocalizedText(question.getText(), question.getTranslationMap()), CHART_WIDTH,
                                CHART_HEIGHT), Workbook.PICTURE_TYPE_PNG);
                        ClientAnchor anchor = creationHelper.createClientAnchor();
                        anchor.setDx1(0);
                        anchor.setDy1(0);
                        anchor.setDx2(0);
                        anchor.setDy2(255);
                        anchor.setCol1(6);
                        anchor.setRow1(tableTopRow);
                        anchor.setCol2(6 + CHART_CELL_WIDTH);
                        anchor.setRow2(tableTopRow + CHART_CELL_HEIGHT);
                        anchor.setAnchorType(2);
                        patriarch.createPicture(anchor, indx);
                        if (tableTopRow + CHART_CELL_HEIGHT > tableBottomRow) {
                            curRow = tableTopRow + CHART_CELL_HEIGHT;
                        }
                    }
                }

                // add a blank row between questions
                getRow(curRow++, sheet);
                // flush the sheet so far to disk; we will not go back up
                ((SXSSFSheet) sheet).flushRows(0); // retain 0 last rows and
                // flush all others

            }
        }
    }
}

From source file:org.waterforpeople.mapping.dataexport.SurveyFormExporter.java

License:Open Source License

/**
 * Writes the survey as an XLS document//ww w  .ja  v  a 2s.c om
 */
private void writeSurvey(String title, File fileName, List<QuestionGroupDto> groupList,
        Map<QuestionGroupDto, List<QuestionDto>> questions) throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();

    sheet.setColumnWidth(0, COL_WIDTH);
    sheet.setColumnWidth(1, COL_WIDTH);
    HSSFCellStyle headerStyle = wb.createCellStyle();
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont headerFont = wb.createFont();
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);

    HSSFCellStyle questionStyle = wb.createCellStyle();
    questionStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    questionStyle.setWrapText(true);

    HSSFCellStyle depStyle = wb.createCellStyle();
    depStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont depFont = wb.createFont();
    depFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    depFont.setItalic(true);
    depStyle.setFont(depFont);

    int curRow = 0;
    HSSFRow row = sheet.createRow(curRow++);
    sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1));
    createCell(row, 0, title, headerStyle);
    row = sheet.createRow(curRow++);
    createCell(row, 0, QUESTION_HEADER, headerStyle);
    createCell(row, 1, RESPONSE_HEADER, headerStyle);

    Long count = 1L;
    if (questions != null) {
        for (int i = 0; i < groupList.size(); i++) {
            HSSFRow groupHeaderRow = sheet.createRow(curRow++);
            sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1));
            createCell(groupHeaderRow, 0, groupList.get(i).getDisplayName(), headerStyle);

            for (QuestionDto q : questions.get(groupList.get(i))) {
                int questionStartRow = curRow;
                HSSFRow tempRow = sheet.createRow(curRow++);
                if (q.getQuestionDependency() != null) {
                    // if there is a dependency, add a row about not
                    // answering unless the dependency is satisfied
                    sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1));
                    Long qNum = idToNumberMap.get(q.getQuestionDependency().getQuestionId());
                    createCell(tempRow, 0,
                            DEP_HEAD + q.getQuestionDependency().getAnswerValue() + DEP_HEAD_TO + "Q" + qNum,
                            depStyle);
                    tempRow = sheet.createRow(curRow++);
                    questionStartRow = curRow;
                }
                createCell(tempRow, 0, (count++) + ". " + formText(q.getText(), q.getTranslationMap()),
                        questionStyle);
                if (q.getOptionContainerDto() != null && q.getOptionContainerDto().getOptionsList() != null) {
                    for (QuestionOptionDto opt : q.getOptionContainerDto().getOptionsList()) {
                        tempRow = sheet.createRow(curRow++);
                        createCell(tempRow, 1, formText(opt.getText(), opt.getTranslationMap()) + SMALL_BLANK,
                                null);
                    }
                    sheet.addMergedRegion(new CellRangeAddress(questionStartRow, curRow - 1, 0, 0));
                } else {
                    createCell(tempRow, 1, BLANK, null);
                }
            }
        }
    }

    FileOutputStream fileOut = new FileOutputStream(fileName);
    wb.write(fileOut);
    fileOut.close();
}

From source file:org.wicketstuff.poi.excel.TableParser.java

License:Apache License

private void doParse(CharSequence gridComponentMarkup, Component tableComponent)
        throws IOException, ResourceStreamNotFoundException, ParseException {
    XmlPullParser parser = new XmlPullParser();
    parser.parse(gridComponentMarkup);/*from  ww  w .  ja va2 s .c  o m*/
    XmlTag tag;
    int tableDeep = 0;
    while ((tag = parser.nextTag()) != null) {
        if ("table".equals(tag.getName().toLowerCase())) {
            if (tag.isOpen()) {
                tableDeep++;
            } else {
                tableDeep--;
            }
        }
        if (tableDeep > 1) {
            // we don't want to read inner tables
            continue;
        }
        if (tag.isOpen()) {
            String tagName = tag.getName().toLowerCase();

            if ("tr".equals(tagName)) {
                if (tableDeep == 0) {
                    // means that root table is outside the component markup
                    tableDeep = 1;
                }
                int index = row == null ? 0 : row.getRowNum() + 1;
                row = targetSheet.createRow(index);
                cell = null;
            } else if ("td".equals(tagName) || "th".equals(tagName)) {
                int index = cell == null ? 0 : cell.getColumnIndex() + 1 + colsToSpan;
                if (skipColumn(index)) {
                    index += columnSpan.get(index);
                }
                colsToSpan = 0;
                CharSequence rowspan = tag.getAttribute("rowspan");
                CharSequence colspan = tag.getAttribute("colspan");
                cell = row.createCell(index);
                if (rowspan != null || colspan != null) {
                    int rowsToSpan = rowspan == null ? 0 : Integer.valueOf(rowspan.toString()) - 1;
                    colsToSpan = colspan == null ? 0 : Integer.valueOf(colspan.toString()) - 1;

                    if (rowsToSpan > 0) {
                        rowsToSpanByColumn.put(index, rowsToSpan);
                        columnSpan.put(index, colsToSpan + 1);
                    }

                    int lastRowNum = row.getRowNum() + rowsToSpan;
                    int lastColIndex = index + colsToSpan;
                    targetSheet.addMergedRegion(new CellRangeAddress(//
                            row.getRowNum(), // first row (0-based)
                            lastRowNum, // last row (0-based)
                            index, // first column (0-based)
                            lastColIndex // last column (0-based)
                    ));
                }
                cellExporter.exportCell(tag, parser, cell, tableComponent);
            }
        }
    }
}

From source file:output.ExcelM3Upgrad.java

private void writeMigration() {
    Sheet sheet = workbook.getSheetAt(0);
    workbook.setSheetName(0, "Migration");
    sheet.setDisplayGridlines(false);/*  w  ww . j  a  va 2s. co m*/
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    styles = createStyles(workbook);

    int rownum = beginROW;
    int cellnum = beginCOL;
    Row row = sheet.createRow(rownum++);
    for (int k = 0; k < model.getListColumn().length; k++) {
        Cell cell = row.createCell(cellnum++);
        cell.setCellValue(i18n.Language.getLabel(model.getListColumn()[k].getIdLng()));
        cell.setCellStyle(styles.get("header"));
        sheet.setColumnHidden(cell.getColumnIndex(), model.getListColumn()[k].isHidden());
        sheet.autoSizeColumn(k);
        dialStatus();
    }
    ArrayList<Integer> listHeader = new ArrayList<>();
    for (int i = 0; i < M3UpdObjModel.header.length; i++) {
        listHeader.add(M3UpdObjModel.header[i]);
    }

    String[] listLevel = i18n.Language.traduce(Ressource.listLevel)
            .toArray(new String[Ressource.listLevel.length]);

    data = model.getData();
    for (int i = 0; i < data.length; i++) {
        busyDial.setText("Alimentation de la ligne " + (i + 1) + " sur " + data.length);
        row = sheet.createRow(rownum++);
        Object[] objArr = data[i];
        cellnum = beginCOL;
        boolean first = true;
        int j = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof Date) {
                cell.setCellValue((Date) obj);
            } else if (obj instanceof Boolean) {
                if (first) {
                    first = false;
                    if ((Boolean) obj) {
                        cell.setCellValue("Oui");
                    } else {
                        cell.setCellValue("Non");
                    }
                } else {
                    if ((Boolean) obj) {
                        cell.setCellValue("OK");
                    } else {
                        cell.setCellValue("KO");
                    }
                }
            } else if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Double) {
                cell.setCellValue((Double) obj);
            }
            if (listHeader.indexOf(218) == j) {
                try {
                    int n = Integer.parseInt(obj.toString().trim());
                    if (n == -1) {
                        cell.setCellValue("ERROR");
                    } else {
                        cell.setCellValue(listLevel[n]);
                    }
                } catch (NumberFormatException ex) {
                    cell.setCellValue("");
                }

            }

            if (j < objArr.length - 3) {
                cell.setCellStyle(styles.get("cell_b_centered_locked"));
            } else {
                cell.setCellStyle(styles.get("cell_b_centered"));
            }
            j++;
            dialStatus();
        }
        dialStatus();
    }

    dialStatus();
    busyDial.setText("Formatage du document");
    CellRangeAddressList userList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 1, beginCOL + data[0].length - 1);
    DataValidationConstraint userConstraint;
    DataValidation userValidation;

    if (type == 0) {
        userConstraint = DVConstraint.createExplicitListConstraint((String[]) model.getM3UserModel()
                .getListUserSelect().toArray(new String[model.getM3UserModel().getListUserSelect().size()]));
        userValidation = new HSSFDataValidation(userList, userConstraint);
    } else {
        XSSFDataValidationHelper userHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        userConstraint = (XSSFDataValidationConstraint) userHelper
                .createExplicitListConstraint((String[]) model.getM3UserModel().getListUserSelect()
                        .toArray(new String[model.getM3UserModel().getListUserSelect().size()]));
        userValidation = (XSSFDataValidation) userHelper.createValidation(userConstraint, userList);
    }
    sheet.addValidationData(userValidation);

    CellRangeAddressList migList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 2, beginCOL + data[0].length - 2);
    DataValidationConstraint migConstraint;
    DataValidation migValidation;

    if (type == 0) {
        migConstraint = DVConstraint.createExplicitListConstraint(new String[] { "OK", "KO" });
        migValidation = new HSSFDataValidation(migList, migConstraint);
    } else {
        XSSFDataValidationHelper migHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        migConstraint = (XSSFDataValidationConstraint) migHelper
                .createExplicitListConstraint(new String[] { "OK", "KO" });
        migValidation = (XSSFDataValidation) migHelper.createValidation(migConstraint, migList);
    }
    sheet.addValidationData(migValidation);

    CellRangeAddressList levelList = new CellRangeAddressList(beginROW + 1, beginROW + data.length,
            beginCOL + data[0].length - 3, beginCOL + data[0].length - 3);
    DataValidationConstraint levelConstraint;
    DataValidation levelValidation;

    ArrayList<String> listNameLevel = new ArrayList<>();
    listNameLevel.add("ERROR");
    listNameLevel.addAll(i18n.Language.traduce(Ressource.listLevel));//.toArray(new String[Ressource.listLevel.length])
    if (type == 0) {
        levelConstraint = DVConstraint
                .createExplicitListConstraint(listNameLevel.toArray(new String[listNameLevel.size()]));
        levelValidation = new HSSFDataValidation(levelList, levelConstraint);
    } else {
        XSSFDataValidationHelper levelHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        levelConstraint = (XSSFDataValidationConstraint) levelHelper.createExplicitListConstraint(
                i18n.Language.traduce(Ressource.listLevel).toArray(new String[Ressource.listLevel.length]));
        levelValidation = (XSSFDataValidation) levelHelper.createValidation(levelConstraint, levelList);
    }
    sheet.addValidationData(levelValidation);

    int irow = beginROW;
    int icol = beginCOL + model.getListColumn().length + 2;
    row = sheet.getRow(irow);
    Cell cell = row.createCell(icol);
    sheet.addMergedRegion(new CellRangeAddress(irow, irow, icol, icol + 1));
    cell.setCellValue("Estimation de la charge");
    cell.setCellStyle(styles.get("header"));

    irow++;
    row = sheet.getRow(irow);

    int cpt = 0;
    ArrayList<String> listStringLevel = i18n.Language.traduce(Ressource.listLevel);
    for (String s : listStringLevel) {
        cell = row.createCell(icol);
        cell.setCellValue(s);
        cell.setCellStyle(styles.get("cell_b_centered_locked"));
        cell = row.createCell(icol + 1);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(218) + beginCOL);
        cell.setCellFormula(
                "COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter
                        + (beginROW + data.length + 1) + ",\"" + s + "\")*" + Ressource.listWeightLevel[cpt]);
        cell.setCellStyle(styles.get("cell_b_centered_locked"));
        irow++;
        row = sheet.getRow(irow);
        cpt++;
    }
    row = sheet.getRow(irow);
    cell = row.createCell(icol);
    cell.setCellValue("Total des charges");
    cell.setCellStyle(styles.get("cell_b_centered_locked"));
    cell = row.createCell(icol + 1);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(icol + 1));
    cell.setCellFormula("SUM(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
            + columnLetter + (beginROW + Ressource.listLevel.length + 1) + ")");
    cell.setCellStyle(styles.get("cell_b_centered_locked"));

    for (int k = 0; k < model.getListColumn().length + 3; k++) {
        sheet.autoSizeColumn(k);
    }

    sheet.protectSheet("3kles2014");
}

From source file:output.ExcelM3Upgrad.java

private void writeGraph() {
    busyDial.setText("Gnration des graphiques statistiques");
    Sheet s = workbook.getSheetAt(1);/*from ww  w .  j a v a 2s .  c  o m*/
    workbook.setSheetName(1, "Statistiques");

    ArrayList<Integer> listHeader = new ArrayList<>();
    for (int i = 0; i < M3UpdObjModel.header.length; i++) {
        listHeader.add(M3UpdObjModel.header[i]);
    }

    int irow = 4;
    Row row = s.createRow(irow);
    Cell cell = row.createCell(2);
    s.addMergedRegion(new CellRangeAddress(irow, irow, 2, 7));
    cell.setCellValue("Rpartition des spcifiques");
    cell.setCellStyle(styles.get("cell_centered_locked"));

    irow = 8;
    row = s.createRow(irow);
    for (int i = 0; i < com.app.main.Ressource.listTypeM3Entity.length; i++) {
        cell = row.createCell(3);
        cell.setCellValue(com.app.main.Ressource.listTypeM3Entity[i]);
        cell = row.createCell(4);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        String columnLetter = CellReference.convertNumToColString(beginCOL);
        cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
                + columnLetter + (beginROW + data.length + 1) + ",\""
                + com.app.main.Ressource.listTypeM3Entity[i] + "\")");
        irow++;
        row = s.createRow(irow);
        dialStatus();
    }

    irow = 4;
    row = s.getRow(irow);
    cell = row.createCell(10);
    s.addMergedRegion(new CellRangeAddress(irow, irow, 10, 15));
    cell.setCellValue("Existance des sources");
    cell.setCellStyle(styles.get("cell_centered_locked"));

    int posVal = listHeader.indexOf(199);
    posVal += beginCOL;

    irow = 8;
    row = s.getRow(irow);
    cell = row.createCell(12);
    cell.setCellValue("OK");
    cell = row.createCell(13);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    String columnLetter = CellReference.convertNumToColString(posVal);
    cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
            + columnLetter + (beginROW + data.length + 1) + ",\"" + "Oui" + "\")");

    irow++;
    row = s.getRow(irow);
    cell = row.createCell(12);
    cell.setCellValue("NOK");
    cell = row.createCell(13);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    //        columnLetter = CellReference.convertNumToColString(posVal);
    cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
            + columnLetter + (beginROW + data.length + 1) + ",\"" + "Non" + "\")");

    irow = 24;
    row = s.createRow(irow);
    cell = row.createCell(2);
    s.addMergedRegion(new CellRangeAddress(irow, irow, 2, 7));
    cell.setCellValue("Synthse de migration");
    cell.setCellStyle(styles.get("cell_centered_locked"));

    int posMig = listHeader.indexOf(201);
    posMig += beginCOL;
    int posUser = listHeader.indexOf(202);
    posUser += beginCOL;

    irow = 28;
    row = s.createRow(irow);
    cell = row.createCell(3);
    cell.setCellValue("OK+USER");
    cell = row.createCell(4);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    String columnMig = CellReference.convertNumToColString(posMig);
    String columnUser = CellReference.convertNumToColString(posUser);
    cell.setCellFormula("SUMPRODUCT((" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":"
            + columnMig + (beginROW + data.length + 1) + "=\"" + "OK" + "\")*(" + workbook.getSheetName(0) + "!"
            + columnUser + (beginROW + 2) + ":" + columnUser + (beginROW + data.length + 1) + "<>\"" + ""
            + "\"))");

    irow++;
    row = s.createRow(irow);
    cell = row.createCell(3);
    cell.setCellValue("OK");
    cell = row.createCell(4);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell.setCellFormula("SUMPRODUCT((" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":"
            + columnMig + (beginROW + data.length + 1) + "=\"" + "OK" + "\")*(" + workbook.getSheetName(0) + "!"
            + columnUser + (beginROW + 2) + ":" + columnUser + (beginROW + data.length + 1) + "=\"" + ""
            + "\"))");

    irow++;
    row = s.createRow(irow);
    cell = row.createCell(3);
    cell.setCellValue("NOK");
    cell = row.createCell(4);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":"
            + columnMig + (beginROW + data.length + 1) + ",\"KO\")");

    irow++;
    row = s.createRow(irow);
    cell = row.createCell(3);
    cell.setCellValue("Somme");
    cell = row.createCell(4);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    String posSum = CellReference.convertNumToColString(4);
    cell.setCellFormula("SUM(" + posSum + (irow - 2) + ":" + posSum + (irow) + ")");

    posVal = listHeader.indexOf(217);
    posVal += beginCOL;

    irow = 24;
    row = s.getRow(irow);
    cell = row.createCell(10);
    s.addMergedRegion(new CellRangeAddress(irow, irow, 10, 15));
    cell.setCellValue("Analyse des objets instanciables");
    cell.setCellStyle(styles.get("cell_centered_locked"));

    irow = 28;
    row = s.getRow(irow);
    cell = row.createCell(12);
    cell.setCellValue("Class OK");
    cell = row.createCell(13);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    columnLetter = CellReference.convertNumToColString(posVal);
    cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":"
            + columnLetter + (beginROW + data.length + 1) + ",\"" + "" + "\")");

    irow++;
    row = s.getRow(irow);
    cell = row.createCell(12);
    cell.setCellValue("Class NOK");
    cell = row.createCell(13);
    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    columnLetter = CellReference.convertNumToColString(posVal);
    //cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + "<>" + "\"&\"" + "*" + "\")");
    cell.setCellFormula("E32-N29");

    s.protectSheet("3kles2014");
}

From source file:OutputStyles.DiffExcelDefault.java

private void SetHeaderRow(Sheet sheet, TreeSet<String> sampleSet, TreeSet<String> comparisonSet) {
    // Merged upper row
    Row FHeaderRow = sheet.createRow(0);

    FHeaderRow.setHeightInPoints(20f);//from   ww w .j av a 2s . c o m
    Cell locCell = FHeaderRow.createCell(0);
    locCell.setCellValue("Gene and Location Data");
    locCell.setCellStyle(this.headerStyles.get("grey"));
    CellRangeAddress first = new CellRangeAddress(0, 0, 0, 4);

    Cell sampCell = FHeaderRow.createCell(5);
    sampCell.setCellValue("Sample RPKM values");
    sampCell.setCellStyle(this.headerStyles.get("grey"));
    CellRangeAddress second = new CellRangeAddress(0, 0, 5, sampleSet.size() + 4);

    sheet.addMergedRegion(first);
    sheet.addMergedRegion(second);

    //int col = 5 + sampleSet.size();
    Iterator<String> compItr = comparisonSet.descendingIterator();
    for (int i = 5 + sampleSet.size(); compItr.hasNext(); i += 6) {
        Cell temp = FHeaderRow.createCell(i);
        String s = compItr.next();
        temp.setCellValue(s);
        temp.setCellStyle(this.headerStyles.get(s));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, i, i + 5));
        //col += 6;
    }
    //CellRangeAddress third = new CellRangeAddress(0,0, 5 + sampleSet.size(), col - 6);

    // Non-merged second row
    Row SHeaderRow = sheet.createRow(1);

    for (int i = 0; i < this.coordheaders.length; i++) {
        Cell temp = SHeaderRow.createCell(i);
        temp.setCellValue(coordheaders[i]);
        temp.setCellStyle(headerStyles.get("grey"));
    }
    Iterator<String> samps = sampleSet.descendingIterator();
    for (int i = coordheaders.length; i < sampleSet.size() + coordheaders.length; i++) {
        Cell temp = SHeaderRow.createCell(i);
        temp.setCellValue(samps.next());
        temp.setCellStyle(headerStyles.get("grey"));
    }

    int op = 0;
    for (int i = coordheaders.length + sampleSet.size(); i < coordheaders.length + sampleSet.size()
            + (comparisonSet.size() * 6); i++) {
        Cell temp = SHeaderRow.createCell(i);
        temp.setCellValue(this.diffheaders[op]);
        temp.setCellStyle(headerStyles.get("grey"));
        op++;
        if (op >= 6)
            op = 0;
    }

    System.err.println("[DIFF OUT] Created Header Row for output");
}