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.openelis.bean.QcChartReport1Bean.java

License:Open Source License

private void setHeaderCells(HSSFSheet sheet, String qcName, String qcType, String analyteName) {
    int i, startRow;
    Cell cell;//from w  w  w.  ja  v  a  2 s . c o  m
    Row row;

    if (qcColumns != null && qcColumns.size() > 0)
        startRow = 29;
    else
        startRow = 0;

    row = sheet.createRow(startRow);

    cell = row.createCell(0);
    cell.setCellStyle(headerStyle);
    setCellValue(cell, "QC Name");
    setMaxChars(cell, maxChars);

    cell = row.createCell(1);
    cell.setCellStyle(baseStyle);
    setCellValue(cell, qcName);
    setMaxChars(cell, maxChars);

    cell = row.createCell(2);
    cell.setCellStyle(baseStyle);
    sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, 1, 2));

    row = sheet.createRow(startRow + 1);

    cell = row.createCell(0);
    cell.setCellStyle(headerStyle);
    setCellValue(cell, "QC Type: Analyte");
    setMaxChars(cell, maxChars);

    cell = row.createCell(1);
    cell.setCellStyle(baseStyle);
    setCellValue(cell, qcType + ": " + analyteName);
    setMaxChars(cell, maxChars);

    cell = row.createCell(2);
    cell.setCellStyle(baseStyle);
    sheet.addMergedRegion(new CellRangeAddress(startRow + 1, startRow + 1, 1, 2));

    row = sheet.createRow(startRow + 2);

    cell = row.createCell(0);
    cell.setCellStyle(headerStyle);
    setCellValue(cell, "Row Number");
    setMaxChars(cell, maxChars);

    cell = row.createCell(1);
    cell.setCellStyle(headerStyle);
    setCellValue(cell, "Accession # / Worksheet #");
    setMaxChars(cell, maxChars);

    cell = row.createCell(2);
    cell.setCellStyle(headerStyle);
    setCellValue(cell, "Lot #");
    setMaxChars(cell, maxChars);

    cell = row.createCell(3);
    cell.setCellStyle(headerStyle);
    setCellValue(cell, "Created Date");
    setMaxChars(cell, maxChars);

    cell = row.createCell(4);
    cell.setCellStyle(headerStyle);
    setCellValue(cell, "QC Link");
    setMaxChars(cell, maxChars);

    if (qcColumns != null && !qcColumns.isEmpty()) {
        for (i = 0; i < qcColumns.size(); i++) {
            cell = row.createCell(i + 5);
            cell.setCellStyle(headerStyle);
            setCellValue(cell, qcColumns.get(i).getEntry());
            setMaxChars(cell, maxChars);
        }
    } else if (worksheetHeaders != null && !worksheetHeaders.isEmpty()) {
        for (i = 0; i < worksheetHeaders.size(); i++) {
            cell = row.createCell(i + 5);
            cell.setCellStyle(headerStyle);
            setCellValue(cell, worksheetHeaders.get(i));
            setMaxChars(cell, maxChars);
        }
    }
}

From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java

License:Open Source License

/**
 * Adds in a Row to the given Sheet//from  w  ww.j a  v  a 2s  . c o  m
 */
