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:net.ceos.project.poi.annotated.core.Engine.java

License:Apache License

/**
 * Apply merge region if necessary./*from   w  ww .ja  v a2s. c o  m*/
 * 
 * @param configCriteria
 *            the {@link XConfigCriteria}
 * @param r
 *            the {@link Row}
 * @param idxR
 *            the position of the row
 * @param idxC
 *            the position of the cell
 * @param isPH
 *            true if propagation horizontally, false if propagation
 *            vertically
 * @throws ConfigurationException
 *             given when problem at the applying the merge region.
 */
private void applyMergeRegion(final XConfigCriteria configCriteria, Row r, final int idxR, final int idxC,
        final boolean isPH) throws ConfigurationException {
    /* Process @XlsNestedHeader */
    if (PredicateFactory.isFieldAnnotationXlsNestedHeaderPresent.test(configCriteria.getField())) {
        XlsNestedHeader annotation = (XlsNestedHeader) configCriteria.getField()
                .getAnnotation(XlsNestedHeader.class);

        /* validation of configuration */
        isValidNestedHeaderConfiguration(isPH, annotation);

        /* if row null is necessary to create it */
        Row row = r;
        if (row == null) {
            /* check if the row already exist */
            row = configCriteria.getSheet().getRow(idxR);
            if (row == null) {
                /* create a new row */
                row = initializeRow(configCriteria.getSheet(), idxR);
            }
        }

        /* prepare position rows / cells */
        int startRow;
        int endRow;
        int startCell;
        int endCell;
        if (isPH) {
            startRow = endRow = idxR;
            startCell = idxC + annotation.startX();
            endCell = idxC + annotation.endX();
        } else {
            startRow = idxR + annotation.startY();
            endRow = idxR + annotation.endY();
            startCell = endCell = idxC;
        }

        /* initialize nested header cell */
        CellStyleHandler.initializeHeaderCell(configCriteria.getStylesMap(), row, startCell,
                annotation.title());

        /* merge region of the nested header cell */
        CellRangeAddress range = new CellRangeAddress(startRow, endRow, startCell, endCell);
        configCriteria.getSheet().addMergedRegion(range);

        /* apply the border to the nested header cell */
        applyBorderToRegion(configCriteria, range);
    }
}

From source file:net.illustrato.ctrl.CtrlCore.java

/**
 *
 * Este metodo toma la informacion que se diligencia en la clase y genera un
 * archivo en excel a partir de una plantilla.
 *
 * @param pathPlan la ruta donde esta la plantilla en excel HU3
 * @param pathDeex la ruta donde se va a depositar el excel generado.
 * @param nombResa Nombre reporte de salida.
 * @throws Exception indica que el metodo genera excepciones que deben ser
 * capturadas para identificar cuando la misma no funcione bien.
 *
 */// ww w . j a  v  a2  s  . co m
