Number stored as text warning in excel using POI

Question

I am getting Number stored as text warning for the created excel file using POI. I am trying to display percentage. This question discusses the same, but it's for python. Can some one please suggest me how to avoid this in java using POI?

Below are the lines where I get this warning.

workbook= new XSSFWorkbook();
sh1 = wb.createSheet("Data Sheet");
    cell = row.createCell(3);
    cell.setCellValue(37 + "%");

Based on Gagravarr answer I did it this way.

XSSFDataFormat df = workbook.createDataFormat();
                    CellStyle cs = wb.createCellStyle();
                    cs.setDataFormat(df.getFormat("%"));
                    cell.setCellValue(0.37);
                    cell.setCellStyle(cs);

But it just shows up as 0.37 with no warning now, not 37%.

The warning is genuine... you are using 37 as number and appending % that is not possible as a number cannot be added with a character. instead if you use "37" instead of 37, it will not give any error. — Sankumarsingh, Feb 21 '14 at 08:32
@Sankumarsingh Well, afaik concatinating the number to a string converts the entire thing to String. Then even after setting the whole thing as string, it throws such error. — rick, Feb 21 '14 at 14:52

Gagravarr · Accepted Answer · 2014-02-22T10:26:30.500

11

You're getting the warning because, as it says, you're storing a number as text.

What you probably want to do is:

CellStyle cs = wb.createCellStyle();
cs.setDataFormat(df.getFormat("%"));
cell.setCellValue(0.37);
cell.setCellStyle(cs);

That will store the number 37 as a number, and tell excel to apply a percentage format string to it. Oh, and since 37% is 0.37, you need to store 0.37 not 37!

Edit By popular request, here's a standalone program you can use to see it in action, for both .xls and .xlsx files. Tested with POI 3.10 final, and with all the required dependencies and component jars on the classpath.

public class TestPercent {
  public static void main(String[] args) throws Exception {
    System.out.println("Generating...");

    for (Workbook wb : new Workbook[] {new HSSFWorkbook(), new XSSFWorkbook()}) {
        Sheet sheet = wb.createSheet("Data Sheet");
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(3);

        DataFormat df = wb.createDataFormat();
        CellStyle cs = wb.createCellStyle();
        cs.setDataFormat(df.getFormat("%"));
        cell.setCellValue(0.37);
        cell.setCellStyle(cs);

        String output = "/tmp/text.xls";
        if (wb instanceof XSSFWorkbook) { output += "x"; }
        FileOutputStream out = new FileOutputStream(output);
        wb.write(out);
        out.close();
    }

    System.out.println("Done");
  }
}

edited Feb 22 '14 at 10:26

answered Feb 21 '14 at 13:43

Gagravarr

47,320
10
111
156

Sorry, this didn't work. Please check my edited question based on your answer. – rick Feb 21 '14 at 14:45
I've just tried this very code with POI 3.10 final, opened the resulting file, and it rendered as "37%" exactly as expected! – Gagravarr Feb 21 '14 at 19:47
+1 for your answer. Can you please check my edited question whether it is different from what you tried? or can you please show the full code what you have done? – rick Feb 22 '14 at 10:21
I've posted a standalone program which I used to test, does both .xls and .xlsx – Gagravarr Feb 22 '14 at 10:26
Thank you so much for your effort. I am accepting your answer. But I don't know where it goes wrong in my case. I even copied your entire code and created a new one and tested. It now shows only "%" symbol and not the number. When I clicked on the particular cell, the value is shown as 0.37 in the fx space above the sheet. – rick Feb 26 '14 at 07:15
Are you sure you're using POI 3.10 final? And you really don't have any older POI jars on your classpath? – Gagravarr Feb 26 '14 at 11:23

score 4 · Answer 2 · edited Sep 12 '14 at 18:21

4

Try also setting the CellType:

cell.setCellType(Cell.CELL_TYPE_NUMERIC);

edited Sep 12 '14 at 18:21

Kevin Panko

8,356
19
50
61

answered Sep 12 '14 at 18:00

Rob

41
1

score 2 · Answer 3 · answered Mar 22 '17 at 11:21

2

    if(NumberUtils.isDigits(text)){
        titleCell.setCellValue(Integer.parseInt(text));
    }else{
        titleCell.setCellValue(text);
    }

answered Mar 22 '17 at 11:21

Thamarai Selvan Dhandapani

29
2

Thanks this worked. You can also use `Double.parseDouble(text)` instead of Int – vikingsteve Dec 21 '21 at 09:14

score 1 · Answer 4 · answered Oct 19 '18 at 16:58

    XSSFWorkbook xSSFWorkbook = new XSSFWorkbook();
    CreationHelper createHelper = xSSFWorkbook.getCreationHelper();
    XSSFCellStyle numberStyle = xSSFWorkbook.createCellStyle();             
    numberStyle.setDataFormat(createHelper.createDataFormat().getFormat("###.00"));
    double d = 50.0;
    XSSFRow dataRow = sheet.createRow(1);
    Cellcel1 = dataRow.createCell(1);
    cel1.setCellValue(d);

score 0 · Answer 5 · answered Dec 16 '15 at 14:36

0

This may be a bit old but try this:

df.getFormat("0.00%")

answered Dec 16 '15 at 14:36

Indian

21
1
2

Number stored as text warning in excel using POI

5 Answers5