aspose file tools*
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
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: 42930
    
  68
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.
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: 42930
    
  68
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 ...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Formating problem