Excel Data Source

Sep 30, 2022
4 minutes to read

The Data Source Configuration Wizard allows you to bind the PivotGridControl to an Excel data source using the ExcelDataSource component.

After you have connected to the Excel data source, you can use the Retrieve Fields command to retrieve the available data source fields. As an alternative, you can add specific fields manually using the PivotGrid Designer. To do this, use the Run Designer… command.

Connect to the Excel Data Source in Code

This example demonstrates how to create the Pivot Grid fields in code and specify their location and format. The Pivot Grid’s data source is the ExcelDataSource instance, created in code.

C#
VB.NET

csharp

using DevExpress.DataAccess.Excel;
using DevExpress.XtraEditors;
using DevExpress.XtraPivotGrid;
using System;

namespace WinFormsPivotGridDataFieldsExample {
    public partial class Form1 : XtraForm {
        public Form1() {
            InitializeComponent();
            this.Load += Form1_Load;
            // Create the Excel Data Source.
            ExcelDataSource ds = new ExcelDataSource();
            ds.FileName = "SalesPerson.xlsx";
            ExcelWorksheetSettings settings = new ExcelWorksheetSettings("Data");
            ds.SourceOptions = new ExcelSourceOptions(settings);
            ds.Fill();
            // Set the pivot's data source.
            pivotGridControl1.DataSource = ds;
            // Create pivot grid fields.
            PivotGridField fieldCategoryName = new PivotGridField() {
                Area = PivotArea.RowArea,
                AreaIndex = 0,
                Caption = "Category Name"
            };
            // Bind fields to columns in the data source.
            DataSourceColumnBinding categoryNameBinding = new DataSourceColumnBinding("CategoryName");
            fieldCategoryName.DataBinding = categoryNameBinding;    

            PivotGridField fieldProductName = new PivotGridField() {
                Area = PivotArea.RowArea,
                AreaIndex = 1,
                Caption = "Product Name"
            };

            DataSourceColumnBinding productNameBinding = new DataSourceColumnBinding("ProductName");
            fieldProductName.DataBinding = productNameBinding;    

            PivotGridField fieldExtendedPrice = new PivotGridField() {
                Area = PivotArea.DataArea,
                AreaIndex = 0,
                Caption = "Extended Price"
            };

            DataSourceColumnBinding extendedPriceBinding = new DataSourceColumnBinding("Extended Price");
            fieldExtendedPrice.DataBinding = extendedPriceBinding; 

            // Specify the field format.
            fieldExtendedPrice.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
            fieldExtendedPrice.CellFormat.FormatString = "c2";

            PivotGridField fieldOrderDate1 = new PivotGridField() {
                Area = PivotArea.ColumnArea,
                AreaIndex = 0,
                Caption = "Year"
            };

            DataSourceColumnBinding fieldOrderDate1Binding = new DataSourceColumnBinding("OrderDate");
            fieldOrderDate1Binding.GroupInterval = PivotGroupInterval.DateYear;
            fieldOrderDate1.DataBinding = fieldOrderDate1Binding; 

            PivotGridField fieldOrderDate2 = new PivotGridField() {
                Area = PivotArea.ColumnArea,
                AreaIndex = 1,
                Caption = "Quarter"
            };

            DataSourceColumnBinding fieldOrderDate2Binding = new DataSourceColumnBinding("OrderDate");
            fieldOrderDate2Binding.GroupInterval = PivotGroupInterval.DateQuarter;
            fieldOrderDate2.DataBinding = fieldOrderDate2Binding; 

            PivotGridField fieldCountry = new PivotGridField() {
                AreaIndex = 0,
                Caption = "Country"
            };

            DataSourceColumnBinding countryBinding = new DataSourceColumnBinding("Country");
            fieldCountry.DataBinding = countryBinding; 

            // Create a field's filter.
            fieldCountry.FilterValues.Clear();
            fieldCountry.FilterValues.FilterType = PivotFilterType.Included;
            fieldCountry.FilterValues.Add("USA");
            // Add fields to the pivot grid.
            pivotGridControl1.Fields.AddRange(new PivotGridField[] {
            fieldCategoryName,
            fieldProductName,
            fieldOrderDate1,
            fieldOrderDate2,
            fieldExtendedPrice,
            fieldCountry});
        }

