Example usage for org.apache.poi.ss.usermodel ClientAnchor setDy1

List of usage examples for org.apache.poi.ss.usermodel ClientAnchor setDy1

Introduction

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

Prototype

public void setDy1(int dy1);

Source Link

Document

Sets the y coordinate within the first cell Note - XSSF and HSSF have a slightly different coordinate system, values in XSSF are larger by a factor of org.apache.poi.util.Units#EMU_PER_PIXEL

Usage

From source file:com.cms.utils.ExportExcell.java

private Comment getcellComment(FormatExcell item, Cell cell) {
    ExcellHeaderComment headerCommand = item.getHeaderCommand();
    Drawing sSFPatriarch = sh.createDrawingPatriarch();
    CreationHelper factory = cell.getSheet().getWorkbook().getCreationHelper();
    ClientAnchor anchor = factory.createClientAnchor();
    Comment comment1 = sSFPatriarch.createCellComment(anchor);

    anchor.setCol1(headerCommand.getRow1());
    anchor.setCol2(headerCommand.getRow2());
    anchor.setRow1(headerCommand.getColumn1());
    anchor.setRow2(headerCommand.getColumn2());
    anchor.setDx1(headerCommand.getDx1());
    anchor.setDx2(headerCommand.getDx2());
    anchor.setDy1(headerCommand.getDy1());
    anchor.setDy2(headerCommand.getDy2());

    RichTextString rtf1 = factory.createRichTextString(headerCommand.getValue());
    Font font = wb.createFont();// w w w . j  a  v  a 2s. com
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    font.setColor(IndexedColors.RED.getIndex());
    rtf1.applyFont(font);
    comment1.setString(rtf1);
    comment1.setAuthor("Logistics");
    //        comment1.setColumn(cell.getColumnIndex());
    //        comment1.setRow(cell.getRowIndex());
    return comment1;
}

From source file:com.cms.utils.ExportExcell.java

private Comment getcellComment(Sheet sh, FormatExcell item, Cell cell) {
    ExcellHeaderComment headerCommand = item.getHeaderCommand();
    Drawing sSFPatriarch = sh.createDrawingPatriarch();
    CreationHelper factory = cell.getSheet().getWorkbook().getCreationHelper();
    ClientAnchor anchor = factory.createClientAnchor();
    Comment comment1 = sSFPatriarch.createCellComment(anchor);

    anchor.setCol1(headerCommand.getRow1());
    anchor.setCol2(headerCommand.getRow2());
    anchor.setRow1(headerCommand.getColumn1());
    anchor.setRow2(headerCommand.getColumn2());
    anchor.setDx1(headerCommand.getDx1());
    anchor.setDx2(headerCommand.getDx2());
    anchor.setDy1(headerCommand.getDy1());
    anchor.setDy2(headerCommand.getDy2());

    RichTextString rtf1 = factory.createRichTextString(headerCommand.getValue());
    Font font = wb.createFont();//w w w . ja  va2  s  .  co m
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    font.setColor(IndexedColors.RED.getIndex());
    rtf1.applyFont(font);
    comment1.setString(rtf1);
    comment1.setAuthor("Logistics");
    //        comment1.setColumn(cell.getColumnIndex());
    //        comment1.setRow(cell.getRowIndex());
    return comment1;
}

From source file:com.runwaysdk.dataaccess.io.ExcelExportSheet.java

License:Open Source License

protected void writeHeader(Sheet sheet, Drawing drawing, Row nameRow, Row labelRow, int i, ExcelColumn column,
        CellStyle boldStyle) {/*w ww . j ava 2  s . c om*/
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();

    // Notify the listeners
    for (ExcelExportListener listener : listeners) {
        listener.preHeader(column);
    }

    nameRow.createCell(i).setCellValue(helper.createRichTextString(column.getAttributeName()));

    Cell cell = labelRow.createCell(i);
    cell.setCellValue(helper.createRichTextString(column.getDisplayLabel()));

    if (column.isRequired() && boldStyle != null) {
        cell.setCellStyle(boldStyle);
    }

    if (column.getDescription() != null && column.getDescription().length() > 0) {
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setDx1(0);
        anchor.setDy1(0);
        anchor.setDx2(0);
        anchor.setDy2(0);
        anchor.setCol1(0);
        anchor.setRow1(0);
        anchor.setCol2(0);
        anchor.setRow2(4);

        Comment comment = drawing.createCellComment(anchor);
        comment.setString(helper.createRichTextString(column.getDescription()));

        cell.setCellComment(comment);
    }

    sheet.autoSizeColumn((short) i);
}

