Excel sorting is stuck when ClosedXML is used

Question

I have a xlsx of 14000 rows. I can sort it by a column in a second using the standard Excel app. When I try to do the same in ClosedXML, it is stuck. I mean that it is extremely slow (like 10 minutes or even more), but eventually it completes the operation. So let's say I create a file

XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("my test");
ws.Cell(1, 1).Value = "A";
ws.Cell(1, 2).Value = "B";
for (int i = 0; i < 14000; i++)
{
    ws.Cell(i + 2, 1).Value = i;
    ws.Cell(i + 2, 2).Value = i % 2;
}
wb.SaveAs("test.xlsx");

Now there are several ways to sort it by the column B, for example

var tab = ws.Range(1,1,14001,2);
Stopwatch stopwatch = new Stopwatch();
Console.WriteLine("start");
stopwatch.Start();
tab.SetAutoFilter();
wb.SaveAs("test.xlsx");
ws.AutoFilter.Sort(2);
stopwatch.Stop();
Console.WriteLine("Sorted after " + (int)stopwatch.Elapsed.TotalSeconds + " seconds");
wb.SaveAs("test.xlsx");

Should I switch to Microsoft.Office.Interop.Excel to sort a table by a column? Or is there a usable method in ClosedXML?

Answer 1

Yes, sure, the Interop is much better for sorting, it is immediate in this case while ClosedXML is not usable. Here is the sample code

 var excelApp = new Microsoft.Office.Interop.Excel.Application();
 // Make the object visible.
 excelApp.Visible = true;
 excelApp.Workbooks.Open(Path.Combine( Directory.GetCurrentDirectory(), "test.xlsx"));
 Worksheet ws = (Worksheet)excelApp.ActiveSheet;

Make sure to use ClosedXML before the above to create the spreadsheet (it is faster than interop in that case).

Finally the sorting part

var tab = (Range)ws.Range[ws.Cells[2, 1], ws.Cells[14001, 2]];
Stopwatch stopwatch = new Stopwatch();
Console.WriteLine("start");
stopwatch.Start();
ws.Cells[1, 2].AutoFilter();
tab.Select();
tab.Sort(ws.Cells[2,2], XlSortOrder.xlAscending);
Console.WriteLine("Sorted after " + (int)stopwatch.Elapsed.TotalSeconds + " seconds");    
excelApp.ActiveWorkbook.Save();
excelApp.ActiveWorkbook.Close();
excelApp.Quit();

It will take a second, as it is supposed to.

Sorting helper library shared