        private void Form1_Load(object sender, EventArgs e) {
            pivotGridControl1.BestFit();
        }
    }
}

Imports DevExpress.DataAccess.Excel
Imports DevExpress.XtraEditors
Imports DevExpress.XtraPivotGrid
Imports System

Namespace WinFormsPivotGridDataFieldsExample
    Partial Public Class Form1
        Inherits XtraForm

        Public Sub New()
            InitializeComponent()
            AddHandler Me.Load, AddressOf Form1_Load
            ' Create the Excel Data Source.
            Dim ds As New ExcelDataSource()
            ds.FileName = "SalesPerson.xlsx"
            Dim settings As New ExcelWorksheetSettings("Data")
            ds.SourceOptions = New ExcelSourceOptions(settings)
            ds.Fill()
            ' Set the pivot's data source.
            pivotGridControl1.DataSource = ds
            ' Create pivot grid fields.
            Dim fieldCategoryName As New PivotGridField() With {
                .Area = PivotArea.RowArea,
                .AreaIndex = 0,
                .Caption = "Category Name"
            }
            ' Bind fields to columns in the data source.
            Dim categoryNameBinding As New DataSourceColumnBinding("CategoryName")
            fieldCategoryName.DataBinding = categoryNameBinding

            Dim fieldProductName As New PivotGridField() With {
                .Area = PivotArea.RowArea,
                .AreaIndex = 1,
                .Caption = "Product Name"
            }

            Dim productNameBinding As New DataSourceColumnBinding("ProductName")
            fieldProductName.DataBinding = productNameBinding

            Dim fieldExtendedPrice As New PivotGridField() With {
                .Area = PivotArea.DataArea,
                .AreaIndex = 0,
                .Caption = "Extended Price"
            }

            Dim extendedPriceBinding As New DataSourceColumnBinding("Extended Price")
            fieldExtendedPrice.DataBinding = extendedPriceBinding

            ' Specify the field format.
            fieldExtendedPrice.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric
            fieldExtendedPrice.CellFormat.FormatString = "c2"

            Dim fieldOrderDate1 As New PivotGridField() With {
                .Area = PivotArea.ColumnArea,
                .AreaIndex = 0,
                .Caption = "Year"
            }

            Dim fieldOrderDate1Binding As New DataSourceColumnBinding("OrderDate")
            fieldOrderDate1Binding.GroupInterval = PivotGroupInterval.DateYear
            fieldOrderDate1.DataBinding = fieldOrderDate1Binding

            Dim fieldOrderDate2 As New PivotGridField() With {
                .Area = PivotArea.ColumnArea,
                .AreaIndex = 1,
                .Caption = "Quarter"
            }

            Dim fieldOrderDate2Binding As New DataSourceColumnBinding("OrderDate")
            fieldOrderDate2Binding.GroupInterval = PivotGroupInterval.DateQuarter
            fieldOrderDate2.DataBinding = fieldOrderDate2Binding

            Dim fieldCountry As New PivotGridField() With {
                .AreaIndex = 0,
                .Caption = "Country"
            }

            Dim countryBinding As New DataSourceColumnBinding("Country")
            fieldCountry.DataBinding = countryBinding

            ' Create a field's filter.
            fieldCountry.FilterValues.Clear()
            fieldCountry.FilterValues.FilterType = PivotFilterType.Included
            fieldCountry.FilterValues.Add("USA")
            ' Add fields to the pivot grid.
            pivotGridControl1.Fields.AddRange(New PivotGridField() { fieldCategoryName, fieldProductName, fieldOrderDate1, fieldOrderDate2, fieldExtendedPrice, fieldCountry})
        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
            pivotGridControl1.BestFit()
        End Sub
    End Class
End Namespace