How to set custom format on data fields of a Pivot Table?

2 mins read

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. Using this library, you can set custom format on data fields of a pivot table.

Steps to set custom formats on data fields of a pivot table programmatically:

Step 1: Create a new C# console application project.

Create new C# console application

Create a new C# console application

Step 2: Install Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework applications from the NuGet.org.

Install NuGet package

Step 3: Include the following namespaces in the Program.cs file.

using Syncfusion.XlsIO;
using Syncfusion.XlsIO.Implementation.PivotTables;

VB.NET

Imports Syncfusion.XlsIO
Imports Syncfusion.XlsIO.Implementation.PivotTables

Step 4: Add the following code snippet to set custom format on data fields of a pivot table using C# and VB.NET.

//Create an instance of ExcelEngine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Instantiate the Excel application object
    IApplication application = excelEngine.Excel;
 
    //Open a workbook with pivot table
    IWorkbook workbook = application.Workbooks.Open("PivotTable.xlsx");
 
    //Access the worksheet that contains the pivot table
    IWorksheet worksheet = workbook.Worksheets[1];
 
    //Access the pivot table
    IPivotTable pivotTable = worksheet.PivotTables[0];
    PivotTableImpl pivotTableImpl = pivotTable as PivotTableImpl;
 
    //Set the custom number format for a data field
    pivotTableImpl.DataFields[0].Field.NumberFormat = "###.00";
 
    //Save the workbook
    workbook.SaveAs("Output.xlsx");
}

VB.NET

'Create an instance of ExcelEngine
Using excelEngine As ExcelEngine = New ExcelEngine()
 
    'Instantiate the Excel application object
    Dim application As IApplication = excelEngine.Excel
 
    'Open a workbook with pivot table
    Dim workbook As IWorkbook = application.Workbooks.Open("PivotTable.xlsx")
 
    'Access the worksheet that contains the pivot table
    Dim worksheet As IWorksheet = workbook.Worksheets(1)
 
    'Access the pivot table
    Dim pivotTable As IPivotTable = worksheet.PivotTables(0)
    Dim pivotTableImpl As PivotTableImpl = TryCast(pivotTable, PivotTableImpl)
 
    'Set the custom number format for a data field
    pivotTableImpl.DataFields(0).Field.NumberFormat = "###.00"
 
    'Save the workbook
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example to set custom format on data fields of a pivot table can be downloaded from SetCustomFormatOnPivotDataFields.zip.

Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions with code examples.

Refer here to explore the rich set of Syncfusion Excel (XlsIO) library features.

An online sample link to generate Excel file.

How to set custom format on data fields of a Pivot Table?

Steps to set custom formats on data fields of a pivot table programmatically:

See Also:

Access denied