public Row addRow(Workbook wb, SheetToAdd sheetToAdd, RowToAdd rowToAdd, int rowIndex, ReportData reportData,
        ReportDesign design, Map<String, String> repeatSections) {

    // Create a new row and copy over style attributes from the row to add
    Row newRow = sheetToAdd.getSheet().createRow(rowIndex);
    Row rowToClone = rowToAdd.getRowToClone();
    try {
        CellStyle rowStyle = rowToClone.getRowStyle();
        if (rowStyle != null) {
            newRow.setRowStyle(rowStyle);
        }
    } catch (Exception e) {
        // No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the rowStyle.  Mysteries of POI
    }
    newRow.setHeight(rowToClone.getHeight());

    // Iterate across all of the cells in the row, and configure all those that need to be added/cloned
    List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>();

    int totalCells = rowToClone.getPhysicalNumberOfCells();
    int cellsFound = 0;
    for (int cellNum = 0; cellsFound < totalCells; cellNum++) {
        Cell currentCell = rowToClone.getCell(cellNum);
        log.debug("Handling cell: " + currentCell);
        if (currentCell != null) {
            cellsFound++;
        }
        // If we find that the cell that we are on is a repeating cell, then add the appropriate number of cells to clone
        String repeatingColumnProperty = getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum,
                repeatSections);
        if (repeatingColumnProperty != null) {
            String[] dataSetSpanSplit = repeatingColumnProperty.split(",");
            String dataSetName = dataSetSpanSplit[0];
            DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData());
            int numCellsToRepeat = 1;
            if (dataSetSpanSplit.length == 2) {
                numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
            }
            log.debug("Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat);
            int repeatNum = 0;
            for (DataSetRow dataSetRow : dataSet) {
                repeatNum++;
                for (int i = 0; i < numCellsToRepeat; i++) {
                    Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i));
                    if (repeatNum == 1 && cell != null && cell != currentCell) {
                        cellsFound++;
                    }
                    Map<String, Object> newReplacements = getReplacementData(rowToAdd.getReplacementData(),
                            reportData, design, dataSetName, dataSetRow, repeatNum);
                    cellsToAdd.add(new CellToAdd(cell, newReplacements));
                    log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow);
                }
            }
            cellNum += numCellsToRepeat;
        } else {
            cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData()));
            log.debug("Adding " + currentCell);
        }
    }

    // Now, go through all of the collected cells, and add them back in

    String prefix = getExpressionPrefix(design);
    String suffix = getExpressionSuffix(design);

    List<CellRangeAddress> newMergedRegions = new ArrayList<CellRangeAddress>();

    for (int i = 0; i < cellsToAdd.size(); i++) {
        CellToAdd cellToAdd = cellsToAdd.get(i);
        Cell newCell = newRow.createCell(i);
        Cell cellToClone = cellToAdd.getCellToClone();
        if (cellToClone != null) {
            Object contents = ExcelUtil.getCellContents(cellToClone);
            newCell.setCellStyle(cellToClone.getCellStyle());

            int numFormattings = sheetToAdd.getSheet().getSheetConditionalFormatting()
                    .getNumConditionalFormattings();
            for (int n = 0; n < numFormattings; n++) {
                ConditionalFormatting f = sheetToAdd.getSheet().getSheetConditionalFormatting()
                        .getConditionalFormattingAt(n);
                for (CellRangeAddress add : f.getFormattingRanges()) {

                    if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum()
                            && add.getLastRow() == rowToClone.getRowNum()) {
                        if (add.getFirstColumn() == cellToClone.getColumnIndex()
                                && add.getLastColumn() == cellToClone.getColumnIndex()) {
                            ConditionalFormattingRule[] rules = new ConditionalFormattingRule[f
                                    .getNumberOfRules()];
                            for (int j = 0; j < f.getNumberOfRules(); j++) {
                                rules[j] = f.getRule(j);
                            }
                            CellRangeAddress[] cellRange = new CellRangeAddress[1];
                            cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i);
                            sheetToAdd.getSheet().getSheetConditionalFormatting()
                                    .addConditionalFormatting(cellRange, rules);
                        }
                    }
                }
            }

            int numMergedRegions = sheetToAdd.getSheet().getNumMergedRegions();
            for (int n = 0; n < numMergedRegions; n++) {
                CellRangeAddress add = sheetToAdd.getSheet().getMergedRegion(n);
                int rowNum = rowToClone.getRowNum();
                if (add.getFirstRow() == rowNum && add.getLastRow() == rowNum) {
                    if (add.getFirstColumn() == cellToClone.getColumnIndex()) {
                        newMergedRegions
                                .add(new CellRangeAddress(rowNum, rowNum, i, i + add.getNumberOfCells() - 1));
                    }
                }
            }

            if (ObjectUtil.notNull(contents)) {
                if (contents instanceof String) {
                    contents = EvaluationUtil.evaluateExpression(contents.toString(),
                            cellToAdd.getReplacementData(), prefix, suffix);
                }
                ExcelUtil.setCellContents(newCell, contents);
            }

            ExcelUtil.copyFormula(cellToClone, newCell);
        }
    }

    for (CellRangeAddress mergedRegion : newMergedRegions) {
        sheetToAdd.getSheet().addMergedRegion(mergedRegion);
    }

    return newRow;
}

From source file:org.openmrs.module.pihmalawi.reporting.definition.renderer.IC3TraceReportRenderer.java

License:Open Source License

