Formating problem

[S Shehab]

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 ?

[Ulf Dittmer]

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.

[S Shehab]

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

  cell.setCellType(cell.CELL_TYPE_STRING);  

but still the problem exists , Do you think it may be related to the computer regional settings ?

[Sanjeev K Jain]

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]

Sorry my mistake ....I missed c11.setCellValue("80524092689197777777.0");. When something comes in quotes it will be displayed as String.

[S Shehab]

Actually i;m using Apache POI 3.1 20080629 ..

[S Shehab]

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]

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.

[S Shehab]

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 ...