Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "myDemo";
Cells cells = sheet.Cells;
//Setting the value to the cells
Aspose.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);
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
PivotTable pivotTable = pivotTables[index];
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
//Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
//Draging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1);
//Draging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
pivotTable.RowHeaderCaption = "Sports";
pivotTable.ColumnHeaderCaption = "Quarter";
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark20;
Random random = new Random(DateTime.Now.Millisecond);
string sRandomExt = "_" + random.Next().ToString();
// saving the first work book to see how it is looking here RowHeaderCaption and ColumnHeaderCaption are good
workbook.Save("C:\\CFOReporting_25may\\MyTestPivot_" + sRandomExt + ".xlsx", FileFormatType.Xlsx);
//copy the workseets of the First work book to another workbook
Workbook Masterbook = new Workbook();
WorksheetCollection wrksheets = workbook.Worksheets;
foreach (Worksheet ws in wrksheets)
{
Masterbook.Worksheets.Add(ws.Name);
Masterbook.Worksheets[ws.Name].Copy(ws);
}
// saving the second work book to see how it is looking here RowHeaderCaption and ColumnHeaderCaption went back to the default.
Masterbook.Save("C:\\CFOReporting_25may\\MyMasterPivot_" + sRandomExt + ".xlsx", FileFormatType.Xlsx);