@Override
public void render(ReportData reportData, String argument, OutputStream out)
        throws IOException, RenderingException {

    ExcelBuilder builder = new ExcelBuilder();
    Date reportDate = reportData.getContext().getEvaluationDate();

    Calendar nextMonday = ApzuReportUtil.nextDayOfWeek(reportDate, Calendar.MONDAY);
    Calendar nextSecondWednesday = ApzuReportUtil.nextDayOfWeek(reportDate, Calendar.WEDNESDAY);
    nextSecondWednesday.add(Calendar.DAY_OF_MONTH, 7);

    for (String key : reportData.getDataSets().keySet()) {
        SimpleDataSet ds = (SimpleDataSet) reportData.getDataSets().get(key);
        if (ds.getRows().size() > 0) {

            DataSetMetaData metaData = ds.getMetaData();
            Location location = getParameterValue(ds, ReportingConstants.LOCATION_PARAMETER, Location.class);
            Integer minWk = getParameterValue(ds, MIN_WEEKS_PARAM, Integer.class);
            Integer maxWk = getParameterValue(ds, MAX_WEEKS_PARAM, Integer.class);
            Boolean isPhase1 = ObjectUtil.nvl(getParameterValue(ds, PHASE_1_ONLY_PARAM, Boolean.class), false);

            builder.newSheet(key);//from   w  w w. ja v  a 2s. c om

            builder.hideGridlinesInCurrentSheet();
            builder.setLandscape();
            builder.fitColumnsToPage();

            Map<String, Object> headerCellValues = getHeaderCellValues(minWk, isPhase1);
            int lastColMerge = isPhase1 ? 9 : location != null ? 12 : 13;

            String topRowStyle = "bold,size=18,color=" + HSSFColor.WHITE.index + ",background-color="
                    + HSSFColor.BLACK.index;
            builder.addCell(headerCellValues.get("traceLabel"), topRowStyle).merge(5, 0);
            builder.addCell(headerCellValues.get("reportLabel"), topRowStyle).merge(lastColMerge, 0);
            builder.nextRow();

            if (location != null) {
                builder.addCell(location.getName(), "bold,size=22,color=" + HSSFColor.BLUE.index).merge(5, 0);
                builder.addCell(builder.createRichTextString("Instructions: ", "bold",
                        "For each patient listed here, verify using the mastercards whether they have truly missed an appointment. If they have really missed the appointment, please find the patient and record the outcome.If they have not missed an appointment, add client name and visit details to \"Mastercard Update\" report. ",
                        null, "Upper Neno: ", "bold", "Return all findings to Chisomo (0884784429). ", null,
                        "Lower Neno: ", "bold", "Return all findings to Maxwell (0884784429).", null),
                        "size=12,wraptext,valign=center").merge(lastColMerge, 5);
                builder.nextRow();

                builder.addCell(minWk + "- <" + maxWk + " weeks missed appointment", "bold");
                builder.nextRow();

                builder.addCell(builder.createRichTextString("Patient tracking for week of ", "bold",
                        DateUtil.formatDate(nextMonday.getTime(), "EEEE, dd-MMM-yyyy"),
                        "color=" + HSSFColor.BLUE.index + ",bold"), null);
                builder.nextRow();
                builder.addCell(builder.createRichTextString("Date Report Printed: ", "bold",
                        DateUtil.formatDate(reportDate, "EEEE, dd-MMM-yyyy"),
                        "color=" + HSSFColor.BLUE.index + ",bold"), null);
                builder.nextRow();
                builder.addCell(builder.createRichTextString("Date Report due back to Chisomo/Maxwell:  ",
                        "bold", DateUtil.formatDate(nextSecondWednesday.getTime(), "EEEE, dd-MMM-yyyy"),
                        "color=" + HSSFColor.BLUE.index + ",bold"), null);
            } else {
                builder.nextRow();
                builder.addCell("12 weeks missed appointment", "bold").merge(5, 0);
                builder.addCell(builder.createRichTextString("Instructions: ", "bold",
                        "When complete, return all findings to Chisomo (0884784429) in Upper Neno and Maxwell (0884784429) in Lower Neno",
                        null), "size=12,wraptext,valign=center").merge(lastColMerge, 2);
                builder.nextRow();
                builder.addCell(builder.createRichTextString("Date Report Printed: ", "bold",
                        DateUtil.formatDate(reportDate, "EEEE, dd-MMM-yyyy"),
                        "color=" + HSSFColor.BLUE.index + ",bold"), null).merge(5, 0);
            }
            builder.nextRow();
            builder.nextRow();

            String headerStyle1 = "bold,size=11,wraptext,border=top";
            String headerStyle2 = headerStyle1 + ",rotation=90";
            String headerStyle3 = headerStyle2 + ",size=8";

            builder.addCell("", null, 6);
            if (metaData.getColumn("parameter.location") != null) {
                builder.addCell("Facility", headerStyle1 + ",border=left", 20);
                builder.addCell("ARV#", headerStyle1);
            } else {
                builder.addCell("ARV#", headerStyle1 + ",border=left", 12);
            }
            if (metaData.getColumn("NCD_NUMBER") != null) {
                builder.addCell("NCD#", headerStyle1, 12);
            }
            builder.addCell("First", headerStyle1, 12);
            builder.addCell("Last", headerStyle1, 15);
            builder.addCell("Village", headerStyle1, 30);
            builder.addCell("VHW", headerStyle1, 20);
            if (metaData.getColumn("DIAGNOSES") != null) {
                builder.addCell("Diagnoses", headerStyle1, 20);
            }
            builder.addCell("Last IC3 Visit Date", headerStyle1, 18);
            builder.addCell("Last Visit Appt Date", headerStyle1, 18);
            builder.addCell("Weeks out of Care", headerStyle1, 8);
            if (metaData.getColumn("PRIORITY_PATIENT") != null) {
                builder.addCell("Priority Patient", headerStyle1, 8);
            }
            builder.addCell(builder.createRichTextString("Patient actually\nvisited clinic.", headerStyle2,
                    "\nComplete Mastercard Update", headerStyle3), headerStyle2, 8);
            builder.addCell("Transferred Out", headerStyle2, 4);
            builder.addCell("Died", headerStyle2, 4);
            builder.addCell("Stopped", headerStyle2, 4);
            builder.addCell("Missed Appt", headerStyle2, 4);
            builder.addCell("Patient Not Found", headerStyle2, 4);
            builder.addCell("Remarks", headerStyle1 + ",border=right", 25);

            // Set this row to repeat when printing on subsequent pages
            int rowNum = builder.getCurrentRowNum();
            int colNum = builder.getCurrentColNum();
            builder.getCurrentSheet().setRepeatingRows(new CellRangeAddress(rowNum, rowNum, 0, colNum));

            builder.nextRow();

            String rowStyle = "border=top";

            DataSetRowList rows = ds.getRows();
            for (int i = 0; i < rows.size(); i++) {
                builder.addCell(i + 1, "color=" + HSSFColor.GREY_50_PERCENT.index);
                DataSetRow row = rows.get(i);
                if (i + 1 == rows.size()) {
                    rowStyle += ",border=bottom";
                }
                if (metaData.getColumn("parameter.location") != null) {
                    Location facility = (Location) row.getColumnValue("parameter.location");
                    builder.addCell(facility.getName(), rowStyle + ",border=left");
                    builder.addCell(row.getColumnValue("ARV_NUMBER"), rowStyle);
                } else {
                    builder.addCell(row.getColumnValue("ARV_NUMBER"), rowStyle + ",border=left");
                }

                if (metaData.getColumn("NCD_NUMBER") != null) {
                    builder.addCell(row.getColumnValue("NCD_NUMBER"), rowStyle);
                }
                builder.addCell(row.getColumnValue("FIRST_NAME"), rowStyle);
                builder.addCell(row.getColumnValue("LAST_NAME"), rowStyle);
                builder.addCell(row.getColumnValue("VILLAGE"), rowStyle);
                builder.addCell(row.getColumnValue("VHW"), rowStyle);
                if (metaData.getColumn("DIAGNOSES") != null) {
                    builder.addCell(row.getColumnValue("DIAGNOSES"), rowStyle);
                }
                builder.addCell(row.getColumnValue("LAST_VISIT_DATE"), rowStyle + ",date");
                builder.addCell(row.getColumnValue("NEXT_APPT_DATE"), rowStyle + ",date");
                builder.addCell(row.getColumnValue("WEEKS_OUT_OF_CARE"), rowStyle + ",format=0.0");
                if (metaData.getColumn("PRIORITY_PATIENT") != null) {
                    Set<String> s = (Set<String>) row.getColumnValue("PRIORITY_PATIENT");
                    builder.addCell(s != null && !s.isEmpty() ? "!!!" : "",
                            rowStyle + ",color=" + HSSFColor.RED.index + ",align=center");
                }
                for (int j = 0; j < 6; j++) {
                    builder.addCell("?", rowStyle + ",align=center,size=18");
                }
                builder.addCell("", rowStyle + ",border=right");
                builder.nextRow();
            }
        }
    }

    builder.write(out);
}

