EasyXLS™ : Manage MS Excel Data & Files Excellent.

By | 2017-04-12

Introduction
Nowadays I get very rarely impressed by any software. I believe I can count them on one hand only. The software component I will introduce with today’s article is actually one of them, EasyXLS. EasyXLS is a powerful .NET component that can manage MS Excel files by reading from them and writing to them. It can be installed on a server or on a workstation. So instead of using MS Excel on the server it can easily be replaced by EasyXLS, which also has a small footprint. By now we should know that Microsoft discourage the use of MS Excel on servers.

EasyXLS is a mature product, very fast, very reliable and at the same time very easy to work with. I’m very impressed with it, the object model it uses gives clean and a minimum of code. It takes a shorter time to learn the object module and how to use it in a recommended way. 

My presentation of EasyXLS starts with how to retrieve data from individual workbooks and worksheets. Due to its construction and how EasyXLS works we overcome all potential issues and shortcomings that may appear when, for instance, using classic ADO/ADO.NET together with SQL. The use of SQL can make it tricky to identify what data type each column holds. Various settings must be made in the connection string depending on the setup of the MS Excel files. In other words, by using EasyXLS these issues are no longer any issues!

EasyXLS is available for the .NET, Java and COM+ platforms. It’s also available in different versions where our needs should control the selection of version. For more information please see Products.

Before moving on, when working with larger MS Excel files I tend to use the binary file format. Using the binary file format means two advantages, the MS Excel files are loaded faster and are also written faster to disc compared with the other available file formats. This is important, especially when it comes to server side operations where one objective is to minimum the operations times.

Import Data From Excel Files
Storing data in workbooks have always been. Many users find it easy to save larger data set in MS Excel files. Remember also that many production and business software generates reports in MS Excel files. Despite the fact that MS Excel is a spreadsheet software it’s also used as a register software. However, sooner or later we want to do something with the stored data like creating reports, adding the data to large databases from which reports are created and distributed and so on.

In this first blog entry about EasyXLS we will take a deeper look on how to import data from MS Excel files by presenting five different scenarios. In the second blog entry I will discuss how to export data to MS Excel files and how to create MS Excel files in easy ways.

The following cases are built up by using the following WinForm GUI:

Just to make it more complete I want to show the following Imports statements that are added on top of the code module:

Imports Syncfusion
Imports Syncfusion.Windows.Forms

Imports System
Imports System.Drawing
Imports System.Windows.Forms
Imports System.Data.SqlClient

Imports EasyXLS
Imports EasyXLS.Util

Using Dataset
In the first case data will be presented in a Data Grid (see the above screen shot). To use the acquired data stored in a Data Table as a data source to the grid we only need to manage a Data set as the following code shows. That’s very smooth, right?

    Private Function Import_Table() As Boolean

        Dim XLWBook As ExcelDocument = New ExcelDocument()
        Dim DocumentsPath As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

        Try
            'Create the dataset and fill the dataset with data from the specific worksheet in the closed Excel Workbook.
            Dim dsData As DataSet = XLWBook.easy_ReadXLSBSheet_AsDataSet(DocumentsPath + "\Excel Files\ReportData.xlsb", "Data")

            'Create and populate the data table.
            Dim dtData As DataTable = dsData.Tables(0)

            'Grab the column names in to an object.
            Dim objHeader As Object() = dtData.Rows(0).ItemArray

            'Delete the first row which contains the field names.
            dtData.Rows.RemoveAt(0)

            'Populate the datagridview with data.
            Me.dgvXL.DataSource = dtData

            'Add the field names to the header's cells.
            For iColumn As Integer = 0 To dtData.Columns.Count - 1
                Me.dgvXL.Columns(iColumn).HeaderText = objHeader(iColumn)
            Next

            'Resize the columns.
            Me.dgvXL.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

            Me.sbPanel.Text = "Number of Records: " + dtData.Rows.Count.ToString

            Import_Table = True

        Catch ex As Exception
            MessageBox.Show(ex.Message, p_stTitle, MessageBoxButtons.OK)
            Import_Table = False
        Finally
            XLWBook.Dispose
            XLWBook = Nothing

            GC.Collect()
            GC.WaitForPendingFinalizers()
            'When calling the first time GC create a list of objects to be collected.
            'The second time we call GC it actually finalize the list of objects.
            GC.Collect()
            GC.WaitForPendingFinalizers()
        End Try

    End Function

