How to format long as currency when using worksheet.ImportData?

[Paul Meems]

I'm getting a List of objects which I get from my database and save it to an Excelsheet using

            var options = new ExcelImportDataOptions

            {

                PreserveTypes = true,

                FirstColumn = 1,

                FirstRow = 1,

                IncludeHeader = true,

                NestedDataLayoutOptions = ExcelNestedDataLayoutOptions.Merge

            };

            worksheet.ImportData(data, options);

This works fine.

In my dto I'm using [DisplayName("Foo bar")] to set the header in the Excelsheet.

This works fine as well.

Can I do something similar to make my 'long' variable format as 'Currency' in the Excelsheet?

I mean using attributes?

I tried formatting after importing but then I can't distinguish between a 'long' variable that needs to be formatted as 'Currency' and a 'ushort' variable that doesn't need to be formatted as a 'Currency'.

Please advise.

[Shamini Kiruba Sobers]

Hi Paul, 

Greetings from Syncfusion support. 

It is not possible to achieve your requirement using attributes. You have to set the number format manually. Kindly look into the following UG links to know about applying number formats using XlsIO. 

https://help.syncfusion.com/file-formats/xlsio/working-with-cell-or-range-formatting?cs-save-lang=1&cs-lang=asp.net%20core#apply-number-formats 

Regards, 

Shamini 

[Paul Meems]

Sadly formatting doesn't work with attributes.

I thought it wouldn't be too hard to implement since you are already getting the name of the column from an attribute. Setting the format of the column could go in the same run.

To use your suggestion to prepend "$" to my value before sending it to 'worksheet.ImportData' is really difficult for me because those values come as long from the database and now I need to create an additional step to convert all my long values to strings and prepend "$" to all values.

I'll have a look at a different approach. Perhaps by adding some special characters to the 'DisplayName', loop afterward through all column names and format the column based on that special character.

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Paul, 

Thanks for the update. 

We are analyzing your requirement and will share the analysis details in two business days, on December 18^th,2020. 

Regards, 

Keerthi. 

[Shamini Kiruba Sobers]

Hi Paul, 

 

Unfortunately, we can’t pass number formats similar to Display Name. So, it is not feasible. The number format needs to be set manually. 

Regards, 

Shamini