5

I'm using an external component called Gembox for creating Excel reports.

When exporting a property that's DateTime in C# towards Excel the value is in Excel shown as a number (for instance '-693593'). All other (basic) property types are displayed as the type the represent.

In Excel I would like a DateTime to be shown as a proper Date, in the Gembox options I could not find a option to set the format to Date.

Alternative option is to convert the DateTime with .ToString() while setting the value in Excel, this gives the desired result but is there a better way of doing so?

Some simplyfied code example:

ExcelFile excelFile = new ExcelFile();
ExcelWorksheet excelWorksheet = excelFile.Worksheets.Add("MyWorksheet");

int rownumber = 0;
int columnnumber = 2;
DateTime dateTime = DateTime.Now;

excelWorksheet.Cells[rownumber, columnnumber].Value = dateTime;

Example of output:

ExcelExportExample

Mario Z
  • 4,328
  • 2
  • 24
  • 38
Alex N
  • 51
  • 1
  • 4
  • When you say, 'as a proper date', do you mean the MM/DD/YYYY format? – Brian May 14 '13 at 16:27
  • As proper date I mean a value that Excel understands as date-time The problem seems to sit in the exporting through Gembox. It looks like the exporting of a DateTime is done as number instead of as a Excel date (and optional time). So users of the report also couldn't format it normaly to a regional date format. – Alex N May 14 '13 at 16:38
  • Can you post an example of it? – Brian May 14 '13 at 16:38
  • 1
    [This](http://gemboxsoftware.com/spreadsheet/help/html/M_GemBox_Spreadsheet_ExcelCell_ConvertExcelNumberToDateTime.htm) is from Gembox's forums. – Brian May 14 '13 at 17:04
  • Thanks! The article you're link is the other way round, from Excel-value to .NET DateTime. But it has some interesting remarks comments: _Excel file format doesn't have a separate data type for date and time. DateTime value is stored as IEEE number encoded in a special way_ Likely when exporting a DateTime value into Excel this should be formatted into some sort of date/time number format that allows Excel to interpret as a special IEEE number. – Alex N May 15 '13 at 06:14

3 Answers3

3

Make sure that the variable 'dateTime' is a DateTime object, then

excelWorksheet.Cells[rownumber, columnnumber].Value = dateTime;
excelWorksheet.Cells[rownumber, columnnumber].Style.NumberFormat = "dd MMM yyyy";
sawe
  • 1,141
  • 14
  • 24
2

format the excel cell to display the date time as you would in Excel itself,

ExcelFile excelFile = new ExcelFile();
ExcelWorksheet excelWorksheet = excelFile.Worksheets.Add("MyWorksheet");

int rownumber = 0;
int columnnumber = 2;
DateTime dateTime = DateTime.Now;

Microsoft.Office.Interop.Excel.Range range = excelWorksheet.Cells[rownumber, columnnumber] as Range;
range.NumberFormat = "dd/MMM/yyyy";
range.Value2 = dateTime;

This will set your excel cell to date format.

In the line range.NumberFormat = "dd/MMM/yyyy"; you can give any of the format below:

dd/MM/yy         // 01/01/01
dd/MMM/yy         // 01/Jan/01
ddd/MMM/yyyy      // Mon/Jan/2001
ddd dd/MMM/yyyy   // Mon 01/Jan/2001
hh:mm:ss          // 01:01:01  - 24 hour
hh:mm:ss AM/PM    // 01:01:01 pm - 12 hour

the format below is for used by the .Net format only, this will not work with Excel.
HH:mm:ss          // 01:01:01  - 24 hour
hh:mm:ss t        // 01:01:01 pm - 12 hour
HH:mm:ss.fff      // 01:01:01.123  - 24 hour with millisecond


or combination of both date and time;
ddd dd/MMM/yyyy hh:mm:ss
Jegan
  • 1,227
  • 9
  • 16
  • the 't' or 'tt' designator is not working for me. Is that the case with you guys as well? hh:mm:ss does work. Looks like a bug. – TheTechGuy Aug 06 '13 at 13:02
  • 1
    For Excel formatting you need to use the hh:mm:ss AM/PM format. The 't' is if you are formatting using the .Net format code. – Jegan Aug 06 '13 at 16:36
0

Actually, you need to set value method:

var value = DateTime.Now;
var cell = excelWorksheet.Cells[rownumber, columnnumber]
cell.SetValue(value);
cell.Style.NumberFormat = "dd/MM/yyyy";

The override recognises the correct type (DateTime) and shows it accordingly in Excel. Tested with version Gembox v3.9

mas_oz2k1
  • 2,851
  • 3
  • 34
  • 41