Export dates to Csv and tell excel the format

Question

My program (C# .Net 4.5) have to be able to generate some reports which can be loaded into Excel. I have choosen to export the reports as .csv format due to it being the most simple way to export to a format excel understands. And it does not requre Excel to be installed on the machine running my program.

My problem is exporting the dates.

My program is going to run on customer PC's which may (most likely) have different ways to show dates, which means excel expects dates in different formats.

My current solution is to export dates as:

DateTime LogTime = DateTime.Now;
String TimeFormat = //The format for the specific location, like: "HH:mm:ss dd/MM-yyyy"

//To csv:
// ="10:24:13 27-05-2014"
String reportTime = "=\""+LogTime.ToString(TimeFormat)+"\"";

The problem with this is i create different files based on different locations. So sending a file from one location to another may result in the date being wrong.

My question is, are there someway to tell Excel which format my date is in? Something like:

//To csv:
// =DateFormatted(10:24:13 27-05-2014,HH:mm:ss dd/MM-yyyy)
String reportTime = "=DateFormatted(" + LogTime.ToString(TimeFormat) + "," + TimeFormat + ")";

Then Excel know the exactly how to read the dates without I have to worry about different locations.

Answer 1

Export the dates in the format yyyy-MM-dd HH:mm:ss

Answer 2

As you are saving to CSV I'm afraid there isn't.

But you can use the yyyy/MM/dd hh:mm:ss format. As fas as I know this will work in all cultures.

Answer 3

In my experience, the open-source NPOI libraries (https://npoi.codeplex.com/) are excellent for this.

• You can export/import data to/from Excel, and Word for that matter
• They do NOT require Excel to be installed on the machine
• Support for both xls and xlsx formats
• Complete control over formatting
• Overall, way more functionality than would ever be possible with csv