@Override
public void escribirExcel(String pathPlan, String pathDeex, String nombResa) throws Exception {
    LOGGER.info("Iniciando escritura del archivo en excel");

    LOGGER.debug("Ruta de la plantilla {}", pathPlan);
    LOGGER.debug("Ruta donde se va a escribir la plantilla {} ", pathDeex);

    //Archivo Origen
    File archOrig = null;
    //Archivo  Destino
    File archDest = null;
    //ruta completa de la plantilla
    String pathDefi = pathDeex + File.separator + nombResa;
    //Registra del archivo de excel
    Row row = null;
    //Celda en el archivo de excel
    Cell cell;
    //Hoja de excel
    Sheet sheet = null;
    //Numero de hojas en el libro de excel
    int numberOfSheets;
    //Constantes
    final String NOMBRE_HOJA = "RESULTADOS EVALUACION";
    // Fila y columna para 
    int fila = 0;
    int columna = 0;
    //Fila inicio evidencia
    int filaEvid;

    try {
        archOrig = new File(pathPlan);

        if (!archOrig.exists()) {
            LOGGER.debug("Plantilla no existe en la ruta {} ", pathPlan);
            throw new IOException("La plantilla no existe en la ruta " + pathPlan);
        }

        archDest = new File(pathDeex);

        if (!archDest.exists()) {
            LOGGER.debug("Ruta no existe donde se va a depositar el excel {} , se va a crear", pathDeex);
            archDest.mkdirs();
        }

        LOGGER.info("Ruta del archivo a crear {}", pathDefi);
        archDest = new File(pathDefi);

        if (!archDest.exists()) {
            LOGGER.info("No existe el archivo en la ruta  {}, se procede a la creacion ", pathDefi);
            archDest.createNewFile();
        } else {

            LOGGER.info("el archivo que se requiere crear, ya existe {} se va a recrear", pathDefi);
            archDest.delete();
            LOGGER.info("archivo en la ruta {}, borrado", pathDefi);
            archDest.createNewFile();

            LOGGER.info("archivo en la ruta {}, se vuelve a crear", pathDefi);

        }

        LOGGER.info("Se inicia con la copia de la plantilla de la ruta {} a la ruta {} ", pathPlan, pathDefi);
        try (FileChannel archTror = new FileInputStream(archOrig).getChannel();
                FileChannel archTrDe = new FileOutputStream(archDest).getChannel();) {

            archTrDe.transferFrom(archTror, 0, archTror.size());

            LOGGER.info("Termina la copia del archivo");

        } catch (Exception e) {
            LOGGER.info("Se genera un error con la transferencia {} ", e.getMessage());
            throw new Exception("Error [" + e.getMessage() + "]");
        }

        LOGGER.info("Se inicia con el diligenciamiento del formato ");

        LOGGER.info("Nombre Archivo {}", archDest.getName());
        if (!archDest.getName().toLowerCase().endsWith("xls")) {
            throw new Exception("La plantilla debe tener extension xls");
        }

        try (FileInputStream fis = new FileInputStream(archDest);
                Workbook workbook = new HSSFWorkbook(fis);
                FileOutputStream fos = new FileOutputStream(archDest);) {

            if (workbook != null) {
                numberOfSheets = workbook.getNumberOfSheets();
                LOGGER.debug("Numero de hojas {}", numberOfSheets);

                LOGGER.info("Hoja seleccionada:{}", NOMBRE_HOJA);
                sheet = workbook.getSheetAt(0);

                fila = 5;

                LOGGER.info("Se inicia con la escritura de las oportunidades de mejora");

                LOGGER.info("Creando las celdas a llenar");

                for (int numeFila = fila; numeFila < this.listOpme.size() + fila; numeFila++) {

                    LOGGER.info("Fila {}", numeFila);
                    if (numeFila > 8) {

                        copyRow(workbook, sheet, numeFila - 2, numeFila - 1);
                        sheet.addMergedRegion(new CellRangeAddress(numeFila - 1, numeFila - 1, 1, 4));
                        sheet.addMergedRegion(new CellRangeAddress(numeFila - 1, numeFila - 1, 6, 7));

                    }

                }

                LOGGER.info("Terminando de llenar celdas");
                LOGGER.info("Poblar registros desde {} ", fila);

                for (OptuMejo optuMejo : this.listOpme) {

                    LOGGER.debug("Se va actualizar la linea {} celda 1. Valor  Capacitacion tecnica {}", fila,
                            optuMejo.getCapaTecn());
                    row = null;
                    cell = null;

                    row = sheet.getRow(fila);
                    cell = row.getCell(1);

                    cell.setCellValue(optuMejo.getCapaTecn());

                    LOGGER.debug("Se va actualizar la linea {} celda 6. Valor  compromisos del area {}", fila,
                            optuMejo.getComporta());
                    row = null;
                    cell = null;

                    row = sheet.getRow(fila);
                    cell = row.getCell(6);

                    cell.setCellValue(optuMejo.getComporta());

                    fila++;

                }
                LOGGER.info("Termino de poblar el registro hasta {} ", fila);
                //Ajustando los formulario
                if (fila > 8) {
                    sheet.addMergedRegion(new CellRangeAddress(fila, fila, 1, 7));
                    sheet.addMergedRegion(new CellRangeAddress(fila + 1, fila + 1, 2, 6));
                } else {
                    fila = 9;
                }

                /* sheet.addMergedRegion(new CellRangeAddress(fila, fila, 1, 7));
                sheet.addMergedRegion(new CellRangeAddress(fila + 1, fila + 1, 2, 6));*/
                LOGGER.info("Fin de la escritura de las oportunidades de mejora");

                LOGGER.info("Se inicia la escritura de las evidencias   ");

                fila += 2;
                filaEvid = fila + 5;

                LOGGER.info("Se inicia la creacion de las celdas desde  el registro {}   ", fila);

                for (Evidenci evidenci : this.listEvid) {

                    if (filaEvid < fila) {
                        copyRow(workbook, sheet, fila - 1, fila);

                    }

                    LOGGER.debug("Se va actualizar la linea {} celda 1. Valor Fecha {}", fila,
                            evidenci.getFecha());
                    row = null;
                    cell = null;

                    row = sheet.getRow(fila);
                    cell = row.getCell(1);

                    cell.setCellValue(evidenci.getFecha());

                    LOGGER.debug("Se va actualizar la linea {} celda 6. Valor  compromisos del area {}", fila,
                            evidenci.getDescripc());
                    row = null;
                    cell = null;

                    row = sheet.getRow(fila);
                    cell = row.getCell(2);

                    cell.setCellValue(evidenci.getDescripc());

                    sheet.addMergedRegion(new CellRangeAddress(fila, fila, 2, 6));

                    fila++;

                }

                LOGGER.info("Fin de la escritura de las Evidencias");

                LOGGER.info("Inicio de escritura de calificaciones");
                //Ajustando los formulario - resultado

                /*sheet.addMergedRegion(new CellRangeAddress(fila, fila, 1, 7));*/
                if (fila > filaEvid) {
                    LOGGER.info("Fila a ejecutar {}", fila);
                    sheet.addMergedRegion(new CellRangeAddress(fila + 1, fila + 1, 2, 5));
                    sheet.addMergedRegion(new CellRangeAddress(fila + 2, fila + 2, 2, 5));
                    sheet.addMergedRegion(new CellRangeAddress(fila + 3, fila + 3, 2, 5));
                    sheet.addMergedRegion(new CellRangeAddress(fila + 4, fila + 4, 2, 5));
                    sheet.addMergedRegion(new CellRangeAddress(fila + 1, fila + 1, 6, 7));
                    sheet.addMergedRegion(new CellRangeAddress(fila + 2, fila + 4, 6, 7));
                    //Firma del evaluado ajuste
                    sheet.addMergedRegion(new CellRangeAddress(fila + 5, fila + 5, 1, 3));
                    sheet.addMergedRegion(new CellRangeAddress(fila + 5, fila + 5, 4, 6));

                    //Ajustando recursos
                    sheet.addMergedRegion(new CellRangeAddress(fila + 6, fila + 6, 1, 7));

                    sheet.addMergedRegion(new CellRangeAddress(fila + 8, fila + 8, 1, 7));
                    sheet.addMergedRegion(new CellRangeAddress(fila + 10, fila + 10, 1, 7));

                } else {
                    fila = filaEvid + 1;
                    LOGGER.info("Fila a ejecutar {}", fila);
                }

                LOGGER.debug("Se va actualizar la linea {} celda 2. Valor Excelente {}", fila + 2,
                        this.excelent);
                row = null;
                cell = null;

                row = sheet.getRow(fila + 2);
                cell = row.getCell(2);

                cell.setCellValue((this.excelent != null ? this.excelent : ""));

                LOGGER.debug("Se va actualizar la linea {} celda 2. Valor satisfactorio {}", fila + 3,
                        this.satisfac);
                row = null;
                cell = null;

                row = sheet.getRow(fila + 3);
                cell = row.getCell(2);

                cell.setCellValue((this.satisfac != null ? this.satisfac : ""));

                LOGGER.debug("Se va actualizar la linea {} celda 3. Valor no satisfactorio {}", fila + 4,
                        this.noSatisf);
                row = null;
                cell = null;

                row = sheet.getRow(fila + 4);
                cell = row.getCell(2);

                cell.setCellValue((this.noSatisf != null ? this.noSatisf : ""));

                //Ajustando Total  Calificacion en Numero
                LOGGER.debug("Se va actualizar la linea {} celda 2. Valor total calificacion {}", fila + 2,
                        this.numeToca);
                row = null;
                cell = null;

                row = sheet.getRow(fila + 2);
                cell = row.getCell(6);

                cell.setCellValue(this.numeToca);

                LOGGER.info("Fin de escritura de calificaciones");

                LOGGER.info("Inicio de escritura de interposicion de recursos");

                LOGGER.debug("Se va actualizar la linea {} celda 5. Valor si interpone recursos {}", fila + 7,
                        this.siinRecu);
                row = null;
                cell = null;

                row = sheet.getRow(fila + 7);
                cell = row.getCell(6);

                cell.setCellValue("SI:" + (this.siinRecu != null ? this.siinRecu : ""));

                LOGGER.debug("Se va actualizar la linea {} celda 5. Valor si interpone recursos {}", fila + 7,
                        this.noinRecu);
                row = null;
                cell = null;

                row = sheet.getRow(fila + 7);
                cell = row.getCell(7);

                cell.setCellValue("NO:" + (this.noinRecu != null ? this.noinRecu : ""));

                LOGGER.debug("Se va actualizar la linea {} celda 5. Valor si interpone recursos {}", fila + 8,
                        this.fech);

                row = null;
                cell = null;

                row = sheet.getRow(fila + 8);
                cell = row.getCell(1);

                cell.setCellValue("FECHA:" + (this.fech != null ? this.fech : ""));

                LOGGER.info("Fin de escritura de interposicion de recursos");

                //Ajustando recursos
                workbook.write(fos);

            } else {
                throw new Exception("No se cargo de manera adecuada el archivo ");
            }

        } catch (Exception e) {
            System.out.println("" + e.getMessage());
        }

    } catch (Exception e) {
        LOGGER.error(e.getMessage());
        throw new Exception(e.getMessage());
    }
}

