Formatting error in excel file using web based Jasper Report

Question

To showing a row-column based report in pdf format where every field is showing perfectly . But in csv and xls format the some fields are not showing properly . Such as, date format showing as but when click the column field just once then the date format is showing perfectly , and for showing the big decimal number it showing exponential number but by clicking the specific exponential number its showing the actual decimal number. How to solve this kind of error?

Picture is given as example

Answer 1

Any date, time or number (dates are really just formatted numbers) that cannot show its full formatted result within the width of its cell will show a series of hashmarks (e.g. #######) that fill the width of the cell. The lack of truncation is so 1234567890 does not display as 123456 if the column width is too narrow to show the entire value.

This is different from text which will either 'spillover' into an adjacent cell if the adjacent cell is empty or truncate at the cell border if the adjacent cell is populated.

Widen your columns so there is sufficient room to properly display the date/time/number in the cell's specified number format.

Your scientific notation (the ones you called 'exponential number') can be resolved by formatting the cells as 0 if integers or with an appropriate decimal place if mixed numbers so long as there is sufficient column width to display the result.

Note that some 'impossible numbers' such as negative time (Excel has no provision to calculate or display negative time) will show a cell filled with hashmarks no matter how big the column's width is.