static void ChangePivotTableDataSource(IWorkbook workbook)
{
#region #Change DataSource
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
Worksheet sourceWorksheet = workbook.Worksheets["Data2"];
pivotTable.ChangeDataSource(sourceWorksheet["A1:H6367"]);
pivotTable.RowFields.Add(pivotTable.Fields["State"]);
PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Yearly Earnings"]);
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average;
#endregion #Change DataSource
}
static void ChangePivotTableDataSource(IWorkbook workbook)
{
#region #ChangeDataSource
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
Worksheet sourceWorksheet = workbook.Worksheets["Data2"];
// Change the data source of the pivot table.
pivotTable.ChangeDataSource(sourceWorksheet["A1:H6367"]);
// Add the "State" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["State"]);
// Add the "Yearly Earnings" field to the data area.
PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Yearly Earnings"]);
// Calculate the average of the "Yearly Earnings" values for each state.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average;
#endregion #ChangeDataSource
}