Opening an Excel File with Multiple Sheets in a Windows Form using EP Plus

Introduction

Uploads an Excel sheet with/without Office Web Components, using EP Plus, into a datagrid with sheet names as buttons aligned horizontally below the grid.

• Steps to install EP plus DLL: https://www.nuget.org/packages/EPPlus
• All about EP Plus here: http://epplus.codeplex.com/

Using the Code

A simple form with a data grid and a panel (to add buttons dynamically for sheets), the data grid has the AllowDrop property set to true so that a sheet can be dragged and dropped onto the window rather than just selecting it from a file upload dialog box.

The method to open the file dialog to select an Excel sheet is as:

 Public Sub ShowOpenFileDialog()
        'DECLARE VARIABLES
        Dim lDialog As System.Windows.Forms.OpenFileDialog
        'INITIALIZE DIALOG
        lDialog = New System.Windows.Forms.OpenFileDialog
        lDialog.Title = "Open worksheet"
        lDialog.CheckFileExists = True
        lDialog.Filter = ("Xls/Xlsx/Xlsm (Excel)|*.xls;*.xlsx;*.xlsm|Xls (Excel 2003)|_
                         *.xls|Xlsx/Xlsm/Xlsb (Excel 2007)|*.xlsx;*.xlsm;*.xlsb|Xml (Xml)|_
                         *.xml|Html (Html)|*.html|Csv (Csv)|*.csv|All files|*.*")
        lDialog.InitialDirectory = "C:\"
        lDialog.Multiselect = False
        'SHOW DIALOG
        If (lDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK) Then
            System.Windows.Forms.Application.DoEvents()
            Try
                CreateDataGridSource(lDialog.FileName)
                DataGridView_ExcelSheet.DataSource = dt_Source
            Finally
                lDialog.Dispose()
            End Try
        End If
 End Sub

dt_source is a DataTable declared globally so that it can be used on every sheet selection.

After the file is uploaded, we create the datatable from the Excel data using EP Plus, defined in the method CreateDataGridSource():

Private Sub CreateDataGridSource(ByVal fileName As String)
        Try
            Dim File As FileInfo = New FileInfo(fileName)
            globalPackage = New ExcelPackage(File)
            globalFileName = globalPackage.File.FullName
            globalLastActiveWorksheet = _
               globalPackage.Workbook.Worksheets.FirstOrDefault(Function(f) f.View.TabSelected)
            filldatagridview(globalLastActiveWorksheet)
            AddSheetButtons()
        Catch e As Exception
            If e.Message = "Can not open the package. Package is an OLE compound document. _
                            If this is an encrypted package, please supply the password" Then
                MessageBox.Show("Please check the file format, .xls files are not supported, _
                      use .xlsx format!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Else MessageBox.Show(e.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            End If
        End Try
End Sub

ExcelPackage is the class in OfficeOpenXML which is present in the EP Plus DLL.

This piece of code will also take care of Excel files with .xls format, since EP Plus doesn't support it, a prompt is given to configure the Excel sheet as .xlsx format.

The last active worksheet fetched by the LINQ expression "globalPackage.Workbook.Worksheets.FirstOrDefault(Function(f) f.View.TabSelected)" gives us the Excel sheet which was opened last, before the workbook was closed.

Now we move on to filldatagridview() method which will fetch the Excel data, convert it into a datatable and set the source of the datagrid view.

 Public Sub filldatagridview(ByVal lWorksheet As ExcelWorksheet)
        dt_Source = WorksheetToDataTable(lWorksheet)
        If dt_Source.Rows.Count = 0 Then
            MessageBox.Show("There is no data in the selected sheet")
        End If
        DataGridView_ExcelSheet.DataSource = dt_Source
 End Sub

 Public Function WorksheetToDataTable(ByVal oSheet As ExcelWorksheet) As System.Data.DataTable
        Dim dt As DataTable
        Try
            If (oSheet.Dimension Is Nothing) Then
                dt = New DataTable()
            Else
                Dim totalRows As Integer = oSheet.Dimension.End.Row
                Dim totalCols As Integer = oSheet.Dimension.End.Column
                dt = New DataTable(oSheet.Name)
                Dim dr As DataRow = Nothing
                For i As Integer = 1 To totalCols
                    dt.Columns.Add("Column_" + i.ToString()) 'adding custom column names 
                                                             'to display in form
                Next
                For i As Integer = 1 To totalRows
                    dr = dt.Rows.Add()
                    For j As Integer = 1 To totalCols
                        dr(j - 1) = oSheet.Cells(i, j).Value
                    Next
                Next
            End If

        Catch lException As Exception
            MessageBox.Show(lException.Message)
            Return New DataTable()
        End Try
        Return dt
 End Function

Here, the worksheet is converted into a datatable and source of the datagridview is updated.

Now, we move on to the method which adds the sheet buttons dynamically:

Public Sub AddSheetButtons()
        Pnl_SheetButtons.Controls.Clear()
        For I As Integer = 1 To globalPackage.Workbook.Worksheets.Count
            Dim SheetButton As New System.Windows.Forms.Button
            SheetButton.Text = globalPackage.Workbook.Worksheets(I).Name.ToString()
            AddHandler SheetButton.Click, AddressOf SheetButtonClick_SheetChanged
            Pnl_SheetButtons.Controls.Add(SheetButton)
            Dim tooltipForSheetName As New ToolTip
            tooltipForSheetName.SetToolTip(SheetButton, SheetButton.Text)
            SheetButton.Left = (I - 1) * 80
        Next
End Sub
   
Private Sub SheetButtonClick_SheetChanged(sender As Object, e As EventArgs)
        globalLastActiveWorksheet = globalPackage.Workbook.Worksheets(sender.Text)
        filldatagridview(globalLastActiveWorksheet)
        'sender.Text has the sheet name
End Sub    

Here, each button is created dynamically and with sheet name, along with a tooltip if in case the name of the sheet goes beyond the width of the button. The event handler is also attached which updates the datagrid view on selection of a particular sheet button.

Apart from the dialog box for uploading a file, on drop of Excel sheet, the application responds, after making the AllowDrop property to true from the designer, we have to implement the drag enter method like this:

Private Sub Form1_DragEnter(sender As Object, e As DragEventArgs) Handles MyBase.DragEnter
        e.Effect = DragDropEffects.Link
        Dim ret As Boolean
        Dim filename As String = String.Empty
        If (e.AllowedEffect And DragDropEffects.Link) = DragDropEffects.Link Then
            Dim data As Array = TryCast(DirectCast(e.Data, IDataObject).GetData("FileName"), Array)
            If data IsNot Nothing Then
                If (data.Length = 1) AndAlso (TypeOf data.GetValue(0) Is [String]) Then
                    filename = DirectCast(data, String())(0)
                    Dim ext As String = Path.GetExtension(filename).ToLower()
                    If (ext = ".xls") OrElse (ext = ".xlsx") Then
                        ret = True
                    End If
                End If
            End If
        End If
        If ret Then
            CreateDataGridSource(filename)
            DataGridView_ExcelSheet.DataSource = dt_Source
        End If
End Sub    

Point to be noted is that this event is filtering the files, only if the dropped file is an Excel, then only it will proceed to open the sheet and fetch the data into the datagrid view.

This is how it looks:

Also to help developers using C#, you can use the link: http://converter.telerik.com/ to convert the code from VB to C# and vice versa.

Points of Interest

Working with EP Plus, you can see a greater improvement on performance as compared to the old OWC or Microsoft Excel Interop services, also for aligning controls, we have used a Table Layout Panel for containing all the controls, thus making it responsive, meaning the size adjusts on resizing of the parent window.

History

• 8^th Feb 2017: 1^st attempt with VB.NET