CODEWITHSUNDEEP
c#excel
kosnkov
kosnkov

Reputation: 5941

Excel locale wrong time formating

i have very weird problem with excel. I have two files , each file has only one column which is date, using c# interoop libraries I am opening each file and save as csv. If I open each excel , then time format is dd/MM/YYYY but now whats happen, after saving to csv one excel has the same time format which is dd/MM/YYYY but the other one has MM/dd/YYYY , how is this possible ?

This is code to save as csv:

   var application = new Application();
    var workbooks = application.Workbooks;
    var workbook = workbooks.Open(file);


    application.DisplayAlerts = false;

    var tempFile = file.Substring(0, file.Length - 4) + ".csv";

    if (File.Exists(tempFile))
        Directory.Delete(tempFile);

    workbook.SaveAs(tempFile, XlFileFormat.xlCSVWindows);
    Extensions.DisposeExcel(workbook, application, workbooks, null, null);

I don't set any localisation in the application. Maybe worth to mention is that one excel has xlsx extenstaion and the other one (whis is flipping day with month) is much older with xls.

One more thing, I've noticed that when I save these excel as csv using excel save as, then all is fine.

Upvotes: 1

Views: 1168

Answers (1)

Pilgerstorfer Franz
Pilgerstorfer Franz

Reputation: 8359

I did an example concerning your described problem. I used your code (adapted it a bit) and two Excel-files. Unfortunatelly I could not reproduce your problem.

// call the method with .xlsx and .xls file.
//export2Csv(@"e:\temp\demoNEW.xlsx");
//export2Csv(@"e:\temp\demoOLD.xls");

private static void export2Csv(string fileName)
{
    string csvFile = Path.ChangeExtension(fileName, "csv");

    var application = new Application();
    var workbooks = application.Workbooks;
    var workbook = workbooks.Open(fileName);

    application.DisplayAlerts = false;

    // ATTENTION!! german number format, must be adapted to your locale culture/language
    workbook.ActiveSheet.Range["A1", "A65535"].NumberFormat = "TT/MM/JJJJ hh:mm:ss";  

    workbook.SaveAs(csvFile, XlFileFormat.xlCSVWindows);

    // clean up Excel - I think Extensions.DisposeExcel(..) does the same?!
    application.Quit();
    workbook = null;
    application = null;
    GC.WaitForPendingFinalizers();
    GC.Collect();
}

First I created a .xlsx file and entered some dates (1.1.2014 to 31.1.2014 german format) and afterwards saved it as .xls file.

When running my code both files created the same result in dd/MM/yyyy. I did some background check too.

Whenever a managed client calls a method on a COM object and it needs to pass in culture-specific information, it does so using the CurrentCulture (locale) that matches the current thread locale. The current thread locale is inherited from the user's regional settings by default.

  • within SO I found a question&answer Determine Excel Language that shows how to display current system culture and excel UI culture

check this snippet

CultureInfo cSystemCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo cExcelCulture = new CultureInfo(application.LanguageSettings.get_LanguageID(Microsoft.Office.Core.MsoAppLanguageID.msoLanguageIDUI));

Console.WriteLine(cSystemCulture);
Console.WriteLine(cExcelCulture);

  • Some posts SO ExcelNumberFormat describe a solution to call all code from one and the same thread and cultureInfo en-US

  • others recommend to format each cell with a certain numberFormat (see social.msdn )

So not quite a solution, but I hope at least some good hints for you.

In case none of these points helps you out, you could still send me your .xlsx and .xls files to [email protected] and I will have a further look into)

EDIT According to the given files, I found the answer to your problem. Your file right.xls is an Excel file, but your file wrong.xls contains HTML markup. So the only problem is the wrong file extension!

wrong.xls should be renamed to wrong.html!

Saying that, I tried to figure out, how my code could work despite the fact that there are two different input files. So I changed my code (already edited above) and added a line to manually set the number format.

// ATTENTION!! german number format, must be adapted to your locale culture/language
workbook.ActiveSheet.Range["A1", "A65535"].NumberFormat = "TT/MM/JJJJ hh:mm:ss";

After that, both input files printed out the same dateTime format.

Upvotes: 1

Related Questions