How to make correct date format when writing data to Excel

Question posted 2010 · +17 upvotes

Iam exporting a DataTable to an Excel-file using office interop. The problem is, that Excel does not recognize dates as such, but instead it displays numbers. In another case I pass a string which it then recognizes as a date. In both cases the data is messed up.

I tried NumberFormat @ which is supposed to store the cell in text format, but it didn’t work either.

            Application app = new Application();
            app.Visible = false;
            app.ScreenUpdating = false;
            app.DisplayAlerts = false;
            app.EnableAnimations = false;
            app.EnableAutoComplete = false;
            app.EnableSound = false;
            app.EnableTipWizard = false;
            app.ErrorCheckingOptions.BackgroundChecking = false; 

            Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet ws = (Worksheet)wb.Worksheets[1];

            for (int j = 0; j < dt.Rows.Count; j++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    Range rng = ws.Cells[j+2, i+1]as Range;
                    rng.Value2 = dt.Rows[j][i].ToString();
                    rng.NumberFormat = "@";
                }   
            }           

            wb.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                   Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            wb.Close(false, Missing.Value, Missing.Value);            

            app.Workbooks.Close();
            app.Application.Quit();
            app.Quit();    
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            ws = null;
            wb = null;
            app = null;
            GC.Collect();

Why doesn’t my NumberFormat @ work? Shouldn’t Textformat display everything the same as I put it in?