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

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?

Have you defined the cell's type? It's probably defined with the default of "General" which will display `14.00` as `14`. — Metro Smurf, Oct 28 '16 at 14:10
@JoelS. And btw, you can use EPPlus for this. It's more flexible (imo). — Renat Zamaletdinov, Oct 28 '16 at 14:26

score 7 · Answer 1 · edited May 23 '17 at 12:10

7

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#

edited May 23 '17 at 12:10

Community

1
1

answered Oct 28 '16 at 14:20

Renat Zamaletdinov

1,210
1
21
36

score 1 · Answer 2 · answered Oct 29 '16 at 02:14

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.

score 0 · Answer 3 · answered Oct 28 '16 at 14:15

0

Wrap lcAmount.ToString("0.00");in a pair of quotes and put an equal sign in front of it. This will prevent excel from overriding the format.

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

answered Oct 28 '16 at 14:15

Matt Schley

187
1
8

4

There are better ways to preserve the formatting without resorting to this. – mason Oct 28 '16 at 14:31

C# - Decimals not writing out to Excel

3 Answers3