Change column format to date and sort

Aspose.Cells Product Family
1

Hi,


Greetings!

Using Aspose.Cells 7.2.1.0, I am trying to sort an excel file with respect to a column whose format is converted from “General” to “DateTime”.

I used the below code to change the format of the column from “General” to “DateTime” format and then sort the excel file.

In this case I want to convert the 8th column(I) to DateTime format and then sort by descending order of date.

private void sortWorkBook(string fPath)
{
var workbook = new Workbook(fPath);
int index = workbook.Worksheets.Count;
for (int i = 0; i < index; i++)
{
Worksheet ws = workbook.Worksheets[i];
//Make column I as DateTime.
Style dateStyle = workbook.CreateStyle();
dateStyle.Number = 14;
StyleFlag flag = new StyleFlag();
flag.All = true;
ws.Cells.Columns[8].ApplyStyle(dateStyle, flag);
var cells = ws.Cells;
DataSorter sorter = workbook.DataSorter;
sorter.Order1 = SortOrder.Descending;
sorter.Key1 = CellsHelper.ColumnNameToIndex(“I”);
var ca = new CellArea
{
StartRow = 1,
StartColumn = 3,
EndRow = cells.Rows.Count - 1,
EndColumn = cells.Columns.Count
};
sorter.Sort(cells, ca);
for (int j = 0; j < ws.Cells.Rows.Count; j++)
{
ws.AutoFitRow(j);
}
ws.AutoFitColumns();
}
workbook.Save(FilePath);
}

When I executed the application I got the format of the column changed, but the sorting was not happening based on date. Could you please help? Or suggest any other ideas to achieve this efficiently?

Thanks in advance!

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please try the following fixed code, it should work fine at your end. Please use the MaxRow and MaxColumn properties to get the maximum row and columns.

C#
string fPath = @“F:\Shak-Data-RW\Downloads\test.xlsx”;

var workbook = new Workbook(fPath);
int index = workbook.Worksheets.Count;
for (int i = 0; i < index; i++)
{
Worksheet ws = workbook.Worksheets[i];
//Make column I as DateTime.
Style dateStyle = workbook.CreateStyle();
dateStyle.Number = 14;
StyleFlag flag = new StyleFlag();
flag.All = true;
ws.Cells.Columns[8].ApplyStyle(dateStyle, flag);
var cells = ws.Cells;
DataSorter sorter = workbook.DataSorter;
sorter.Order1 = Aspose.Cells.SortOrder.Descending;
sorter.Key1 = CellsHelper.ColumnNameToIndex(“I”);
var ca = new CellArea
{
StartRow = 1,
StartColumn = 3,
EndRow = cells.MaxRow - 1,
EndColumn = cells.MaxColumn
};
sorter.Sort(cells, ca);
for (int j = 0; j < ws.Cells.MaxRow; j++)
{
ws.AutoFitRow(j);
}
ws.AutoFitColumns();
}
workbook.Save(fPath + “.out.xlsx”);