Get the cell value as how it was presented in excel

by

Excel stores some cells as strings, but most as numbers with special formatting rules applied to them. What you’ll need to do is have those formatting rules run against the numeric cells, to produce strings that look like they do in Excel.

Luckily, Apache POI has a class to do just that – DataFormatter

All you need to do is something like:

 Workbook wb = WorkbookFactory.create(new File("myfile.xls"));
 DataFormatter df = new DataFormatter();

 Sheet s = wb.getSheetAt(0);
 Row r1 = s.getRow(0);
 Cell cA1 = r1.getCell(0);

 String asItLooksInExcel = df.formatCellValue(cA1);

Doesn’t matter what the cell type is, DataFormatter will format it as best it can for you, using the rules applied in Excel

More Related Contents:

  1. What does the statement Object[][] data = new Object [][] mean?
  2. Get Cell Value from Excel Sheet with Apache Poi
  3. Writing a large resultset to an Excel file using POI
  4. Best language to parse extremely large Excel 2007 files [closed]
  5. How to read Excel cell having Date with Apache POI?
  6. Apache POI – FileInputStream works, File object fails (NullPointerException)
  7. How to set axis’ labels in a Line Chart using Apache Poi
  8. Replacing a text in Apache POI XWPF
  9. Removing a row from an Excel sheet with Apache POI HSSF
  10. Apache POI autoSizeColumn Resizes Incorrectly
  11. What is the better API to Reading Excel sheets in java – JXL or Apache POI [closed]
  12. How to get an Excel Blank Cell Value in Apache POI?
  13. Getting Exception(org.apache.poi.openxml4j.exception – no content type [M1.13]) when reading xlsx file using Apache POI?
  14. Password Protected Excel File
  15. Apache POI Streaming (SXSSF) for Reading
  16. Exception when writing to the xlsx document several times using apache poi 3.7
  17. Apache-POI sorting rows in excel
  18. Creating a Table of Contents for a XWPFDocument with page numbers’ indication
  19. Java POI : How to read Excel cell value and not the formula computing it?
  20. How to convert HSSFWorkbook to XSSFWorkbook using Apache POI?
  21. Convert Word to HTML with Apache POI
  22. apache POI java.lang.NoClassDefFoundError: org/apache/commons/compress/archivers/zip/ZipFile
  23. How can I change charts generated by apache poi to not use smoothed lines and show empty cells as gaps?
  24. How to check if an excel cell is empty using Apache POI?
  25. Java POI the supplied data appears to be in the Office 2007+ XML
  26. Determine MS Excel file type with Apache POI
  27. Limitation while generating excel drop down list with Apache POI
  28. Make column read-only using apache poi
  29. Reading data from xlsx with Apache POI’s SXSSFSheet
  30. POI DataFormatter returns 2 digits year instead of 4 digits year for date cells [duplicate]

Leave a Comment