CODEWITHSUNDEEP
javaexcelformatapache-poicell
Mayuso
Mayuso

Reputation: 1329

Change cell format (Length?) in Java Poi (Excel)

I have been searching for a way to do this but only found a few methods to resize columns, and wasn´t enough.

It´s simple:

When I´m writting into an excel file, I get to a point where numbers get higher than 10.000.000, and the format changes, example:

row 11: 9.800.000

row 12: 9.900.000

row 13: 1.0E7

row 14: 1.01E7

How can I prevent this from happening? I need the whole number, as I later access this information again from my code.

(I can´t edit excel manually, as I create it dinamically, I guess (and hope) there is a variable in some Java POI class that I can assign a value for this to change?)

Thank you all.

Upvotes: 0

Views: 2455

Answers (3)

Oussa
Oussa

Reputation: 135

It's a cell formatting case, by default Excel use exponential format for numbers below a certain value, for the 2013 version it's below 1E10. And for older versions I guess it's arround 1E7.

To solve this you need to format the cell in your Java code before creating the xls file, using HSSFCellStyle, check the following code:

HSSFCellStyle style = workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat("#"));

Double d = 10000000000.0;
thecell.setCellValue(d);
thecell.setCellType(Cell.CELL_TYPE_NUMERIC);
thecell.setCellStyle(style);

You may also need to re-size the column the fit the number by adding

sheet.autoSizeColumn(0);

Here's an output exemple with the cell formatting (A1) and without it (B1):

exemple with the cell formatting

Dont forget to import the necessary classes.

Upvotes: 2

dlopatin
dlopatin

Reputation: 3812

According to your example, you have to set DataFormat as "#.000":

DataFormat format = workbook.createDataFormat();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(format.getFormat("#.000"));
cell.setCellStyle(cellStyle);

Where:

0 - Digit placeholder. For example, if you type 8.9 and you want it to display as 8.90, then use the format #.00

# - Digit placeholder. Follows the same rules as the 0 symbol except Excel does not display extra zeros when the number you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.## and you type 8.9 in the cell, the number 8.9 is displayed.

More excel formats: How to control and understand settings in the Format Cells dialog box in Excel

Upvotes: 1

asiew
asiew

Reputation: 493

The easiest way would probably be to just convert the value when you extract it from the excel file.

BigDecimal bigNumber = new BigDecimal(cell.getNumericCellValue());
//Use bigNumber when doing the calculations

Upvotes: 1

Related Questions