From source file:com.upbest.utils.AddDimensionedImage.java

License:Apache License

/**
 * Add an image to a worksheet.// w  ww  .  j  a  v  a2 s.com
 *
 * @param colNumber A primitive int that contains the index number of a
 *                  column on the worksheet; POI column indices are zero
 *                  based. Together with the rowNumber parameter's value,
 *                  this parameter identifies a cell on the worksheet. The
 *                  images top left hand corner will be aligned with the
 *                  top left hand corner of this cell.
 * @param rowNumber A primitive int that contains the index number of a row
 *                  on the worksheet; POI row indices are zero based.
 *                  Together with the rowNumber parameter's value, this
 *                  parameter identifies a cell on the worksheet. The
 *                  images top left hand corner will be aligned with the
 *                  top left hand corner of this cell.
 * @param sheet A reference to the sheet that contains the cell identified
 *              by the two parameters above.
 * @param drawing An instance of the DrawingPatriarch class. This is now
 *                passed into the method where it was, previously, recovered
 *                from the sheet in order to allow multiple pictures be
 *                inserted. If the patriarch was not 'cached in this manner
 *                each time it was created any previously positioned images
 *                would be simply over-written.
 * @param imageFile An instance of the URL class that encapsulates the name
 *                  of and path to the image that is to be 'inserted into'
 *                  the sheet.
 * @param reqImageWidthMM A primitive double that contains the required
 *                        width of the image in millimetres.
 * @param reqImageHeightMM A primitive double that contains the required
 *                         height of the image in millimetres.
 * @param resizeBehaviour A primitive int whose value will determine how
 *                        the code should react if the image is larger than
 *                        the cell referenced by the colNumber and
 *                        rowNumber parameters. Four constants are provided
 *                        to determine what should happen;
 *                          AddDimensionedImage.EXPAND_ROW
 *                          AddDimensionedImage.EXPAND_COLUMN
 *                          AddDimensionedImage.EXPAND_ROW_AND_COLUMN
 *                          AddDimensionedImage.OVERLAY_ROW_AND_COLUMN
 * @throws java.io.FileNotFoundException If the file containing the image
 *                                       cannot be located.
 * @throws java.io.IOException If a problem occurs whilst reading the file
 *                             of image data.
 * @throws java.lang.IllegalArgumentException If an invalid value is passed
 *                                            to the resizeBehaviour
 *                                            parameter or if the extension
 *                                            of the image file indicates that
 *                                            it is of a type that cannot
 *                                            currently be added to the worksheet.
 */
public void addImageToSheet(int colNumber, int rowNumber, Sheet sheet, Drawing drawing, URL imageFile,
        double reqImageWidthMM, double reqImageHeightMM, int resizeBehaviour)
        throws IOException, IllegalArgumentException {
    ClientAnchor anchor = null;
    ClientAnchorDetail rowClientAnchorDetail = null;
    ClientAnchorDetail colClientAnchorDetail = null;
    int imageType = 0;

    // Validate the resizeBehaviour parameter.
    if ((resizeBehaviour != AddDimensionedImage.EXPAND_COLUMN)
            && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW)
            && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW_AND_COLUMN)
            && (resizeBehaviour != AddDimensionedImage.OVERLAY_ROW_AND_COLUMN)) {
        throw new IllegalArgumentException("Invalid value passed to the "
                + "resizeBehaviour parameter of AddDimensionedImage.addImageToSheet()");
    }

    // Call methods to calculate how the image and sheet should be
    // manipulated to accomodate the image; columns and then rows.
    colClientAnchorDetail = this.fitImageToColumns(sheet, colNumber, reqImageWidthMM, resizeBehaviour);
    rowClientAnchorDetail = this.fitImageToRows(sheet, rowNumber, reqImageHeightMM, resizeBehaviour);

    // Having determined if and how to resize the rows, columns and/or the
    // image, create the ClientAnchor object to position the image on
    // the worksheet. Note how the two ClientAnchorDetail records are
    // interrogated to recover the row/column co-ordinates and any insets.
    // The first two parameters are not used currently but could be if the
    // need arose to extend the functionality of this code by adding the
    // ability to specify that a clear 'border' be placed around the image.
    anchor = sheet.getWorkbook().getCreationHelper().createClientAnchor();

    anchor.setDx1(0);
    anchor.setDy1(0);
    anchor.setDx2(colClientAnchorDetail.getInset());
    anchor.setDy2(rowClientAnchorDetail.getInset());
    anchor.setCol1(colClientAnchorDetail.getFromIndex());
    anchor.setRow1(rowClientAnchorDetail.getFromIndex());
    anchor.setCol2(colClientAnchorDetail.getToIndex());
    anchor.setRow2(rowClientAnchorDetail.getToIndex());

    // For now, set the anchor type to do not move or resize the
    // image as the size of the row/column is adjusted. This could easilly
    // become another parameter passed to the method. Please read the note
    // above regarding the behaviour of image resizing.
    anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);

    // Now, add the picture to the workbook. Note that unlike the similar
    // method in the HSSF Examples section, the image type is checked. First,
    // the image files location is identified by interrogating the URL passed
    // to the method, the images type is identified before it is added to the
    // sheet.
    String sURL = imageFile.toString().toLowerCase();
    if (sURL.endsWith(".png")) {
        imageType = Workbook.PICTURE_TYPE_PNG;
    } else if (sURL.endsWith("jpg") || sURL.endsWith(".jpeg")) {
        imageType = Workbook.PICTURE_TYPE_JPEG;
    } else {
        throw new IllegalArgumentException("Invalid Image file : " + sURL);
    }
    int index = sheet.getWorkbook().addPicture(IOUtils.toByteArray(imageFile.openStream()), imageType);
    drawing.createPicture(anchor, index);
}

