DataTable -> Excel Write Range: How to format a column to currency?


#1

If I have Read Range from Excel file A into a DataTable A, then do some work from each row, then add each row into new DataTable B (previously defined by a Build DataTable activity, including an “Amount” column), how can I programmatically format the “Amount” column of DataTable B as currency, so when a viewer opens it… it won’t just look like plain decimal numbers?


#2

Might be possible using assigning into variable n arguments passing


#3

Hello,

There is no currency datatype that a datatable can contain.
If your idea is to reinsert later in a workbook, you can change all the column cells into currency format there.
Another option is to deal with value as string

To convert your decimal(double) to string with your format you can use something like that.

Ctype(dt.Rows(0).Item(0),double).ToString(“C”,Globalization.CultureInfo.CreateSpecificCulture(“en-US”))

Here is some info about String currency formats.

https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings

Cheers.


#4

Thanks. Yep I intend to eventually write DT B into Excel B, so that’s what I was referring to when I said “so when a viewer opens it, it won’t look like decimal numbers”.

Great idea about formatting before I write into DT B. I am doing that elsewhere in the project and it didn’t occur to me to do it in this instance. The column in DT B is declared to hold a string… so that will work!


#5

There is also the possibility of formatting the fields after you write the DT to excel sheet.

Select the column/range you need to update in excel scope and use a macro or hotkey(ctrl-shift-$) to update the fields into currency format