From source file:org.openpythia.utilities.SSUtilities.java

License:Apache License

public static Row copyRow(Sheet sheet, Row sourceRow, int destination) {
    Row newRow = sheet.createRow(destination);
    // get the last row from the headings
    int lastCol = sheet.getRow(0).getLastCellNum();
    for (int currentCol = 0; currentCol <= lastCol; currentCol++) {
        Cell newCell = newRow.createCell(currentCol);

        // if there is a cell in the template, copy its content and style
        Cell currentCell = sourceRow.getCell(currentCol);
        if (currentCell != null) {
            newCell.setCellStyle(currentCell.getCellStyle());
            newCell.setCellComment(currentCell.getCellComment());
            switch (currentCell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(currentCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(currentCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                String dummy = currentCell.getCellFormula();
                dummy = dummy.replace("Row", String.valueOf(destination + 1));
                newCell.setCellFormula(dummy);
                newCell.setCellFormula(//  w w w.  j a v  a 2 s  .co  m
                        currentCell.getCellFormula().replace("Row", String.valueOf(destination + 1)));
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(currentCell.getBooleanCellValue());
                break;
            default:
            }
        }
    }

    // if the row contains merged regions, copy them to the new row
    int numberMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numberMergedRegions; i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

        if (mergedRegion.getFirstRow() == sourceRow.getRowNum()
                && mergedRegion.getLastRow() == sourceRow.getRowNum()) {
            // this region is within the row - so copy it
            sheet.addMergedRegion(new CellRangeAddress(destination, destination, mergedRegion.getFirstColumn(),
                    mergedRegion.getLastColumn()));
        }
    }

    return newRow;
}

From source file:org.pentaho.reporting.engine.classic.core.modules.output.fast.xls.FastExcelPrinter.java

License:Open Source License

private void mergeCellRegion(final TableRectangle rectangle, final CellStyle spannedStyle) {
    final int rowSpan = rectangle.getRowSpan();
    final int columnSpan = rectangle.getColumnSpan();
    if (rowSpan <= 1 && columnSpan <= 1) {
        return;//from  w w w . ja v  a  2 s  .  c o m
    }

    int row = rectangle.getY1();
    int col = rectangle.getX1();

    sheet.addMergedRegion(new CellRangeAddress(row, (row + rowSpan - 1), col, (col + columnSpan - 1)));

    for (int spannedRow = 0; spannedRow < rowSpan; spannedRow += 1) {
        for (int spannedCol = 0; spannedCol < columnSpan; spannedCol += 1) {
            final Cell regionCell = getCellAt((col + spannedCol), row + spannedRow);
            if (spannedStyle != null) {
                regionCell.setCellStyle(spannedStyle);
            }
        }
    }
}

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

License:Open Source License

private void mergeCellRegion(final TableRectangle rectangle, final int row, final int col,
        final SheetLayout sheetLayout, final LogicalPageBox logicalPage, final RenderBox content,
        final TableContentProducer contentProducer) {
    if (content == null) {
        throw new NullPointerException();
    }// w w w  .  j  ava  2 s  .co m

    final int rowSpan = rectangle.getRowSpan();
    final int columnSpan = rectangle.getColumnSpan();
    if (rowSpan <= 1 && columnSpan <= 1) {
        return;
    }

    sheet.addMergedRegion(new CellRangeAddress(row, (row + rowSpan - 1), col, (col + columnSpan - 1)));
    final int rectX = rectangle.getX1();
    final int rectY = rectangle.getY1();

    for (int spannedRow = 0; spannedRow < rowSpan; spannedRow += 1) {
        for (int spannedCol = 0; spannedCol < columnSpan; spannedCol += 1) {
            final CellMarker.SectionType sectionType = contentProducer.getSectionType(row, col);
            final CellBackground bg = cellBackgroundProducer.getBackgroundForBox(logicalPage, sheetLayout,
                    rectX + spannedCol, rectY + spannedRow, 1, 1, false, sectionType, content);
            final Cell regionCell = getCellAt((col + spannedCol), row + spannedRow);
            final CellStyle spannedStyle = getCellStyleProducer().createCellStyle(content.getInstanceId(),
                    content.getStyleSheet(), bg);
            if (spannedStyle != null) {
                regionCell.setCellStyle(spannedStyle);
            }
        }
    }
}

From source file:org.phenotips.export.internal.SpreadsheetExporter.java

License:Open Source License

protected void write(DataSection section, Sheet sheet) {
    DataCell[][] cells = section.getMatrix();
    Styler styler = new Styler();

    Row row;/* ww w.  j  av  a2  s.co m*/
    for (Integer y = 0; y <= section.getMaxY(); y++) {
        row = sheet.createRow(y);
        Integer maxLines = 0;

        for (Integer x = 0; x <= section.getMaxX(); x++) {
            DataCell dataCell = cells[x][y];
            if (dataCell == null) {
                continue;
            }
            Cell cell = row.createCell(x);
            cell.setCellValue(dataCell.getValue());
            styler.style(dataCell, cell, this.wBook);

            if (dataCell.getNumberOfLines() != null) {
                maxLines = maxLines < dataCell.getNumberOfLines() ? dataCell.getNumberOfLines() : maxLines;
            }
        }
        if (maxLines > 1) {
            Integer height = maxLines * 400;
            row.setHeight(height.shortValue());
        }
    }
    for (int col = 0; section.getMaxX() >= col; col++) {
        sheet.autoSizeColumn(col);
        if (sheet.getColumnWidth(col) > (DataToCellConverter.charactersPerLine * 210)) {
            sheet.setColumnWidth(col, DataToCellConverter.charactersPerLine * 210);
        }
    }

    /** Merging has to be done after autosizing because otherwise autosizing breaks */
    for (Integer y = 0; y <= section.getMaxY(); y++) {
        for (Integer x = 0; x <= section.getMaxX(); x++) {
            DataCell dataCell = cells[x][y];
            if (dataCell != null && dataCell.getMergeX() != null) {
                sheet.addMergedRegion(new CellRangeAddress(y, y, x, x + dataCell.getMergeX()));
            }
            /*
             * No longer will be merging cells on the Y axis, but keep this code for future reference. if
             * (dataCell.getYBoundry() != null) { sheet.addMergedRegion(new CellRangeAddress(dataCell.y,
             * dataCell.getYBoundry(), dataCell.x, dataCell.x)); }
             */
        }
    }
}

From source file:org.pivot4j.ui.poi.ExcelExporter.java

License:Common Public License

/**
 * @param context//from  w w  w .  j  av  a 2s.co  m
 * @return
 */
protected CellRangeAddress createMergedRegion(TableRenderContext context) {
    if (context.getColumnSpan() > 1 || context.getRowSpan() > 1) {
        int firstRow = context.getRowIndex() + getRowOffset();
        int lastRow = firstRow + context.getRowSpan() - 1;
        int firstCol = context.getColumnIndex() + getColOffset();
        int lastCol = firstCol + context.getColumnSpan() - 1;

        return new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
    } else {
        return null;
    }
}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, DataTable table, int columnCount,
        String facetType) {/*ww w.j ava  2  s  . c  o  m*/
    Map<String, UIComponent> map = table.getFacets();
    UIComponent component = map.get(facetType);
    if (component != null) {
        String headerValue = null;
        if (component instanceof HtmlCommandButton) {
            headerValue = exportValue(context, component);
        } else if (component instanceof HtmlCommandLink) {
            headerValue = exportValue(context, component);
        } else if (component instanceof UIPanel) {
            String header = "";
            for (UIComponent child : component.getChildren()) {
                headerValue = exportValue(context, child);
                header = header + headerValue;
            }
            headerValue = header;
        } else {
            headerValue = exportFacetValue(context, component);
        }

        int sheetRowIndex = sheet.getLastRowNum() + 1;
        Row row = sheet.createRow(sheetRowIndex);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue(headerValue);
        cell.setCellStyle(facetStyle);

        sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                sheetRowIndex, //last row  (0-based)
                0, //first column (0-based)
                columnCount //last column  (0-based)
        ));

    }
}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java

License:Apache License

protected void tableColumnGroup(Sheet sheet, SubTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }/*from  w w  w . j  av a  2  s.com*/
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getPhysicalNumberOfRows() > 0 ? sheet.getLastRowNum() + 1 : 0;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {

                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue(value);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                        sheetRowIndex + rowSpan - 1, //last row  (0-based)
                                        i, //first column (0-based)
                                        i //last column  (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(value);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                    sheetRowIndex, //last row  (0-based)
                                    i, //first column (0-based)
                                    i + colSpan - 1 //last column  (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);

                    }
                    i++;
                }
            }

        }
    }

}