F#Excel Range.Sort失败或重新排列列 [英] 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.".

然后我尝试以下排序:

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.

推荐答案

我还没有弄清楚为什么第一次排序"尝试不起作用.但是我签了文档在第二个排序上.通过使用命名参数并删除所有必要的参数，我得出以下结论:

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

默认方向是xlSortRows.我将其解释为对行进行排序，这是Excel在手动进行排序时所做的常规，默认，直观的排序.不好了.如果您希望Excel在手动进行排序时进行正常的，默认的，直观的排序，请指定xlSortColumns.

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.

这篇关于F#Excel Range.Sort失败或重新排列列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！