From source file:demo.poi.AddDimensionedImage.java

License:Apache License

/**
 * Add an image to a worksheet./*from www  .j  a  va 2  s.  c  o m*/
 *
 * @param colNumber
 *            A primitive int that contains the index number of a column on
 *            the worksheet; POI column indices are zero based. Together
 *            with the rowNumber parameter's value, this parameter
 *            identifies a cell on the worksheet. The images top left hand
 *            corner will be aligned with the top left hand corner of this
 *            cell.
 * @param rowNumber
 *            A primitive int that contains the index number of a row on the
 *            worksheet; POI row indices are zero based. Together with the
 *            rowNumber parameter's value, this parameter identifies a cell
 *            on the worksheet. The images top left hand corner will be
 *            aligned with the top left hand corner of this cell.
 * @param sheet
 *            A reference to the sheet that contains the cell identified by
 *            the two parameters above.
 * @param drawing
 *            An instance of the DrawingPatriarch class. This is now passed
 *            into the method where it was, previously, recovered from the
 *            sheet in order to allow multiple pictures be inserted. If the
 *            patriarch was not 'cached in this manner each time it was
 *            created any previously positioned images would be simply
 *            over-written.
 * @param imageFile
 *            An instance of the URL class that encapsulates the name of and
 *            path to the image that is to be 'inserted into' the sheet.
 * @param reqImageWidthMM
 *            A primitive double that contains the required width of the
 *            image in millimetres.
 * @param reqImageHeightMM
 *            A primitive double that contains the required height of the
 *            image in millimetres.
 * @param resizeBehaviour
 *            A primitive int whose value will determine how the code should
 *            react if the image is larger than the cell referenced by the
 *            colNumber and rowNumber parameters. Four constants are
 *            provided to determine what should happen;
 *            AddDimensionedImage.EXPAND_ROW
 *            AddDimensionedImage.EXPAND_COLUMN
 *            AddDimensionedImage.EXPAND_ROW_AND_COLUMN
 *            AddDimensionedImage.OVERLAY_ROW_AND_COLUMN
 * @throws java.io.FileNotFoundException
 *             If the file containing the image cannot be located.
 * @throws java.io.IOException
 *             If a problem occurs whilst reading the file of image data.
 * @throws java.lang.IllegalArgumentException
 *             If an invalid value is passed to the resizeBehaviour
 *             parameter or if the extension of the image file indicates
 *             that it is of a type that cannot currently be added to the
 *             worksheet.
 */
