如何根据货币代码在不同的单元格中设置不同的货币

我正在尝试使用 Apache POI 在不同的单元格中设置不同的货币。

我有 JSON 数据：

{
"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
}
]
}
}

我想得到这样的结果(Excel 数据)：

销售$100.00$1,000,000.00100.00

shop	SKU	数量
商店1	SKU1	1	¥500.00
商店1	SKU2	1	¥50,000,000.00
商店2	SKU1	1 COP	¥500.00

设置数据格式(如£#,##0.00)总是会导致用户定义的数据格式。如果您想使用Excel货币数据格式，就像格式化单元格->数字->货币->符号-> 选择一样，那么您需要知道在这种情况下Excel存储什么。这些货币数据格式使用后跟$货币符号以及方括号中的 Windows 语言代码标识符 (LCID) 进行存储。例如：[$£-809].那就是大不列颠及北爱尔兰联合王国LCID 809英镑£。

要从ISO国家/地区代码创建此类格式，需要一张将货币代码映射到ExcelLCID货币代码的地图。不幸的是，据我所知，没有这样的地图可用。所以我们需要从头开始创建它。为此，请创建一个具有此类货币值的Excel*.xlsx。然后解压缩*.xlsx(它只是一个ZIP存档)并查看/xl/styles.xml。在那里你会发现类似的东西：

<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>

使用该信息，我们可以创建以下地图：

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]
}
};

在创建Excel单元格时，我们可以使用该映射获取单元格数据格式所需的LCID货币代码。

完整示例：

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();
}
}

在您的情况下，最可靠的解决方案是映射CODE -> SYMBOL。只需将Currency.getInstance("CNY").getSymbol();替换为codeSymbolMap.get("CNY")即可。

货币代码和符号的完整列表可用，即这里.

private static Map<String, String> codeSymbolMap = new HashMap<String, String>() {
{
put("AED", "د.إ");
put("AFN", "؋");
put("ALL", "L");
put("AMD", "֏");
put("ANG", "ƒ");
put("AOA", "Kz");
put("ARS", "$");
put("AUD", "$");
put("AWG", "ƒ");
put("AZN", "₼");
put("BAM", "KM");
put("BBD", "$");
put("BDT", "৳");
put("BGN", "лв");
put("BHD", ".د.ب");
put("BIF", "FBu");
put("BMD", "$");
put("BND", "$");
put("BOB", "$b");
put("BOV", "BOV");
put("BRL", "R$");
put("BSD", "$");
put("BTC", "₿");
put("BTN", "Nu.");
put("BWP", "P");
put("BYN", "Br");
put("BYR", "Br");
put("BZD", "BZ$");
put("CAD", "$");
put("CDF", "FC");
put("CHE", "CHE");
put("CHF", "CHF");
put("CHW", "CHW");
put("CLF", "CLF");
put("CLP", "$");
put("CNY", "¥");
put("COP", "$");
put("COU", "COU");
put("CRC", "₡");
put("CUC", "$");
put("CUP", "₱");
put("CVE", "$");
put("CZK", "Kč");
put("DJF", "Fdj");
put("DKK", "kr");
put("DOP", "RD$");
put("DZD", "دج");
put("EEK", "kr");
put("EGP", "£");
put("ERN", "Nfk");
put("ETB", "Br");
put("ETH", "Ξ");
put("EUR", "€");
put("FJD", "$");
put("FKP", "£");
put("GBP", "£");
put("GEL", "₾");
put("GGP", "£");
put("GHC", "₵");
put("GHS", "GH₵");
put("GIP", "£");
put("GMD", "D");
put("GNF", "FG");
put("GTQ", "Q");
put("GYD", "$");
put("HKD", "$");
put("HNL", "L");
put("HRK", "kn");
put("HTG", "G");
put("HUF", "Ft");
put("IDR", "Rp");
put("ILS", "₪");
put("IMP", "£");
put("INR", "₹");
put("IQD", "ع.د");
put("IRR", "﷼");
put("ISK", "kr");
put("JEP", "£");
put("JMD", "J$");
put("JOD", "JD");
put("JPY", "¥");
put("KES", "KSh");
put("KGS", "лв");
put("KHR", "៛");
put("KMF", "CF");
put("KPW", "₩");
put("KRW", "₩");
put("KWD", "KD");
put("KYD", "$");
put("KZT", "₸");
put("LAK", "₭");
put("LBP", "£");
put("LKR", "₨");
put("LRD", "$");
put("LSL", "M");
put("LTC", "Ł");
put("LTL", "Lt");
put("LVL", "Ls");
put("LYD", "LD");
put("MAD", "MAD");
put("MDL", "lei");
put("MGA", "Ar");
put("MKD", "ден");
put("MMK", "K");
put("MNT", "₮");
put("MOP", "MOP$");
put("MRO", "UM");
put("MRU", "UM");
put("MUR", "₨");
put("MVR", "Rf");
put("MWK", "MK");
put("MXN", "$");
put("MXV", "MXV");
put("MYR", "RM");
put("MZN", "MT");
put("NAD", "$");
put("NGN", "₦");
put("NIO", "C$");
put("NOK", "kr");
put("NPR", "₨");
put("NZD", "$");
put("OMR", "﷼");
put("PAB", "B/.");
put("PEN", "S/.");
put("PGK", "K");
put("PHP", "₱");
put("PKR", "₨");
put("PLN", "zł");
put("PYG", "Gs");
put("QAR", "﷼");
put("RMB", "￥");
put("RON", "lei");
put("RSD", "Дин.");
put("RUB", "₽");
put("RWF", "R₣");
put("SAR", "﷼");
put("SBD", "$");
put("SCR", "₨");
put("SDG", "ج.س.");
put("SEK", "kr");
put("SGD", "S$");
put("SHP", "£");
put("SLL", "Le");
put("SOS", "S");
put("SRD", "$");
put("SSP", "£");
put("STD", "Db");
put("STN", "Db");
put("SVC", "$");
put("SYP", "£");
put("SZL", "E");
put("THB", "฿");
put("TJS", "SM");
put("TMT", "T");
put("TND", "د.ت");
put("TOP", "T$");
put("TRL", "₤");
put("TRY", "₺");
put("TTD", "TT$");
put("TVD", "$");
put("TWD", "NT$");
put("TZS", "TSh");
put("UAH", "₴");
put("UGX", "USh");
put("USD", "$");
put("UYI", "UYI");
put("UYU", "$U");
put("UYW", "UYW");
put("UZS", "лв");
put("VEF", "Bs");
put("VES", "Bs.S");
put("VND", "₫");
put("VUV", "VT");
put("WST", "WS$");
put("XAF", "FCFA");
put("XBT", "Ƀ");
put("XCD", "$");
put("XOF", "CFA");
put("XPF", "₣");
put("XSU", "Sucre");
put("XUA", "XUA");
put("YER", "﷼");
put("ZAR", "R");
put("ZMW", "ZK");
put("ZWD", "Z$");
put("ZWL", "$");
}
};

相关内容

最新更新

热门标签：