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

Introduction

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

Prototype

String getStringCellValue();

Source Link

Document

Get the value of the cell as a string

For numeric cells we throw an exception.

Usage

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.SummaryReportGenerator.java

License:Apache License

/**
 * Creates Leveraging Sheet//from w  w w  . j a  va  2 s. c  o m
 */
private void createCostsSheet(Workbook p_workbook, Sheet p_sheet, ReportSearchOptions p_options,
        Map<String, ReportWordCount> p_wordCounts) throws Exception {
    int rowLen = p_sheet.getPhysicalNumberOfRows();
    int colLen = p_sheet.getRow(2).getPhysicalNumberOfCells();
    int wordTotalCol = colLen - 2;
    int row = ROWNUMBER, column = colLen - 1;
    int costCol;
    Map<String, Double> p_ratesMap = null;
    for (int r = 2; r < rowLen + ROWNUMBER; r++) {
        Row theRow = getRow(p_sheet, r);
        theRow.removeCell(getCell(theRow, column));
    }
    p_sheet.removeColumnBreak(column);
    // Rates Columns
    for (int dis = column - 1; column < colLen + dis - 2; column++) {
        Cell cell_From = p_sheet.getRow(row).getCell(column - dis);
        Cell cell_To = getCell(p_sheet.getRow(row), column);
        cell_To.setCellValue(cell_From.getStringCellValue());
        cell_To.setCellStyle(cell_From.getCellStyle());
        p_sheet.setColumnWidth(column, p_sheet.getColumnWidth(column - dis));
        // Adds Rates for Match Type
        for (int rateRow = row + 1; rateRow <= rowLen; rateRow++) {
            String matchType = p_sheet.getRow(ROWNUMBER).getCell(column).getStringCellValue();
            String targetLocale = p_sheet.getRow(rateRow).getCell(0).getStringCellValue();
            double rate = getRate(matchType, targetLocale, p_ratesMap);
            addNumberCell(p_sheet, column, rateRow, rate, getMoneyStyle(p_workbook));
        }
    }

    // Cost Columns Head
    costCol = column;
    p_sheet.setColumnWidth(column, 20 * 256);
    Cell cell_CostWithLeveraging = getCell(getRow(p_sheet, row), column++);
    cell_CostWithLeveraging.setCellValue(bundle.getString("lb_report_costWithLeveraging"));
    cell_CostWithLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook));

    p_sheet.setColumnWidth(column, 20 * 256);
    Cell cell_CostNoLeveraging = getCell(getRow(p_sheet, row), column++);
    cell_CostNoLeveraging.setCellValue(bundle.getString("lb_report_costNoLeveraging"));
    cell_CostNoLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook));

    p_sheet.setColumnWidth(column, 15 * 256);
    Cell cell_Savings = getCell(getRow(p_sheet, row), column++);
    cell_Savings.setCellValue(bundle.getString("lb_savings"));
    cell_Savings.setCellStyle(getHeaderOrangeStyle(p_workbook));

    Cell cell_Percent = getCell(getRow(p_sheet, row), column++);
    cell_Percent.setCellValue("%");
    cell_Percent.setCellStyle(getHeaderOrangeStyle(p_workbook));
    // Cost Columns Data
    for (row = ROWNUMBER + 1; row < (rowLen + ROWNUMBER); row++) {
        String leveragingForm = getCostWithLeveraging(1, wordTotalCol - 1, wordTotalCol, (row + 1));
        String noLeveragingForm = getColumnName(wordTotalCol) + (row + 1) + "*"
                + getColumnName(wordTotalCol + 5) + (row + 1);
        String savingForm = getColumnName(costCol + 1) + (row + 1) + "-" + getColumnName(costCol) + (row + 1);
        String percent = getColumnName(costCol + 2) + (row + 1) + "/" + getColumnName(costCol + 1) + (row + 1);

        Row theRow = getRow(p_sheet, row);
        Cell cell_Leveraging = getCell(theRow, costCol);
        cell_Leveraging.setCellFormula(leveragingForm);
        cell_Leveraging.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_NoLeveraging = getCell(theRow, costCol + 1);
        cell_NoLeveraging.setCellFormula(noLeveragingForm);
        cell_NoLeveraging.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_Saving = getCell(theRow, costCol + 2);
        cell_Saving.setCellFormula(savingForm);
        cell_Saving.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_PercentData = getCell(theRow, costCol + 3);
        cell_PercentData.setCellFormula(percent);
        cell_PercentData.setCellStyle(getPercentStyle(p_workbook));
    }

    if (rowLen > 1) {
        row = rowLen + 1;
        column = 1;
        for (; column < colLen - 1; column++) {
            Cell cell_Total = getCell(getRow(p_sheet, row), column);
            cell_Total.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column));
            cell_Total.setCellStyle(getHeaderOrangeStyle(p_workbook));
        }
        for (; column < costCol; column++) {
            Cell cell = getCell(getRow(p_sheet, row), column);
            cell.setCellValue("");
            cell.setCellStyle(getHeaderOrangeStyle(p_workbook));
        }

        // Summary Cost Columns
        Cell cell_SumLeveraging = getCell(getRow(p_sheet, row), column);
        cell_SumLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumLeveraging.setCellStyle(getMoneySumStyle(p_workbook));

        Cell cell_SumNoLeveraging = getCell(getRow(p_sheet, row), column);
        cell_SumNoLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumNoLeveraging.setCellStyle(getMoneySumStyle(p_workbook));

        Cell cell_SumSaving = getCell(getRow(p_sheet, row), column);
        cell_SumSaving.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumSaving.setCellStyle(getMoneySumStyle(p_workbook));

        String percent = getColumnName(column - 1) + (row + 1) + "/" + getColumnName(column - 2) + (row + 1);
        Cell cell_AvgPercent = getCell(getRow(p_sheet, row), column);
        cell_AvgPercent.setCellFormula(percent);
        cell_AvgPercent.setCellStyle(getPercentSumStyle(p_workbook));
    }
}

