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 <one...@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" <jornfra...@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 <heh...@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-unsubscr...@poi.apache.org > > For additional commands, e-mail: user-h...@poi.apache.org > > >
