This project has moved. For the latest updates, please go here.

Leading Zeroes?

Mar 10, 2011 at 3:17 PM

Has anyone been able to preserve leading zeroes?  It seems like everything I've tried simply hasn't been working...


excelSheet.Cell(x, y).Value = value.ToString() - still defaults to a vanilla number if it detects a number, chopping off zeroes

excelSheet.Cell(x, y).DataType = XLCellValues.Text - trying to force it to text makes has no impact, it simply reverts back to a number upon assignment and by then it's too late as the leading zeroes are gone

excelSheet.Cell(x, y).Style.NumberFormat - not all the normal options for handling seem to work, for example NumberFormatId 49 or simply using "@" simply replaces numbers with an @ symbol instead of what it's supposed to do?


Even forcing it to use a fixed length number, e.g. NumberFormat = "00000" doesn't seem to work even though that would pretty useless to me for this particular need as the length is variable anyway.  Am I doing something blatantly wrong here?

Mar 11, 2011 at 12:49 AM

            // Option 1:
            ws.Cell(1, 1).Value = "'0001";
            // Option 2:
            ws.Cell(2, 1).Value = 1;
            ws.Cell(2, 1).Style.NumberFormat.Format = "0000";

Mar 11, 2011 at 4:10 AM
Edited Mar 11, 2011 at 4:18 AM

I feel like this question shouldn't have been asked in the first place so I added a new method to the cell class:


            // Option 3 (will appear on v0.47):
            ws.Cell(3, 1).SetValue("0001");

Thanks for the feedback.

Mar 11, 2011 at 12:53 PM

// Option 1
ws.Cell(1, 1).Value = "'0001";


Worked great, thanks a lot!