---

From source file:com.globalsight.util.ExcelUtil.java

License:Apache License

public static String getCellValue(Sheet sheet, int row, int col) {
    String value = "";
    if (sheet == null || row < 0 || col < 0)
        return "";

    Row rowData = sheet.getRow(row);/*ww w . j  a va2s.c o  m*/
    if (rowData == null)
        return "";
    Cell cell = rowData.getCell(col);
    if (cell == null)
        return "";
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        value = String.valueOf((int) cell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;

    default:
        value = cell.toString();
    }

    return value;
}

---

From source file:com.google.gdt.handler.impl.ExcelHandler.java

License:Open Source License

/**
 * /*from   w  w w. j a v a  2 s .  co m*/
 * @param inputFile
 * @throws IOException
 * @throws InvalidFormatException
 */
@Override
public void handle(String inputFile, ProgressLevel pLevel) throws IOException, InvalidFormatException {
    String outPutFile = getOuputFileName(inputFile);
    OutputStream outputStream = new FileOutputStream(outPutFile);
    InputStream is = new FileInputStream(inputFile);

    Workbook wb = WorkbookFactory.create(is);
    List<Sheet> sheets = getSheets(wb);

    pLevel.setTrFileName(outPutFile);

    //iterate over sheet
    for (int index = 0; index < sheets.size(); index++) {
        Sheet sheet = sheets.get(index);

        if (sheets.size() > 1) {
            pLevel.setString("Translating sheet " + (index + 1) + "/" + sheets.size());
        }

        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        int rowCount = lastRowNum - firstRowNum;
        // check for empty sheet, don't perform any operation
        if (rowCount == 0) {
            continue;
        }
        pLevel.setValue(0);
        pLevel.setMaxValue(rowCount);
        pLevel.setStringPainted(true);

        int pBarUpdate = 0;
        //iterate over row
        for (Row row : sheet) {
            //iterate over cells
            for (Cell cell : row) {
                if (isInterrupted) {
                    outputStream.close();
                    new File(outPutFile).delete();
                    pLevel.setString("cancelled");
                    return;
                }

                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    String inputText = cell.getStringCellValue();
                    String translatedTxt = inputText;
                    try {
                        translatedTxt = translator.translate(inputText);
                        cell.setCellValue(translatedTxt);
                    } catch (Exception e) {
                        logger.log(Level.SEVERE,
                                "Input File : " + inputFile + " cannot translate the text : " + inputText, e);
                        continue;
                    }
                }
            } //cell iteration ends
            pBarUpdate++;
            pLevel.setValue(pBarUpdate);
        } //row iteration ends
        pLevel.setValue(rowCount);
    }
    pLevel.setString("done");

    wb.write(outputStream);
    outputStream.close();
}

---

From source file:com.gsecs.GSECSFrame.java

private Object getCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();

    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();

    case Cell.CELL_TYPE_NUMERIC:
        DataFormatter df = new DataFormatter();
        String stringCellValue = df.formatCellValue(cell);
        return stringCellValue;
    }//  w w w .j  a  v  a 2  s  .  c  o  m

    return null;
}

---

From source file:com.hauldata.dbpa.file.book.XlsxSourceSheet.java

License:Apache License

private Object fromXLSX(Cell cell) {
    if (cell == null) {
        return null;
    }/*ww w. j  a  v  a  2s  . co  m*/

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_NUMERIC:
        double numericValue = cell.getNumericCellValue();
        return DateUtil.isCellDateFormatted(cell) ? DateUtil.getJavaDate(numericValue) : (Double) numericValue;
    case Cell.CELL_TYPE_STRING:
    default:
        return cell.getStringCellValue();
    }
}

---

From source file:com.haulmont.mp2xls.helper.XlsHelper.java

License:Apache License

