This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Java in General and the fly likes Formating problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "Formating problem" Watch "Formating problem" New topic
Author

Formating problem

Sherif Shehab
Ranch Hand

Joined: Mar 05, 2007
Posts: 483

Hi Guys,

I;m facing a weird problem in my system , actually it's a reporting tool using servlets and jsp getting the data from the DB then display it in an excel sheet , the problem in that some numbers was displayed in the excel sheet as this : 8.052409268919E12 although it was found like this in the DB : 8052409268919.0 , so why is the format was changed in the excel sheet , i;m using apache POI for make these excel files my IDE is myeclipse ...

Any recommendations ?

Thanks,
Sherif
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41123
    
  45
Excel has limited numerical accuracy regarding the display of numbers. If the number gets too big it automatically switches to the scientific format (like n.nnnnEnn).
If you *need* the non-scientific notation, you may have to set the cell type to text instead of number.


Ping & DNS - my free Android networking tools app
Sherif Shehab
Ranch Hand

Joined: Mar 05, 2007
Posts: 483

Ulf Dittmer wrote:Excel has limited numerical accuracy regarding the display of numbers. If the number gets too big it automatically switches to the scientific format (like n.nnnnEnn).
If you *need* the non-scientific notation, you may have to set the cell type to text instead of number.

Hi Ulf ,
actually i set the cell type by but still the problem exists , Do you think it may be related to the computer regional settings ?
Sanjeev K Jain
Greenhorn

Joined: May 11, 2009
Posts: 7
I tried this sample code

HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet sheet = workBook.createSheet();
HSSFRow row1 = sheet.createRow((short) 0);
HSSFCell c11 = row1.createCell((short) 0);
c11.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
c11.setCellValue("80524092689197777777.0");
try{
FileOutputStream stream = new
FileOutputStream("c:/temp/Book1.xls");
workBook.write(stream);
}catch(Exception e) {
e.printStackTrace();
}
and generated excel. Output of the excel is coming fine and I am using 3.0-rc4-20070503 version of POI API. Might be shifting version of POI will help to resolve your problem.
Sanjeev K Jain
Greenhorn

Joined: May 11, 2009
Posts: 7
Sorry my mistake ....I missed c11.setCellValue("80524092689197777777.0");. When something comes in quotes it will be displayed as String.
Sherif Shehab
Ranch Hand

Joined: Mar 05, 2007
Posts: 483

Actually i;m using Apache POI 3.1 20080629 ..
Sherif Shehab
Ranch Hand

Joined: Mar 05, 2007
Posts: 483

Guys,
When i was debugging my code to see from where this problem came , i fill the cell value form vector of Strings , but i found that the value was putted in the vector in this format : 6.4552410135625E13 although i;m not doing any formatting process before filling the vector all i do read from DB put in a vector ..

Any recommendations ?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41123
    
  45
OK, so this has nothing to do with POI or Excel, then? It's a question of where between the DB and your Java code it gets reformatted; is that correct? If so, some strategically placed println statements, or running the code through a debugger, should pinpoint the problem pretty quickly.
Sherif Shehab
Ranch Hand

Joined: Mar 05, 2007
Posts: 483

Ulf Dittmer wrote:OK, so this has nothing to do with POI or Excel, then? It's a question of where between the DB and your Java code it gets reformatted; is that correct? If so, some strategically placed println statements, or running the code through a debugger, should pinpoint the problem pretty quickly.

Ok, i;ll debug my code again and see what's wrong ...
 
 
subject: Formating problem
 
Similar Threads
Files processing on server
unable to open excel document
POI invocation target exception
Problem in reading the excel file path from WINDOWs machine from UNIX environment
Unncessary empty spaces in excel file exported from websphere server