static void ChangePivotTableDataSource(IWorkbook workbook) { #region #Change DataSource Worksheet worksheet = workbook.Worksheets["Report1"]; workbook.Worksheets.ActiveWorksheet = worksheet; PivotTable pivotTable = worksheet.PivotTables["PivotTable1"]; Worksheet sourceWorksheet = workbook.Worksheets["Data2"]; pivotTable.ChangeDataSource(sourceWorksheet["A1:H6367"]); pivotTable.RowFields.Add(pivotTable.Fields["State"]); PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Yearly Earnings"]); dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average; #endregion #Change DataSource }
static void AddFieldToAxis(IWorkbook workbook) { #region #Add to Axis Worksheet sourceWorksheet = workbook.Worksheets["Data1"]; Worksheet worksheet = workbook.Worksheets.Add(); workbook.Worksheets.ActiveWorksheet = worksheet; PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:D41"], worksheet["B2"]); pivotTable.RowFields.Add(pivotTable.Fields["Product"]); pivotTable.ColumnFields.Add(pivotTable.Fields["Category"]); PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Sales"], "Sales(Sum)"); dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"; pivotTable.PageFields.Add(pivotTable.Fields["Region"]); #endregion #Add to Axis }
static void RankLargestToSmallest(IWorkbook workbook) { #region #RankLargestToSmallest Worksheet worksheet = workbook.Worksheets["Report13"]; workbook.Worksheets.ActiveWorksheet = worksheet; // Access the pivot table by its name in the collection PivotTable pivotTable = worksheet.PivotTables["PivotTable1"]; // Add the "Amount" field to the data area for the second time and assign the custom name to the field. PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Amount"], "Rank"); // Display the rank of sales values for the "Customer" field, listing the largest item in the field as 1. dataField.ShowValuesWithCalculation(PivotShowValuesAsType.RankDescending, pivotTable.Fields["Customer"]); #endregion #RankLargestToSmallest }
static void DifferenceFrom(IWorkbook workbook) { #region #DifferenceFrom Worksheet worksheet = workbook.Worksheets["Report14"]; workbook.Worksheets.ActiveWorksheet = worksheet; // Access the pivot table by its name in the collection PivotTable pivotTable = worksheet.PivotTables["PivotTable1"]; // Access the data field by its index in the collection. PivotDataField dataField = pivotTable.DataFields[0]; // Display the difference in product sales between the current quarter and the previous quarter. dataField.ShowValuesWithCalculation(PivotShowValuesAsType.Difference, pivotTable.Fields["Quarter"], PivotBaseItemType.Previous); #endregion #DifferenceFrom }
private void btnRun_Click(object sender, System.EventArgs e) { Workbook workbook = new Workbook(); workbook.LoadFromFile(@"..\..\..\..\..\..\Data\PivotTable.xlsx"); Worksheet sheet = workbook.Worksheets[0]; // Accessing the PivotTable XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable; // Accessing the data field. PivotDataField pivotDataField = pt.DataFields[0]; // Setting data display format pivotDataField.ShowDataAs = PivotFieldFormatType.PercentageOfColumn; workbook.SaveToFile("Sample.xlsx", ExcelVersion.Version2010); ExcelDocViewer(workbook.FileName); }
static void AddCalculatedField(IWorkbook workbook) { #region #AddCalculatedField Worksheet worksheet = workbook.Worksheets["Report1"]; workbook.Worksheets.ActiveWorksheet = worksheet; // Access the pivot table by its name in the collection. PivotTable pivotTable = worksheet.PivotTables["PivotTable1"]; // Create a calculated field based on data in the "Sales" field. PivotField field = pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax"); // Add the calculated field to the data area and specify the custom field name. PivotDataField dataField = pivotTable.DataFields.Add(field, "Total Tax"); // Specify the number format for the data field. dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"; #endregion #AddCalculatedField }
static void PercentOfParentRowTotal(IWorkbook workbook) { #region #PercentOfParentRowTotal Worksheet worksheet = workbook.Worksheets["Report16"]; workbook.Worksheets.ActiveWorksheet = worksheet; // Access the pivot table by its name in the collection PivotTable pivotTable = worksheet.PivotTables["PivotTable1"]; // Add the "Amount" field to the data area for the second time and assign the custom name to the field. PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Amount"], "% of Parent Row Total"); // Show sales values for each product as the percentage of its category total. // Total values for each category are displayed as the percentage of the Grand Total value. dataField.ShowValuesWithCalculation(PivotShowValuesAsType.PercentOfParentRow); #endregion #PercentOfParentRowTotal }
static void RunningTotalIn(IWorkbook workbook) { #region #RunningTotalIn Worksheet worksheet = workbook.Worksheets["Report15"]; workbook.Worksheets.ActiveWorksheet = worksheet; // Access the pivot table by its name in the collection PivotTable pivotTable = worksheet.PivotTables["PivotTable1"]; // Add the "Amount" field to the data area for the second time and assign the custom name to the field. PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Amount"], "Running Total"); // Display values for successive items in the "Quarter" field as a running total. dataField.ShowValuesWithCalculation(PivotShowValuesAsType.RunningTotal, pivotTable.Fields["Quarter"]); // Specify the number format for the data field. dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"; #endregion #RunningTotalIn }
static void RemoveCalculatedField(IWorkbook workbook) { #region #RemoveCalculatedField Worksheet worksheet = workbook.Worksheets["Report1"]; workbook.Worksheets.ActiveWorksheet = worksheet; // Access the pivot table by its name in the collection. PivotTable pivotTable = worksheet.PivotTables["PivotTable1"]; // Create a calculated field based on data in the "Sales" field. pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax"); // Access the calculated field by its name in the collection. PivotField field = pivotTable.CalculatedFields["Sales Tax"]; // Add the calculated field to the data area. PivotDataField dataField = pivotTable.DataFields.Add(field); //Remove the calculated field. pivotTable.CalculatedFields.RemoveAt(0); #endregion #RemoveCalculatedField }
static void PercentOf(IWorkbook workbook) { #region #PercentOf Worksheet worksheet = workbook.Worksheets["Report14"]; workbook.Worksheets.ActiveWorksheet = worksheet; // Access the pivot table by its name in the collection PivotTable pivotTable = worksheet.PivotTables["PivotTable1"]; // Access the data field by its index in the collection. PivotDataField dataField = pivotTable.DataFields[0]; // Select the base field ("Quarter"). PivotField baseField = pivotTable.Fields["Quarter"]; // Select the base item ("Q1"). PivotItem baseItem = baseField.Items[0]; // Show values as the percentage of the value of the base item in the base field. dataField.ShowValuesWithCalculation(PivotShowValuesAsType.Percent, baseField, baseItem); #endregion #PercentOf }
static void NumberFormat(IWorkbook workbook) { #region #NumberFormat Worksheet sourceWorksheet = workbook.Worksheets["Data5"]; Worksheet worksheet = workbook.Worksheets.Add(); workbook.Worksheets.ActiveWorksheet = worksheet; // Create a pivot table using the cell range "A1:E65" as the data source. PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:E65"], worksheet["B2"]); // Add the "Category" field to the row axis area. pivotTable.RowFields.Add(pivotTable.Fields["Category"]); // Add the "Product" field to the row axis area. pivotTable.RowFields.Add(pivotTable.Fields["Product"]); // Add the "Amount" field to the data area. PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Amount"]); // Specify the number format for the data field. dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"; #endregion #NumberFormat }
static void ChangePivotTableDataSource(IWorkbook workbook) { #region #ChangeDataSource Worksheet worksheet = workbook.Worksheets["Report1"]; workbook.Worksheets.ActiveWorksheet = worksheet; // Access the pivot table by its name in the collection. PivotTable pivotTable = worksheet.PivotTables["PivotTable1"]; Worksheet sourceWorksheet = workbook.Worksheets["Data2"]; // Change the data source of the pivot table. pivotTable.ChangeDataSource(sourceWorksheet["A1:H6367"]); // Add the "State" field to the row axis area. pivotTable.RowFields.Add(pivotTable.Fields["State"]); // Add the "Yearly Earnings" field to the data area. PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Yearly Earnings"]); // Calculate the average of the "Yearly Earnings" values for each state. dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average; #endregion #ChangeDataSource }
static void AddFieldToAxis(IWorkbook workbook) { #region #AddToAxis Worksheet sourceWorksheet = workbook.Worksheets["Data1"]; Worksheet worksheet = workbook.Worksheets.Add(); workbook.Worksheets.ActiveWorksheet = worksheet; // Create a pivot table. PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:D41"], worksheet["B2"]); // Add the "Product" field to the row axis area. pivotTable.RowFields.Add(pivotTable.Fields["Product"]); // Add the "Category" field to the column axis area. pivotTable.ColumnFields.Add(pivotTable.Fields["Category"]); // Add the "Sales" field to the data area and specify the custom field name. PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Sales"], "Sales(Sum)"); // Specify the number format for the "Sales" field. dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"; // Add the "Region" field to the filter area. pivotTable.PageFields.Add(pivotTable.Fields["Region"]); #endregion #AddToAxis }
static void ChangeSummaryFunction(IWorkbook workbook) { #region #ChangeSummaryFunction Worksheet sourceWorksheet = workbook.Worksheets["Data5"]; Worksheet worksheet = workbook.Worksheets.Add(); workbook.Worksheets.ActiveWorksheet = worksheet; // Create a pivot table using the cell range "A1:E65" as the data source. PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:E65"], worksheet["B2"]); // Add the "Category" field to the row axis area. pivotTable.RowFields.Add(pivotTable.Fields["Category"]); // Add the "Product" field to the row axis area. pivotTable.RowFields.Add(pivotTable.Fields["Product"]); // Add the "Amount" field to the data area. PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Amount"]); // Use the "Average" function to summarize values in the data field. dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average; // Specify the number format for the data field. dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"; #endregion #ChangeSummaryFunction }