The Syncfusion .NET Excel Library (XlsIO) supports the creation and manipulation of pivot tables. Use pivot tables to organize, summarize, or calculate data dynamically. These functionalities can be achieved using just a few lines of code.
This example code shows how to create a pivot table in Excel using the Syncfusion .NET Excel Library with just a few lines of code in C#.
// Initialize Excel engine and application.
using ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
// Open the existing workbook.
using FileStream inputStream = new FileStream("InputTemplate.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet worksheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
// Create a pivot cache with the given data range.
IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
// Create "PivotTable1" with the cache at the specified range.
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
// Add pivot table fields (Row and Column fields).
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
// Add data field.
IPivotField field = pivotTable.Fields[5];
pivotTable.DataFields.Add(field, "Sum", PivotSubtotalTypes.Sum);
// Save the Excel document.
using FileStream outputStream = new FileStream("PivotTable.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(outputStream);Discover how pivot table features in the Syncfusion Excel Library allow you to summarize, analyze, and organize data dynamically, enabling powerful data insights and streamlined reporting in Excel.
Enhance your pivot table with various built-in styles to improve visual appeal and interpretation of data. You can also format individual cells to emphasize key metrics by adjusting font styles, colors, borders, and number formats, ensuring clarity and readability in your presentations.
Customize the layout of your pivot table to enhance readability and organization. You can choose from several layout types, including compact, tabular, outline, and classic layouts, to suit your analysis needs. Rearrange fields and adjust the display of data to optimize the presentation of your information.
Easily refresh your pivot table to update the data it displays. This ensures that any changes made to the source data are reflected in your pivot table, allowing for accurate and current insights.
Utilize sorting and filtering features within your Pivot Table to organize data effectively. You can sort information to highlight key metrics and apply filters to focus on specific data points, making analysis more manageable.
Group data in your pivot table to aggregate information meaningfully. This allows you to organize data by time periods, facilitating deeper analysis and clearer presentation of results.
You can expand or collapse data in your pivot table to focus on specific details or to get an overview of the information. This feature enhances your data exploration by allowing you to navigate through different levels of data granularity easily.
Discover valuable resources from our blog and knowledge base to enhance your efficiency in using pivot tables.
Knowledge base
Knowledge base
Explore these resources for comprehensive guides, knowledge base articles, insightful blogs, and ebooks.
Product Updates
Technical Support
Yes, the Syncfusion Excel Library allows you to edit and format pivot tables. You can customize pivot table formatting and pivot cell formatting to enhance the appearance of your pivot tables.
Yes. Syncfusion Excel library allows you to expand or collapse rows in your pivot table effortlessly. This feature enables you to control the level of detail displayed, facilitating focused analysis on specific data points within your pivot table.
Yes, you can apply sorting to pivot tables using the Syncfusion Excel Library. The library enables you to sort pivot table fields both top to bottom and left to right.
Yes. The Syncfusion Excel Library allows you to apply various layout options for pivot tables. You can choose a compact layout, outline layout, or tabular layout.
Yes, you can apply pivot table filters using the Syncfusion Excel Library. The library supports two types of filters: page filters and row/column filters. You can easily apply these filters to manipulate the data displayed within the pivot table.
Yes, you can group date and time fields in a pivot table using the Syncfusion Excel Library. It supports grouping pivot data based on various categories including date, month, year, quarters, hours, minutes, and seconds. This feature empowers you to organize and analyze your data efficiently based on different time-related intervals.
Greatness—it’s one thing to say you have it, but it means more when others recognize it. Syncfusion® is proud to hold the following industry awards.
