CODEWITHSUNDEEP
javaexcelapache-poi
Roman Makhlin
Roman Makhlin

Reputation: 993

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

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:

Example

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?

Upvotes: 4

Views: 3073

Answers (2)

Raf
Raf

Reputation: 7649

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();
        }
    }
}

Upvotes: 0

hughs
hughs

Reputation: 91

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);

Upvotes: 5

Related Questions