public static Object getCellValue(Cell cell) {
    if (cell == null) {
        return null;
    }/*from w w  w.  ja v a  2 s  .c o  m*/

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_STRING:
        String formattedCellValue = cell.getStringCellValue().replace(String.valueOf(NON_BREAKING_SPACE), " ")
                .trim();
        return formattedCellValue.isEmpty() ? null : formattedCellValue;
    case Cell.CELL_TYPE_NUMERIC:
        if (isDateCell(cell)) {
            return cell.getDateCellValue();
        }

        Double numericCellValue = cell.getNumericCellValue();
        return isAlmostInt(numericCellValue) ? numericCellValue.intValue() : numericCellValue;
    case Cell.CELL_TYPE_FORMULA:
        /*
        formattedCellValue = cell.getStringCellValue();
        if (formattedCellValue != null) {
            formattedCellValue = formattedCellValue.replace(String.valueOf(NON_BREAKING_SPACE), " ").trim();
        }
        */
        return getFormulaCellValue(cell/*, formattedCellValue*/);
    default:
        throw new CellTypeIsNotSupportedException(cell);
    }
}

---

From source file:com.haulmont.mp2xls.helper.XlsHelper.java

License:Apache License

protected static Object getFormulaCellValue(Cell cell/*, String formattedCellValue*/) {
    switch (cell.getCachedFormulaResultType()) {
    case Cell.CELL_TYPE_NUMERIC:
        return cell.getNumericCellValue();
    case Cell.CELL_TYPE_STRING:
        String formattedCellValue = cell.getStringCellValue();
        if (formattedCellValue != null) {
            formattedCellValue = formattedCellValue.replace(String.valueOf(NON_BREAKING_SPACE), " ").trim();
            if (formattedCellValue.isEmpty())
                return null;
        }/* w  w  w  . j a va  2s  . com*/
        return formattedCellValue;
    default:
        throw new IllegalStateException(
                String.format("Formula cell type '%s' is not supported", cell.getCachedFormulaResultType()));
    }
}

---

From source file:com.haulmont.mp2xls.helper.XlsHelper.java

License:Apache License

public static Object getCellValue(Cell cell, Boolean forceToString) {
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return null;
    }//from   w ww  .  j a va  2  s .com

    cell.setCellType(Cell.CELL_TYPE_STRING);
    return cell.getStringCellValue().replace(String.valueOf(NON_BREAKING_SPACE), " ").trim();
}

---

From source file:com.heimaide.server.common.utils.excel.ImportExcel.java

License:Open Source License

/**
 * ??/*from  ww w .j  a va2  s . co m*/
 * @param row ?
 * @param column ???
 * @return ?
 */
public Object getCellValue(Row row, int column) {
    Object val = "";
    try {
        Cell cell = row.getCell(column);
        if (cell != null) {
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                val = cell.getNumericCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                val = cell.getStringCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                try {
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        val = cell.getDateCellValue();
                    } else {
                        val = String.valueOf(cell.getNumericCellValue());
                    }
                } catch (IllegalStateException e) {
                    val = String.valueOf(cell.getRichStringCellValue());
                }
            } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                val = cell.getBooleanCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                val = cell.getErrorCellValue();
            }
        }
    } catch (Exception e) {
        return val;
    }
    return val;
}

---

From source file:com.helger.poi.excel.ExcelReadUtils.java

License:Apache License

/**
 * Return the best matching Java object underlying the passed cell.<br>
 * Note: Date values cannot be determined automatically!
 * /*from   w  ww .j av  a2s.co m*/
 * @param aCell
 *        The cell to be queried. May be <code>null</code>.
 * @return <code>null</code> if the cell is <code>null</code> or if it is of
 *         type blank.
 */
@Nullable
public static Object getCellValueObject(@Nullable final Cell aCell) {
    if (aCell == null)
        return null;

    final int nCellType = aCell.getCellType();
    switch (nCellType) {
    case Cell.CELL_TYPE_NUMERIC:
        return _getAsNumberObject(aCell.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
        return aCell.getStringCellValue();
    case Cell.CELL_TYPE_BOOLEAN:
        return Boolean.valueOf(aCell.getBooleanCellValue());
    case Cell.CELL_TYPE_FORMULA:
        final int nFormulaResultType = aCell.getCachedFormulaResultType();
        switch (nFormulaResultType) {
        case Cell.CELL_TYPE_NUMERIC:
            return _getAsNumberObject(aCell.getNumericCellValue());
        case Cell.CELL_TYPE_STRING:
            return aCell.getStringCellValue();
        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.valueOf(aCell.getBooleanCellValue());
        default:
            throw new IllegalArgumentException(
                    "The cell formula type " + nFormulaResultType + " is unsupported!");
        }
    case Cell.CELL_TYPE_BLANK:
        return null;
    default:
        throw new IllegalArgumentException("The cell type " + nCellType + " is unsupported!");
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »