You can import data from Excel files (.xls, .xlsx) to your FlexPivot application. The code sample given below illustrates importing Excel files in FlexPivotPage.

Complete the following steps to import data from an Excel file to FlexPivotPage control. This example uses a sample Excel file named Sales.xlsx for importing data.

1. Create a Windows Forms Application project in Visual Studio.
2. Add FlexPivotPage control to the form through Toolbox.
3. Click once on the smart tag icon ( ) to open the FlexPivotPage Tasks smart tag panel.
4. Select Undock in Parent Container option to undock the FlexPivotPage control in the parent container i.e. Form.
5. Navigate to the Toolbox again and add a general button control to the Form.
6. Place the button control above the FlexPivotPage control.
7. Set the Text property for the button control as Import Data from the Properties window. The designer appears similar to the image given below.

   

8. Switch to the code view and add the following code to set up a connection string with the Sales.xlsx file.
   • Visual Basic
   • C#

   • Visual Basic

   'get sample Excel file connection string
   Private Function GetConnectionString(Optional firstRowHasNames As Boolean = True, Optional mixedTypesAsText As Boolean = True) As String
       Dim conn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR={1};IMEX={2};ReadOnly=true"""
       Return String.Format(conn, samplePath, firstRowHasNames, mixedTypesAsText)
   End Function
   

   • C#

   //get sample Excel file connection string
   private string GetConnectionString(bool firstRowHasNames = true, bool mixedTypesAsText = true)
   {
       string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR={1};IMEX={2};ReadOnly=true\"";
       return string.Format(conn, samplePath, firstRowHasNames, mixedTypesAsText);
   
   }
   

   You can store this file at Documents\ComponentOne Samples\Common\Sales.xlsx location on your system. In case you want to store the file at a different location then make changes in the path defined in the GetConnectionString method.

9. Switch to the code view and add the following import statements.
   • Visual Basic
   • C#

   • Visual Basic

   Imports C1.DataEngine
   Imports System.Data.OleDb
   

   • C#

   using C1.DataEngine;
   using System.Data.OleDb;
   

10. Initialize data path and sample path as illustrated in the following code.
    • Visual Basic
    • C#

    • Visual Basic

    Dim dataPath As String = Path.Combine(System.Windows.Forms.Application.StartupPath, "Data")
    Dim samplePath As String = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + "\ComponentOne Samples\Common\Sales.xlsx"
    

    • C#

    string dataPath = Path.Combine(System.Windows.Forms.Application.StartupPath, "Data");
    string samplePath = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + @"\ComponentOne Samples\Common\Sales.xlsx";
    

11. Create a class named Sales in the code view to read the data from Excel file.
    • Visual Basic
    • C#

    • Visual Basic

    Public Class Sales
        Public Property salesperson() As String
            Get
                Return m_salesperson
            End Get
            Set(value As String)
                m_salesperson = Value
            End Set
        End Property
        Private m_salesperson As String
        Public Property region() As String
            Get
                Return m_region
            End Get
            Set(value As String)
                m_region = Value
            End Set
        End Property
        Private m_region As String
        Public Property account_number() As Double
            Get
                Return m_account_number
            End Get
            Set(value As Double)
                m_account_number = Value
            End Set
        End Property
        Private m_account_number As Double
        Public Property amount() As Decimal
            Get
                Return m_amount
            End Get
            Set(value As Decimal)
                m_amount = Value
            End Set
        End Property
        Private m_amount As Decimal
        Public Property month() As String
            Get
                Return m_month
            End Get
            Set(value As String)
                m_month = Value
            End Set
        End Property
        Private m_month As String
    
        Public Sub New(reader As IDataReader)
            Dim nv = New NullValue()
            salesperson = If(reader.IsDBNull(0), nv.NullString, reader.GetString(0))
            region = If(reader.IsDBNull(1), nv.NullString, reader.GetString(1))
            account_number = If(reader.IsDBNull(2), nv.NullDouble, reader.GetDouble(2))
            amount = If(reader.IsDBNull(3), nv.NullDecimal, reader.GetDecimal(3))
            month = If(reader.IsDBNull(4), nv.NullString, reader.GetString(4))
        End Sub
    
        Public Shared Iterator Function GetSalesInfo(reader As IDataReader) As IEnumerable(Of Sales)
            While reader.Read()
                Yield New Sales(reader)
            End While
        End Function
    
    End Class
    

    • C#

    public class Sales
    {
        public string salesperson { get; set; }
        public string region { get; set; }
        public double account_number { get; set; }
        public decimal amount { get; set; }
        public string month { get; set; }
    
        public Sales(IDataReader reader)
        {
            var nv = new NullValue();
            salesperson = reader.IsDBNull(0) ? nv.NullString : reader.GetString(0);
            region = reader.IsDBNull(1) ? nv.NullString : reader.GetString(1);
            account_number = reader.IsDBNull(2) ? nv.NullDouble : reader.GetDouble(2);
            amount = reader.IsDBNull(3) ? nv.NullDecimal : reader.GetDecimal(3);
            month = reader.IsDBNull(4) ? nv.NullString : reader.GetString(4);
        }
    
        public static IEnumerable<Sales> GetSalesInfo(IDataReader reader)
        {
            while (reader.Read())
                yield return new Sales(reader);
        }
    }
    

12. Initialize workspace in the Form's constructor.
    • Visual Basic
    • C#

    • Visual Basic

    Public Sub New()
        InitializeComponent()
        FlexPivotPage1.FlexPivotPanel.Workspace.Init(dataPath)
    End Sub
    

    • C#

    public Form1()
    {
        InitializeComponent();
        FlexPivotPage1.FlexPivotPanel.Workspace.Init(dataPath);
    }
    

13. Add the following code to fetch data from the Excel file.
    • Visual Basic
    • C#

    • Visual Basic

    Private Function GetFirstSalesData() As String
        Using conn As New OleDbConnection(GetConnectionString())
            conn.Open()
            ' get workbook table list
            Dim tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
    
            Dim name As String = tables.Rows(0)("TABLE_NAME").ToString()
    
            Dim command = New OleDbCommand((Convert.ToString("select * from [") & name) + "]", conn)
            Using reader = command.ExecuteReader()
                Dim connector = New ObjectConnector(Of Sales)(Sales.GetSalesInfo(reader))
                connector.GetData(name)
            End Using
            Return name
        End Using
    End Function
    

    • C#

    private string GetFirstSalesData()
    {
        using (OleDbConnection conn = new OleDbConnection(GetConnectionString()))
        {
            conn.Open();
            // get workbook table list
            var tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    
            string name = tables.Rows[0]["TABLE_NAME"].ToString();
    
            var command = new OleDbCommand("select * from [" + name + "]", conn);
            using (var reader = command.ExecuteReader())
            {
                var connector = new ObjectConnector<Sales>(Sales.GetSalesInfo(reader));
                connector.GetData(name);
            }
            return name;
        }
    }
    

14. Subscribe button1_click event from the Properties window.
15. Add the following code to the event handler created in the above step.
    • Visual Basic
    • C#

    • Visual Basic

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim tableName As String = GetFirstSalesData()
        FlexPivotPage1.FlexPivotPanel.ConnectDataEngine(tableName)
    
        'build a default view
        Dim engine = FlexPivotPage1.FlexPivotPanel.FlexPivotEngine
        engine.BeginUpdate()
        engine.RowFields.Add("salesperson")
        engine.ValueFields.Add("amount")
        engine.EndUpdate()
    End Sub
    

    • C#

    private void button1_Click(object sender, EventArgs e)
    {
        string tableName = GetFirstSalesData();
        flexPivotPage1.FlexPivotPanel.ConnectDataEngine(tableName);
    
        //build a default view
        var engine = flexPivotPage1.FlexPivotPanel.FlexPivotEngine;
        engine.BeginUpdate();
        engine.RowFields.Add("salesperson");
        engine.ValueFields.Add("amount");
        engine.EndUpdate();
    }
    

    This code connects the Data Engine to the sample table and builds a default view to be displayed on running the application.

16. Press F5 to run the application and click the button control appearing on the form to import data from the sample file.