CODEWITHSUNDEEP
javaapacheapache-poi
Crackling Chetan
Crackling Chetan

Reputation: 35

Formatting cellvalue in apache poi as accounting or currency for numbers

The Below Code is the apache poi code for create excel cell with text value want the text value as currency.

Code :

    row1=   sheetNormal.createRow((short)2);
    row1.setHeight((short)(256 *1.5));
    header = row1.createCell((short)1);
    header.setCellValue(Integer.parseInt(500000));

desired output in excel:-5,00,000

the output is shown in the form of the text.

Upvotes: 2

Views: 3306

Answers (1)

DenisFLASH
DenisFLASH

Reputation: 742

To get the output in currency, you can do something like this. Be careful, method setStringValue(value) accepts the value to be a double, but not an Integer. You can parse it from String to Double and than manipulate this value depending on the precision which you use to enter your values. This precision can be:

  • entire dollars;
  • cents;
  • 1/1000 of a cent.

In the code below you can find all three examples (header is changed by cell1):

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("test sheet");

HSSFCellStyle currencyStyle = wb.createCellStyle();
HSSFDataFormat dataFormat = wb.createDataFormat();
currencyStyle.setDataFormat(dataFormat.getFormat("$#,##0.00.000"));

HSSFRow row1 = sheet.createRow(2);
row1.setHeight((short) (256*1.5));

// Input in DOLLARS
HSSFCell cell1 = row1.createCell(1);
cell1.setCellStyle(currencyStyle);
cell1.setCellValue(Double.parseDouble("5")); // output $5,00,000

// Input in CENTS
HSSFCell cell2 = row1.createCell(2);
cell2.setCellStyle(currencyStyle);
cell2.setCellValue(Double.parseDouble("499") / 100); // output $4,99,000

// Input in 1/1000 OF A CENT
HSSFCell cell3 = row1.createCell(3);
cell3.setCellStyle(currencyStyle);
cell3.setCellValue(Double.parseDouble("123456789") / 100000); // output $1 234,56,789

try {
    FileOutputStream out = new FileOutputStream("currency_data_format.xls");
    wb.write(out);
    out.close();
} catch (IOException e) {
    System.out.println(e.getMessage());
}

If you see ######## in a cell, it's because your value has too many characters. Increase cell width.

I can modify above code, if you give some precisions about how you enter your data. Good luck!

Upvotes: 3

Related Questions