Win a copy of Terraform in Action this week in the Cloud forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Apache POI: Empty cells converted to numbers

 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm using apache poi
While i'm designing my application i found it read the empty cells as numbers
for example an empty cell is read as 5.332664015E9
so i checked and found this number values is in the place of the empty cells from the beginning in the iterator in this code



I checked the excel and I made sure there isn't any formulas or anything in the empty cells.
so what causes that and how to fix this problem?
 
Rancher
Posts: 4801
50
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you show us the code that reads the cells in question and logs the value?

Empty cells in Excel usually (not always) don't exist.  That is, there is no Cell there to read.
 
Abdullah Attia
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Can you show us the code that reads the cells in question and logs the value?

Empty cells in Excel usually (not always) don't exist.  That is, there is no Cell there to read.



I checked the iterator variable in the debug mode after the three shown code lines in the post, before assigning the cell values, and there were already numbers in the place of empty cells

but this is the method's full code

 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In that loop (the Iterator<Cell> one) log out the values for CellType, the cell coordinates (row and column), and the value.
Since it's only numeric cells that are the issue you can limit that to the CELL_TYPE_NUMERIC case.

Don't do this with the debugger...you want a "physical" copy of the logged data so you can then post it here, as well as look in the Excel file yourself.
 
Bartender
Posts: 3323
86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
BTW what version of POI are you using?
I ask because the getCellType() method and Cell.CELL_TYPE_XXXX constants are deprecated since version 3.15.
 
Abdullah Attia
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
First: very sorry, the application wasn't replacing the empty cells with null values while reading it, it was skipping the empty cells entirely and adding the next cell value to the empty cell.
Second: the problem are solved by changing the following loop




to this new code




 
Tony Docherty
Bartender
Posts: 3323
86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Congratulations on solving the problem and thanks for posting the solution.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic