Strange result sorting Excel file using Aspose.Cells

Situation. I have a .xls file. I need to sort all data in the file by “D” column and I use Aspose.Cells to achieve this. Here is my code:

public static void RemoveWhiteSpacesAndSortBySecondColumn(string filePath)
{
if (String.IsNullOrEmpty(filePath))
throw new ArgumentException(“should be not null”, “filePath”);

filePath = Path.GetFullPath(filePath);
var fileName = Path.GetFileNameWithoutExtension(filePath);
var fileExtension = Path.GetExtension(filePath);
var filePathCopy = filePath.Replace(fileName + fileExtension, fileName + “_copy” + fileExtension);
File.Copy(filePath, filePathCopy, true);

//Opening an existing workbook
var workbook = new Workbook(filePathCopy);

//Accessing first worksheet
var worksheet = workbook.Worksheets[0];

var cells = worksheet.Cells;

var k = 0;
for (var i = 1; i < cells.Rows.Count + 1; i++)
{
var cellValue = cells[“D” + i].Value;
if (cellValue == null)
{
k
++;
cellValue
= string.Empty;
}
if (String.IsNullOrWhiteSpace(cellValue.ToString()))
k
++;
cellValue
= Regex.Replace(cellValue.ToString(), @"\s+", " ");
cells
[“D” + i].Value = cellValue;
}
Console.WriteLine(“Amount of empty cells in the D1 column: {0}”, k);

</span><span class="com" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 128, 128);">//Get the workbook datasorter object.</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">DataSorter</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> sorter </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> workbook</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">DataSorter</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">

</span><span class="com" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 128, 128);">//Set the first order for datasorter object.</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
sorter</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">Order1</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">Aspose</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">Cells</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">SortOrder</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">Ascending</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">

</span><span class="com" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 128, 128);">//Define the first key.</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
sorter</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">Key1</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="lit" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">3</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">

</span><span class="com" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 128, 128);">//Create a cells area (range).</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">var</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> ca </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">new</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">CellArea</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">{</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
     </span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">StartRow</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="lit" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">0</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
     </span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">StartColumn</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="lit" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">0</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
     </span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">EndRow</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> cells</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">Rows</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">Count</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">-</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="lit" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">1</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
     </span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">EndColumn</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> cells</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">Columns</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">Count</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">};</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">

</span><span class="com" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 128, 128);">//Sort data</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
sorter</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">Sort</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">cells</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> ca</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">);</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">

workbook</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">.</span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(43, 145, 175);">Save</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">filePathCopy</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">);</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">

}

Problem. The result of sorting looks strange: there are two groups of strings in the “D” column, which are sorted in alphabetical order. BUT, all strings should be in one group. Why is this happening?

Hi Ivan,

Thanks for your posting and using Aspose.Cells.

Please download and use the latest version: Aspose.Cells for .NET 8.0.0 it is working fine.

I have tested this issue with these steps.

I made a copy of Data.xls and sorted with respect to column D manually using MS-Excel 2010 and then saved it as “Data-Sorted.xlsx

Then I compared the results of Aspose.Cells with the values in Data-Sorted.xls. If it does not match, it would throw exception but all of the values matched and it did not throw any exception. It means, sorting is being done by Aspose.Cells successfully.

Here is my code. I have highlighted the code which is comparing the results in a red color.

C#
string filePath = @“F:\Shak-Data-RW\Downloads\Data.xls”;

filePath = Path.GetFullPath(filePath);
var fileName = Path.GetFileNameWithoutExtension(filePath);
var fileExtension = Path.GetExtension(filePath);
var filePathCopy = filePath.Replace(fileName + fileExtension, fileName + “_copy” + fileExtension);
File.Copy(filePath, filePathCopy, true);

//Opening an existing workbook
var workbook = new Workbook(filePathCopy);

//Accessing first worksheet
var worksheet = workbook.Worksheets[0];

var cells = worksheet.Cells;

//var k = 0;
//for (var i = 1; i < cells.Rows.Count + 1; i++)
//{
// var cellValue = cells[“D” + i].Value;
// if (cellValue == null)
// {
// k++;
// cellValue = string.Empty;
// }
// if (String.IsNullOrWhiteSpace(cellValue.ToString()))
// k++;
// cellValue = Regex.Replace(cellValue.ToString(), @“\s+”, " ");
// cells[“D” + i].Value = cellValue;
//}
///Console.WriteLine(“Amount of empty cells in the D1 column: {0}”, k);

//Get the workbook datasorter object.
DataSorter sorter = workbook.DataSorter;

//Set the first order for datasorter object.
sorter.Order1 = Aspose.Cells.SortOrder.Ascending;

//Define the first key.
sorter.Key1 = 3;

//Create a cells area (range).
var ca = new CellArea
{
StartRow = 0,
StartColumn = 0,
EndRow = cells.Rows.Count - 1,
EndColumn = cells.Columns.Count
};

//Sort data
sorter.Sort(cells, ca);

Workbook twb = new Workbook(“Data-Sorted.xls”);
Worksheet tws = twb.Worksheets[0];

for (int i = 1; i < 1000; i++)
{
string val1 = worksheet.Cells[“D” + i].StringValue;
string val2 = tws.Cells[“D” + i].StringValue;

if (val1 != val2)
{
throw new Exception(“Did not match D” + i);
}
}

workbook.Save(filePathCopy);

Thanks for your reply. I’ve installed the latest version of Aspose, but the problem still remains.

Hi,

Thanks for your posting and using Aspose.Cells.

We were not able to observe any issue. Aspose.Cells is sorting the data correctly. You can manually sort the data using MS-Excel and compare the results with Aspose.Cells and it will match with each other. So, there is no issue with sorting.