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


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies 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: 477

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: 39537
    
  27
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 - updated with new look and Ping home screen widget
Sherif Shehab
Ranch Hand

Joined: Mar 05, 2007
Posts: 477

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: 477

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

Joined: Mar 05, 2007
Posts: 477

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: 39537
    
  27
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: 477

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
 
Similar Threads
Problem in reading the excel file path from WINDOWs machine from UNIX environment
Files processing on server
unable to open excel document
POI invocation target exception
Unncessary empty spaces in excel file exported from websphere server