Issue with decimal value 0.00 after exporting to Excel using c#

Question

I have some data that is exported to excel. In the below code When rowText = 4.0321, it shows same 4.0321 in excel. But if rowText = 0.00 it shows just 0 in the excel report. I need it to be 0.00. Any help would be appreciated..

                var rowText = base.GetColumnText(column, columnValue);

                _writer.Write(string.Format("\"{0}\"", rowText.Replace("\"", "\"\"")));

                _writer.Write(i < columns.Count - 1 ? "," : Environment.NewLine);




    protected string GetColumnText(ViewColumn column, object value)
    {
        string text = string.Empty;

        if (value != null && value != DBNull.Value)
        {
            var dataFormatString = column.DataFormatString;                

            if (string.IsNullOrEmpty(dataFormatString))
            {
                switch (column.ColumnType)
                {
                    case ViewColumnType.Date:
                        {
                            dataFormatString = "{0:MM/dd/yyyy}";
                        }
                        break;
                    case ViewColumnType.Numeric:
                        {
                            dataFormatString = "{0:N2}";
                        }
                        break;
                    case ViewColumnType.Account:
                        {
                            string AccountNo = value.ToString().Trim();

                            if (AccountNo.Length == 8)
                                return string.Concat(AccountNo.Substring(0, 4), "-", AccountNo.Substring(4));
                        }
                        break;
                }
            }

            text = !string.IsNullOrEmpty(dataFormatString) ? string.Format(dataFormatString, value) : value.ToString();

        }
        return text;
    }

Maybe not related to your problem, but why do you use `base.` in `var rowText = base.GetColumnText(column, columnValue);` when `GetColumnText` is not virtual? Is it hidden by another method with the same name? Otherwise I would use `this.` not `base.`. Have you debugged to see what happens? I'm pretty sure `string.Format("{0:N2}", 0)` produces `"0.00"` (if `"."` is the decimal separator in the current culture). — Jeppe Stig Nielsen, Dec 07 '12 at 21:52
Yes rowText = string.Format("{0:N2}", 0) produces "0.00". So now I have rowText = "0.00" but when I export using _writer.Write(string.Format("\"{0}\"", rowText.Replace("\"", "\"\""))); I am seeing just 0 in excel file — Naveen Reddy, Dec 10 '12 at 20:15

score 1 · Accepted Answer · answered Dec 07 '12 at 21:49

1

If you right-click on the cell it is being exported to and choose 'Format cells...' what number type does it come up with? If it is 'General' then that makes sense as to why is had no decimal spaces. If you select 'Number' then you can define how many decimal spaces will be displayed. Is this what you were looking for?

answered Dec 07 '12 at 21:49

user8128167

6,929
6
66
79

Here is a link that talks about setting cell types in excel programmatically from C#: http://stackoverflow.com/questions/7503298/how-to-format-cells-in-excel-sheet-programmatically – user8128167 Dec 17 '12 at 22:28

Issue with decimal value 0.00 after exporting to Excel using c#

1 Answers1