How to write a very large number in xlsx via apache poi

Question

I need to write to an excel cell a very large numbers(>91430000000000000000) The issue is that max value for cell is 9143018315613270000, and all values which is larger - would be replaced by max value. This issue will simply resolved by hands if an apostrophe is added to an number, for example '9143018315313276189 But how to the same trick via apache POI? I have follow code:

    attrId.setCellValue(new XSSFRichTextString('\'' + value.getId().toString()));

But it doesn't work:

Here the first row haven't any apostrophe at all, second one is written by hands and it is the result I'm looking for. Third is a result of my code. I also tried to use setCellValue which takes double and String, both of them doesn't help me ether.

So, here goes the question: How to write in excel a very large numbers via apache POI?

Set cell type to **text** using `cell.setCellType(Cell.CELL_TYPE_STRING)` and write **number as text**? — Developer Marius Žilėnas, Nov 13 '15 at 12:24

score 5 · Accepted Answer · answered Nov 13 '15 at 12:35

5

Set the cell style first

DataFormat format = workbook.createDataFormat();
CellStyle testStyle = workbook.createCellStyle();
testStyle.setDataFormat(format.getFormat("@"));
String bigNumber = "9143018315313276189";
row.createCell(40).setCellStyle(testStyle);
row.getCell(40).setCellValue(bigNumber);

answered Nov 13 '15 at 12:35

hughs

91
3

Yeah, that works for me. It's look a little bit tricky for me. Thank's a lot. – Roman Makhlin Nov 13 '15 at 12:39

score 0 · Answer 2 · edited May 23 '17 at 12:08

Can you set the Cell type and see what happens. Or if you have already set that then please post your code so that others look at it.

cell.setCellType(Cell.CELL_TYPE_STRING);

Please refer to the question in here for details on how to set string value to cell How can I read numeric strings in Excel cells as string (not numbers) with Apache POI?

I did the following sample and worked for me (poi-3.1.3)

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;


public class WriteToExcel {

    public static void main(String[] args) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sample sheet");
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue("91430183153132761893333");

        try {
            FileOutputStream out = 
                    new FileOutputStream(new File("C:\\test_stackoverflow\\new.xls"));
            workbook.write(out);
            out.close();
            System.out.println("Excel written successfully..");

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

unfortunately, the result is the same as in cell number 3. If I edit via hands the value without any changing(just point on value via mouse) - I gain the result I expect to, but is there any automatic way? — Roman Makhlin, Nov 13 '15 at 12:33
When I clicked in the cell, I saw the effect. I guess addition of the style as shown by @hughs is the way forward. — Raf, Nov 13 '15 at 12:56

How to write a very large number in xlsx via apache poi

2 Answers2