Any way to dynamically sort on multiple columns using ExcelSort?

Help Activities
, ,
1

I’m pulling a sort order from another location to populate the ExcelSort>SortColumns section. If my variable ExcelSortOrder is a single column name, then it works perfectly. If I try to use multiple columns separated by a comma, it fails with “Column Not Found”. This happens even if I put in the string directly.

Is there a way to do this so that I can sort on multiple columns with a variable number of column quantities and names?

2

Hi @Kevin_McGrath

  1. Create a variable called “ExcelSortOrder” to hold the sort order string. This variable should be initialized to an empty string.
  2. Populate the “ExcelSortOrder” variable with the column names you want to sort by. You can use a string variable to hold the column names, and then split that string into an array of column names using the “Split” method. For example, if your column names are stored in a variable called “sortColumns”, you can use the following code to split the string into an array:
    Dim columnArray As String() = sortColumns.Split(","c)
  3. Build the sort order string dynamically by looping through the array of column names and adding them to the “ExcelSortOrder” variable. You can use a “For Each” loop to iterate through the array, and then use the “String.Format” method to add each column name to the “ExcelSortOrder” variable.
    4.Once you have built the sort order string, you can use it to sort the data in your Excel file using a dynamic LINQ query. To do this, you will need to add the “System.Linq.Dynamic” namespace to your project and use the “OrderBy” method to sort the data.
    Dim sortedData = dt.AsEnumerable().OrderBy(ExcelSortOrder)

Hope this will resolve your issue.

3

Thank you, @Nitya1! Question: in Step 3, how am I delimiting the column names in the ExcelSortOrder variable?

4

How do you want to delimit the column names ?

5

I would assume that a comma would work as a delimiter, but if that is the case, and the column sort order I’m pulling in as an input already has them delimited by comma. Could I just use that string and send it into the LINQ query?

6

Yes you can do this way , let me know if you stuck somewhere.

7

I’m unable to add the System.Linq.Dynamic namespace. My only options are System.Linq , System.Linq.Expressions , System.Linq.Expressions.Interpreter, and System.Xml.Linq.

8

Dynamic LINQ is not part of the .Net and is offered when an additional nuget package is referenced, done with “Manage Packages”

For an option without dynamic LINQ have a look here: