CODEWITHSUNDEEP
javaexcelspringapache-poi
Shaikh Mudassir
Shaikh Mudassir

Reputation: 151

Java Spring apache POI, store long number in excel without exponents

I'm trying to insert the BigDecimal value in excel but it is storing as exponential value.

The value is storing as exponential value with below code: i/p: 1234567891012.12 o/p: 1.23457E+12

cell = rowhead.createCell(cellnumberno++);
        cell.setCellStyle(wrapRightDataNoBottomCellStyle);
        cell.setCellValue(Double.parseDouble(cellValue));

when i'm trying to insert value in excel with below, then i'm getting error like "Number is strored as string": i/p: 1234567891012.12 o/p: 1234567891012.12 with exclamation mark

cell = rowhead.createCell(cellnumberno++);
        cell.setCellStyle(wrapRightDataNoBottomCellStyle);
        cell.setCellValue(cellValue);

Is there a way to remove this number is stored as text error from java code before entering value.

Formatting as below is working fine but i dont want to restrict the number after decimal. 

   XSSFDataFormat format=wb.createDataFormat();
   wrapRightDataNoBottomCellStyle.setDataFormat(format.getFormat("0.00"));
   cell.setCellStyle(wrapRightDataNoBottomCellStyle);
   cell.setCellValue(Double.parseDouble(cellValue));

I dont want to format the cell with something like this "0.00", is there a way where i can set cell format to text and then enter the value and still avoid the number as text error?

I want insert the long decimal like below in excel as a string by avoiding "number stored as text" error. Input : 1234567891012.12222 Expected o/p: 1234567891012.122222

Please help me, any pointers would be of great help.

Upvotes: 1

Views: 3927

Answers (1)

Axel Richter
Axel Richter

Reputation: 61915

I would never storing numeric values as text. The requirement having numeric cell content with as much decimal places as needed and not switching to exponential would be fulfilled using the number format 0.0##############. Each # means: use this digit if needed. Digits before the decimal point are always showed if needed. So there only one 0 is necessary. The format means in whole: at least one digit before the decimal point and at least one decimal place but 15 further decimal places maximal if needed. Excel always stores floating point values only with up to 15 significant digits precision. Thats why more than 15 decimal places are not useful.

But if the requirement really is, storing numeric values as text, then this also is possible using the number format @. This is the "Text" number format. To avoiding "number stored as text" warning, the usage of XSSFSheet.addIgnoredErrors is possible if the Excel is of type XSSF.

Example:

import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.util.CellReference;

public class ExcelStoreBigNumbers {

 public static void main(String[] args) throws Exception {
  String valueInput = "123456789012.12345";

  Workbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet();

  CellStyle style;
  DataFormat format = workbook.createDataFormat();
  Row row;
  Cell cell;

  //numeric cell value formatted having as much decimal places as needed
  style = workbook.createCellStyle();
  style.setDataFormat(format.getFormat("0.0##############"));
  row = sheet.createRow(0);
  cell = row.createCell(0);
  cell.setCellStyle(style);
  cell.setCellValue(Double.parseDouble(valueInput)); //precision only up to 15 significant digits

  //string cell value formatted as text
  style = workbook.createCellStyle();
  style.setDataFormat(format.getFormat("@"));
  row = sheet.createRow(1);
  cell = row.createCell(0);
  cell.setCellStyle(style);
  cell.setCellValue(valueInput);

  //avoiding "number stored as text" warning
  if (sheet instanceof XSSFSheet) {
   ((XSSFSheet)sheet).addIgnoredErrors(new CellReference(cell), IgnoredErrorType.NUMBER_STORED_AS_TEXT);
  }

  sheet.autoSizeColumn(0);

  workbook.write(new FileOutputStream("ExcelStoreBigNumbers.xlsx"));
  workbook.close(); 
 }

}

Upvotes: 2

Related Questions