From source file:net.rrm.ehour.ui.common.report.AbstractExcelReport.java

License:Open Source License

protected int createHeaders(int rowNumber, Sheet sheet, Report report, ExcelWorkbook workbook) {
    Row row = sheet.createRow(rowNumber++);
    CellFactory.createCell(row, 0, getHeaderReportName(), workbook, ExcelStyle.BOLD_FONT);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));

    row = sheet.createRow(rowNumber++);/*w  ww. j  a v a 2  s .  c  o m*/
    CellFactory.createCell(row, 0, new ResourceModel("report.dateStart"), workbook, ExcelStyle.BOLD_FONT);

    if (report.getReportRange() == null || report.getReportRange().getDateStart() == null) {
        CellFactory.createCell(row, 1, "--", workbook, ExcelStyle.BOLD_FONT);
    } else {
        CellFactory.createCell(row, 1, report.getReportCriteria().getReportRange().getDateStart(), workbook,
                ExcelStyle.BOLD_DATE);
    }

    CellFactory.createCell(row, 3, new ResourceModel("report.dateEnd"), workbook, ExcelStyle.BOLD_FONT);

    if (report.getReportRange() == null || report.getReportRange().getDateEnd() == null) {
        CellFactory.createCell(row, 4, "--", workbook, ExcelStyle.BOLD_FONT);
    } else {
        CellFactory.createCell(row, 4, report.getReportCriteria().getReportRange().getDateEnd(), workbook,
                ExcelStyle.BOLD_DATE);
    }

    rowNumber++;

    return rowNumber;
}

