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