Pivot Table (5)
By default, the data source of PivotTable won't be refreshed automatically. If we update the data source of our PivotTable, the PivotTable that was built on that data source needs to be refreshed. This article is going to elaborate how to refresh PivotTable in Excel programmatically in c# using Spire.XLS.
Below is the screenshot of the example Excel file:
Detail steps:
Step 1: Instantiate a Workbook object and load the Excel file.
Workbook workbook = new Workbook(); workbook.LoadFromFile(@"Sample.xlsx");
Step 2: Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];
Step 3: update the data source of PivotTable.
sheet.Range["C2"].Value = "199";
Step 4: Get the PivotTable that was built on the data source.
XlsPivotTable pt = workbook.Worksheets[0].PivotTables[0] as XlsPivotTable;
Step 5: Refresh the data of PivotTable.
pt.Cache.IsRefreshOnLoad = true;
Step 6: Save the file.
workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);
Output after updating data source and refreshing the PivotTable:
Full code:
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;
namespace Refresh_Pivot_Table_in_Excel
{
class Program
{
static void Main(string[] args)
{
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile(@"Sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Update the data source of PivotTable
sheet.Range["C2"].Value = "199";
//Get the PivotTable that was built on the data source
XlsPivotTable pt = workbook.Worksheets[0].PivotTables[0] as XlsPivotTable;
//Refresh the data of PivotTable
pt.Cache.IsRefreshOnLoad = true;
//Save the file
workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);
}
}
}
We have already demonstrated how to create the excel pivot table with Spire.XLS for .NET. It enables developers to set the property of PivotFieldFormatType to set format for the Data fields on pivot table. The following code sample will show you how to set display formats for data fields in C#.
Note: Before Start, please download the latest version of Spire.XLS and add Spire.Xls.dll in the bin folder as the reference of Visual Studio.
Firstly please check the original DataField format on PivotTable:
Step 1: Create a new Excel workbook and load from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Get the first worksheet from the workbook.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Accessing the first Pivot table from the first worksheet.
XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;
Step 4: Accessing the Data Field.
PivotDataField pivotDataField = pt.DataFields[0];
Step 5: Setting data display format by setting the property of PivotFieldFormatType as PercentageOfColumn.
pivotDataField.ShowDataAs = PivotFieldFormatType.PercentageOfColumn;
Step 6: Save the document to file.
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
The effective screenshot after setting the Datafield format in PivotTable:
Full codes of how to set the Datafields type in Excel Pivot Table.
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;
namespace SetDataFieldsformat
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;
PivotDataField pivotDataField = pt.DataFields[0];
pivotDataField.ShowDataAs = PivotFieldFormatType.PercentageOfColumn;
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
}
}
}
To view the large amount of Excel data's easily, Spire.XLS for .NET supports create Pivot table and add excel table with filters. Spire.XLS also offers a method of pivotTable.ReportFilters.Add(); to enable developers to add the report filter to the pivot table.
This article will show you how to add a report filter to the Excel Pivot table in C#.
Note: Before Start, please download the latest version of Spire.XLS and add Spire.xls.dll in the bin folder as the reference of Visual Studio.
Firstly, please check the original screenshot of excel pivot table.
Step 1: Create a new workbook and load from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Get the PivotTable from the Excel worksheet.
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets["Pivot Table"].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
Step 3: Add a filter to the pivot table.
PivotReportFilter filter = new PivotReportFilter("JAN", true);
pivotTable.ReportFilters.Add(filter);
Step 4: Save the document to file and launch to preview it.
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
Effective screenshot after add a filter to the pivot table:
Full codes:
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;
namespace AddReportFilter
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets["Pivot Table"].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
PivotReportFilter filter = new PivotReportFilter("JAN", true);
pivotTable.ReportFilters.Add(filter);
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}
How to Expand/Collapse the rows in an existing Pivot Table in C#
2014-07-25 02:52:19 Written by support icebluePivot table displays the data in sort, count total or give the average of the data stored in one table or spreadsheet. So it gives readers clear information of the data's trends and patterns rather than a large amount of similar data. Sometimes, there are so many rows in one pivot table and we may need to expand or collapse them to make the pivot table more clearly.
By using Spire.XLS for .NET, developers can create pivot table. This article will show you how to expand and collapse the rows in an existing Pivot table in C#.
Firstly, make sure that Spire.XLS for .NET (version7.5.5 or above) has been installed on your machine. And then, adds Spire.XLS.dll as reference in the downloaded Bin folder thought the below path: "..\Spire.XLS\Bin\NET4.0\ Spire.XLS.dll".
//Create a new excel document
Workbook book = new Workbook();
//load an excel document with Pivot table from the file
book.LoadFromFile("test.xlsx");
//Find the Pivot Table sheet
Worksheet sheet = book.Worksheets["Pivot Table"];
//Get the data in Pivot Table
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = sheet.PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
//Calculate Data
pivotTable.CalculateData();
//Collapse the rows
(pivotTable.PivotFields["Vendor No"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("1501", true);
//Expand the rows
(pivotTable.PivotFields["Vendor No"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("1502", false);
//Save the document to file
book.SaveToFile("result.xlsx", ExcelVersion.Version2007);
Effective screenshots:
Collapse the rows in Pivot table in C#
Expand the rows in Pivot table in C#
Full codes:
using Spire.Xls;
namespace HighlightValues
{
class Program
{
static void Main(string[] args)
{
Workbook book = new Workbook();
book.LoadFromFile("test.xlsx");
Worksheet sheet = book.Worksheets["Pivot Table"];
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = sheet.PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
pivotTable.CalculateData();
(pivotTable.PivotFields["Vendor No"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("1501", true);
(pivotTable.PivotFields["Vendor No"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("1502", true);
book.SaveToFile("result_1.xlsx", ExcelVersion.Version2007);
(pivotTable.PivotFields["Vendor No"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("1502", false);
book.SaveToFile("result_2.xlsx", ExcelVersion.Version2007);
}
}
}
Pivot table is a kind of interactive table that is capable of quickly calculating, summarizing and analyzing large amounts of data. As one of the most powerful tools in Excel, it enables users to view static data from different perspectives, and also makes the comparisons between data more intuitive. In this article, you will learn how to programmatically create a pivot table in Excel using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Create a Pivot Table in Excel
The detailed steps are as follows.
- Create a Workbook object.
- Load a sample Excel document using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[] property.
- Select the data source range using Worksheet.Range[] property, and then create a PivotCache to save the data information using Workbook.PivotCaches.Add (CellRange) method.
- Add a pivot table to the specified worksheet and set the location and cache of it using Worksheet.PivotTables.Add(String, CellRange, PivotCache) method.
- Define row labels of the pivot table and then add fields to the data area to calculate data using PivotTable.DataFields.Add(IPivotField, String, SubtotalTypes) method.
- Set the pivot table style using PivotTable.BuiltInStyle property.
- Save the result document using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace createPivotTable
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.LoadFromFile(@"E:\Files\sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Select the data source range
CellRange dataRange = sheet.Range["B1:F11"];
PivotCache cache = workbook.PivotCaches.Add(dataRange);
//Add a PivotTable to the worksheet and set the location and cache of it
PivotTable pt = sheet.PivotTables.Add("Pivot Table", sheet.Range["H3"], cache);
//Define row labels
PivotField r1 = pt.PivotFields["Country"] as PivotField;
r1.Axis = AxisTypes.Row;
pt.Options.RowHeaderCaption = "Country";
PivotField r2 = pt.PivotFields["Product"] as PivotField;
r2.Axis = AxisTypes.Row;
//Add data fields to calculate data
pt.DataFields.Add(pt.PivotFields["Quantity"], "SUM of Quantity", SubtotalTypes.Sum);
pt.DataFields.Add(pt.PivotFields["Total Amount"], "SUM of Total Amount", SubtotalTypes.Sum);
//Set pivot table style
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium10;
//Save the document
workbook.SaveToFile("CreatePivotTable.xlsx", ExcelVersion.Version2010);
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
