0

I have an issue with taking a number string from SQL and putting it into excel, but excel formats the number without it's leading zeros.

Another program I wrote reads the excel and put it into a pdf. It is in this second program I decided to reappend the missing leading zeros.

I am using .PadLeft to do this and ran into some errors. For documentation sake, I am going to add this issue I found to SO and also answer it. Here was my problem:

I need the number string to be 10 digits, with zeros on the front up to 10 digits.

I have numbers like 77776666 coming from excel, they should be 0077776666 (10 digits) Here is what DIDN'T work. It did not append any zeros:

  string insuranceID = Convert.ToString(xlRange.Cells[i, 21].Value2 ?? "");
  insuranceID.PadLeft(10, '0');
twinklefingers
  • 59
  • 1
  • 9

3 Answers3

4

To make this code work, I actually had to write the code like so: 

string insuranceID = Convert.ToString(xlRange.Cells[i, 21].Value2 ?? "");
insuranceID = insuranceID.PadLeft(10, '0');

It had to have the assignment part of the code on the front of the .PadLeft bit.

I didn't find an answer to this on SO and just discovered my answer. I hope this helps someone.

twinklefingers
  • 59
  • 1
  • 9
  • 5
    This is related to [string immutability](https://stackoverflow.com/questions/93091/why-cant-strings-be-mutable-in-java-and-net) – Nicholas Sideras Jul 02 '18 at 15:46
  • 1
    @NicholasSideras: More accurately it is BECAUSE of string immutability - PadLeft creates a new string & OP was just discarding it by not assigning it. The [documentation](https://msdn.microsoft.com/en-us/library/92h5dc07(v=vs.110).aspx) clearly states _"If the PadLeft method pads the current instance with whitespace characters, this method does not modify the value of the current instance. Instead, it returns a new string that is padded with leading paddingChar characters so that its total length is totalWidth characters."_ – PaulF Jul 02 '18 at 16:11
0

In C# Strings/strings are immutable, so simply declaring string.PadLeft(x, '0') will not work.

Try this instead:

string insuranceID = Convert.ToString(xlRange.Cells[i, 21].Value2 ?? "");   
insuranceID = insuranceID.PadLeft(10, '0');

To save you time in the future, the immutability of strings also comes into play for many of the string methods such as string.Replace(), string.ToUpper(), string.ToLower(), etc.

Tom Hood
  • 497
  • 7
  • 16
0

You could have also just used your original code but append the .PadLeft(10, '0') to the end of your statement and remove your second line, like this:

string insuranceID = Convert.ToString(xlRange.Cells[i, 21].Value2 ?? "").PadLeft(10, '0');

Thus eliminating the need to create a separate statement other than possibly for debugging purposes.

Scott Prokopetz
  • 474
  • 3
  • 6