Solved

Sorte a Datefield in ExcelSheet using C#

Posted on 2013-06-25
1
205 Views
Last Modified: 2013-06-25
Hi Experts,

i try to sort an excelsheet based on a column polulated with date entries.

The problem seems to be, that the dates are handled as text.

What i want:
01.01.2013
02.01.2013
..
01.02.2013
02.02.2013

What i get:
01.01.2013
01.02.2013
..
02.01.2013
02.02.2013

The column (to be precise: The cells in the range to sort) is formated as a "Date" field in the worksheet.

Here is the Code i use:
Excel.Range range = oSheet.get_Range("A4:W" + iLastUsedRow, Type.Missing);

range.Sort(range.Columns[1, Type.Missing], Excel.XlSortOrder.xlAscending,Excel.XlYesNoGuess.xlNo,Type.Missing,Type.Missing,Excel.XlSortOrientation.xlSortColumns,Excel.XlSortMethod.xlPinYin,Excel.XlSortDataOption.xlSortTextAsNumbers);
 
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);

Open in new window


Any help is greatly appreciated!
BR
Tom
0
Comment
Question by:oczos
1 Comment
 

Accepted Solution

by:
oczos earned 0 total points
ID: 39274882
Now that i have written down the problem, i instantly see a possible answer:

This may have to do with localization?

Here in europe the date is generally of a "DD.MM.YYYY" format.

Since my routine changes globalization to "en-US" (otherwise the file wont open) maybe excel treats the date in a "MM.DD.YYYY" format and therefore the current result is "correct"?

UPDATE:
I have done a little testing and found out that the localization indeed was the problem.

When i chance it to "de-DE" (German) before sorting, it works like i need it.

Might not be the sexiest solution out there, but fits me needs.

Question closed
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now