poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jörn Franke <jornfra...@gmail.com>
Subject Re: Getting the cell contents
Date Wed, 02 Aug 2017 22:24:38 GMT
You need to evaluate the formula in case no cached value is stored. 
formulaEvaluator = workbook.getCreationHelper().createFormulaEv
> formatter.formatCellValue(cell,formulaEvaluator)


C34 could also be empty

> On 2. Aug 2017, at 23:41, Hehabr <hehabr@web.de> wrote:
> 
> Output in Console : 
> ....................
> D5 - C34
> C34
> ....................
> Cell D5 is formula-cell with formula: =C34
> Why is output - formula itself ?
> How do I make output - Cell-value?
> 
> import org.apache.poi.ss.usermodel.*;
> import org.apache.poi.ss.util.CellReference;
> import org.apache.poi.xssf.usermodel.XSSFSheet;
> import org.apache.poi.xssf.usermodel.XSSFWorkbook;
> import java.io.*;
> 
> public class POITest {
> 
>    public static void main(String[] args) throws IOException {
>        FileInputStream fis = new FileInputStream("C:/Test/1.xlsm");
>        XSSFWorkbook workbook = new XSSFWorkbook(fis);
>        XSSFSheet sheet = workbook.getSheetAt(1);
>        CellReference ref = new CellReference("D5");
>        int row = ref.getRow();
>        int col = ref.getCol();
>        Cell cell = sheet.getRow(row).getCell(col);
>        gettingTheCellContents(ref, cell);
>        fis.close();
>        workbook.close();
>    }
> 
>    // https://poi.apache.org/spreadsheet/quick-guide.html#CellContents
>    private static void gettingTheCellContents(CellReference cellRef, Cell
> cell) {
> 
>        DataFormatter formatter = new DataFormatter();
>        System.out.print(cellRef.formatAsString());
>        System.out.print(" - ");
> 
>        // get the text that appears in the cell by getting the cell value
> and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
>        String text = formatter.formatCellValue(cell);
>        System.out.println(text);
> 
>        // Alternatively, get the value and format it yourself
>        switch (cell.getCellTypeEnum()) {
>            case STRING:
> 
> System.out.println(cell.getRichStringCellValue().getString());
>                break;
>            case NUMERIC:
>                if (DateUtil.isCellDateFormatted(cell)) {
>                    System.out.println(cell.getDateCellValue());
>                } else {
>                    System.out.println(cell.getNumericCellValue());
>                }
>                break;
>            case BOOLEAN:
>                System.out.println(cell.getBooleanCellValue());
>                break;
>            case FORMULA:
>                System.out.println(cell.getCellFormula());
>                break;
>            case BLANK:
>                System.out.println();
>                break;
>            default:
>                System.out.println();
>        }
>    }
> 
> }
> 
> 
> 
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Getting-the-cell-contents-tp5728401.html
> Sent from the POI - User mailing list archive at Nabble.com.
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
> 

Mime
  • Unnamed multipart/alternative (inline, 7-Bit, 0 bytes)
View raw message