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?
This question is related to
c#
.net
excel
office-interop
To format by code Date in Excel cells try this:
Excel.Range rg = (Excel.Range)xlWorkSheet.Cells[numberRow, numberColumn];
rg.NumberFormat = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
After this you can set the DateTime value to specific cell
xlWorkSheet.Cells[numberRow, numberColumn] = myDate;
If you want to set entire column try this: Excel.Range rg = (Excel.Range)xlWorkSheet.Cells[numberRow, numberColumn];
rg.EntireColumn.NumberFormat =
CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
This worked for me:
sheet.Cells[currentRow, ++currentColumn] = "'" + theDate.ToString("MM/dd/yy");
Note the tick mark added before the date.
Hope this help
private bool isDate(Range cell)
{
if (cell.NumberFormat.ToString().Contains("/yy"))
{
return true;
}
return false;
}
isDate(worksheet.Cells[irow, icol])
This worked for me:
hoja_trabajo.Cells[i + 2, j + 1] = fecha.ToString("dd-MMM-yyyy").Replace(".", "");
Expanding slightly on @Assaf answer, to apply formatting correctly I also had to convert the DateTime
via the .ToOADate()
function before the formatting took effect. You can do this on a cell by cell basis:
xlWorkSheet.Cells[Row, Col].NumberFormat = "<Required Format>"; // e.g. dd-MMM-yyyy
xlWorkSheet.Cells[Row, Col] = DateTimeObject.ToOADate();
Or you can apply the formatting to the entire column:
xlWorkSheet.Cells[Row, Col].EntireColumn.NumberFormat = "<Required Format>"; // e.g. dd-MMM-yyyy
xlWorkSheet.Cells[Row, Col] = DateTimeObject.ToOADate();
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.
I know this question is old but populating Excell Cells with Dates via VSTO has a couple of gotchas.
Formatting the entire column did NOT work for me.
Not even this approach from Microsoft worked: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.numberformat.aspx
I found Formula's don't work on dates with yyyy-mmm-dd format - even though the cells were DATE FORMAT! You have to translate Dates to a dd/mm/yyyy format for use in formula's.
For example the dates I am getting come back from SQL Analysis Server and I had to flip them and then format them:
using (var dateRn = xlApp.Range["A1"].WithComCleanup())
{
dateRn.Resource.Value2 = Convert.ToDateTime(dateRn.Resource.Value2).ToString("dd-MMM-yyyy");
}
using (var rn = xlApp.Range["A1:A10"].WithComCleanup())
{
rn.Resource.Select();
rn.Resource.NumberFormat = "d-mmm-yyyy;@";
}
Otherwise formula's using Dates doesn't work - the formula in cell C4 is the same as C3:
This is an old thread. By this time, people either use OpenXML. OpenXML is much better. Well, many people like me are stuck because the initial developers use the interops.
I had same struggle for couple hours. I have tried everything here and other usage. It still gave me numerical representation.
Then I found out that I set the style. The style property ruined everything. I just added the NumberFormatproperty
Here is what I did. It works
//set the font style and size
Excel.Style styleDate = MyBook.Styles.Add("StyleDate");
styleDate.NumberFormat = "mm/dd/yyyy";//remember to include this when setting style property
styleDate.Font.Size = 10;
styleDate.Font.Name = "Arial"
//the function will return datetime value from database or whatever
DateTime DtVal= GetdatetimeVal();
xlWorkSheet.Cells[Row, Col].Style = styleDate
xlWorkSheet.Cells[Row, Col] = DtVal;
Old question but still relevant. I've generated a dictionary that gets the appropriate datetime format for each region, here is the helper class I generated:
https://github.com/anakic/ExcelDateTimeFormatHelper/blob/master/FormatHelper.cs
FWIW this is how I went about it:
Try this solution, in my softwarew work very well:
if (obj != null)
{
if (obj is DateTime)
{
if (DateTime.MinValue == ((DateTime)obj))
{
xlWorkSheet.Cells[x,y] = String.Empty;
}
else
{
dynamic opp = ((DateTime)obj);
xlWorkSheet.Cells[x,y] = (DateTime)opp;
}
}
}
Source: Stackoverflow.com