NPOI: Achieve Currency format as if formatted by Excel

Question

I have seen some questions (like this one) here asking about if a cell in Excel can be formatted by NPOI/POI as if formatted by Excel. As most of you, I have to deal with issues with Currency and DateTime. Here let me ask how the formatting can be achieved as if it has been formatted by Excel? (I will answer this question myself as to demonstrate how to do it.)

Setting: Windows 10, English, Region: Taiwan Excel format: XLSX (version 2007 and later)

(Sorry about various edit of this question as I have pressed the 'Enter' button at unexpected time.)

Answer 1

var cell5 = row.CreateCell(5, CellType.Numeric);
                cell5.SetCellValue(item.OrderTotal);
                var styleCurrency = workbook.CreateCellStyle();
                styleCurrency.DataFormat= workbook.CreateDataFormat().GetFormat(string.Format("\"{0}\"#,##0.00", item.CurrencySymbol));//styleCurrency;
                cell5.CellStyle = styleCurrency;
                styleCurrency = null;

Iterate over loop for multiple currency.

Function to GetCurrencySymbol against currency Code on C#

private string GetCurencySymbol(string isOcurrencyCode)
        {
            return  CultureInfo.GetCultures(CultureTypes.AllCultures).Where(c => !c.IsNeutralCulture)
                    .Select(culture =>
                    {
                        try
                        {
                            return new RegionInfo(culture.LCID);
                        }
                        catch
                        {
                            return null;
                        }
                    })
                    .Where(ri => ri != null && ri.ISOCurrencySymbol == isOcurrencyCode)
                    .Select(ri => ri.CurrencySymbol).FirstOrDefault();}

Answer 2

If you format a cell as Currency, you have 4 choices:

The internal format of each style is as follow:

1. -NT$1,234.10
   <numFmt formatCode=""NT$"#,##0.00" numFmtId="164"/>

2. [RED]NT$1,234.10
   <numFmt formatCode=""NT$"#,##0.00;[Red]"NT$"#,##0.00" numFmtId="164"/>

3. -NT$1,234.10
   <numFmt formatCode=""NT$"#,##0.00_);("NT$"#,##0.00)" numFmtId="7"/>

4. [RED]-NT$1,234.10
   <numFmt formatCode=""NT$"#,##0.00_);[Red]("NT$"#,##0.00)" numFmtId="8"/>

Note: There is a pair of double quote (") comes before and after NT$.

(To get internal format of XLSX, just unzip it. The Style information is available in <unzip dir>\xl\Styles.xml Check out this answer if you need more information.)

(FYI: In formatCode, the '0' represent a digit. The '#' also represent a digit, but will not appear if the number is not large enough. So any number less than 1000 will not have the comma inside it. The '_' is a space holder. In format 3, '1.75' appears as 'NT$1.75 '. The last one is a space.)

(FYI: In numFmtId, for case 1 and case 2, number 164 is for user-defined. For case 3 and 4, number 7 and 8 are build-in style.)

For developers using POI/NPOI, you may find out if you format your currency column using Build In Format using 0x7 or 0x8, you can get only the third or fourth choice. You cannot get the first or second choice.

To get the first choice, you build upon style 0x7 "$#,##0.00);($#,##0.00)". You need to add the currency symbol and the pair of double quotes in front of it.

styleCurrency.DataFormat = workbook.CreateDataFormat().GetFormat("\"NT$\"#,##0.00");

Apply this format to a cell with number. Once you open the Excel result file, right click to check formatting, you will see the first choice.

Please feel free to comment on this post.