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
>