Thanks for your prompt reply, but the new dll did not fix my issue. Please find attached my input (COR.csv), output (COR.xls) and my expected output (ExpectedResults_COR.xls). The expected results shows the second set of sorting on just the first (master) tab. The sorting is for column M rows 176-351 and 486-569.
My code for the first sort, the grouping/subtotaling and the second sort, along with my sorting and subtotal classes follows.
Private Sub PreSortColumns()
Const SORT_KEY_1 As Integer = 22
Const SORT_KEY_2 As Integer = 23
Const SORT_KEY_3 As Integer = 11
Const START_COL As Integer = 0
Dim sortCol As New SortData()
'sort by exposed total value (descending), then by by realized/unrealized (ascending) and finally by winner/losers (ascending).
sortCol.SortColumn(CORWorkbook, SortOrder.Ascending, SortOrder.Ascending, SortOrder.Descending, SORT_KEY_1, SORT_KEY_2, SORT_KEY_3, _
StartRow, CORCells.MaxRow, START_COL, CORCells.MaxColumn, WorksheetIndex)
End Sub
Private Sub SubtotalGroups()
Const START_COL As Integer = 1
Const REALIZED_KEY As String = "1 Total"
Const UNREALIZED_KEY As String = "2 Total"
Const WINNERS_KEY As String = "3 Total"
Const LOSERS_KEY As String = "4 Total"
Dim groupCol As Integer
Dim subtotal As New Subtotal()
Dim calc As New CalculateFormula()
Dim totaledCell As Cell
Dim cellToFlip As Cell = Nothing
'create an array of the columns to subtotal
Dim tl() As Integer
tl = New Integer() {4, 5, 6, 7, 8, 9, 10, 11, 12} 'For some reason the Aspose Subtotal method adds one to each element of the array, so make each index one less.
groupCol = 22
'subtotal winners and losers
subtotal.SubtotalGroup(CORCells, StartRow, CORCells.MaxDataRow, START_COL, CORCells.MaxDataColumn, tl, groupCol)
groupCol = 21
'subtotal realized/unrealized
tl = New Integer() {4, 5, 6, 7, 8, 9, 10, 11, 12} 'you have to re-initialize the array to keep the column indices correct.
subtotal.SubtotalGroup(CORCells, StartRow, CORCells.MaxDataRow, START_COL, CORCells.MaxDataColumn, tl, groupCol)
'find the first winners subtotaled row to format them
totaledCell = FindCellContainingString(StartRow, WINNERS_KEY, Nothing)
If Not totaledCell Is Nothing Then
Dim r As String = CStr(totaledCell.Row + 1)
'calculate investment multiple subtotal
calc.ApplyFormula(CORCells, "N" & r, "=IF(ISERROR(L" & r & "/I" & r & "),0,L" & r & "/I" & r & ")")
FormatSubtotalRows(totaledCell.Row, "Winners", 3, "A", "X")
cellToFlip = totaledCell
cellToFlip = CORCells.LastCell
End If
'the second winners row
totaledCell = FindCellContainingString(StartRow, WINNERS_KEY, totaledCell)
If Not totaledCell Is Nothing Then
Dim r As String = CStr(totaledCell.Row + 1)
'calculate investment multiple subtotal
calc.ApplyFormula(CORCells, "N" & r, "=IF(ISERROR(L" & r & "/I" & r & "),0,L" & r & "/I" & r & ")")
FormatSubtotalRows(totaledCell.Row, "Winners", 3, "A", "X")
cellToFlip = totaledCell
End If
'find the first losers subtotaled row to format them
totaledCell = FindCellContainingString(StartRow, LOSERS_KEY, Nothing)
If Not totaledCell Is Nothing Then
calc.ApplyFormula(CORCells, "N" & totaledCell.Row + 1, "=IF(ISERROR(L" & totaledCell.Row + 1 & "/I" & totaledCell.Row + 1 & "),0,L" & totaledCell.Row + 1 & "/I" & totaledCell.Row + 1 & ")")
FormatSubtotalRows(totaledCell.Row, "Losers", 3, "A", "X")
'there is a blank row above the Losers row that needs to be deleted
'DeleteRow(totaledCell.Row - 1, 1)
cellToFlip = totaledCell
End If
'the second losers row
totaledCell = FindCellContainingString(StartRow, LOSERS_KEY, totaledCell)
If Not totaledCell Is Nothing Then
calc.ApplyFormula(CORCells, "N" & totaledCell.Row + 1, "=IF(ISERROR(L" & totaledCell.Row + 1 & "/I" & totaledCell.Row + 1 & "),0,L" & totaledCell.Row + 1 & "/I" & totaledCell.Row + 1 & ")")
FormatSubtotalRows(totaledCell.Row, "Losers", 3, "A", "X")
'there is a blank row above the Losers row that needs to be deleted
'DeleteRow(totaledCell.Row - 1, 1)
cellToFlip = totaledCell
End If
'realized row
totaledCell = FindCellContainingString(StartRow, REALIZED_KEY, Nothing)
Dim realizedCell As Cell
realizedCell = totaledCell
If Not totaledCell Is Nothing Then
calc.ApplyFormula(CORCells, "N" & totaledCell.Row + 1, "=IF(ISERROR(L" & totaledCell.Row + 1 & "/I" & totaledCell.Row + 1 & "),0,L" & totaledCell.Row + 1 & "/I" & totaledCell.Row + 1 & ")")
FormatSubtotalRows(totaledCell.Row, "Realized", 3, "A", "X")
cellToFlip = totaledCell
End If
'unrealized row
totaledCell = FindCellContainingString(StartRow, UNREALIZED_KEY, Nothing)
If Not totaledCell Is Nothing Then
FormatSubtotalRows(totaledCell.Row, "Unrealized", 3, "A", "X")
cellToFlip = totaledCell
End If
FlipSubtotaledRows(cellToFlip)
'subtotal Row
If Not totaledCell Is Nothing Then
For Each col As Integer In tl
calc.ApplyFormula(CORCells, ColIndexToString(col) & totaledCell.Row + 3, "=(" & ColIndexToString(col) & totaledCell.Row + 2 & "+" & ColIndexToString(col) & realizedCell.Row + 1 & ")")
Next
calc.ApplyFormula(CORCells, "N" & totaledCell.Row + 3, "=IF(ISERROR(L" & totaledCell.Row + 3 & "/I" & totaledCell.Row + 3 & "),0,L" & totaledCell.Row + 3 & "/I" & totaledCell.Row + 3 & ")")
FormatSubtotalRows(totaledCell.Row + 2, "Total", 3, "A", "X")
End If
End Sub
Private Sub SortLosers()
Const REAL_UNREAL_COL As Integer = 22
Const WIN_LOSE_COL As Integer = 23
Const REALIZED_KEY As Integer = 1
Const UNREALIZED_KEY As Integer = 2
Const LOSERS_KEY As Integer = 4
Const SORT_KEY As Integer = 12 'gain/loss
Const START_COL As Integer = 1
Dim losers1 As New List(Of Integer)
Dim losers2 As New List(Of Integer)
Dim cellValue As New ReadWriteCell()
'find the losers rows and add them to the lists
For row As Integer = StartRow To CORCells.MaxRow Step 1
Select Case True
Case cellValue.IntCellValue(Sheet, row, REAL_UNREAL_COL) = REALIZED_KEY And cellValue.IntCellValue(Sheet, row, WIN_LOSE_COL) = LOSERS_KEY
losers1.Add(row)
Case cellValue.IntCellValue(Sheet, row, REAL_UNREAL_COL) = UNREALIZED_KEY And cellValue.IntCellValue(Sheet, row, WIN_LOSE_COL) = LOSERS_KEY
losers2.Add(row)
End Select
Next
Dim sortCol As New SortData()
If losers1.Count > 0 Then
'get the first and last row of losers1 and sort by gain/loss
sortCol.SortColumn(CORWorkbook, SortOrder.Ascending, SORT_KEY, losers1.Min, losers1.Max, START_COL, CORCells.MaxColumn, WorksheetIndex)
End If
If losers2.Count > 0 Then
'get the first and last row of losers2 and sort by gain/loss
sortCol.SortColumn(CORWorkbook, SortOrder.Ascending, SORT_KEY, losers2.Min, losers2.Max, START_COL, CORCells.MaxColumn, WorksheetIndex)
End If
End Sub
Imports Aspose.Cells
Public Class SortData
''' Used to sort data in Ascending or Descending order.
''' the active workbook
''' The primary sort order (Ascending or descending)
''' The primary sort key (column index)
''' Starting row index of the sort range.
''' Ending row index of the sort range.
''' Starting column index of the sort range.
''' Ending column index of the sort range.
Public Sub SortColumn(ByVal workbook As Workbook, ByVal so1 As SortOrder, ByVal sk1 As Integer, ByVal strtRow As Integer, _
ByVal endRow As Integer, ByVal strtCol As Integer, ByVal endCol As Integer, ByVal wkSheetIndx As Integer)
'Get the workbook datasorter object.
Dim sorter As DataSorter = workbook.DataSorter
'Set the sort orders for datasorter object
sorter.Order1 = so1
'Define the sort keys.
sorter.Key1 = sk1
'Create a cells area (range).
Dim ca As CellArea = New CellArea
'Specify the start/end row index.
ca.StartRow = strtRow
ca.EndRow = endRow
'Specify the start/end column index.
ca.StartColumn = strtCol
ca.EndColumn = endCol
'Sort the data in the specified data range
sorter.Sort(workbook.Worksheets(wkSheetIndx).Cells, ca)
'clear the sorter
sorter.Clear()
End Sub
''' Used to sort data in Ascending or Descending order.
''' the active workbook
''' The primary sort order (Ascending or descending)
''' The secondary sort order (Ascending or descending)
''' The tertiary sort order (Ascending or descending)
''' The primary sort key (column index)
''' The secondary sort key (column index)
''' The tertiary sort key (column index)
''' Starting row index of the sort range.
''' Ending row index of the sort range.
''' Starting column index of the sort range.
''' Ending column index of the sort range.
Public Sub SortColumn(ByVal workbook As Workbook, ByVal so1 As SortOrder, ByVal so2 As SortOrder, ByVal so3 As SortOrder, ByVal sk1 As Integer, ByVal sk2 As Integer, _
ByVal sk3 As Integer, ByVal strtRow As Integer, ByVal endRow As Integer, ByVal strtCol As Integer, ByVal endCol As Integer, ByVal wkSheetIndx As Integer)
'Get the workbook datasorter object.
Dim sorter As DataSorter = workbook.DataSorter
'Set the sort orders for datasorter object
sorter.Order1 = so1
sorter.Order2 = so2
sorter.Order3 = so3
'Define the sort keys.
sorter.Key1 = sk1
sorter.Key2 = sk2
sorter.Key3 = sk3
'Create a cells area (range).
Dim ca As CellArea = New CellArea
'Specify the start/end row index.
ca.StartRow = strtRow
ca.EndRow = endRow
'Specify the start/end column index.
ca.StartColumn = strtCol
ca.EndColumn = endCol
'Sort the data in the specified data range
sorter.Sort(workbook.Worksheets(wkSheetIndx).Cells, ca)
'clear the sorter
sorter.Clear()
End Sub
End Class
Imports Aspose.Cells
Public Class Subtotal
''' Subtotal a grouping
''' The cells to be edited.
''' The starting row index of the range to sort.
''' The ending row index of the range to sort.
''' The starting column index of the range to sort.
''' The ending column index of the range to sort.
''' An array of the column indices to total.
''' The column to group on
Public Sub SubtotalGroup(ByVal cells As Cells, ByVal startRow As Integer, ByVal endRow As Integer, _
ByVal startCol As Integer, ByVal endCol As Integer, ByVal tl() As Integer, ByVal groupCol As Integer)
'Create a cellarea
Dim ca As New CellArea()
ca.StartRow = startRow
ca.StartColumn = startCol
ca.EndRow = endRow
ca.EndColumn = endCol
'Apply subtotal
cells.Subtotal(ca, groupCol, ConsolidationFunction.Sum, tl, False, False, True)
End Sub
End Class