CODEWITHSUNDEEP
javaexcelapache-poi
prashanth
prashanth

Reputation: 9

How to format merged cell as currency in excel using java?

i have merged the cell using apache poi and java but i wanted those merged cell to formats as currency.

after merged the cell in the code i added the cellstyle.setDataformat((short)8); but when i open the excel it is not showing format as currency . it shows as general.

Could you please share a working code to merge a cell with data format as currency ?

thank you in advance.

Upvotes: 0

Views: 219

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

It is not really clear from your question what exactly is the problem here. There is no special requirement for cells having number format whether in merged regions or not.

What one needs to know is that merged regions only show the top left cell in whole merged region. That means the cell value as well as the cell style of the top left cell in whole merged region gets used.

The following is a minimal, reproducible example to show this.

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


class CreateExcelMergedCellsCurrencyFormat {

 public static void main(String[] args) throws Exception {
     
  Object[][] data = new Object[][] {
   new String[] {"Name", "va", "lu", "es"},
   new Object[] {"a", 123.456d, null, null},
   new Object[] {"b", 0, null, null},
   new Object[] {"c", -123.456d, null, null},
   new Object[] {"d", 1000000L, null, null},
   new Object[] {"e", -1000000, null, null}   
  };

  int firstRow = 3;
  int firstCol = 2;
  int mergeFromCol = firstCol + 1;
  int mergeToCol = firstCol + 3;
  
  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("./Excel.xlsx") ) {

   CellStyle currencyStyle = workbook.createCellStyle();
   currencyStyle.setDataFormat(workbook.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(8)));

   Sheet sheet = workbook.createSheet();
   
   int r = firstRow;
   for (Object[] rowData : data) {
    Row row = sheet.createRow(r);
    int c = firstCol;
    for (Object cellValue : rowData) {
     Cell cell = row.createCell(c);
     if (cellValue instanceof String) {
      cell.setCellValue((String)cellValue); 
     } else if (cellValue instanceof Number) {
      cell.setCellValue(((Number)cellValue).doubleValue()); 
      cell.setCellStyle(currencyStyle);
     }
     c++;
    }
    if (r > firstRow) sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), mergeFromCol, mergeToCol));
    r++;
   }
      
   workbook.write(fileout);
  }

 }
}

Upvotes: 1

Related Questions