How to create dynamic custom format for currency for thousand, millions & billions

Aspose.Cells Product Family
1

Hi! Aspose team,
I want to apply custom format for currency in excel like thousand, millions & billions based on the no.
Can you help me with this?
Can I create a single custom format for the nos of type currency to apply thousand, millions & billions based on number?
thanks!CustomFormattingPNG.PNG (18.0 KB)

2

@Rohan_Wankar,

Thanks for the screenshot.

Could you please share a sample Excel file containing your desired (dynamic) custom format set upon numbers in the worksheet cells. We will check and help you on how to do it via Aspose.Cells API.

PS. please zip the Excel file prior attaching.

3

Hi! Amjad
I have attached the excel file for reference.
Let me know if you need additional information for the same.
thanks!CurrencyFormatting (2).zip (6.5 KB)

4

@Rohan_Wankar,

Thanks for the sample file.

Please see the sample code that will apply your desired custom formatting to the second column (B). Please refer to it and you may update your code to accomplish your task. You can apply formatting to a single cell, the whole row or range of cells if you want:
e.g.
Sample code:

        Workbook workbook =   new Workbook();
        // Obtaining the reference of the first (default) worksheet by passing its sheet index
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Add some data to the second column cells.
        worksheet.getCells().get("B1").putValue("test");
        worksheet.getCells().get("B2").putValue(612346576.014);
        worksheet.getCells().get("B3").putValue(502343465.345);
        worksheet.getCells().get("B4").putValue(1314);

        // Adding a new Style to the styles collection of the Workbook object
        Style style = workbook.createStyle();

        // Setting the Custom format
        style.setCustom("\\$##0.0,,\"M\";-\\$##0.0,,\"M\";\\$##0.0,,\"M\"");

        // Creating an object of StyleFlag
        StyleFlag flag = new StyleFlag();

        // Setting NumberFormat property to true so that only this aspect takes effect from Style object
        flag.setNumberFormat(true);

        // Applying style to the second column of the worksheet
        worksheet.getCells().getColumns().get(1).applyStyle(style, flag);

        // Saving spreadsheet on disc
        workbook.save("f:\\files\\out1.xlsx");

For further reference, see the document for your reference:

5

Hi! Amjad
But it will take the format for the whole column, it will take million as a symbol, I want it based on no, I want it to be calculated dynamically, based on no, whether it is thousand or Millions or Billions.
Is there a way that we can do it?
thanks!

6

@Rohan_Wankar,

I am not sure there can be created one such dynamic custom format for MS Excel which determines itself based on the value/number. If you know, please share that custom string and sample Excel file, we can check.

Yes, it will apply custom formatting string to the whole column. This is just an example. if you want to specify different custom numbers formatting based on cell value, you have to apply custom formattings cell by cell.

You have to evaluate the value by yourselves in code then using if condition(s) or switch case, you may apply different custom format string cell by cell accordingly. To evaluate the number dynamically, you have to write your own Java code by yourselves, browse/see the relevant forums (e.g., determine million, billion, etc. for a number) for assistance.