From source file:net.rrm.ehour.ui.timesheet.export.excel.part.ExportReportBody.java

License:Open Source License

private int addColumnsToRow(Date date, List<FlatReportElement> elements, int rowNumber, boolean isBorder) {
    boolean addedForDate = false;

    for (FlatReportElement flatReportElement : elements) {
        Row row = getSheet().createRow(rowNumber);

        if (flatReportElement.getTotalHours() != null
                && flatReportElement.getTotalHours().doubleValue() >= 0.0) {
            createDateCell(date, row, isBorder);
            createProjectCell(flatReportElement.getProjectName(), row, isBorder);
            createProjectCodeCell(flatReportElement.getProjectCode(), row, isBorder);
            createHoursCell(flatReportElement.getTotalHours(), row, isBorder);
            createCustomerCodeCell(flatReportElement.getCustomerCode(), row, isBorder);

            if (isBorder) {
                createEmptyCells(row, ExcelStyle.BORDER_NORTH_THIN);

                getSheet().addMergedRegion(
                        new CellRangeAddress(rowNumber, rowNumber, getCellMargin() + 3, getCellMargin() + 5));
            }/*w w  w .  j  a v  a 2 s. c  om*/

            rowNumber++;
            addedForDate = true;
        }
    }

    if (!addedForDate) {
        Row row = getSheet().createRow(rowNumber++);
        createDateCell(date, row, isBorder);
    }

    return rowNumber;

}

