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.
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()
Dim lDialog As System.Windows.Forms.OpenFileDialog
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
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())
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)
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
- 8th Feb 2017: 1st attempt with VB.NET