Pivot table help

Aspose.Cells Product Family
1

Laurence,

I have an .xls where the first 5 sheet have pivot table on them. Now what is currently done manually is that the 6th sheet is populated with data and that data is used by the pivot table on the first 5 sheets. I want to do this programmatically. I took a sample .xls and strip out the data on the 6th sheet and I am using Aspose to populate the sheet. However, how do I assign each pivot table a data source in code.

Here is what I have so far:

templateSheet = xlApp.Worksheets[5];
templateSheet.Name = UserID;
selectedCells = templateSheet.Cells;
selectedCells.ImportDataTable(DataSource, true, 1, 0);

Range range = templateSheet.Cells.CreateRange(1, 0, DataSource.Rows.Count, DataSource.Columns.Count);
range.Name = “DataRange”;

Please provide some sample code. I saw another example on a similat thread but did not have enough info to do what I wanted.

Thanks,
Jean
2

Hi Jean,

Please check PivotTable for reference.

3

@jvictor,
Aspose.Cells has replaced Aspose.Excel that is no more available now. You can create, format, use consolidated functions, set data sources and perform other operations in the pivot tables. Here is an example that demonstrates pivot table creation.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];

Cells cells = sheet.Cells;

// Setting the value to the cells
Cell cell = cells["A1"];
cell.PutValue("Sport");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Sales");

cell = cells["A2"];
cell.PutValue("Golf");
cell = cells["A3"];
cell.PutValue("Golf");
cell = cells["A4"];
cell.PutValue("Tennis");
cell = cells["A5"];
cell.PutValue("Tennis");
cell = cells["A6"];
cell.PutValue("Tennis");
cell = cells["A7"];
cell.PutValue("Tennis");
cell = cells["A8"];
cell.PutValue("Golf");

cell = cells["B2"];
cell.PutValue("Qtr3");
cell = cells["B3"];
cell.PutValue("Qtr4");
cell = cells["B4"];
cell.PutValue("Qtr3");
cell = cells["B5"];
cell.PutValue("Qtr4");
cell = cells["B6"];
cell.PutValue("Qtr3");
cell = cells["B7"];
cell.PutValue("Qtr4");
cell = cells["B8"];
cell.PutValue("Qtr3");

cell = cells["C2"];
cell.PutValue(1500);
cell = cells["C3"];
cell.PutValue(2000);
cell = cells["C4"];
cell.PutValue(600);
cell = cells["C5"];
cell.PutValue(1500);
cell = cells["C6"];
cell.PutValue(4070);
cell = cells["C7"];
cell.PutValue(5000);
cell = cells["C8"];
cell.PutValue(6430);

Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet.PivotTables;

// Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", "E3", "PivotTable2");

// Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

// Unshowing grand totals for rows.
pivotTable.RowGrand = false;

// Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);

// Draging the second field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 1);

// Draging the third field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);
         
// Saving the Excel file
workbook.Save(dataDir + "pivotTable_test_out.xls");

For more information about pivot tables, refer to the following articles:
Pivot Tables

Here you can download the latest free trial version:
Aspose.Cells for .NET (Latest Version)

A ready-to-run application is available here that can be used to test the product features without writing any code.