From source file:net.rrm.ehour.ui.timesheet.export.excel.part.ExportReportSignOff.java

License:Open Source License

private int createSignOffBox(int rowNumber) {
    int cellMargin = getCellMargin();

    getSheet().addMergedRegion(new CellRangeAddress(rowNumber, rowNumber + 2, cellMargin, cellMargin + 2));
    getSheet().addMergedRegion(new CellRangeAddress(rowNumber, rowNumber + 2, cellMargin + 4, cellMargin + 6));

    // doesn't work properly, box is not around the whole merged cells
    //      Row boxRow = getSheet().createRow(rowNumber);
    //      CellFactory.createCell(boxRow, cellMargin, getWorkbook(), BORDER_THIN);
    //      CellFactory.createCell(boxRow, cellMargin  + 4, getWorkbook(), BORDER_THIN);

    return rowNumber;
}

From source file:net.sf.excelutils.ExcelParser.java

License:Apache License

/**
 * parse the cell//ww w. ja  v  a2  s  . c  o m
 * 
 * @param context data object
 * @param cell excel cell
 */
public static void parseCell(Object context, Sheet sheet, Row row, Cell cell) {

    String str = cell.getStringCellValue();
    if (null == str || "".equals(str)) {
        return;
    }

    if (str.indexOf(VALUED_DELIM) < 0)
        return;

    boolean bJustExpr = str.length() == (str.length() - str.lastIndexOf(VALUED_DELIM));
    boolean bMerge = "!".equals(str.substring(str.indexOf(VALUED_DELIM) + VALUED_DELIM.length(),
            str.indexOf(VALUED_DELIM) + VALUED_DELIM.length() + 1));

    if (str.indexOf(VALUED_DELIM) < 0)
        return;

    Object value = parseStr(context, str);

    // replace the cell
    if (null != value) {
        if (bJustExpr && "java.lang.Integer".equals(value.getClass().getName())) {
            cell.setCellValue(Double.parseDouble(value.toString()));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        } else if (bJustExpr && "java.lang.Double".equals(value.getClass().getName())) {
            cell.setCellValue(((Double) value).doubleValue());
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        } else if (bJustExpr && "java.util.Date".equals(value.getClass().getName())) {
            cell.setCellValue((Date) value);
        } else if (bJustExpr && "java.lang.Boolean".equals(value.getClass().getName())) {
            cell.setCellValue(((Boolean) value).booleanValue());
            cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
        } else if (bJustExpr && Number.class.isAssignableFrom(value.getClass())) {
            cell.setCellValue(((Number) (value)).doubleValue());
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        } else {
            // cell.setEncoding(Workbook.ENCODING_UTF_16); POI3.2?
            cell.setCellValue(value.toString());
        }
    } else {
        cell.setCellValue("");
    }

    // merge the cell that has a "!" character at the expression
    if (row.getRowNum() - 1 >= sheet.getFirstRowNum() && bMerge) {
        Row lastRow = WorkbookUtils.getRow(row.getRowNum() - 1, sheet);
        Cell lastCell = WorkbookUtils.getCell(lastRow, cell.getColumnIndex());
        boolean canMerge = false;
        if (lastCell.getCellType() == cell.getCellType()) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                canMerge = lastCell.getStringCellValue().equals(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                canMerge = lastCell.getBooleanCellValue() == cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                canMerge = lastCell.getNumericCellValue() == cell.getNumericCellValue();
                break;
            }
        }
        if (canMerge) {
            CellRangeAddress region = new CellRangeAddress(lastRow.getRowNum(), row.getRowNum(),
                    lastCell.getColumnIndex(), cell.getColumnIndex());
            sheet.addMergedRegion(region);
        }
    }

}

From source file:net.sf.excelutils.tags.EachTag.java

License:Apache License

