Example usage for org.apache.poi.ss.usermodel Cell getRichStringCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getRichStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getRichStringCellValue

Introduction

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

Prototype

RichTextString getRichStringCellValue();

Source Link

Document

Get the value of the cell as a XSSFRichTextString

For numeric cells we throw an exception.

Usage

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.
 * /*from w ww .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   www. ja v  a2  s .c om
 * 
 * @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 o llenar las columnas en la hoja objetivo o la hoja
 * en la que se desea trabajar, con los datos que se encuentran dentro de la hoja fuente.
 * Los datos que seran toamdos encuenta son todas las columnas menos: "Productos", "Clases Terapeuticas",
 * "Key Competitors".//w w  w  .  j a va  2s  .  com
 * 
 * @param sheet
 *          Instancia que modela la hoja objetivo o de destino.
 * 
 * @param source
 *          Instancia que modela la hoja fuente o que contiene los datos que sedesea
 *          procesar.
 * 
 * @param start 
 *          Indica el indice de la primer fila donde se encuentran los datos.   
 */
private void writerOthersValues(Sheet sheet, Sheet source, int start) {
    int indexTarget = 3;

    for (int indexRow = start; indexRow < (source.getLastRowNum() - 1); indexRow++) {
        Row rowSource = source.getRow(indexRow);

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

            if (type != null && type.getRichStringCellValue().getString().equals("Packs")) {
                Row row = sheet.getRow(indexTarget++);

                if (row != null) {
                    Cell sku = row.createCell(7);
                    Cell laboratory = row.createCell(8);
                    Cell typeOfMark = row.createCell(9);
                    Cell molecules = row.createCell(10);

                    sku.setCellType(Cell.CELL_TYPE_STRING);
                    sku.setCellValue(rowSource.getCell(6).getRichStringCellValue().getString());

                    laboratory.setCellType(Cell.CELL_TYPE_STRING);
                    laboratory.setCellValue(rowSource.getCell(8).getRichStringCellValue().getString());

                    typeOfMark.setCellType(Cell.CELL_TYPE_STRING);
                    typeOfMark.setCellValue(rowSource.getCell(9).getRichStringCellValue().getString());

                    molecules.setCellType(Cell.CELL_TYPE_STRING);
                    molecules.setCellValue(rowSource.getCell(10).getRichStringCellValue().getString());

                    writeUnitValues(row, rowSource, 11, 155, 14);

                    sku = null;
                    laboratory = null;
                    typeOfMark = null;
                    molecules = null;
                }

                row = null;
            }

            type = null;
        }

        rowSource = null;
    }
}

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

License:Open Source License

/**
 * Este metodo se encarga de escribir todos los valores correspondientes a las 
 * unidades en dolares, estandares y de valores.
 * //from   ww w  . j a va2 s .c om
 * @param target
 *          Instancia que modela la fila sobre la cual se escribiran en la celdas.
 * 
 * @param source
 *          Instancia que modela la fila que contiene las celdas con los datos a escribir.
 * 
 * @param startValues
 *          Indice que marca la primer celda a escribir
 * 
 * @param endValues
 *          Indice que indica la ultima celda sobre la que se debe escribir.
 * 
 * @param indexValuesSource 
 *          Indice que indica la primer celda que contiene los datos a escribir dentro de la fila fuente.
 */
private void writeUnitValues(Row target, Row source, int startValues, int endValues, int indexValuesSource) {
    try {
        for (int indexCell = startValues; indexCell < endValues; indexCell++) {
            if (indexValuesSource == 62 || indexValuesSource == 111)
                indexValuesSource++;

            Cell value = target.createCell(indexCell);
            Cell valueSource = source.getCell(indexValuesSource++);

            if (valueSource != null) {
                if (valueSource.getCellType() == Cell.CELL_TYPE_NUMERIC)
                    value.setCellValue(valueSource.getNumericCellValue());
                else if (valueSource.getCellType() == Cell.CELL_TYPE_STRING)
                    value.setCellValue(Double.parseDouble(valueSource.getRichStringCellValue().getString()));

                value.setCellType(Cell.CELL_TYPE_NUMERIC);
            }

            value = null;
            valueSource = null;
        }
    } catch (NumberFormatException ex) {
        Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);

        Util.showException("Ocurrio un error procensado los valores de las unidades", ex);
    }
}

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
 * // w w  w.ja 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 int getLasRow(Sheet source) {
    int count = 0;

    for (int index = 13; index < source.getLastRowNum(); index++) {
        try {//from  w  w w . j av a2 s  . c o m
            if (source.getRow(index) != null) {
                Row r = source.getRow(index);
                Cell cell = r.getCell(2);

                if (cell != null && cell.getRichStringCellValue().getString().equalsIgnoreCase("Packs")
                        || cell != null && cell.getRichStringCellValue().getString().equalsIgnoreCase("Prds.")
                        || cell != null && cell.getRichStringCellValue().getString().equalsIgnoreCase("ATC IV"))
                    count++;

                r = null;
                cell = null;
            }
        } catch (NullPointerException ex) {
            Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, String.format("Index: %d", index), ex);

            Util.showException("Ocurrio un error opteniendo la ultima fila del archivo", ex);
        }
    }

    return count;
}

