CODEWITHSUNDEEP
javaapache-poi
Ashutosh
Ashutosh

Reputation: 111

How To Convert From Scientific To Number Value While Writing Excel In Apache Poi Java

I need to create an XL sheet using Apache POI with some values which are long numeric for ex:1234567891011.

When i use below sample code to generate the excel sheet, the data is stored in form of Scientific format (1.23457E+12). But i want to store this data as a number ex. 1234567891011.00 is fine.

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");
String str = "1234567891011";
Row row;
Cell cell;
short rowNum = 0;
short colNum = 0;

row = sheet.createRow(rowNum++);
cell = row.createCell(colNum);
cell.setCellValue(str);


FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

I tried to set the cell format to Numeric as below code but doesnt work. 

row = sheet.createRow(rowNum++);
cell = row.createCell(colNum);
cell.setCellValue(str);
dateCell1.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
double converted = new Double(str);
dateCell1.setCellValue(converted);

Please tell if any way possible to do it.

Upvotes: 0

Views: 1849

Answers (2)

Axel Richter
Axel Richter

Reputation: 61852

Excel is using number format General per default. This switches to scientific format if needed. That means if cell width is too small or numeric value is too big.

So do using a cell style having a special number format 0.00 or 0.

Example:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

class CreateExcelBigDecimal {

 public static void main(String[] args) throws Exception {

  //Workbook workbook = new XSSFWorkbook(); String filePath = "./Excel.xlsx";
  Workbook workbook = new HSSFWorkbook(); String filePath = "./Excel.xls";

  DataFormat format = workbook.createDataFormat();

  CellStyle numberCellStyle = workbook.createCellStyle();
  numberCellStyle.setDataFormat((short)8);
  numberCellStyle.setDataFormat(format.getFormat("0.00"));

  Sheet sheet = workbook.createSheet();
  Cell cell = sheet.createRow(0).createCell(0);

  double value = 1234567891011d;

  cell.setCellValue(value);
  cell.setCellStyle(numberCellStyle);

  sheet.setColumnWidth(0, 20*256);

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}

That leads to showing 1234567891011.00 in cell A1.

Upvotes: 2

Zartof
Zartof

Reputation: 185

Leave the cell as String type and format your number java side.

long yourLongNumber = 1234567891011;
String strLong = String.format("%d", yourLongNumber);
//out: "1234567891011"

Upvotes: 0

Related Questions