public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell)
        throws ExcelException {
    String expr = "";
    String each = curCell.getStringCellValue();

    LOG.debug("EachTag:" + each);

    StringTokenizer st = new StringTokenizer(each, " ");
    String widthstr = "";
    String onstr = "";
    int pos = 0;// w w w.  j  a  v a  2 s.  c  o  m
    while (st.hasMoreTokens()) {
        String str = st.nextToken();
        if (pos == 1) {
            expr = str;
        }
        if (pos == 2 && !"on".equals(str)) {
            widthstr = str;
        }
        if (pos == 3 && !"on".equals(str)) {
            onstr = str;
        }
        if (pos == 4) {
            onstr = str;
        }
        pos++;
    }

    int[] widths = new int[0];
    if (null != widthstr && !"".equals(widthstr)) {
        Object o = ExcelParser.parseStr(context, widthstr);
        if (null != o) {
            String[] s = o.toString().split(",");
            widths = new int[s.length];
            for (int i = 0; i < widths.length; i++) {
                widths[i] = Integer.parseInt(s[i]);
            }
        }
    }

    Object obj = ExcelParser.parseExpr(context, expr);
    if (null == obj)
        return new int[] { 0, 0, 0 };

    // by onstr get the property
    if (!"".equals(onstr)) {
        obj = ExcelParser.parseExpr(context, onstr);
        if (null == obj)
            return new int[] { 0, 0, 0 };
    }

    // iterator properties
    Iterator it = ExcelParser.getIterator(obj);
    if (null == it) {
        if (obj instanceof DynaBean) {
            it = ExcelParser.getIterator(ExcelParser.getBeanProperties(((DynaBean) obj).getDynaClass()));
        } else {
            it = ExcelParser.getIterator(ExcelParser.getBeanProperties(obj.getClass()));
        }
    }
    if (null == it) {
        return new int[] { 0, 0, 0 };
    }

    int index = 0;
    int arrayIndex = 0;
    int eachPos = curCell.getColumnIndex();
    String modelName = expr.substring(ExcelParser.VALUED_DELIM.length(),
            expr.length() - ExcelParser.VALUED_DELIM2.length());

    // restore the obj
    obj = ExcelParser.parseExpr(context, expr);
    while (it.hasNext()) {
        Object o = it.next();
        String property = "";
        if (o instanceof Field) {
            property = ((Field) o).getName();
        } else if (o instanceof Map.Entry) {
            property = ((Map.Entry) o).getKey().toString();
        } else if (o instanceof DynaProperty) {
            property = ((DynaProperty) o).getName();
        } else if (null != o) {
            property = o.toString();
        }

        // test the object is array/list or other
        if (obj.getClass().isArray() || obj instanceof Collection) {
            property = modelName + "[" + arrayIndex++ + "]";
        } else {
            property = modelName + "." + property;
        }

        Object value = ExcelParser.getValue(context, property);
        if (null == value)
            value = "";

        if (ExcelUtils.isCanShowType(value)) {

            // get cell merge count
            int width = 1;
            if (index < widths.length) {
                width = widths[index];
            } else if (1 == widths.length) {
                width = widths[0];
            }

            // get row merged of the curCell
            int rowMerged = 1;
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress r = sheet.getMergedRegion(i);
                if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == curCell.getColumnIndex()
                        && r.getLastColumn() == curCell.getColumnIndex()) {
                    rowMerged = r.getLastRow() - r.getFirstRow() + 1;
                    break;
                }
            }

            Cell cell = WorkbookUtils.getCell(curRow, eachPos);

            // shift the after cell
            if (index > 0) {
                WorkbookUtils.shiftCell(sheet, curRow, cell, 1, rowMerged);
            }
            if (width > 1) {
                Cell nextCell = WorkbookUtils.getCell(curRow, eachPos + 1);
                WorkbookUtils.shiftCell(sheet, curRow, nextCell, width - 1, rowMerged);
            }

            // copy the style of curCell
            for (int rownum = curRow.getRowNum(); rownum < curRow.getRowNum() + rowMerged; rownum++) {
                for (int i = 0; i < width; i++) {
                    Row r = WorkbookUtils.getRow(rownum, sheet);
                    Cell c = WorkbookUtils.getCell(r, eachPos + i);
                    Cell cc = WorkbookUtils.getCell(r, curCell.getColumnIndex());
                    c.setCellStyle(cc.getCellStyle());
                }
            }

            // merge cells
            if (width > 1 || rowMerged > 1) {
                sheet.addMergedRegion(
                        new CellRangeAddress(curRow.getRowNum(), curRow.getRowNum() + rowMerged - 1,
                                cell.getColumnIndex(), cell.getColumnIndex() + width - 1));
            }

            cell.setCellValue("${" + property + "}");
            ExcelParser.parseCell(context, sheet, curRow, cell);

            eachPos += width;
            index++;
        }
    }

    return new int[] { 0, 0, 0 };
}

