Write Double value in numeric cell with specific format in Apache Poi 3.7

Question

I need to write a Double value in a numeric cell using a specific format, i mean, the generated xls must have numeric cells containing Double values like, for example: 8,1. I am trying something like:

DecimalFormat dFormat = new DecimalFormat("##.#");
dFormat.format(doubleValue);

But, since format method returns a String, no matter whether I create cells as numeric or not, they always behave as text cells. I was thinking about two options:

Forcing cells to behave as numeric cells.
Forgetting about DecimalFormat and use Double class specifying comma as the decimal separator, what i'm not sure it's possible.

Any idea?

score 19 · Accepted Answer · edited Apr 03 '14 at 10:45

19

I may be missing something, but I think you just want to style the cell with a format rule to display a single decimal place

// Do this only once per file
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
    wb.getCreationHelper().createDataFormat().getFormat("#.#"));

// Create the cell
Cell c = row.createCell(2);
c.setCellValue(8.1);
c.setCellStyle(cellStyle);

That will create a formatting rule, create a cell, set the cell to be the value 8.1, then style it

edited Apr 03 '14 at 10:45

Yasin Okumuş

2,299
7
31
62

answered Mar 20 '12 at 16:38

Gagravarr

47,320
10
111
156

Upvote for friendly environment and it does help me finish my work and made my day! – PSo Apr 12 '21 at 04:58

score 8 · Answer 2 · edited Oct 21 '14 at 15:24

8

I tried this working fine...

HSSFCellStyle cellStyle = wb.createCellStyle();
HSSFDataFormat hssfDataFormat = wb.createDataFormat(); 
String cellVal = "2500";
cellStyle.setDataFormat(hssfDataFormat.getFormat("#,##0.000"));
newCell.setCellStyle(cellStyle);
newCell.setCellValue(new Double(cellVal));
newCell.setCellType(Cell.CELL_TYPE_NUMERIC);

The output is a numeric value with desired format: 2,500.000

edited Oct 21 '14 at 15:24

PhiLho

40,535
6
96
134

answered Mar 05 '14 at 06:30

Santosh MV

81
1
3

suppose i want to set decimal by using parameter for exmp. in cellStyle.setDataFormat(hssfDataFormat.getFormat("#,##0.000")); in that instead of "#,##0.000" , 4 or 5 decimal which is int or long ? – Heartbreak-kid Mar 07 '14 at 09:42

score 0 · Answer 3 · answered Mar 20 '12 at 15:32

Do you need to put both values in one cell? Can you split them up into 2 columns? Excel has a built-in "Texts to Columns" function that you should be able to reference that will split an entire column of text strings into multiple columns based on a delimiter (like a comma). In VBA, it looks something like this:

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

As for your first proposed solution, it is not possible to force Excel to treat something as a number if it is not convertable to a number.

Thanks for answering, @carmalize. I may have explained myself badly, but i don't have "both values", is only one Double value using a comma as the decimal separator. — Dani, Mar 20 '12 at 15:48

Write Double value in numeric cell with specific format in Apache Poi 3.7

3 Answers3

Linked