Importing Data from Excel

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

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

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

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

//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.

Switch to the code view and add the following import statements.

Visual Basic
C#

Visual Basic

Imports C1.DataEngine
Imports System.Data.OleDb

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

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"

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

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

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);
    }
}

Initialize workspace in the Form's constructor.

Visual Basic
C#

Visual Basic

Public Sub New()
    InitializeComponent()
    C1.DataEngine.Workspace.Init(dataPath)
End Sub

public Form1()
{
    InitializeComponent();
    C1.DataEngine.Workspace.Init(dataPath);
}

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

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;
    }
}

Subscribe button1_click event from the Properties window.

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()
    C1FlexPivotPage1.FlexPivotPanel.ConnectDataEngine(tableName)

    'build a default view
    Dim engine = C1FlexPivotPage1.FlexPivotPanel.FlexPivotEngine
    engine.BeginUpdate()
    engine.RowFields.Add("salesperson")
    engine.ValueFields.Add("amount")
    engine.EndUpdate()
End Sub

private void button1_Click(object sender, EventArgs e)
{
    string tableName = GetFirstSalesData();
    c1FlexPivotPage1.FlexPivotPanel.ConnectDataEngine(tableName);

    //build a default view
    var engine = c1FlexPivotPage1.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.

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