ComponentOne WinForms Edition
Task-Based Help / Importing Data from Excel
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 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 ( Smart tag ) 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.

    ImportExcel_DesignView

  8. Switch to the code view and add the following code to set up a connection string with the Sales.xlsx file. 
    '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

    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. 
    Imports C1.DataEngine
Imports System.Data.OleDb
  10. Initialize data path and sample path as illustrated in the following code. 
    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"
  11. Create a class named Sales in the code view to read the data from Excel file. 
    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
  12. Initialize workspace in the Form's constructor. 
    Public Sub New()
    InitializeComponent()
    FlexPivotPage1.FlexPivotPanel.Workspace.Init(dataPath)
End Sub
  13. Add the following code to fetch data from the Excel file. 
    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
  14. Subscribe button1_click event from the Properties window.
  15. Add the following code to the event handler created in the above step. 
    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

    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.

    FlexPivot_ImportfromExcel