Filters in pivot tables enable users to narrow down the displayed data based on specific criteria. By adding filters, users can focus on subsets of data that are most relevant to their analysis, allowing for a more targeted and efficient data exploration. In this article, we will demonstrate how to add filters to pivot tables in Excel in C# using Spire.XLS for .NET.

• Add Report Filter to Pivot Table in Excel in C#
• Add Filter to a Row Field of Pivot Table in Excel in C#
• Add Filter to a Column Field of Pivot Table in Excel in C#

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

Add Report Filter to Pivot Table in Excel in C#

Spire.XLS for .NET offers the XlsPivotTable.ReportFilters.Add() method to add report filters to a pivot table. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet in the file using Workbook.Worksheets[index] property.
• Get a specific pivot table in the worksheet using Worksheet.PivotTables[index] property.
• Create a report filter using PovotReportFilter class.
• Add the report filter to the pivot table using XlsPivotTable.ReportFilters.Add() method.
• Save the resulting file using Workbook.SaveToFile() method.

using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;

namespace AddReportFilter
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create an object of the Workbook class
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the first pivot table
            XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;

            // Create a report filter
            PivotReportFilter reportFilter = new PivotReportFilter("Product", true);

            // Add the report filter to the pivot table
            pt.ReportFilters.Add(reportFilter);

            // Save the resulting file
            workbook.SaveToFile("AddReportFilter.xlsx", FileFormat.Version2016);
            workbook.Dispose();
        }
    }
}

Add Filter to a Row Field of Pivot Table in Excel in C#

You can add a value filter or label filter to a specific row field in a pivot table using the XlsPivotTable.RowFields[index].AddValueFilter() or XlsPivotTable.RowFields[index].AddLabelFilter() method. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet in the file using Workbook.Worksheets[index] property.
• Get a specific pivot table in the worksheet using Worksheet.PivotTables[index] property.
• Add a value filter or label filter to a specific row field in the pivot table using XlsPivotTable.RowFields[index].AddValueFilter() or XlsPivotTable.RowFields[index].AddLabelFilter() method.
• Calculate the data in the pivot table using XlsPivotTable.CalculateData() method.
• Save the resulting file using Workbook.SaveToFile() method.

using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;

namespace AddRowFilter
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create an object of the Workbook class
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the first pivot table
            XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;


            // Add a value filter to the first row field in the pivot table
            pt.RowFields[0].AddValueFilter(PivotValueFilterType.GreaterThan, pt.DataFields[0], 5000, null);
            // Or add a label filter to the first row field in the pivot table
            //pt.RowFields[0].AddLabelFilter(PivotLabelFilterType.Equal, "Mike", null);

            // Calculate the pivot table data
            pt.CalculateData();

            // Save the resulting file
            workbook.SaveToFile("AddRowFilter.xlsx", FileFormat.Version2016);
            workbook.Dispose();
        }
    }
}

Add Filter to a Column Field of Pivot Table in Excel in C#

To add a value filter or label filter to a specific column field in a pivot table, you can use the XlsPivotTable.ColumnFields[index].AddValueFilter() or XlsPivotTable.ColumnFields[index].AddLabelFilter() method. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet in the file using Workbook.Worksheets[index] property.
• Get a specific pivot table in the worksheet using Worksheet.PivotTables[index] property.
• Add a value filter or label filter to a specific column field in the pivot table using XlsPivotTable.ColumnFields[index].AddValueFilter() or XlsPivotTable.ColumnFields[index].AddLabelFilter() method.
• Calculate the data in the pivot table using XlsPivotTable.CalculateData() method.
• Save the resulting file using Workbook.SaveToFile() method.

using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;

namespace AddColumnFilter
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create an object of the Workbook class
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the first pivot table
            XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;

            // Add a label filter to the first column field in the pivot table
            pt.ColumnFields[0].AddLabelFilter(PivotLabelFilterType.Equal, "Laptop", null);
            // Or add a value filter to the first column field in the pivot table
            // pt.ColumnFields[0].AddValueFilter(PivotValueFilterType.Between, pt.DataFields[0], 5000, 10000);


            // Calculate the pivot table data
            pt.CalculateData();

            // Save the resulting file
            workbook.SaveToFile("AddColumnFilter.xlsx", FileFormat.Version2016);
            workbook.Dispose();
        }
    }
}

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.

Digital signatures serve as a critical layer of security, ensuring that an Excel file has not been altered since it was signed and verifying the identity of its originator. However, there are scenarios where the detection and removal of these digital signatures become necessary, such as when consolidating multiple documents, updating content, or preparing files for systems that do not support digitally signed documents. This article shows how to detect and remove digital signatures in Excel files with Python code using Spire.XLS for Python, providing a simple way to batch process Excel file digital signatures.

• Detecting the Presence of Digital Signatures in Excel Files
• Removing Digital Signatures from Excel Files

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip commands.

pip install Spire.XLS

If you are unsure how to install, please refer to: How to Install Spire.XLS for Python on Windows

Detecting the Presence of Digital Signatures in Excel Files

