Add pivot table on new sheet in .NET

Aspose.Cells Product Family
1

I’m importing data into a new sheet in a new workbook:

Dim workbook As Workbook = New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(“data”)
sheet.Cells.ImportDataTable(dt, True, 0, 0, True, False)
workbook.Save(Me.Response, “CampaignReports.xls”, ContentDisposition.Attachment, New XlsSaveOptions())
Response.End()

How can I add a pivot table on a new sheet “pivot” using the data on the sheet"data" as data source?


2

Hi,

Please see the document for your reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/create-pivot-tables-and-pivot-charts.html

Thank you.

3

Thanks for your reply.
I’ve added the code below but get an error (below+attachment). The error attachment contains a few strange characters (marked in screen shot).

Code:

’ pivot table
Dim pivotSheet As Worksheet = workbook.Worksheets(workbook.Worksheets.Add())
pivotSheet.Name = “PivotTable”
Dim pivotTables As Aspose.Cells.Pivot.PivotTableCollection = pivotSheet.PivotTables

Dim index As Integer = pivotTables.Add(String.Format(“={0}!A1:{1}{2}”, sheetName, lastColumn, rowCount + 1), “A1”, “PivotTable1”)
Dim pivotTable As Aspose.Cells.Pivot.PivotTable = pivotTables(index)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, VwCampaignReportsFields.LocationName.Alias)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, VwCampaignReportsFields.UserInitials.Alias)
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, VwCampaignReportsFields.CampaignReportId.Alias)

workbook.Save(Me.Response, “CampaignReports.xls”, ContentDisposition.Attachment, New XlsSaveOptions())

Error:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Source Error:

Line 154: pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, VwCampaignReportsFields.CampaignReportId.Alias)
Line 155:
Line 156: workbook.Save(Me.Response, “CampaignReports.xls”, ContentDisposition.Attachment, New XlsSaveOptions())
Line 157:
Line 158: Response.End()

Stack Trace:

[ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index]
System.Collections.ArrayList.get_Item(Int32 index) +9373696
Aspose.Cells.Pivot.PivotTable.?(ArrayList ?, PivotFieldCollection ?, PivotFieldCollection ?, Boolean ?) +1190
Aspose.Cells.Pivot.PivotTable.?() +529
Aspose.Cells.Pivot.PivotTable.get_ColumnRange() +20
?.?.?() +171
Aspose.Cells.Pivot.PivotTable.?(? ?) +114
Aspose.Cells.Worksheet.?(? ?) +1757
Aspose.Cells.WorksheetCollection.?(? ?) +510
Aspose.Cells.WorksheetCollection.?(FileFormatType ?, ? ?, Stream ?) +273
Aspose.Cells.WorksheetCollection.?(Stream ?, FileFormatType ?) +431
Aspose.Cells.Workbook.Save(Stream stream, SaveOptions saveOptions) +2643
Aspose.Cells.Workbook.Save(HttpResponse response, String fileName, ContentDisposition contentDisposition, SaveOptions saveOptions) +41
Adm_ReportMngt_Reports.btnExportToExcel_Click(Object sender, EventArgs e) in C:\Visual Studio 2008\Projects\X\Reports.aspx.vb:156
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

4

Hi,

I have tested the code given in the article/document(I mentioned in my previous post), it works fine. I have a doubt about your code, kindly debug your code by yourself and check each line of code is performing as expected, the pointers are filled with valid values.

I have also attached latest version/fix here for you.

If you still could not evaluate, kindly do create an application, zip it and post it here to show the issue, we will check it soon.

Thank you.