Sorting in Excel /Aspose for Week Range

Hi,

I have a week Data as below which will have Data as Week Start date - End Date in MM/dd/YY -MM/dd/YY format

But when I try to Sort it it gets Unevenly Sorted. Please Help top Sort the below data.
image.png (2.0 KB)
Week
01/02/17-01/08/17
01/02/17-01/08/17
01/02/17-01/08/17
02/01/16-02/07/16
02/01/16-02/07/16
02/13/17-02/19/17
02/13/17-02/19/17
03/07/16-03/13/16
03/07/16-03/13/16
03/20/17-03/26/17
03/20/17-03/26/17
05/02/16-05/08/16
05/02/16-05/08/16
08/08/16-08/14/16
08/08/16-08/14/16
09/05/16-09/11/16
09/05/16-09/11/16
10/31/16-11/06/16
10/31/16-11/06/16

@aymarzapak,

Thanks for the screenshot and details.

We did evaluate your issue a bit. We doubt whether MS Excel can support such kind of data sorting. I did try to sort your data (after pasting the values into a column) in MS Excel but to no avail. I am afraid, we cannot support it as we can only sort it by characters order because it is just string value. Also, it is hard or even impossible for anyone to give one custom list for such kind of scenario.

By the way, we think you may try to use some other ways around to make this column values as simple DateTime values for data sorting, and you can make another parallel column for this string expression or so. For example, appending one column which holds the value that in the range of the corresponding week:
i.e.,
01/02/17-01/08/17 —> 01/02/17
02/01/16-02/07/16 —> 02/01/16
etc.
and then sort the column with DateTime values.

Hi Amjad Thanks So much for replying. I’ve attached a source file Can you please help me this if I have to … would be so kind of you

AfterSorting.zip (10.3 KB)

@aymarzapak,

As I told you, please devise your own logic and use your own code to make all your week intervals to simple (single) DateTime values (starting date) and then apply data sorting upon data.

@aymarzapak

For your requirement, please see the following sample code.

C#

Workbook wb = new Workbook("AfterSorting.ods");

Cells cells = wb.Worksheets[0].Cells;

int maxRow = 19;

for (int i = 1; i <= maxRow; i++)
{
	cells[i, 1].Formula = "=DATEVALUE(LEFT(A" + (i+1) + ",8))";
}

wb.CalculateFormula();

DataSorter sorter = wb.DataSorter;
sorter.Key1 = 1;
sorter.Order1 = SortOrder.Descending;

sorter.Sort(cells, 1, 0, maxRow, 1);
1 Like

Thanks Shakeel for your kind reply. I figured out the way for JAVA. by inserting a new column with the date of the prevoius and the Sorting and deleting the new added column before saving the sheet

@aymarzapak

Thanks for your feedback and using Aspose.Cells.

It is good to know that you were able to sort out this issue. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

1 Like