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?
First, pick the cells that contain dates, then right-click and select Format Cells. Select Custom in the Number Tab, then type 'dd-mmm-yyyy' in the Type text box, then click okay. It will format the dates you specify.
Convert Date to Text using Text to Column Here are the steps to do this: Select all the cells that contain dates that you want to convert to text. Go to Data –> Data Tools –> Text to Column. This would instantly convert the dates into text format.
Did you try formatting the entire column as a date column? Something like this:
Range rg = (Excel.Range)worksheetobject.Cells[1,1]; rg.EntireColumn.NumberFormat = "MM/DD/YYYY";
The other thing you could try would be putting a single tick before the string expression before loading the text into the Excel cell (not sure if that matters or not, but it works when typing text directly into a cell).
Try using
DateTime.ToOADate()
And putting that as a double in the cell. There could be issues with Excel on Mac Systems (it uses a different datetime-->double conversion), but it should work well for most cases.
Hope this helps.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With