File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

POI Converting Long Number to Sciebtific Notarion

 
Pat Flickner
Ranch Hand
Posts: 173
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To start off, I Googled this and couldn't find any good answer; most of the time, the questioner never received a response or they were told to go to the Apache POI website, and I couldn't find any answers there, either. I am uploading a spreadsheet and processing it through a Java program using Apache POI.

The problem I've encountered is that we have a column called source_cd that can contain any 10-character combination of numbers and characters. The issue we have comes into play when a user enters all numbers for a cell in this column. POI is converting the number to scientific notation, so a number like 1234567890 becomes 1.29532E9. I know the work-around is to put a tic in front of the number to force it to be read as text, but we don't want the users to have to do this. A number, even if treated as a double, should be read as a number and not converted. Is there any way to force this?

Thanks very much for any help.
 
Ulf Dittmer
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To ask the obvious: Is the cell type set to string (and not to number)?
 
Pat Flickner
Ranch Hand
Posts: 173
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is general formatting, so POI automatically reads a number as a numeric field because even if it's general format, Excel automatically converts numbers not specifically formatted as text (read the tic (') mark) as numeric fields. So yes, it is a numeric field if there's a number in it. And if it's a number, POI treats it as a double regardless of its actual state.
 
Pat Flickner
Ranch Hand
Posts: 173
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Figured it out -- I don't know why it never occurred to anyone, but I created a long variable and cast the cell as a long. That did the trick. Simplest solution. Son of a gun.



Worked like a charm.

I'm beginning to get why they call me Merlin.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic