3

Currently using the following C# in a script task in SSIS to format an Excel column as a Number.

sheet.Columns[6].NumberFormat = "0.00";

However when the column is created the thousands separator is missing. Is there a way to add this in?

Hadi
  • 36,233
  • 13
  • 65
  • 124
OllieSP
  • 45
  • 5

3 Answers3

3

I believe you can use this:

sheet.Columns[6].NumberFormat = "#,##0.00";

Places with zeros will show even if the value is 0, whereas places with pound signs (#) will only show if needed.

The following are some examples of what will show when using this format:

10000 -> 10,000.00
 1000 -> 1,000.00
   10 -> 10.00
 5.25 -> 5.25
  .25 -> 0.25
   .1 -> 0.10
elmer007
  • 1,412
  • 14
  • 27
  • Hi, i've tried this and it doesn't work i'm afraid, there is no thousands seperator present – OllieSP Feb 11 '20 at 10:49
  • @OllieSP Interesting. Perhaps could you update your question to have more of the code involved? It may be affected by how the data is populated in the column – elmer007 Feb 11 '20 at 12:59
1

After doing some research, here is what I found:

Looking here, I found a solution that should work for you: format a number with commas and decimals in C# (asp.net MVC3)

To summarize the post, it states you should define a variable (number) and then initialize it. After, you will format twice: First using .ToDecimal(number) then .ToString(("#,##0.00")

Now, let's apply this to your example:

Define & initialize your variable -- let's say you call it the name of the 6th column in your sheet (for this example, let's assume you define it as constant that never changes named 'cost')

public const int cost = sheet.Columns[6]

Then you want to format:

Convert.ToDecimal(cost).ToString("#,##0.00");

This SHOULD get you the desired result.

mykhailo.romaniuk
  • 1,058
  • 11
  • 20
Sion
  • 11
  • 2
0

The Number Format expression consists of the following numeric specifiers:

# : Digit placeholder
0 : Zero placeholder
, : Decimal point
. : Decimal separator
[Red] : Color specifier
% : Percentage placeholder

You can use the following expression to show the thousands separators:

sheet.Columns[6].NumberFormat = "#,##0.00";

Examples (image taken from references below):

enter image description here

References:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Hi, i've tried this and it doesn't work i'm afraid, there is no thousands seperator present – OllieSP Feb 11 '20 at 10:49
  • @OllieSP can you provide the whole code you are implementing, since it may be other lines of codes that change the column behavior. – Hadi Feb 11 '20 at 14:47