Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Wed Feb 02, 2022 6:55 pm

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)
Attachments
2022-02-02_13-47-00.png
2022-02-02_13-47-00.png (8.91 KiB) Viewed 415 times

spire_sphillips
 
Posts: 10
Joined: Tue Feb 01, 2022 10:59 pm

Thu Feb 03, 2022 10:07 am

Hello,

Thanks for your inquiry.
Because your code contains some unknown variables (such as "dtblresult" and "dtblcolumns"), I can't run it directly, To help us reproduce your problem and help you solve it, please provide the complete executable code. Thanks in advance.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Sat Feb 05, 2022 10:00 pm

Thanks for the reply, here's a simple version of my code...
Any time I change the axis type to Page, the XLS gets corrupted.

Dim oWorkbook As Workbook = Nothing
Dim oSheet As Worksheet = Nothing
Dim oRange As CellRange
Dim sSql As String
oWorkbook = New Workbook
oSheet = oWorkbook.Worksheets(0)

sSql = ""
sSql = sSql & ""
sSql = sSql & " SET NOCOUNT on"
sSql = sSql & " CREATE TABLE #temptable ( [UserId] varchar(50), [SurveyYear] int, [SurveyPeriod] int, [RowIdentifier] varchar(100), [RowDescription] varchar(2000), [DOMESTIC_NETSALES_BILLED] decimal(16,2), [DOMESTIC_NETORDERS_RECD] decimal(16,2) )"
sSql = sSql & " INSERT INTO #temptable ([UserId], [SurveyYear], [SurveyPeriod], [RowIdentifier], [RowDescription], [DOMESTIC_NETSALES_BILLED], [DOMESTIC_NETORDERS_RECD])"
sSql = sSql & " VALUES"
sSql = sSql & " ( '208', 2018, 1, '1', 'Long Description 1', 123.00, 456.00 ), "
sSql = sSql & " ( '208', 2018, 1, '2', 'Long Description 2', 789.00, 101112.00 ), "
sSql = sSql & " ( '208', 2018, 1, 'MEMO', 'Long Description 3', 131415.00, 161718.00 ), "
sSql = sSql & " ( '212', 2018, 1, '1', 'Long Description 1', 192021.00, 212223.00 ),"
sSql = sSql & " ( '212', 2018, 1, '2', 'Long Description 2', 242526.00, 272829.00 ), "
sSql = sSql & " ( '212', 2018, 1, 'MEMO', 'Long Description 3', 3012332.00, 333435.00 )"
sSql = sSql & " SET NOCOUNT off"
sSql = sSql & " SELECT * FROM #temptable"
sSql = sSql & " DROP TABLE #temptable"


Dim oDb As clsDataAccess = New clsDataAccess(strGetConnectionString())
Dim dtblResult As Data.DataTable
dtblResult = oDb.BuildDatatable(sSql)

oSheet.SetActiveCell(oSheet("A1"))
Dim oDataSheet As Worksheet
Dim oPivot As PivotTable
Dim oCache As PivotCache

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)
oPivot = oSheet.PivotTables.Add("Pivot Table", oSheet.Range("A12"), oCache)

With oPivot.PivotFields("UserId")
.Axis = Spire.Xls.AxisTypes.Row
End With
With oPivot.PivotFields("SurveyYear")
.Axis = Spire.Xls.AxisTypes.Row 'set this to AxisTypes.Page and the XLS throws error when opening
End With
With oPivot.PivotFields("RowDescription")
.Axis = Spire.Xls.AxisTypes.Row
End With
oPivot.DataFields.Add(oPivot.PivotFields("DOMESTIC_NETSALES_BILLED"), "Sum of " & "DOMESTIC_NETSALES_BILLED", Spire.Xls.SubtotalTypes.Sum)
oPivot.DataFields.Add(oPivot.PivotFields("DOMESTIC_NETORDERS_RECD"), "Sum of " & "DOMESTIC_NETORDERS_RECD", Spire.Xls.SubtotalTypes.Sum)

oSheet.AllocatedRange.AutoFitColumns()
oSheet.AllocatedRange.AutoFitRows()

oSheet.SetActiveCell(oSheet("A1"))
oWorkbook.SaveToFile(strDestinationLocation)

spire_sphillips
 
Posts: 10
Joined: Tue Feb 01, 2022 10:59 pm

Mon Feb 07, 2022 6:03 am

Hello,

Thanks for providing more details.
Our Spire.XLS is based on Microsoft Excel. And in Microsoft Excel, setting the pivot field's axis type to page is not supported. I'm sorry that there is no way to solve this issue. Hope you can understand and apologize for the inconvenience caused.
screenshot.png
screenshot.png (9.15 KiB) Viewed 380 times


Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Mon Feb 07, 2022 2:07 pm

Oh! Duh!
I assumed PAGE was the Filter portion of the pivot.

Found the proper one, and this works great.
oPivot.ReportFilters.Add(New Core.Spreadsheet.PivotTables.PivotReportFilter("SurveyPeriod"))

Thanks guys!

spire_sphillips
 
Posts: 10
Joined: Tue Feb 01, 2022 10:59 pm

Tue Feb 08, 2022 1:18 am

Okay, thanks for your feedback.
If you encounter any issues related to our products in the future, please feel free to contact us.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS