Get Cell Value from Excel Sheet with Apache Poi?

May be by:- for(Row row : sheet) { for(Cell cell : row) { System.out. Print(cell. GetStringCellValue()); } } For specific type of cell you can try: switch (cell.getCellType()) { case Cell.

CELL_TYPE_STRING: cellValue = cell. GetStringCellValue(); break; case Cell. CELL_TYPE_FORMULA: cellValue = cell.getCellFormula(); break; case Cell.

CELL_TYPE_NUMERIC: if (DateUtil. IsCellDateFormatted(cell)) { cellValue = cell. GetDateCellValue().toString(); } else { cellValue = Double.

ToString(cell. GetNumericCellValue()); } break; case Cell. CELL_TYPE_BLANK: cellValue = ""; break; case Cell.

CELL_TYPE_BOOLEAN: cellValue = Boolean. ToString(cell. GetBooleanCellValue()); break; }.

You have to use the FormulaEvaluator, as shown here. This will return a value that is either the value present in the cell or the result of the formula if the cell contains such a formula : FileInputStream fis = new FileInputStream("/somepath/test. Xls"); Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.

Xls") Sheet sheet = wb. GetSheetAt(0); FormulaEvaluator evaluator = wb. GetCreationHelper().

CreateFormulaEvaluator(); // suppose your formula is in B3 CellReference cellReference = new CellReference("B3"); Row row = sheet. GetRow(cellReference.getRow()); Cell cell = row. GetCell(cellReference.getCol()); if (cell!

=null) { switch (evaluator. EvaluateFormulaCell(cell)) { case Cell. CELL_TYPE_BOOLEAN: System.out.

Println(cell. GetBooleanCellValue()); break; case Cell. CELL_TYPE_NUMERIC: System.out.

Println(cell. GetNumericCellValue()); break; case Cell. CELL_TYPE_STRING: System.out.

Println(cell. GetStringCellValue()); break; case Cell. CELL_TYPE_BLANK: break; case Cell.

CELL_TYPE_ERROR: System.out. Println(cell. GetErrorCellValue()); break; // CELL_TYPE_FORMULA will never occur case Cell.

CELL_TYPE_FORMULA: break; } } if you need the exact contant (ie the formla if the cell contains a formula), then this is shown here. Edit : Added a few example to help you. First you get the cell (just an example) Row row = sheet.

GetRow(rowIndex+2); Cell cell = row. GetCell(1); If you just want to set the value into the cell using the formula (without knowing the result) : String formula ="ABS((1-E"+(rowIndex + 2)+"/D"+(rowIndex + 2)+")*100)"; cell. SetCellFormula(formula); cell.

SetCellStyle(this. ValueRightAlignStyleLightBlueBackground); if you want to change the message if there is an error in the cell, you have to change the formula to do so, something like IF(ISERR(ABS((1-E3/D3)*100));"N/A"; ABS((1-E3/D3)*100)) (this formula check if the evaluation return an error and then display the string "N/A", or the evaluation if this is not an error). If you want to get the value corresponding to the formula, then you have to use the evaluator.

Hope this help, Guillaume.

As far as I know, you don't "have to" use FormulaEvaluator, if you don't have a formula in the cell :) If your cells contain text or numeric values you can get them by cell. GetStringCellValue(); or cell. GetNumericCellValue();.

Your answer applies to situations where the cell contains a formula, there is no indication that's the case for the OP – posdef Apr 7 '11 at 9:24 @posdef : you're right, I don't "have to". However, if the cell does not contains a formula, the method "evaluateFormulaCell" still returns the value. I find it simpler to call this method than to perform special condition check, since I then always get the value of the cell.

However, this is indeed void if you're sure that you don't have a formula. – PATRY Apr 7 '11 at 9:38 @PATRY: you have a good point... I was pointing it out just to make sure there are no misunderstandings. – posdef Apr 7 '11 at 9:45 @PARTY.. Thanks for your help But if there is a cell type of formula and after evaluation of that that cell formula it contain error value like DIV/0!

Error than how can I know that cell contain this error and how can I replace this with 'N/A'? – water Apr 7 '11 at 9:58 1 in case of an error, the type of the cell is Cell. CELL_TYPE_ERROR.

You can get the value of the error by the getErrorCellValue() method. Affter that, it depends onn your needs : if you just need an standard error message ("N/A"), then just chek the nature of the content. If you need different error messages, the use a HashMap mapping an excel error message to your own (this is just an exemple)... – PATRY Apr 7 '11 at 10:29.

Here's a set of guides that can get you started.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.