You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Hehabr <he...@web.de> on 2017/08/02 21:41:40 UTC

Getting the cell contents

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


Re: Getting the cell contents

Posted by Hehabr <he...@web.de>.
It works!

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;

public class POITestRB {

    public static void main(String[] args) throws IOException {
        FileInputStream fis = new FileInputStream("C:/Test/1.xlsm");
        XSSFWorkbook workbook = new XSSFWorkbook(fis);

        gettingTheCellContents(workbook, "D5");

        fis.close();
        workbook.close();
    }

    private static void gettingTheCellContents(XSSFWorkbook workbook, String
cellId) {

        XSSFSheet sheet = workbook.getSheetAt(1);
        CellReference ref = new CellReference(cellId);
        int row = ref.getRow();
        int col = ref.getCol();
        Cell cell = sheet.getRow(row).getCell(col);

        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("Formula is: " + cell.getCellFormula());
                switch(cell.getCachedFormulaResultType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.println("1. case Cell.CELL_TYPE_NUMERIC
--> Last evaluated as: " + cell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.println("2. case Cell.CELL_TYPE_STRING
--> Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
                        break;
                }
                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-tp5728401p5728406.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


Re: Getting the cell contents

Posted by Hehabr <he...@web.de>.
private static void gettingTheCellContents(CellReference cellRef, Cell cell,
XSSFWorkbook workbook) {

        DataFormatter formatter = new DataFormatter();
        FormulaEvaluator formulaEvaluator =
workbook.getCreationHelper().createFormulaEvaluator();
        formatter.formatCellValue(cell,formulaEvaluator);

        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();
        }
    }

------------------------------------------

Output is :
C34

But I need cell-value as output...



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Getting-the-cell-contents-tp5728401p5728405.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


Re: Getting the cell contents

Posted by Robert Cochran <r2...@gmail.com>.
I'm following this thread and the code posted by Hehabr to help myself
learn the POI. I'm going very slowly, doing this as time allows for me. I
have not yet put all of Hehabr's code (in POITestEva) in my own
corresponding Java project that tries to track Hehabr's. Rather, I'm adding
methods slowly so I can study them. I'm using POI-3.17-Beta1 for this.

I notice that Hehabr appears to be opening and writing to an Excel *.xlsm
file which could contain embedded macros. I'm not sure if this has an
effect on the POI.

I have been playing with the HOWTO and Quick Guide examples in other
contexts, also to learn, and I agree that the DataFormatter is very nice. I
played with it in a separate project. I have not yet added it to Hehabr's
code but I'll likely do that. Also I will learn to evaluate formulae.

Best of luck to you, Hehabr!

Thanks a ton

Bob


On Wed, Aug 2, 2017 at 6:31 PM, Javen O'Neal <on...@apache.org> wrote:

> Thanks for the suggestion, Jörn!
>
> Hehabr, you may also want to use a DataFormatter to avoid your switch
> statement.
> https://poi.apache.org/spreadsheet/quick-guide.html#
> Getting+the+cell+contents
>
> DataFormatter formatter = new DataFormatter();
>
> // 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);
>
> String text = formatter.formatCellValue(cell);
>
>
> On Aug 2, 2017 3:24 PM, "Jörn Franke" <jo...@gmail.com> wrote:
>
> 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 <he...@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
> >
>

Re: Getting the cell contents

Posted by Javen O'Neal <on...@apache.org>.
Thanks for the suggestion, Jörn!

Hehabr, you may also want to use a DataFormatter to avoid your switch
statement.
https://poi.apache.org/spreadsheet/quick-guide.html#Getting+the+cell+contents

DataFormatter formatter = new DataFormatter();

// 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);

String text = formatter.formatCellValue(cell);


On Aug 2, 2017 3:24 PM, "Jörn Franke" <jo...@gmail.com> wrote:

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 <he...@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
>

Re: Getting the cell contents

Posted by Jörn Franke <jo...@gmail.com>.
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 <he...@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
>