Hello,
We use Aspose.Cells in our application and have come across what seems to be a bug in Aspose.Cells.
The following code throws an exception on the line “pivotTable.CalculateData()” with the following error:
Exception:
An unhandled exception of type ‘System.IndexOutOfRangeException’ occurred in Aspose.Cells.dll
Additional information: Index was outside the bounds of the array.
at . (Cell )
at . (Int32 , Int32 )
at . ()
at . ()
at Aspose.Cells.Pivot.PivotTable.CalculateData()
VB Code:
Private Sub DoPivot()
Dim book As Aspose.Cells.Workbook = New Aspose.Cells.Workbook(“C:\Users\RichardS\Documents\Dev\AposePivotTableCalculateDataInput.xlsx”)
Dim pivotSheet As Aspose.Cells.Worksheet = book.Worksheets.Add(“Pivot Table”)
Dim pivotTables As Aspose.Cells.Pivot.PivotTableCollection = pivotSheet.PivotTables
Dim dataSheet As Aspose.Cells.Worksheet = book.Worksheets(“Data”)
Dim pivotIndex As Integer = pivotTables.Add(String.Format("=Data!A1:{0}", Aspose.Cells.CellsHelper.CellIndexToName(dataSheet.Cells.MaxDataRow, dataSheet.Cells.MaxDataColumn)), “A1”, “PivotTable1”)
Dim pivotTable As Aspose.Cells.Pivot.PivotTable = pivotTables(pivotIndex)
Dim style As Aspose.Cells.Style = book.CreateStyle()
style.Font.Size = 8
style.Font.Name = “Calibri”
pivotTable.PivotTableStyleType = Aspose.Cells.Pivot.PivotTableStyleType.PivotTableStyleMedium15
pivotTable.FormatAll(style)
pivotTable.EnableWizard = False
pivotTable.EnableFieldList = True
pivotSheet.MoveTo(0)
AddPivotRow(pivotTable, “Property Name”, “Property Name”, layoutInOutline:=True, layoutCompactRow:=True, isAutoSort:=True)
AddPivotRow(pivotTable, “Section Description”, “Section Description”, subTotals:=True, layoutSubtotalAtTop:=True, layoutInOutline:=True, layoutBlankLine:=True, hideDetail:=False, layoutCompactRow:=True)
AddPivotRow(pivotTable, “Account Category Two”, “Account Category Two”, subTotals:=True, layoutSubtotalAtTop:=True, layoutInOutline:=True, hideDetail:=False, layoutCompactRow:=True, isAutoSort:=True)
AddPivotRow(pivotTable, “Account Category Three”, “Account Category Three”, subTotals:=True, layoutSubtotalAtTop:=True, layoutInOutline:=True, hideDetail:=False, layoutCompactRow:=True, isAutoSort:=True)
AddPivotRow(pivotTable, “Transaction Description”, “Transaction Description”, layoutInOutline:=True, layoutCompactRow:=True, isAutoSort:=True)
AddPivotColumn(pivotTable, “Range”, “Range”, subTotals:=True)
AddPivotColumn(pivotTable, “PeriodDescription”, “Periods”)
AddPivotData(pivotTable, “Amount”, “Values”, “# ##0.00”)
pivotTable.ColumnGrand = False
pivotTable.RowGrand = False 'Setting to False causes .CalculateData() to throw the exception. If set to True, the exception does not occur.
pivotTable.IsGridDropZones = True
pivotTable.RefreshDataFlag = True
pivotTable.RefreshData()
pivotTable.CalculateData() ’ Throws Exception
pivotTable.RefreshDataFlag = False
pivotSheet.AutoFitColumns()
pivotSheet.Cells.StandardHeight = 11.25
book.Worksheets.ActiveSheetIndex = 0
book.Settings.HidePivotFieldList = True
book.Save(“C:\Users\RichardS\Documents\Dev\AsposePivotTableCalculateDataOutput.xlsx”, New Aspose.Cells.OoxmlSaveOptions)
End Sub
Public Shared Sub AddPivotRow(ByRef pivotTable As Aspose.Cells.Pivot.PivotTable, _
ByVal fieldName As String, _
caption As String, _
Optional ByVal subTotals As Boolean = False, _
Optional ByVal layoutInOutline As Boolean = False, _
Optional ByVal layoutBlankLine As Boolean = False, _
Optional ByVal layoutCompactRow As Boolean = False, _
Optional ByVal layoutSubtotalAtTop As Boolean = False, _
Optional ByVal numberFormat As String = “”, _
Optional ByVal hideDetail As Boolean = False,
Optional ByVal isAutoSort As Boolean = False,
Optional ByVal isDate As Boolean = False)
Dim pivotField As Aspose.Cells.Pivot.PivotField = pivotTable.RowFields.Item(pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, fieldName))
With pivotField
.DisplayName = caption
.ShowCompact = layoutCompactRow
.ShowInOutlineForm = layoutInOutline ’ xlTabular = 0, xlOutline = 1
.ShowSubtotalAtTop = layoutSubtotalAtTop
.InsertBlankRow = layoutBlankLine
.IsAutoSubtotals = subTotals
.NumberFormat = numberFormat
.HideDetail(hideDetail)
.IsAutoSort = isAutoSort
If isDate Then
.Number = 14
End If
End With
End Sub
Public Shared Sub AddPivotColumn(ByRef pivotTable As Aspose.Cells.Pivot.PivotTable, _
ByVal fieldName As String, _
caption As String, _
Optional ByVal subTotals As Boolean = False)
Dim pivotField As Aspose.Cells.Pivot.PivotField = pivotTable.ColumnFields.Item(pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, fieldName))
With pivotField
.DisplayName = caption
.IsAutoSubtotals = subTotals
.IsAscendSort = True
End With
End Sub
Public Shared Sub AddPivotData(ByRef pivotTable As Aspose.Cells.Pivot.PivotTable, _
ByVal fieldName As String, _
caption As String, _
numberFormat As String, _
Optional ByVal consolidationFunction As Aspose.Cells.ConsolidationFunction = Aspose.Cells.ConsolidationFunction.Sum)
Dim pivotField As Aspose.Cells.Pivot.PivotField = pivotTable.DataFields(pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, fieldName))
With pivotField
.DisplayName = caption
.Function = consolidationFunction
.NumberFormat = numberFormat
End With
End Sub
The exception is thrown if we set pivotTable.RowGrand = False, however if we set it to True the exception does not occur.
Please see attached input sheet.
Occurred on Aspose.Cells v 8.3.2.
Your help will be greatly appreciated.
Thank you.