this file is created by usig Aspose API.
---
dsForecast = (DataSet)Session["dsForecast"];
int iRow = dsForecast.Tables[0].Rows.Count; // row count is one short
int iCol = dsForecast.Tables[0].Columns.Count;
Workbook workbook = new Workbook();
Worksheet sheet1 = workbook.Worksheets[0]; // Data
sheet1.Name = "Data";
Cells cells = sheet1.Cells;
//Import data
cells.ImportDataTable(dsForecast.Tables[0], true, 0, 0, iRow, iCol);
//Add a new worksheet for Pivot Table
workbook.Worksheets.Add();
Worksheet sheet2 = workbook.Worksheets[1];
sheet2.Name = "SLT";
/*
// create Title for Pivot Table
cells = sheet2.Cells;
Cell cell = cells["A2"];
cell.PutValue("SLT Forecast Summary");
cell.Style.Font.Size = 14;
cell.Style.Font.Color = Color.Blue;
//Auto-fitting the 3rd row of the worksheet
sheet2.AutoFitRow(1);
*/
//string strRange = "!A1:C6";
int iTotalRow = iRow + 1; // one row short
string strRange = "!A1:" + "L" + iTotalRow.ToString();
string sourceData = "=" + sheet1.Name + strRange; // "=SheetName!A1:L29"
string destCellName = "A3";
// create the pivot table
PivotTables pTables = sheet2.PivotTables;
int index = pTables.Add(sourceData,destCellName,"Pivot_Table_2");
// access the new pivot table
PivotTable pTable = pTables[index];
//pTable.RowGrand = false; // unshowing grand totals for rows
// drag product field to the row data
pTable.AddFieldToArea(PivotFieldType.Row, 1); // product
// drag fcastdate field to the column data
pTable.AddFieldToArea(PivotFieldType.Column, 6); // fcastdate
// drag quantity field to the data
pTable.AddFieldToArea(PivotFieldType.Data, 4); // quantity
// drag department_name field to the page
pTable.AddFieldToArea(PivotFieldType.Page, 3); // department_name
// 1. Accessing the row fields.
PivotFields rowPivotFields = pTable.RowFields;
//Accessing the first row field in the row fields.
PivotField rowPivotField = rowPivotFields[0];
//Setting Subtotals.
rowPivotField.SetSubtotals(PivotFieldSubtotalType.Sum,true);
rowPivotField.SetSubtotals(PivotFieldSubtotalType.Count,true);
//Setting the field auto sort.
rowPivotField.IsAutoSort = true;
//Setting the field auto sort ascend.
//rowPivotField.IsAscendSort = true;
//2. Accessing the column fields.
PivotFields colPivotFields = pTable.ColumnFields;
//Accessing the first col field in the col fields.
PivotField colPivotField = colPivotFields[0];
//Setting Subtotals.
colPivotField.SetSubtotals(PivotFieldSubtotalType.Sum,true);
colPivotField.SetSubtotals(PivotFieldSubtotalType.Count,true);
//Setting the field auto sort.
colPivotField.IsAutoSort = true;
//Setting the field auto sort ascend.
//colPivotField.IsAscendSort = true;
//workbook.Save("SLT_Forecast.xls"); // save to c:\windows\system32\
workbook.Save("C:\\FCast\\SLT_Forecast.xls"); // to c:\FCast\