It’s very straight forward and only use a few rows of code makes it fast and reliable.

Using List
The next case shows how to import a list of data (without field names). What is notetable is that we populate the List object the same way as with the Data Table object in the above case. To populate the grid we need to iterate through the list an add each row separately to the grid. It should also be noted that each row itself is a list object (see the below code).

    Private Function Import_List() As Boolean

        Dim XLWBook As ExcelDocument = New ExcelDocument()
        Dim DocumentsPath As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
        
        
        Try
            'Get the list of data from the sheet "Data"   
            Dim XLList As Object = XLWBook.easy_ReadXLSBSheet_AsList(DocumentsPath + "\Excel Files\ReportData.xlsb", "Data")
                        
            With Me.dgvXL
                'Create columns in the grid.
                .ColumnCount = 2
                .Columns(0).Name = "Name"
                .Columns(1).Name = "Sale"
            End With

            If (XLWBook.easy_getError().Equals("")) Then
                'Each row in the list is also a list with one row and two columns with data.
                Dim lstRow As List
                'Since the first row includes the field names we start at 1.
                For iRowIndex As Integer = 1 To XLList.size()-1
                    'Grab the row/list.
                    lstRow = XLList.elementAt(iRowIndex)
                    'Create the row entry.
                    Dim row As String() = New String() {lstRow.Item(0).ToString, lstRow.Item(1).ToString}
                    'Add the row to the grid.
                    Me.dgvXL.Rows.Add(row)
                Next            
            End If

            Me.sbPanel.Text = "Number of Records: " + (XLList.size()-1).ToString 
            Import_List = True

        Catch ex As Exception
            MessageBox.Show(ex.Message, p_stTitle, MessageBoxButtons.OK)
            Import_List = False
        Finally
            XLWBook.Dispose
            XLWBook = Nothing
        End Try

    End Function

Again, it’s straight forward and a very clean code solution.

Using Range
This is a rather unique feature that EasyXLS offers. We can specify a range in a worksheet to acquire data from. It’s a flexible and useful way as it offers more options to us to retrieve data from worksheets. The following code shows how we can retrieve data from one specific range:

    Private Function Import_Range() As Boolean

        Dim XLWBook As ExcelDocument = New ExcelDocument()
        Dim DocumentsPath As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

        Try
            'Create the dataset and fill the dataset with data from the specific worksheet and range in the closed Excel Workbook.
            Dim dsData As DataSet = XLWBook.easy_ReadXLSBSheet_AsDataSet(DocumentsPath + "\Excel Files\ReportData.xlsb", "Data", "A1:B9;C10:E20")

            'Create and populate the data table.
            Dim dtData As DataTable = dsData.Tables(0)

            'Grab the column names in to an object.
            Dim objHeader As Object() = dtData.Rows(0).ItemArray

            'Delete the first row which contains the field names.
            dtData.Rows.RemoveAt(0)

            'Populate the datagridview with data.
            Me.dgvXL.DataSource = dtData

            'Add the field names to the header's cells.
            For iColumn As Integer = 0 To dtData.Columns.Count - 1
                Me.dgvXL.Columns(iColumn).HeaderText = objHeader(iColumn)
            Next

            'Resize the columns.
            Me.dgvXL.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

            Me.sbPanel.Text = "Number of Records: " + dtData.Rows.Count.ToString

            Import_Range = True
            
        Catch ex As Exception
            MessageBox.Show(ex.Message, p_stTitle, MessageBoxButtons.OK)
            Import_Range = False
        Finally
            XLWBook.Dispose
            XLWBook = Nothing
        End Try

    End Function

The next case shows again what a versatile tool EasyXLS is. Not only can it manage to read data from native MS Excel files but also from Text files, CSV, XML and HTML files!

