0

In C# using asp/MVC the app generates an Excel .xlsx file based on data thats filled in to the specific columns, works great.

But the goal is to provide additional worksheets that use the same columns but sort on specific colums, such as Column "J"

  var wb = new XLWorkbook();
  var ws = wb.Worksheets.Add("Proj Info");
  var ws2 = wb.Worksheets.Add("Sort By Dates");

The worksheet ws has values filled in by variables or formulas, the data is correct, but cannot make it sort on a column

  ws.AutoFilter.Column("J");   //no, nothing changes
  ws.Column("J").Sort(); -> this shifts all the columns up but does not sort
  ws.Column("J").Sort(XLSortOrder.Ascending);  ->same, doesnt sort only shifts

Update: ws.Sort(9); worked in sorting, but the problem is that Column 10 has a Formula, and I need to sort on that Column.

   ws.Cell("J" + c).FormulaR1C1 = "=C$2-F" + c;

With this? it WILL NOT SORT. The ws.Sort(10); works when the cell contains a final value, but when its got the Formula? Is there any workaround to force the Excel page to sort after its implemented the formula's in each cell?

Brad Rogers
  • 89
  • 2
  • 12

1 Answers1

2

You are not sorting the table, but the values in column J.

Try this:

ws.Sort("Column10");
Xiaoy312
  • 14,292
  • 1
  • 32
  • 44
  • 1
    Had to write it as: ws.Sort(10); because ws.Sort("Column10"); got an error: must enter a valid value 1 - 65536? But, if I use this on the other column it works! ws.Sort(11) sorted by that column. Just does not work on Column 10, only difference is Col 10 gets the values from a formula. Every cell in Col 10 has "=C$4-E6" (Or E7, E8, etc..) Can you sort by column when its got a formula? – Brad Rogers Aug 29 '16 at 20:50
  • Sorting the formula wouldn't make any sense, as the excel formula would not be executed by ClosedXml. What you can do is... compute the result and write it to a column, sort by that one, and then delete it. However, for this case, you can simply sort by column 5(`E`) desc. – Xiaoy312 Aug 30 '16 at 15:14
  • Something is odd because now all worksheets sort on Column A, it was working fine, ws2.Sort(3); would sort on Column "C". Not anymore, they sort on Column "A". But it was working. Took out all formulas and do the compute part in the controller, just feed in variables. Any idea why the Sort would work, then not work later? – Brad Rogers Sep 12 '16 at 21:40