public void addImageToSheet(int colNumber, int rowNumber, Sheet sheet, Drawing drawing, URL imageFile,
        double reqImageWidthMM, double reqImageHeightMM, int resizeBehaviour)
        throws IOException, IllegalArgumentException {
    ClientAnchor anchor = null;
    ClientAnchorDetail rowClientAnchorDetail = null;
    ClientAnchorDetail colClientAnchorDetail = null;
    int imageType = 0;

    // Validate the resizeBehaviour parameter.
    if ((resizeBehaviour != AddDimensionedImage.EXPAND_COLUMN)
            && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW)
            && (resizeBehaviour != AddDimensionedImage.EXPAND_ROW_AND_COLUMN)
            && (resizeBehaviour != AddDimensionedImage.OVERLAY_ROW_AND_COLUMN)) {
        throw new IllegalArgumentException("Invalid value passed to the "
                + "resizeBehaviour parameter of AddDimensionedImage.addImageToSheet()");
    }

    // Call methods to calculate how the image and sheet should be
    // manipulated to accomodate the image; columns and then rows.
    colClientAnchorDetail = this.fitImageToColumns(sheet, colNumber, reqImageWidthMM, resizeBehaviour);
    rowClientAnchorDetail = this.fitImageToRows(sheet, rowNumber, reqImageHeightMM, resizeBehaviour);

    // Having determined if and how to resize the rows, columns and/or the
    // image, create the ClientAnchor object to position the image on
    // the worksheet. Note how the two ClientAnchorDetail records are
    // interrogated to recover the row/column co-ordinates and any insets.
    // The first two parameters are not used currently but could be if the
    // need arose to extend the functionality of this code by adding the
    // ability to specify that a clear 'border' be placed around the image.
    anchor = sheet.getWorkbook().getCreationHelper().createClientAnchor();

    anchor.setDx1(0);
    anchor.setDy1(0);
    anchor.setDx2(colClientAnchorDetail.getInset());
    anchor.setDy2(rowClientAnchorDetail.getInset());
    anchor.setCol1(colClientAnchorDetail.getFromIndex());
    anchor.setRow1(rowClientAnchorDetail.getFromIndex());
    anchor.setCol2(colClientAnchorDetail.getToIndex());
    anchor.setRow2(rowClientAnchorDetail.getToIndex());

    // For now, set the anchor type to do not move or resize the
    // image as the size of the row/column is adjusted. This could easilly
    // become another parameter passed to the method. Please read the note
    // above regarding the behaviour of image resizing.
    anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);

    // Now, add the picture to the workbook. Note that unlike the similar
    // method in the HSSF Examples section, the image type is checked.
    // First,
    // the image files location is identified by interrogating the URL
    // passed
    // to the method, the images type is identified before it is added to
    // the
    // sheet.
    String sURL = imageFile.toString().toLowerCase();
    if (sURL.endsWith(".png")) {
        imageType = Workbook.PICTURE_TYPE_PNG;
    } else if (sURL.endsWith("jpg") || sURL.endsWith(".jpeg")) {
        imageType = Workbook.PICTURE_TYPE_JPEG;
    } else {
        throw new IllegalArgumentException("Invalid Image file : " + sURL);
    }
    int index = sheet.getWorkbook().addPicture(IOUtils.toByteArray(imageFile.openStream()), imageType);
    drawing.createPicture(anchor, index);
}

From source file:org.bbreak.excella.reports.tag.ImageParamParser.java

License:Open Source License

/**
 * ??/*from  w  ww .jav  a2  s  .  com*/
 * 
 * @param sheet ?
 * @param cell 
 * @param filePath ?
 * @param dx1 ??
 * @param dy1 ???
 * @param scale ???
 * @throws ParseException
 */
public void replaceImageValue(Sheet sheet, Cell cell, String filePath, Integer dx1, Integer dy1, Double scale)
        throws ParseException {

    Workbook workbook = sheet.getWorkbook();

    int format = -1;
    if (filePath.toLowerCase().endsWith(JPEG_SUFFIX) || filePath.toLowerCase().endsWith(JPG_SUFFIX)) {
        format = Workbook.PICTURE_TYPE_JPEG;
    } else if (filePath.toLowerCase().endsWith(PNG_SUFFIX)) {
        format = Workbook.PICTURE_TYPE_PNG;
    }
    if (format == -1) {
        throw new ParseException(cell,
                "????????" + filePath);
    }

    byte[] bytes = null;
    InputStream is = null;
    try {
        is = new FileInputStream(filePath);
        bytes = IOUtils.toByteArray(is);
    } catch (Exception e) {
        throw new ParseException(cell, e);
    } finally {
        try {
            is.close();
        } catch (IOException e) {
            throw new ParseException(cell, e);
        }
    }

    int pictureIdx = workbook.addPicture(bytes, format);

    CreationHelper helper = workbook.getCreationHelper();

    @SuppressWarnings("rawtypes")
    Drawing drawing = drawingCash.get(sheet);
    if (drawing == null) {
        drawing = sheet.createDrawingPatriarch();
        drawingCash.put(sheet, drawing);
    }

    ClientAnchor anchor = helper.createClientAnchor();

    anchor.setRow1(cell.getRowIndex());
    anchor.setCol1(cell.getColumnIndex());
    anchor.setRow2(cell.getRowIndex() + 1);
    anchor.setCol2(cell.getColumnIndex() + 1);
    if (dx1 != null) {
        anchor.setDx1(dx1);
    }
    if (dy1 != null) {
        anchor.setDy1(dy1);
    }

    Picture picture = drawing.createPicture(anchor, pictureIdx);
    picture.resize(scale);

}

