We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Data sorting multiple times not working after SubTotaling in .NET

I'm trying to build an Excel worksheet with multiple sort levels and subtotals.

I initally sort the sheet on three columns, which works fine. I then subtotal into four groups using the SubTotal function. This also works. Finally I'm trying to sort on a single column within two of my subtotaled groups. This does not work.

I'm not sure why the sorting fails the second time since I am calling the same code from the first sort only with a single sort key the second time.

Are you aware with any issues sorting a sheet after the data has been subtotaled into groups?

Thanks,
Keith

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and try the latest version:
Aspose.Cells
for .NET v7.1.1.1


If the problem persists, then please provide us your source xls/xlsx files, actual output and expected output files and the sample code replicating the problem.

We will look into it and help you asap.

The link gives me a DNS error:

Network Error (dns_unresolved_hostname)

Your requested host "www.aspose1.com" could not be resolved by DNS.

Hi,

Please download and use this latest version: Aspose.Cells for .NET v7.1.1.2

It includes the fixes of previous version too.

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

Hi,

Please download and try this fix:
Aspose.Cells
for .NET v7.1.1.5

and let us know your feedback.

Hi,

Please call Workbook.CalculateFormula() method before the second sorting.

The Workbook.CalculateFormula() fixed my issue. Thank you so much for your prompt replies and expert solution.

Hi,

It’s good to know, your issue is now fixed.

If you find any other problem or get questions, please feel free to post it on our forum, we will help you asap.