Hi, I trying to create a simple pivot table with Spire... but when its created, I get an error opening the excel file.
It says "We found a problem with some content in...." and asks "Do you want us to try and recover as much..."
Here's my code.. super simple...
Know what's going on?
Thanks,
Sam
oWorkbook = New Spire.Xls.Workbook
oDataSheet = oWorkbook.CreateEmptySheet("Data")
oDataSheet.InsertDataTable(dtblResult, True, 1, 1)
oRange = oDataSheet.Range(oDataSheet.FirstRow, oDataSheet.FirstColumn, oDataSheet.LastRow, oDataSheet.LastColumn)
oCache = oWorkbook.PivotCaches.Add(oRange)
oSheet = oWorkbook.CreateEmptySheet("Pivot2")
oPivot = oSheet.PivotTables.Add("Pivot Table", oSheet.Range("A12"), oCache)
With oPivot.PivotFields("UserId")
.Axis = Spire.Xls.AxisTypes.Row
End With
With oPivot.PivotFields("RowDescription")
.Axis = Spire.Xls.AxisTypes.Row
End With
For iIndex = 0 To dtblColumns.Rows.Count - 1
oPivot.DataFields.Add(oPivot.PivotFields(dtblColumns.Rows(iIndex)("ColumnSqlId").ToString), "Sum of " & dtblColumns.Rows(iIndex)("ColumnSqlId").ToString, Spire.Xls.SubtotalTypes.Sum)
Next
/*changing the axis to PAGE on any column causes the XLS be corrupted*/
oPivot.PivotFields("ContactName").Axis = Spire.Xls.AxisTypes.Page
oWorkbook.SaveToFile(strDestinationLocation)