From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelImageHandler.java

License:Open Source License

protected ClientAnchor computeExcel97ClientAnchor(final SlimSheetLayout currentLayout,
        final TableRectangle rectangle, final StrictBounds cb) {
    final int cell1x = rectangle.getX1();
    final int cell1y = rectangle.getY1();
    final int cell2x = Math.max(cell1x, rectangle.getX2() - 1);
    final int cell2y = Math.max(cell1y, rectangle.getY2() - 1);

    final long cell1width = currentLayout.getCellWidth(cell1x);
    final long cell1height = currentLayout.getRowHeight(cell1y);
    final long cell2width = currentLayout.getCellWidth(cell2x);
    final long cell2height = currentLayout.getRowHeight(cell2y);

    final long cell1xPos = currentLayout.getXPosition(cell1x);
    final long cell1yPos = currentLayout.getYPosition(cell1y);
    final long cell2xPos = currentLayout.getXPosition(cell2x);
    final long cell2yPos = currentLayout.getYPosition(cell2y);

    final int dx1 = (int) (1023 * ((cb.getX() - cell1xPos) / (double) cell1width));
    final int dy1 = (int) (255 * ((cb.getY() - cell1yPos) / (double) cell1height));
    final int dx2 = (int) (1023 * ((cb.getX() + cb.getWidth() - cell2xPos) / (double) cell2width));
    final int dy2 = (int) (255 * ((cb.getY() + cb.getHeight() - cell2yPos) / (double) cell2height));

    final ClientAnchor anchor = printerBase.getWorkbook().getCreationHelper().createClientAnchor();
    anchor.setDx1(dx1);//www  . jav  a2s . c o  m
    anchor.setDy1(dy1);
    anchor.setDx2(dx2);
    anchor.setDy2(dy2);
    anchor.setCol1(cell1x);
    anchor.setRow1(cell1y);
    anchor.setCol2(cell2x);
    anchor.setRow2(cell2y);
    anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
    return anchor;
}

From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelImageHandler.java

License:Open Source License

protected ClientAnchor computeExcel2003ClientAnchor(final SlimSheetLayout currentLayout,
        final TableRectangle rectangle, final StrictBounds cb) {
    final int cell1x = rectangle.getX1();
    final int cell1y = rectangle.getY1();
    final int cell2x = Math.max(cell1x, rectangle.getX2() - 1);
    final int cell2y = Math.max(cell1y, rectangle.getY2() - 1);

    final long cell1xPos = currentLayout.getXPosition(cell1x);
    final long cell1yPos = currentLayout.getYPosition(cell1y);
    final long cell2xPos = currentLayout.getXPosition(cell2x);
    final long cell2yPos = currentLayout.getYPosition(cell2y);

    final int dx1 = (int) StrictGeomUtility.toExternalValue((cb.getX() - cell1xPos) * XSSFShape.EMU_PER_POINT);
    final int dy1 = (int) StrictGeomUtility.toExternalValue((cb.getY() - cell1yPos) * XSSFShape.EMU_PER_POINT);
    final int dx2 = (int) Math.max(0, StrictGeomUtility
            .toExternalValue((cb.getX() + cb.getWidth() - cell2xPos) * XSSFShape.EMU_PER_POINT));
    final int dy2 = (int) Math.max(0, StrictGeomUtility
            .toExternalValue((cb.getY() + cb.getHeight() - cell2yPos) * XSSFShape.EMU_PER_POINT));

    final ClientAnchor anchor = printerBase.getWorkbook().getCreationHelper().createClientAnchor();
    anchor.setDx1(dx1);/*  ww w.j a v  a 2s .co m*/
    anchor.setDy1(dy1);
    anchor.setDx2(dx2);
    anchor.setDy2(dy2);
    anchor.setCol1(cell1x);
    anchor.setRow1(cell1y);
    anchor.setCol2(cell2x);
    anchor.setRow2(cell2y);
    anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
    return anchor;
}

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 v  a 2s  .  c  o 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

            }
        }
    }
}