How to set different currency in different cell depending on currency code

Question

I am trying to set different currencies in different cells with Apache POI.

I have JSON data:

{
    "data": {
        "shop1": [
            {
                "sku": "sku1",
                "quantity": 1,
                "sale": "100",
                "currency": "USD",
                "cost": 500
            },
            {
                "sku": "sku2",
                "quantity": 1,
                "sale": "100",
                "currency": "USD",
                "cost": 500
            }
        ],
        "shop2": [
            {
                "sku": "sku1",
                "quantity": 1,
                "sale": "100",
                "currency": "COP",
                "cost": 500
            }
        ]
    }
}

I want get the result like this (Excel data):

shop	sku	quantity	sale	cost CNY
shop1	sku1	1	$100.00	¥500.00
shop1	sku2	1	$1,000,000.00	¥50,000,000.00
shop2	sku1	1	COP 100.00	¥500.00

I want it to show a different currency symbol according to the JSON data.shop1[0].currency, such as CNY show ¥ or USD show $

So I tried this:

XSSFCellStyle currency = xssfWorkbook.createCellStyle();
currency.setDataFormat(7);

It fails, it only shows ¥. It seems to parse the currency depending on where my system is located (China).

Then I tried:

String symbol = Currency.getInstance("CNY").getSymbol();
currency.setDataFormat(xssfWorkbook.createDataFormat().getFormat("{SYMBOL}#,#0.00".replace("{SYMBOL}", symbol)));

When I use CNY, it seem OK: I get ¥123.0, but when I use USD, it fails because Currency.getInstance("CNY").getSymbol(); uses system default locale.

In Excel, I can set the symbol through right click a cell -> Format cells -> Number -> Currency -> Symbol (select).

What can I do to set the currency in Apache POI?

Answer 1

Setting data formats like £#,##0.00 always leads to user defined data formats. If you wants using Excel currency data formats, as done with Format cells -> Number -> Currency -> Symbol -> select, then you need th know what Excel stores in that case. Those currency data formats are stored using $ followed by currency symbols in combination with Windows Language Code Identifier (LCID) in square brackets. For example: [$£-809]. That is Pound Sterling £ of United Kingdom of Great Britain and Northern Ireland LCID 809.

To create such formats from ISO country codes, one would need a map which maps currency codes to Excel LCID currency codes. Unfortunately there is no such map available, as far as I know. So we need creating it from scratch. To do so, create a Excel *.xlsx having such currency values. Then unzip the *.xlsx (it is just a ZIP archive) and have a look into /xl/styles.xml. There you will find something like:

<numFmts count="6">
 <numFmt formatCode="[$$-409]#,##0.00" numFmtId="164"/>
 <numFmt formatCode="[$€-407]#,##0.00" numFmtId="165"/>
 <numFmt formatCode="[$¥-804]#,##0.00" numFmtId="166"/>
 <numFmt formatCode="[$₽-419]#,##0.00" numFmtId="167"/>
 <numFmt formatCode="[$£-809]#,##0.00" numFmtId="168"/>
 <numFmt formatCode="[$₺-41F]#,##0.00" numFmtId="169"/>
</numFmts>

Using that informations we can create following map:

static final Map<String, String> currencyCodesExcelLCID = new HashMap<>() {
 {
  put("USD", "[$\u0024-409]");  //[$$-409]
  put("CNY", "[$\u00A5-804]");  //[$¥-804]
  put("EUR", "[$\u20AC-407]");  //[$€-407]
  put("RUB", "[$\u20BD-419]");  //[$₽-419]
  put("GBP", "[$\u00A3-809]");  //[$£-809]
  put("TRY", "[$\u20BA-41F]");  //[$₺-41F]
 }
};

While creating the Excel cells we then can use that map to get needed LCID currency code for the cell's data format.

Complete example:

import java.io.FileOutputStream;

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

import java.util.Map;
import java.util.HashMap;

public class CreateExcelDifferentCurrencies {

 static final Map<String, String> currencyCodesExcelLCID = new HashMap<>() {
  {
   put("USD", "[$\u0024-409]");  //[$$-409]
   put("CNY", "[$\u00A5-804]");  //[$¥-804]
   put("EUR", "[$\u20AC-407]");  //[$€-407]
   put("RUB", "[$\u20BD-419]");  //[$₽-419]
   put("GBP", "[$\u00A3-809]");  //[$£-809]
   put("TRY", "[$\u20BA-41F]");  //[$₺-41F]
  }
 };

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

  Object[][] data = {
   {"Name", "Code", "Value"},
   {"US Dollar", "USD", 1234.56d},
   {"Euro (Germany)", "EUR", 1234.56d},
   {"Yuan Renminbi", "CNY", 1234.56d},
   {"Russian Ruble", "RUB", 1234.56d},
   {"Pound Sterling", "GBP", 1234.56d},
   {"Turkish Lira", "TRY", 1234.56d},
   {"Not available", "NA", 1234.56d}
  };

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

  DataFormat dataFormat = workbook.createDataFormat();

  Sheet sheet = workbook.createSheet();

  for (int r = 0; r < data.length; r++) {
   Object[] dataRow = data[r];
   Row row = sheet.createRow(r);
   String currencyCode = "";
   for (int c = 0; c < dataRow.length; c++) {   
    Object value = dataRow[c];
    Cell cell = row.createCell(c);
    if (value instanceof String) {
     cell.setCellValue((String)value);
     if (c == 1) currencyCode = (String)value;
    } else if (value instanceof Double) {
     cell.setCellValue((Double)value);
     String symbolLCID = currencyCodesExcelLCID.getOrDefault(currencyCode, "");
     CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, dataFormat.getFormat(symbolLCID + "#,##0.00"));
    }
   }
  }

  for (int c = 0; c < data[0].length; c++) {
   sheet.autoSizeColumn(c);
  }

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