From source file:DB.TopStockDescriptionList.java

public static void writeToFileExcel(String fileName, List<TopStockDescription> tsdData) {
    int SHORTNAME = 1;
    int LONGNAME = 0;
    int TEXT = 2;

    try {/*w  w w  . j  a v a  2 s  . co m*/
        FileInputStream fileIn = new FileInputStream(fileName);

        Workbook wb = WorkbookFactory.create(fileIn);
        Sheet sheet = wb.getSheetAt(0);

        for (TopStockDescription tsd : tsdData) {
            if (tsd.getContentText().isEmpty())
                continue;
            //?  ? or Create
            int rowInt = 1;
            while (true) {
                Row row = sheet.getRow(rowInt);

                if (row == null) {// THE END OF THE ROW not found
                    //Create
                    row = sheet.createRow(rowInt);
                    Cell cellShortName = row.createCell(SHORTNAME);
                    cellShortName.setCellType(Cell.CELL_TYPE_STRING);
                    cellShortName.setCellValue(tsd.getShortName());

                    String longName = tsd.getShortName();
                    int endIndex = tsd.getContentText().indexOf(tsd.getShortName());
                    if (endIndex > 0 && endIndex < tsd.getContentText().length())
                        longName = tsd.getContentText().substring(0, endIndex - 1).trim();

                    Cell cellLongName = row.createCell(LONGNAME);
                    cellLongName.setCellType(Cell.CELL_TYPE_STRING);
                    cellLongName.setCellValue(longName);

                    Cell cellText = row.createCell(TEXT);
                    cellText.setCellType(Cell.CELL_TYPE_STRING);
                    cellText.setCellValue(tsd.getContentText());
                    break;
                }
                Cell cellShortName = row.getCell(SHORTNAME);
                String shortName = cellShortName.getRichStringCellValue().getString();
                if (shortName.equalsIgnoreCase(tsd.getShortName())) {//If Match, Update
                    //Cell cellLongName = row.getCell(LONGNAME);
                    //cellLongName.setCellType(Cell.CELL_TYPE_STRING);
                    //cellLongName.setCellValue(tsd.getLongName());

                    Cell cellText = row.getCell(TEXT);
                    cellText.setCellType(Cell.CELL_TYPE_STRING);
                    cellText.setCellValue(tsd.getContentText());
                    break;
                }
                rowInt++;
            }
        }

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        fileOut.close();
        fileIn.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception ex) {
        Logger.getLogger(TopStockDescriptionList.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:de.fhg.fokus.odp.portal.uploaddata.service.Worker.java

/**
 * loop through all Cells and rows. Firstly, add correct keys to strings.
 * Secondly, parse corresponding value into correct json and add this
 * dataset to ckan via middleware.//  w  w w. ja  v  a  2  s .c om
 * 
 * @param args
 * @throws Exception
 * 
 * @return a String of dataset indices, which were not uploaded.
 */
public String readXlsx() {
    final StringBuilder errormessage = new StringBuilder("");
    final StringBuilder resourceStringBuilder = new StringBuilder("[{");
    final StringBuilder extrasStringBuilder = new StringBuilder("{");

    HashMap<String, String> map = new HashMap<String, String>();
    ArrayList<String> strings = new ArrayList<String>();
    XSSFWorkbook workBook = null;
    try {
        workBook = new XSSFWorkbook(uploadFolder + "file.xlsx");
    } catch (IOException e1) {
        e1.printStackTrace();
    }
    int counter = 0;
    XSSFSheet sheet = workBook.getSheetAt(0);
    for (Row row : sheet) {
        for (Cell cell : row) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                String value = cell.getRichStringCellValue().getString();
                // first row, add value to strings
                if (counter == 0) {
                    if (!value.startsWith("resources:") && !value.startsWith("extras:")) {
                        map.put(value, null);
                    }

                    strings.add(value);
                    break;
                }
                // compute columnIndex for later use
                int columnIndex = cell.getColumnIndex();
                // compute parameter for later use in if-statements
                String parameter = strings.get(columnIndex);
                handleString(resourceStringBuilder, extrasStringBuilder, map, value, parameter);
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // is a date;
                    parameter = strings.get(cell.getColumnIndex());
                    handleDate(map, parameter, cell, extrasStringBuilder);
                } else {
                    // is a number;
                    parameter = strings.get(cell.getColumnIndex());
                    handleNumber(map, parameter, cell, extrasStringBuilder);
                }
                break;
            default:
                break;
            }
        }
        // finish extras and resources
        finishParseResource(resourceStringBuilder);
        finishParseExtras(extrasStringBuilder);

        Validator.checkTagAndGroupsForEmptyValues(map);
        Validator.setlicenseAndNameToLowerCase(map);

        // add resources and extras to map
        map.put("resources", resourceStringBuilder.toString());
        map.put("extras", extrasStringBuilder.toString());

        createDataSet(errormessage, gw, map, counter);

        ++counter;
        // reset resourceStringBuilder and extrasStringBuilder
        resetStringBuilder(resourceStringBuilder, extrasStringBuilder);

        // reset map
        map.clear();
    }

    if (errormessage.toString().equalsIgnoreCase("")) {
        // no errors
        return errormessage.toString();
    } else {
        // return list of dataset indices
        return errormessage.substring(0, errormessage.length() - 1);
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ExcelImportUtilities.java

License:Open Source License

/**
 * Returns cell content or cell content being referenced in a formula as String. Also reads
 * numeric cells, but these must not be referenced (a poi formulaCell). A cell being NULL or empty
 * returns "". For formulaCells that are not string Formulas, an empty String is returned, too.
 * Directly using poi's getRichStringCellValue() on a HSSFCell being NULL would throw an
 * exception. Cell in row is specified by the headline map and the key. Warning: Does not support
 * Dates (see currentRowCoreKeyToCell)/*  w w  w . j  a  va2 s. c o m*/
 * 
 * @param cell
 *          a cell
 * @return a String from cell or an referenced cell, leading and ending white space removed
 */
public static String contentAsString(Cell cell, ProcessingLog processingLog) {
    if (cell == null) {
        return "";
    }
    switch (cell.getCellType()) {

    case Cell.CELL_TYPE_STRING: // for richStringsCells and formulaCells
        return cell.getRichStringCellValue().getString().trim();

    case Cell.CELL_TYPE_FORMULA: // for formulaCells / Hyperlinks(id)
        int type = cell.getCachedFormulaResultType();
        String result = "";
        switch (type) {
        case Cell.CELL_TYPE_BOOLEAN:
            result = String.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            result = String.valueOf(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            result = String.valueOf(cell.getRichStringCellValue());
            break;
        default:
            break;
        }
        if (result.isEmpty()) {
            processingLog.warn(
                    "Cell [{0}]  Cannot get a String from a numeric cell being referenced by a formula: {1}",
                    getCellRef(cell), cell.getCellFormula());
        }
        return result;

    case Cell.CELL_TYPE_NUMERIC:
        return getNumericCellContentAsString(cell, processingLog);

    default:
        return cell.toString().trim();
    }

}

From source file:domain.Excel.java

private static void showExelData(List sheetData) {

        ////from  w  w  w.j  av a 2 s.  c om
        // Iterates the data and print it out to the console.
        //
        for (int i = 0; i < sheetData.size(); i++) {

            List list = (List) sheetData.get(i);
            for (int j = 0; j < list.size(); j++) {

                Cell cell = (Cell) list.get(j);

                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

                    System.out.print(cell.getNumericCellValue());
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {

                    System.out.print(cell.getRichStringCellValue());
                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

                    System.out.print(cell.getBooleanCellValue());
                }
                if (j < list.size() - 1) {
                    System.out.print(", ");
                }
            }
            System.out.println("");
        }
    }