C# - Decimals not writing out to Excel

Question

I have an ETL that's saving data to an Excel file. The issue is that the decimals are not being written out for integers. Example:

14.00

is being written out as

14

My code for writing out that line is

loWorksheet.Cells[liRowNum, 5] = lcAmount.ToString("0.00");

When I step through the code, it shows as 14.00, but on the Excel file it is not retaining the decimal places. Is this something that can be fixed in my code or is this an Excel issue? Any suggestions?

Answer 1

I'm quite sure you have to set format for your cells. I can't check right now, but it will be something like

 xlYourRange.NumberFormat = "0.00";

You can check this question Set data type like number, text and date in excel column using Microsoft.Office.Interop.Excel in c#

Answer 2

If you really want the data to be displayed literally the way it is in the source file, you have to deal with trade-offs. The simplest way is to format the data as text. You can do this a cell at a time or for entire columns:

loWorksheet.Columns["A:E"].NumberFormat = "@";

The trade-off is it's just text at this point. You can't add, sum, average, whatever.

On the other hands, if your data looks like this:

4.0
4.00
4.000

You can't really keep it as numbers and expect to retain the original format without doing some funny business.

If it's consistently two decimal places, and you know it's going to be, then I agree with @RenatZamaletdinov's solution.

And you might want to consider other strings and what Excel might to do them

0000123 becomes 123
10/23 will probably render as a date, depending on your localization
12345678901234567890 will render as scientific notation probably

These are all avoided if you make the numeric format text (@), but again without knowing what you plan to do with the data, it's hard to say if this is the correct approach.