//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"; } }