Spire.XLS for Python provides the Workbook class to deal with Excel files and the Workbook.IsDigitallySigned property to check if an Excel file has digital signatures. Developers can use the Boolean value returned by this property to determine whether the Excel file contains a digital signature.

The detailed steps for detecting if an Excel file has digital signatures are as follows:

• Create an instance of Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Check whether the workbook is digitally signed by the value of the Workbook.IsDigitallySigned property.

from spire.xls import *

# Create an instance of Workbook
workbook = Workbook()

# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Check whether the workbook is digitally signed
if workbook.IsDigitallySigned is False:
    print("The workbook is not digitally signed.")
else:
    print("The workbook is digitally signed.")

Removing Digital Signatures from Excel Files

Developers can use the Workbook.RemoveAllDigitalSignatures() method to effortlessly delete all digital signatures in an Excel workbook. The detailed steps are as follows:

• Create an instance of Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Remove all digital signatures from the workbook using Workbook.RemoveAllDigitalSignatures() method.
• Save the workbook using Workbook.SaveToFile() method.

from spire.xls import *

# Create an instance of Workbook
workbook = Workbook()

# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Remove digital signatures
workbook.RemoveAllDigitalSignatures()

# Save the document
workbook.SaveToFile("output/RemoveExcelDigitalSignature.xlsx", FileFormat.Version2016)
workbook.Dispose()

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.

Importing and exporting PDF form data allows users to seamlessly exchange form information with external files in formats such as FDF (Forms Data Format), XFDF (XML Forms Data Format), or XML. The import function enables quick population of PDF forms using data from external sources, while the export function extracts data from PDF forms and saves it to external files. This capability simplifies data management, making it especially valuable for processing large volumes of form data or integrating with other systems. In this article, we will demonstrate how to import PDF form data from FDF, XFDF, or XML files, or export PDF form data to FDF, XFDF, or XML files in C# using Spire.PDF for .NET.

• Import PDF Form Data from FDF, XFDF or XML Files in C#
• Export PDF Form Data to FDF, XFDF or XML Files in C#

Install Spire.PDF for .NET

To begin with, you need to add the DLL files included in the Spire.PDF for.NET package as references in your .NET project. The DLLs files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.PDF

Import PDF Form Data from FDF, XFDF or XML Files in C#

Spire.PDF for .NET offers the PdfFormWidget.ImportData() method for importing PDF form data from FDF, XFDF, or XML files. The detailed steps are as follows.

• Create an object of the PdfDocument class.
• Load a PDF document using PdfDocument.LoadFromFile() method.
• Get the form of the PDF document using PdfDocument.Form property.
• Import form data from an FDF, XFDF or XML file using PdfFormWidget.ImportData() method.
• Save the resulting document using PdfDocument.SaveToFile() method.

using Spire.Pdf;
using Spire.Pdf.Widget;

namespace ImportPdfFormData
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Create an object of the PdfDocument class
            PdfDocument document = new PdfDocument();
            //Load a PDF document
            document.LoadFromFile("Forms.pdf");

            //Get the form of the PDF document 
            PdfFormWidget loadedForm = document.Form as PdfFormWidget;
             
            //Import PDF form data from an XML file
            loadedForm.ImportData("Data.xml", DataFormat.Xml);

            //Import PDF form data from an FDF file
            //loadedForm.ImportData("Data.fdf", DataFormat.Fdf);

            //Import PDF form data from an XFDF file
            //loadedForm.ImportData("Data.xfdf", DataFormat.XFdf);

            //Save the resulting document
            document.SaveToFile("Output.pdf");
            //Close the PdfDocument object
            document.Close();
        }
    }
}

Export PDF Form Data to FDF, XFDF or XML Files in C#

Spire.PDF for .NET also enables you to export PDF form data to FDF, XFDF, or XML files by using the PdfFormWidget.ExportData() method. The detailed steps are as follows.

• Create an object of the PdfDocument class.
• Load a PDF document using PdfDocument.LoadFromFile() method.
• Get the form of the PDF document using PdfDocument.Form property.
• Export form data to an FDF, XFDF or XML file using PdfFormWidget.ExportData() method.

using Spire.Pdf;
using Spire.Pdf.Fields;
using Spire.Pdf.Widget;

namespace ExportPdfFormData
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Create an object of the PdfDocument class
            PdfDocument document = new PdfDocument();
            //Load a PDF document
            document.LoadFromFile("Forms.pdf");

            //Get the form of the PDF document 
            PdfFormWidget loadedForm = document.Form as PdfFormWidget;

            //Export PDF form data to an XML file
            loadedForm.ExportData("Data.xml", DataFormat.Xml, "Form");

            //Export PDF form data to an FDF file
            //loadedForm.ExportData("Data.fdf", DataFormat.Fdf, "Form");

            //Export PDF form data to an XFDF file
            //loadedForm.ExportData("Data.xfdf", DataFormat.XFdf, "Form");

            //Close the PdfDocument object
            document.Close();
        }
    }
}

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.