Example usage for org.apache.poi.ss.usermodel Sheet getWorkbook

List of usage examples for org.apache.poi.ss.usermodel Sheet getWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getWorkbook

Introduction

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

Prototype

Workbook getWorkbook();

Source Link

Document

Return the parent workbook

Usage

From source file:das.pf.io.IOExcel.java

License:Open Source License

private void createHeaderData(Sheet sheet, CellStyle style) {
    Row row = sheet.createRow(2);//from  ww w . j  ava  2  s  .co m
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();

    String[] titles = new String[] { "Pais", "Regiones", "Mercados Relevantes", "CT", "Key Markets", "Producto",
            "Key Competitors", "Sku", "Laboratorios", "Tipo de Mercado", "Molculas" };

    for (int index = 0; index < titles.length; index++) {
        Cell cell = row.createCell(index);

        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(helper.createRichTextString(titles[index]));
        cell.setCellStyle(style);

        cell = null;
    }

    row = null;
    helper = null;
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

/**
 * Este metodo se encarga de escribir en la hoja de destino todos los datos relacionados con las Clases
 * Terapeuticas, que se encuentren dentro de la hoja fuente.
 * //  w  w  w. j a v  a  2 s. c o m
 * @param sheet
 *          Instancia que modela la hoja objetivo o hoja en la que se desea trabajar.
 * 
 * @param source
 *          Instancia que modela la hoja que posee todos las Clases Terapeuticas.
 * 
 * @param start 
 *          Indica el indice de la primer fila donde se encuentran las clases terapeuticas.
 * 
 * @param endRow 
 *          Inidca el indice de la ultima fila que se desea escribir.
 */
private void writeCT(Sheet sheet, Sheet source, int start, int endRow) {
    int indexTarget = 3;
    int indexRow = start;

    String ct = "";
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();

    while (indexRow < (endRow + start)) {
        try {
            Row rowSource = source.getRow(indexRow);

            if (rowSource != null) {
                Cell type = rowSource.getCell(2);

                if (type != null) {

                    if (type.getRichStringCellValue().getString().equals("ATC IV")) {
                        ct = rowSource.getCell(6).getRichStringCellValue().getString();

                        int totalPacks = 0;
                        int indexProd = indexRow + 1;
                        int count = 0;
                        int totalProdu = getNumberOfSubNodes(source, indexRow, "Prds.", "ATC IV");

                        // se obtienen los totales de productos de todos los paquetes que pertenescan a la clase terapeutica
                        for (int subNodeProduc = 0; subNodeProduc < totalProdu; subNodeProduc++) {
                            count = getNumberOfSubNodes(source, indexProd, "Packs", "Prds.");
                            totalPacks += count;

                            indexProd += count + 1;
                        }

                        // se escribes la cantidad correcta de filas con la clase terapeutica correspondiente
                        for (int indexCT = 0; indexCT < totalPacks; indexCT++) {
                            Row row = sheet.createRow(indexTarget++);
                            Cell cellCT = row.createCell(3);

                            cellCT.setCellType(Cell.CELL_TYPE_STRING);
                            cellCT.setCellValue(helper.createRichTextString(ct));

                            cellCT = null;
                            row = null;
                        }

                        indexRow += (totalPacks + totalProdu);
                    } else
                        indexRow++;

                    type = null;
                } else
                    indexRow++;
            } else
                indexRow++;
        } catch (NullPointerException ex) {
            Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, String.format("indexRow: %d", indexRow),
                    ex);

            Util.showException("Ocurrio un error procesando las Clases Terapeuticas", ex);
        }
    }
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

/**
 * Este metodo se encarga de escribir o llenar todos los productos en la hoja de destino u objetivo,
 * que se encuentran en la hoja fuente.//from   w  w w .j a va2 s .  c o m
 * 
 * @param sheet
 *          Intancia que modela la hoja objetivo o la hoja que se desea trabajar.
 * 
 * @param source
 *          Instancia que modela la hoja que contiene todos los productos.
 * 
 * @param start 
 *          Indica la posicion de la primer fila donde se encuentran los productos en
 *          la hoja fuente.
 */
private void writeProducts(Sheet sheet, Sheet source, int start) {
    AtomicInteger rowIndex = new AtomicInteger(3);

    final CreationHelper helper = sheet.getWorkbook().getCreationHelper();

    Stream<Row> rows = getRows(source, start).stream().filter((Row r) -> {
        Cell type = r.getCell(2);

        return type != null && type.getRichStringCellValue().getString().equals("Prds.");
    });

    if (rows != null) {
        rows.forEach(r -> {
            String product = r.getCell(6).getRichStringCellValue().getString()
                    .replaceFirst("\\[[0-9]+/[0-9]+\\]", "");

            int numbeerOfSubNodes = getNumberOfSubNodes(source, r.getRowNum(), "Packs", "Prds.");

            for (int subNodes = 0; subNodes < numbeerOfSubNodes; subNodes++) {
                Row row = sheet.getRow(rowIndex.getAndIncrement());

                if (row != null) {
                    Cell cellProductSubNode = row.createCell(5);

                    cellProductSubNode.setCellType(Cell.CELL_TYPE_STRING);
                    cellProductSubNode.setCellValue(helper.createRichTextString(product));

                    cellProductSubNode = null;
                    row = null;
                }
            }
        });
    }
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

/**
 * Este metodo se encarga de escribir los datos correspondientes a los Key Competitors
 * //from   w ww.  j  a  v a  2 s .  c  o m
 * @param sheet
 *          Instancia que modela la hoja en la que se va a trabajar.
 * 
 * @param startRow
 *          Indica el indice de la primer fila en la que se trabajara.
 * 
 *  @param endRow
 *          Indica el indice para la ultima fila que se desea escribir.
 * 
 * @param indexColumSource
 *          Indica el indice de la celda donde se tomara como parametro los datos
 * 
 * @param indexColumnProduct 
 *          Indica el indice de la celda donde estan los productos.
 */
private void writeKeyCompetitors(Sheet sheet, int startRow, int endRow, int indexColumSource,
        int indexColumnProduct) {
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();
    int index = 6;

    for (int indexRow = startRow; indexRow < (endRow + startRow); indexRow++) {
        Row row = sheet.getRow(indexRow);

        if (row != null) {
            Cell cellKeyCompetitor = row.createCell(index);
            Cell cellSource = row.getCell(indexColumSource);

            if (cellSource != null) {
                cellKeyCompetitor.setCellType(Cell.CELL_TYPE_STRING);

                if (cellSource.getRichStringCellValue().getString().trim().equals("Generico"))
                    cellKeyCompetitor.setCellValue(helper.createRichTextString("Genricos"));
                else {
                    Cell cellProduct = row.getCell(indexColumnProduct);

                    if (cellProduct != null)
                        cellKeyCompetitor.setCellValue(cellProduct.getRichStringCellValue().getString());
                }
            }

            cellKeyCompetitor = null;
            cellSource = null;
        }

        row = null;
    }
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

private XmlContry writeContries(Sheet sheet, int startRow, int column, Path pathFile) {
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();

    XmlContry contry = Util.getContryByAcronym(getAcromynName(pathFile));

    if (contry != null) {
        try {//from  w w  w  .j av a2s .  c o  m
            for (int index = startRow; index < sheet.getLastRowNum() + 1; index++) {
                Row row = sheet.getRow(index);

                if (row != null) {
                    Cell cellContry = row.createCell(column);

                    cellContry.setCellType(Cell.CELL_TYPE_STRING);
                    cellContry.setCellValue(helper.createRichTextString(contry.getName()));

                    updateMessages(String.format("Escribiendo el pais: %s en la fila: %d", contry.getName(),
                            index + 1));
                }
            }
        } catch (Exception ex) {
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error al escribir la columna Pais", ex);

            Util.showException("Error al escribir la columna Pais", ex);
        }
    }

    return contry;
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

private void writeRegions(XmlContry contry, Sheet sheet, int startRow, int column) {
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();
    XmlRegion region = Util.getRegionByContry(contry);

    if (region != null) {
        for (int index = startRow; index < sheet.getLastRowNum() + 1; index++) {
            Row r = sheet.getRow(index);

            try {
                if (r != null) {
                    Cell cellRegion = r.createCell(column);

                    cellRegion.setCellType(Cell.CELL_TYPE_STRING);
                    cellRegion.setCellValue(helper.createRichTextString(region.getName()));

                    updateMessages(String.format("Escribiendo la region: %s en la fila: %d", region.getName(),
                            r.getRowNum() + 1));
                }/* www .j  a va  2s . c  o  m*/
            } catch (Exception ex) {
                Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error al escribir la columna Region",
                        ex);

                Util.showException("Error al escribir la columna Region", ex);
            }
        }
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.exporter.ExportWorkbook.java

License:Open Source License

/**
 * Adds headers stored in <code>headers</code> to the current sheet. If required, a special width
 * for the corresponding column can be set by providing a value in <code>headersWidth</code> using
 * header as key.<br/>/*from w  ww  .  j  a v  a  2s.c om*/
 * <b>IMPORTANT</b>: Headers are added in the order provided in <code>headers</code>.
 * 
 * @param headers
 *          headers to be added
 */
public void addHeaders(int sheetId, List<ExcelSheet.Header> headers) {
    Sheet sheet = getSheetById(sheetId);
    Drawing drawing = sheet.createDrawingPatriarch();
    CreationHelper factory = sheet.getWorkbook().getCreationHelper();
    Row row = sheet.createRow(this.getCurrentRowOfSheet(sheet, 3));
    int columnIndex = 0;
    for (ExcelSheet.Header header : headers) {
        int currColumnIndex = columnIndex;
        Cell cell = row.createCell(columnIndex);
        if (header.getDescription() != null) {
            ClientAnchor commentAnchor = factory.createClientAnchor();
            //Sizing the comment 1x3 cells
            commentAnchor.setCol1(cell.getColumnIndex());
            commentAnchor.setCol2(cell.getColumnIndex() + 1);
            commentAnchor.setRow1(row.getRowNum());
            commentAnchor.setRow2(row.getRowNum() + 3);

            Comment comment = drawing.createCellComment(commentAnchor);
            RichTextString str = factory.createRichTextString(header.getDescription());
            comment.setString(str);
            comment.setAuthor("");
            cell.setCellComment(comment);
        }

        setCellValue(cell, header.getLabel(), getHeaderTableStyle());
        Integer width = header.getWidth();
        if (width != null) {
            sheet.setColumnWidth(currColumnIndex, width.intValue());
        }
        columnIndex++;
    }

    LOGGER.debug("Added headers.");
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.nettoExport.AbstractNettoTransformer.java

License:Open Source License

/**
 * Set the sheet name like "Information System (IS)".
 * @param sheet  A POI {@link Sheet}/*w ww .j av  a 2s .  co  m*/
 * @param typeOfBuildingBlock  The used type of building block.
 */
protected final void configSheetName(Sheet sheet, TypeOfBuildingBlock typeOfBuildingBlock) {
    String msgLookupKey = typeOfBuildingBlock.getValue();
    String msgLookupKeyAbbr = typeOfBuildingBlock.getAbbreviationValue();
    String tobName = MessageAccess.getString(msgLookupKey);
    String tobAbbrName = MessageAccess.getString(msgLookupKeyAbbr);
    Workbook workbook = sheet.getWorkbook();
    int sheetIndex = workbook.getSheetIndex(sheet);
    workbook.setSheetName(sheetIndex, tobName + " (" + tobAbbrName + ")");
}

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

License:Open Source License

private void preformatCells(Sheet sheet, Map<IteraExcelStyle, CellStyle> styles) {
    sheet.setDefaultColumnStyle(TimeseriesExcelImporter.BB_COL_NO, styles.get(IteraExcelStyle.DATA));
    sheet.setDefaultColumnStyle(TimeseriesExcelImporter.DATE_COL_NO, styles.get(IteraExcelStyle.DATA_DATE));
    sheet.setDefaultColumnStyle(TimeseriesExcelImporter.VALUE_COL_NO, styles.get(IteraExcelStyle.DATA));

    CellStyle workbookDefaultStyle = sheet.getWorkbook().getCellStyleAt((short) 0);
    for (int rowNum = 0; rowNum < TimeseriesExcelImporter.FIRST_DATA_ROW_NO; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int colNum = 0; colNum < 3; colNum++) {
            row.createCell(colNum).setCellStyle(workbookDefaultStyle);
        }// w ww  .ja  v  a2  s.  com
    }
}

From source file:de.ks.idnadrev.expimp.xls.XlsxExporter.java

License:Apache License

protected void exportSource(Sheet sheet, EntityExportSource<?> source) {
    List<XlsxColumn> columns = getColumnDefinitions(source);
    createTitle(sheet, columns);// ww  w.j  a  v  a 2s . c  om

    int rowId = 1;
    for (AbstractPersistentObject object : source) {
        Row row = sheet.createRow(rowId);
        for (int columnId = 0; columnId < columns.size(); columnId++) {
            XlsxColumn column = columns.get(columnId);
            Object value = column.getValue(object);
            if (value == null) {
                row.createCell(columnId, Cell.CELL_TYPE_BLANK);
            } else {
                Cell cell = row.createCell(columnId, column.getCellType());
                cell.setCellStyle(column.getCellStyle(workbook));
                setCellValue(sheet.getWorkbook().getCreationHelper(), cell, value);
            }
        }
        rowId++;
    }
    for (int columnId = 0; columnId < columns.size(); columnId++) {
        try {
            sheet.autoSizeColumn(columnId);
        } catch (NullPointerException e) {
            //
        }
    }
}