Getting the cell contents

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Getting the cell contents

Hehabr
This post was updated on .
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();
        }
    }
   
}
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Getting the cell contents

Jörn Franke
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 <[hidden email]> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Getting the cell contents

Javen O'Neal-2
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" <[hidden email]> 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 <[hidden email]> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Getting the cell contents

Robert Cochran
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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]> 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: [hidden email]
> > For additional commands, e-mail: [hidden email]
> >
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Getting the cell contents

Hehabr
In reply to this post by Javen O'Neal-2
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...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Getting the cell contents

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

}
Loading...