When using text files as sources for data import we need to specify the separation commands for both columns and rows as the following example also shows:

    Private Function Import_TXT_File() As Boolean

        Dim txtFile As ExcelDocument = New ExcelDocument()
        Dim DocumentsPath As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)


        Try
            'Create the dataset and fill the dataset with data from the (closed) text file.
            Dim dsData As DataSet = txtFile.easy_ReadTXTFile_AsDataSet(DocumentsPath + "\Excel Files\ReportData.txt", vbNewLine, vbTab)
            
            'Create and populate the data table.
            Dim dtData As DataTable = dsData.Tables(0)

            'Grab the column names to place them into an object.
            Dim objHeader As Object() = dtData.Rows(0).ItemArray

            'Delete the first row which contains the field names.
            dtData.Rows.RemoveAt(0)

            'Populate the datagridview with data.
            Me.dgvXL.DataSource = dtData

            'Add the field names to the header's cells.
            For iColumn As Integer = 0 To dtData.Columns.Count - 1
                Me.dgvXL.Columns(iColumn).HeaderText = objHeader(iColumn)
            Next

            'Resize the columns.
            Me.dgvXL.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

            Me.sbPanel.Text = "Number of Records: " + dtData.Rows.Count.ToString

            Import_TXT_File = True

        Catch ex As Exception
            MessageBox.Show(ex.Message,p_stTitle)
            Import_TXT_File = False
        Finally
            txtFile.Dispose
            txtFile = Nothing
        End Try

    End Function

The final case shows a very common procedure, to add data from Excel files into a MS SQL Server database. If using the MS SQL Server Management Studio (MSSMS) we can “skin the cat” in many ways with T-SQL.

But it’s not always possible to use the Studio. In addition, many times we must also manage large data sets (>100.000 records) which can be time consuming. By using EasyXLS we make sure that MS Excel and MS SQL Server have a long and happy marriage:

    Private Function Import_MSSQL_Dt() As Boolean
        
        Dim XLWBook As ExcelDocument = New ExcelDocument()
        Dim DocumentsPath As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

        Const stCon As String = "Data Source=WIN-77AOABVR6DL\SQLEXPRESS;Initial Catalog=production;Integrated Security=True"
        
        'Initialize the MS SQL Server database.
        Dim sqlCon As New SqlConnection(stCon)
        Dim sqlbCopy As New SqlBulkCopy(sqlCon)
        
        Try
            'Create a new data set to grab all the records in the closed Excel workbook.
            Dim dsData As DataSet = XLWBook.easy_ReadXLSBSheet_AsDataSet(DocumentsPath + "\Excel Files\DataSQL.xlsb", "Stats")
            Dim dtData As DataTable = dsData.Tables(0)
            
            'The first record stores the field names that we don't want to have inserted in the MS SQL Server database.            
            dtData.Rows.RemoveAt(0)

            'Open up for data transfer.
            sqlCon.Open
            
            'The dataset I use here includes > 400 000 records which are inserted in the table by these few lines!
            'It's amazing and very fast!
            With sqlbCopy
                .DestinationTableName="dbo.prod_1"
                .WriteToServer(dtData)
            End With

            'Job done so report it.
            Import_MSSQL_Dt = True

        Catch ex As Exception
            MessageBox.Show(ex.Message, p_stTitle, MessageBoxButtons.OK)
            Import_MSSQL_Dt = False

        Finally
            'Cleaning up and restore memory.
            sqlCon.Close
            sqlCon = Nothing
            XLWBook.Dispose
            XLWBook = Nothing
        End Try

    End Function

The above last case gives an indication that EasyXLS has lot of horse power “under the hood”, when the file size increase. It’s difficult to detect any weakness of it. It’s fast, reliable and feature rich. In addition, the documentation is great and the vendor provides us with a good guide, many examples together with tutorials and white papers. I have been using EasyXLS for some weeks now, in all my solutions where this kind of tool can be used with. So far I’m very pleased with the outcome. The issues I have dealt with are more related to the developer (that’s me) then to EasyXLS.

Manage MS Excel files by importing data from workbooks is only one side of EasyXLS. In upcoming blog entries I will discuss the other side, exporting data to Excel files. It will be a pleasure 🙂

To come to EasyXLS’ start page, click on the picture below:

By using EasyXLS we have come a long way from using MS Excel in a Server Environment. It’s really easy to use!

All the best until next time,
Dennis

Edit Note:
When it comes to reading text files the files can use any string values as delimiters. That means we can read all kind of formatted text files as long as we know the string values in use as delimiters!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.