//Creates pivot table
private void CreatePivotSheet(Excel._Workbook workbook, Excel._Worksheet dataSheet, Excel._Worksheet pivotSheet, string tableName, bool unified)
{
//If consolidated sheet, range selects up until column C, if normal pivot, selects up until K
string col;
if (unified == true)
{
col = "C";
}
else
{
col = "K";
}
//Get last used row
var lastUsedRow = getLastUsedRow(dataSheet); //Select all data from starting cell to last column + row
var dataRange = dataSheet.get_Range("A1", col + lastUsedRow);
var pivotRange = pivotSheet.Cells[1, 1]; //Select target location
var oPivotCache = (Excel.PivotCache)workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, dataRange); //Create cache specifying data is coming from a table
var oPivotTable = (Excel.PivotTable)pivotSheet.PivotTables().Add(PivotCache: oPivotCache, TableDestination: pivotRange, TableName: tableName); //Create table
if (unified == true) //If consolidated sheet
{
//Set Row field to 'APP'
var RowPivotField = (Excel.PivotField)oPivotTable.PivotFields("APP");
RowPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
//Set Values field to 'Total'
var SumPivotField = (Excel.PivotField)oPivotTable.PivotFields("Total");
SumPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
SumPivotField.Function = Excel.XlConsolidationFunction.xlSum;
SumPivotField.Name = "CPU Time";
//Set Column field to 'LPAR'
var ColPivotField = (Excel.PivotField)oPivotTable.PivotFields("LPAR");
ColPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
}
else //If normal sheet
{
//Set Row field to 'APP'
Excel.PivotField RowPivotField = (Excel.PivotField)oPivotTable.PivotFields("APP");
RowPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
//Set Values field to 'CPUTIME'
Excel.PivotField SumPivotField = (Excel.PivotField)oPivotTable.PivotFields("CPUTIME");
SumPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
SumPivotField.Function = Excel.XlConsolidationFunction.xlSum;
SumPivotField.Name = "CPU Time";
}
}
