Let's say I have an Excel sheet with A, V1 and V2 columns... I would like to create a PivotTable that would :
- create a single line for each value in the A column
- display for this line the sum of V1 values and V2 values
For now I'm able to create two lines for each value of A (one for the V1 total, one for the V2 total). In Excel I can drag /drop the "Data" header to the "Total" header and I've got then a single line for each value of A with a "Sum of V1" and "sum of v2" headers... The macro created by Excel for this is :
If you drag two or more PivotFields to Data Area, we will init PivotTable.DataField and drag it to Row Area(Same as MS Excel). So you should drag PivotTable.DataField to Column Area.See following codes:
Workbook workbook = new Workbook(); workbook.Open(@"F:\FileTemp\Sample.xls");//source sheet
Worksheet pivotSheeet = workbook.Worksheets[0];
PivotTables pTables = pivotSheeet.PivotTables; string sourceData = "Feuil1!A1:C4"; string destCellName = "D3"; int index = pTables.Add(sourceData, destCellName, "PivotTable1");