From source file:net.sf.excelutils.WorkbookUtils.java

License:Apache License

/**
 * copy row//from   w  w  w  .j  a  v a2s.com
 *
 * @param sheet
 * @param from begin of the row
 * @param to destination fo the row
 * @param count count of copy
 */
public static void copyRow(Sheet sheet, int from, int to, int count) {

    for (int rownum = from; rownum < from + count; rownum++) {
        Row fromRow = sheet.getRow(rownum);
        Row toRow = getRow(to + rownum - from, sheet);
        if (null == fromRow)
            return;
        toRow.setHeight(fromRow.getHeight());
        toRow.setHeightInPoints(fromRow.getHeightInPoints());
        int lastCellNum = fromRow.getLastCellNum();
        lastCellNum = lastCellNum > 255 ? 255 : lastCellNum;
        for (int i = fromRow.getFirstCellNum(); i <= lastCellNum && i >= 0; i++) {
            Cell fromCell = getCell(fromRow, i);
            Cell toCell = getCell(toRow, i);
            // toCell.setEncoding(fromCell.getEncoding());
            toCell.setCellStyle(fromCell.getCellStyle());
            toCell.setCellType(fromCell.getCellType());
            switch (fromCell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                toCell.setCellFormula(fromCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                toCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                toCell.setCellValue(fromCell.getStringCellValue());
                break;
            default:
            }
        }
    }

    // copy merged region
    List shiftedRegions = new ArrayList();
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() >= from && r.getLastRow() < from + count) {
            CellRangeAddress n_r = new CellRangeAddress(r.getFirstRow() + to - from, r.getLastRow() + to - from,
                    r.getFirstColumn(), r.getLastColumn());
            shiftedRegions.add(n_r);
        }
    }

    // readd so it doesn't get shifted again
    Iterator iterator = shiftedRegions.iterator();
    while (iterator.hasNext()) {
        CellRangeAddress region = (CellRangeAddress) iterator.next();
        sheet.addMergedRegion(region);
    }
}

From source file:net.sf.jasperreports.engine.export.JRXlsExporter.java

License:Open Source License

protected void createMergeRegion(JRExporterGridCell gridCell, int colIndex, int rowIndex,
        HSSFCellStyle cellStyle) {//from   ww  w .jav  a2s . co  m
    boolean isCollapseRowSpan = getCurrentItemConfiguration().isCollapseRowSpan();
    int rowSpan = isCollapseRowSpan ? 1 : gridCell.getRowSpan();
    if (gridCell.getColSpan() > 1 || rowSpan > 1) {
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, (rowIndex + rowSpan - 1), colIndex,
                (colIndex + gridCell.getColSpan() - 1)));

        for (int i = 0; i < rowSpan; i++) {
            HSSFRow spanRow = sheet.getRow(rowIndex + i);
            if (spanRow == null) {
                spanRow = sheet.createRow(rowIndex + i);
            }
            for (int j = 0; j < gridCell.getColSpan(); j++) {
                HSSFCell spanCell = spanRow.getCell((colIndex + j));
                if (spanCell == null) {
                    spanCell = spanRow.createCell((colIndex + j));
                }
                spanCell.setCellStyle(cellStyle);
            }
        }
    }
}

From source file:net.sourceforge.fenixedu.domain.phd.reports.PhdReport.java

License:Open Source License

protected void addHeaderCell(HSSFSheet sheet, String value, int columnNumber) {
    HSSFRow row = sheet.getRow(0);/*  ww  w  .  j  a  v  a2  s  .co m*/
    if (row == null) {
        row = sheet.createRow(0);
    }

    HSSFCell cell = row.createCell(columnNumber);

    cell.setCellValue(value);
    cell.setCellStyle(headerStyle);

    cell.setCellValue(value);

    sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));
}