F# Excel Range.Sort Fails or Rearranges Columns

问题:

I have two cases. The preliminary code:

open Microsoft.Office.Interop.Excel

let xl = ApplicationClass()
xl.Workbooks.OpenText(fileName...)
let wb = xl.Workbooks.Item(1)
let ws = wb.ActiveSheet :?> Worksheet

let rows = string ws.UsedRange.Rows.Count

First, I try the following to sort:

ws.Sort.SortFields.Clear()
ws.Sort.SortFields.Add(xl.Range("A8:A" + rows), XlSortOn.xlSortOnValues, XlSortOrder.xlAscending, XlSortDataOption.xlSortNormal) |> ignore
ws.Sort.SortFields.Add(xl.Range("H8:H" + rows), XlSortOn.xlSortOnValues, XlSortOrder.xlAscending, XlSortDataOption.xlSortNormal) |> ignore
ws.Sort.SetRange(xl.Range("A7:I" + rows))
ws.Sort.Header <- XlYesNoGuess.xlYes
ws.Sort.MatchCase <- false
ws.Sort.Orientation <- XlSortOrientation.xlSortRows
ws.Sort.SortMethod <- XlSortMethod.xlPinYin
ws.Sort.Apply()

This results in "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.".

Then I try the following to sort:

ws.Range("A7:I" + rows).Sort(xl.Range("A8:A" + rows), XlSortOrder.xlAscending,
                             xl.Range("H8:H" + rows), "", XlSortOrder.xlAscending,
                             "", XlSortOrder.xlAscending, XlYesNoGuess.xlYes,
                             XlSortOrientation.xlSortRows) |> ignore

This results in my columns being rearranged, though I don't see any logic to the rearrangement. And the rows are not sorted.

Please tell me what I'm doing wrong.

回答1:

I haven't figured out why the first Sort attempt doesn't work. But I checked out the documentation on the second Sort. Using named parameters and dropping all but necessary parameters, I came up with the following:

ws.Range("A8:H" + rows).Sort(Key1=xl.Range("A8:A" + rows), Key2=xl.Range("H8:H" + rows),
                             Orientation=XlSortOrientation.xlSortColumns) |> ignore

The default Orientation is xlSortRows. I interpret this as sorting the rows, the normal, default, intuitive sort that Excel does when one sorts manually. Oh no. If you want the normal, default, intuitive sort that Excel does when one sorts manually, specify xlSortColumns.

回答2:

The first Sort doesn't work because you're probably trying to sort a table (judging by property Header set to XlYesNoGuess.xlYes) by columns located on A and H. In this case you can only sort using "Sort top to bottom"(xlSortColumns).

You can also convince yourself by trying this in Excel first and see what options are available:
1) Select the range you want to apply sorting (e.g. in your case "A7:I" + rows)
2) Click on "Data" menu -> "Sort & Filter" task pane -> Sort
3) Add/Delete columns/rows by using "Add Level"/"Delete Level" buttons
4) Then you can select whatever level you want and click "Options..." button and you will see your available "Sort Options".
You will notice that in case of a table you will have only one available option for "Orientation", which is "Sort top to bottom"

xlSortColumns - sorts by columns (meaning rows are sorted based on those columns)

xlSortRows - sort by rows (meaning columns are sorted based on those rows)