C# Object containing datetime and specific cultureinfo to be fully compatible with Excel datetime cell

Question

As I am trying to import excel file into C# object, I notice that so far the only way to create a dateTime type including the specific cultureinfo and the datetimeformat of the specific cell is converting into string.

For example, it can be instantiated:

var date = new DateTime(DateTime.Now.Ticks, DateTimeKind.Unspecified);
var culturedDateString = date.ToString(specifiedCulture);

But I have not seen any c# object that can contain both datetime value as well as the specific cultureInfo and datetimeformat that Excel cell might contain.

Please let me know if there is any equivalent type in c# which can contain both dateTime and specific cultureinfo including datetimeFormat. Otherwise I need to think about taking different approaches.

If whoever voted for closing, I wish this someone knows more than me in the limitation I am seeing in C# types. The problem using DateTime or any other C# type is that there is no equivalent of object that can contain specific cultureInfo so that later this type can be reverted back to desired string representation as how Excel saves values. Currently there is no possible two way conversions from string representation of datetime with specific cultureformat or Excel date value -> C# type -> back to the value — swcraft, May 02 '20 at 14:09
Maybe you're refering to this?https://stackoverflow.com/questions/13797727/datetime-and-cultureinfo — Gintas, May 02 '20 at 14:20
Regarding above link, you need to understand whether that string representation is in mm/dd/yyyy or dd/mm/yyyy format (first argument of Parse or TryParse). Some object in Excel contains that information so that you can convert interpreting the specific cultureInfo. Try to save specific cultureInfo to DateTime type. Let me know if any C# type can do that which support it. Othewise at best creating a custom type and somehow include the cultureinfo then I need to figure out conversion of that type throughout system (DataSet, into Database, back to object layer, etc.) — swcraft, May 02 '20 at 14:27
You should get a DateTime object from the Excel library you are using. You should put that DateTime in the DB and convert to a string only when printing that date on screen. You would typically never parse a string into DateTime perhaps unless the user is allowed to edit the date. — user8728340, May 02 '20 at 14:42
That's why specific custom type is mentioned in above thread to contain the specific culture info. How else can you give back to user as correct exported Excel file back from your db (including correct culture format of any datetime value)? For example once saved as string in database, when you try to retrieve back to export as Excel data, there is no way to know if it is dd/mm/yyyy or mm/dd/yyyy. — swcraft, May 02 '20 at 14:58
There is nothing built-in in .NET for that, to answer your original question. I agree you need a custom type of sorts. The number format must be saved in the DB. F.ex one DB column for the raw DateTime value, and another DB column for the number format. You need both to print datetimes as expected. — user8728340, May 02 '20 at 15:02
Right, I have tried numerous different ways to output C# type containing specific cultureInfo other than currentCulture, and they all lose that information. Quiet disappointed. — swcraft, May 02 '20 at 15:07
You didn't say how you're importing the Excel data nor how the dates in the Excel file are formatted. If the Cell Format in the Excel file is set to `Date`, the Cell value is imported as a DateTime object (thus, you don't need to parse it). If the original Cell Format is instead Generic (a string, in practice), you'll need to know what language pattern was used to fill the data. If you use OleDb to read the data and fill a DataTable, the `DataTable.Locale` returns the CultureInfo. To format the DateTime for presentation using the current thread culture, you just need `DateTime.ToString()`. — Jimi, May 02 '20 at 16:09
... using a specific DateTime Format, eventually. Or use `CultureInfo.CreateSpecificCulture()` to generate a Culture that provides the Formats. Or set `Thread.CurrentThread.CurrentCulture` and `Thread.CurrentThread.CurrentUICulture` to a specific CultureInfo. If you're dealing with strings, you have the usual *interpretation* / *lost in translation* problem, so User intervention may be needed (to enter the format/culture used to generate the strings, unless you can derive it yourself). — Jimi, May 02 '20 at 16:14
If you want to go *deeper*, inside the `xlsx` archive, the `styles.xml` file contains the FormatIDs applied to each Column: a `numFmtId="[Number]"` linked to a ``, where `formatCode="d/m/yy;@"` in case of a Date Column formatted using the `InvariantCulture` (or `en-US`) format. — Jimi, May 02 '20 at 16:32
@Jimi your comment seems answered partially but not the actual request of this thread, which is the .net or c# type that contains specific CultureInfo as it saves the datetime. I thought I saw some hope when you wrote "DataTable.Locale" returns the CultureInfo then after 2 second realized that is almost like a forest level when my question was asking if each tree can contain what its kind (cutlureInfo) is, which is not there once you save to .net DateTime type and DataTable.Locale does not represent each cell's cultureformat. (continue in next thread..) — swcraft, May 04 '20 at 15:47
@Jimi .... If you set the specific culture in CurrentThread level, that shows all DateTime objects in your array as that format as DataTime doesn't hold on cultureInfo and even if you parse with specific culture, that's to parse correct but not to save into DateTime as specific culture format. — swcraft, May 04 '20 at 15:47
You're mixing things up. DateTime is a number, it doesn't hold any *format*. A Format is a string conversion that represents a Date or Time value in the current System or UI thread culture. Now, what date and time (note: not DateTime) value (note: value, not Format) you get from an Excel Sheet, depends on how you read it. If you use OleDb, the value of a Cell in a WorkSheet comes in as DateTime (which, I repeat, doesn't have any Format) **if the Column/Cell Format was set to Date or Date/Time**, otherwise is returned as a string, the way a User stored it. — Jimi, May 04 '20 at 16:03
If you get a DateTime Cell value, you can then apply a format, for presentation, as you see fit. Just call [DateTime].ToString("[Format]"), and/or pass a CultureInfo object, it contains the localized Date and Time formats. **If you instead read a string**, then you need to know what format was used to write that string. Then use `DateTime.TryParseExact()` to convert to a DateTime object that you can format as required. As already mentioned, this format is hard-coded in the configuration XML document inside the `.xlsx` archive (an `xlsx` is a zip file) and nowhere else. — Jimi, May 04 '20 at 16:03
@Jimi Ok, so now here is the question I originally posted. Then after importing an excel cell to .net using C# type, can you convert back to the excel value correctly including original format user used? Because then true two way conversion can be worked. If you are only able to convert back using your current culture, then user will get super confused if month and day are reverted, etc. "You're mixing things up." you said but from the beginning sorry to tell you but you have been not correctly understanding original request but it's ok because our conversation actually is helpful. — swcraft, May 04 '20 at 16:06
Again: if the Cells values were imported as DateTime (for example, using OleDb to load the data in a DataSet/DataTable), you change the value then save. The values are formatted in the Excel sheet as defined inside the XML configuration file, nothing changes. The XML configuration file contains formats that were defined by the User when a Cell or Column format was decided. If you read them as strings (because the User didn't really know what they were doing :), you need to send them back formatted using the same string format that was used to read them. — Jimi, May 04 '20 at 16:14
@Jimi, thanks that is one solution for exporting back, but that is still work around as the saved DateTime in database doesn't have the cultureInfo. But at least that is helpful to know that as long as user has that sheet not removed then at least user can export back to the sheet correct. But why can't you just say know there is none like in c# type? so people reading these don't get confused. I think previous threads already answered. Although your work around working for partial scenarios could be helpful. — swcraft, May 04 '20 at 16:18
The *you're mixing things up* is related to the confusion between DateTime (which has no format whatsoever, the Culture is irrelevant) and the string Format used for presentation (and for presentation purposes alone) determined by current UI language/culture. You, as a dev, only work with DateTime objects, never with strings. You just present a DateTime as a formatted string for the User convenience. In a database, you store only DateTime or DateTimeOffset values, never (ever) a formatted string. The Format is decided when the data is presented in a specific UI and Culture. — Jimi, May 04 '20 at 16:20
That's what have been the original question... so my complain to .net type has been, why didn't they just create a type which has the original cultureInfo as one of properties, etc. Because look at unnecessary work we have to do to display correctly to user not to confuse them. It is not only confusing but actually losing original information. Probably the only way to display correctly the dateTime values saved is by using the exact same Excel file to export these values. — swcraft, May 04 '20 at 22:33

score 1 · Answer 1 · answered May 06 '20 at 14:16

There is no type in .NET which embeds both a DateTime and a CultureInfo. But you can make one yourself:

public class DateTimeAndCultureInfo {
    public DateTime DateTime { get; set; }
    public CultureInfo CultureInfo { get;set; }
}

However, when you get a date from an Excel library, such a type probably won't help much, because Excel doesn't (exclusively) rely on a CultureInfo to render its cells. Internally Excel uses its own kind of number format strings as described here:

https://support.office.com/en-us/article/available-number-formats-in-excel-0afe8f52-97db-41f1-b972-4b46e9f1e8d2

Tangentially, .NET supports its own kind of number format strings, which are similar to, but incompatible with Excel's number format strings:

https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings

Usually you need three separate pieces of information to display a value: the raw value, a number format string and a CultureInfo.

The CultureInfo stores basic stuff like decimal separator, thousand separator, currency symbol, default long/short date formats, and other locale-specific stuff, but technically does not control how the value appears on screen.

The CultureInfo is typically global to your application, f.ex taken from a configuration or OS, and indicates overall what language/country the application is running in. Only rarely would you want to bundle a CultureInfo with every DateTime instance.

What number format string to use may depend on the use case: some times you might want to display a short date (like "01-01-2020" in a list), some times you might want to display a longer version of the same date (like "1. january 2020" in an overview).

Other times, you might want to render a spreadsheet exactly like Excel. In these cases it makes sense to treat both the raw cell value and number format string as a single type, f.ex:

public class Cell {
    public object Value { get; set; }
    public string FormatString { get;set; }
}

.NET provides the individual pieces to work with dates/values however you want, but you have to write the code yourself to glue it all together depending on the use case, which can vary plenty.

Maybe yours can be accepted as answer with some verifications. I have seen in the excel config setting of each cell containing Hex values (i.e [$-1043B]) as well as datetime format (i.e. d.M.yyyy but in the format of 'd.M.yyyy;@'). And I believe we can look up MS standard such as this: https://learn.microsoft.com/en-us/openspecs/windows_protocols/ms-lcid/70feba9f-294e-491e-b6eb-56532684c37f The HEX values seem actually 8 digit. More info: https://stackoverflow.com/questions/54134729/what-does-the-130000-in-excel-locale-code-130000-mean — swcraft, May 06 '20 at 16:05

C# Object containing datetime and specific cultureinfo to be fully compatible with Excel datetime cell

1 Answers1

Linked