EasyXLS™: Export data to MS Excel Workbooks

By | 2017-04-27

Introduction
In my second article about EasyXLS focus is on how to export data to MS Excel Workbooks by using the excellent .NET component. Again it strikes me how easy it is to work with EasyXLS’s Excel object model. Also, the few code lines it requires to create powerful solutions. It’s difficult to find new approaches in view of how well documented EasyXLS actually is. My approach is therefore to provide wide and well commented solutions.

Before looking on the cases, here are the imported classes on top of the code module:

Imports System.Data
Imports System.Data.OleDb
Imports EasyXLS
Imports EasyXLS.Constants
Imports EasyXLS.Util

Export Data to MS Excel Workbooks
The first case shows how to export a data table to a workbook:

Private Function Export_Table() As Boolean

        'Create a workbook with one worksheet.
        Dim XLWBook As ExcelDocument = New ExcelDocument()

        'Get the path to My Documents.
        Dim DocumentsPath As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

        'SQL query to acquire wanted data from the Access database.
        Const stSQL As String = "SELECT CustomerID, Freight, ShipCountry FROM Orders ORDER BY ShipCountry;"

        'Path to the Access database.
        Dim stPath As String = AppDomain.CurrentDomain.BaseDirectory.ToString()

        'The connection string for the Access database.
        Dim stCon As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}\db\NorthWind.accdb;Persist Security Info=False;", stPath)

        'Shorten the code for using a built-in format.
        Dim XLFormat As New ExcelAutoFormat
        XLFormat.InitAs(Constants.Styles.AUTOFORMAT_EASYXLS1)

        Try
            'Setup connection, retrieve the data, add data to a datatable.
            Dim oleCon As OleDbConnection = New OleDbConnection(stCon)
            Dim oleAdtp As OleDbDataAdapter = New OleDbDataAdapter(stSQL, oleCon)
            Dim dsData As New DataSet

            oleAdtp.Fill(dsData)

            'Create the workbook, add the DataTable and apply the built-in format.
            XLWBook.easy_WriteXLSBFile_FromDataSet(DocumentsPath + "\Excel Reports\DataTable.xlsb", dsData, XLFormat, "Data Table")

            'Add the DataTable as data source to the Grid.
            Me.gcMain.DataSource = dsData.Tables(0)

            'Free up memory.
            oleAdtp.Dispose
            oleCon.Close
            oleCon.Dispose

            'Work well done!
            Export_Table = True

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, p_stTitle)
            Export_Table = False
        Finally
            'Free up memory.
            XLWBook.Dispose
            XLWbook = Nothing
        End Try

    End Function

As we can see from the case, pretty straight forward and do not require any knowledge of rocket science.

Export Data Using a Simple List
In the next case I show how to work with Lists and how to export a list to a workbook. The key here is to understand that each row is a list of itself. It needs some practice to fully understand it. However, from a practical point of view, using data tables and data sets are the preferrable methods.

Private Function Export_Simple_List() As Boolean

        'Create a workbook with one worksheet.
        Dim XLWbook As ExcelDocument = New ExcelDocument()

        'Get the path to My Documents.
        Dim DocumentsPath As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

        'Shorten the code for using this built-in format.
        Dim XLFormat As New ExcelAutoFormat
        XLFormat.InitAs(Constants.Styles.AUTOFORMAT_EASYXLS2)

        Try
            'Since every row itself is a list we must use the same number of
            'lists as the number of rows.
            'To collect the lists we must also create a main list.
            Dim MainList As List = New List
            Dim HeadList As List = New List
            Dim SubList0 As List = New List
            Dim SubList1 As List = New List
            Dim SubList2 As List = New List
            Dim SubList3 As List = New List

            'Caption row.               
            With HeadList
                .Add("Name")
                .Add("Dept")
                .Add("Budget")
            End With

            'First record.
            With SubList0
                .Add("Dennis")
                .Add("HQ")
                .Add("100")
            End With

            'Second record.
            With SubList1
                .Add("Bert")
                .Add("PR")
                .Add("75")
            End With

            '...
            With SubList2
                .Add("Barbro")
                .Add("PD")
                .Add("150")
            End With

            With SubList3
                .Add("Kent")
                .Add("SD")
                .Add("125")
            End With

            'Here we collect the lists to one list.
            With MainList
                .Add(HeadList)
                .Add(SubList0)
                .Add(SubList1)
                .Add(SubList2)
                .Add(SubList3)
            End With

            'Write the data to workbook and save the workbook.    
            XLWbook.easy_WriteXLSBFile_FromList(DocumentsPath + "\Excel Files\ReportDataList.xlsb", MainList, XLFormat, "List Table")

            'Just to show the data that have been added to the created workbook.
            Dim dsData As DataSet = XLWbook.easy_ReadXLSBSheet_AsDataSet(DocumentsPath + "\Excel Files\ReportDataList.xlsb", "List Table")

            '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() = CType(dtData.Rows(0).ItemArray, Object())

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

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

            'Add the field names to the header's cells.
            For inColumn As Integer = 0 To dtData.Columns.Count - 1
                Me.GridView1.Columns(inColumn).Caption = CType(objHeader(inColumn), String)
            Next

            'Work well done!           
            Export_Simple_List = True

        Catch ex As Exception
            MessageBox.Show(ex.Message, p_stTitle, MessageBoxButtons.OK)
            Export_Simple_List = False
        Finally
            'Free up memory.
            XLWbook.Dispose
            XLWbook = Nothing
        End Try

    End Function

Export Data To a Text File
In the third case I show how to export to a text file. Again, the code is straight forward and there are no surprises. What may be notable in this case is that we actually can format cells to receive various data types, like String and Numeric:

Private Function Export_To_TextFile() As Boolean

        'Create a workbook with one worksheet.
        Dim XLWbook As ExcelDocument = New ExcelDocument(2)

        'Get the path to My Documents.
        Dim DocumentsPath As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

        'Set the worksheets' names.
        XLWbook.easy_getSheetAt(0).setSheetName("DataTable")
            
        'Variable for the first worksheet.
        Dim xlWSheetData As ExcelWorksheet = CType(XLWbook.easy_getSheet("DataTable"), ExcelWorksheet)

        'In order to get access to the worksheet's cells we need to create
        'a variable that represent an Excel Table.
        Dim XLWSheetDataTable As ExcelTable = xlWSheetData.easy_getExcelTable()

        'Shorten the code for using a built-in format.
        Dim XLFormat As New ExcelAutoFormat
        XLFormat.InitAs(Constants.Styles.AUTOFORMAT_EASYXLS1)

        'SQL query to acquire wanted data from the Access database.   
        Const stSQL As String = "SELECT CustomerID, Freight, ShipCountry FROM Orders ORDER BY ShipCountry;"

        'Path to the Access database.    
        Dim stPath As String = AppDomain.CurrentDomain.BaseDirectory.ToString()

        'The connection string for the Access database.
        Dim stCon As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}\db\NorthWind.accdb;Persist Security Info=False;", stPath)


        Try
            'Setup connection, retrieve the data, add data to a datatable.
            Dim oleCon As OleDbConnection = New OleDbConnection(stCon)
            Dim oleAdtp As OleDbDataAdapter = New OleDbDataAdapter(stSQL, oleCon)
            Dim dsData As New DataSet
            Dim dtTable As New DataTable

            oleAdtp.Fill(dsData)
            dtTable = dsData.Tables(0)

            'Write the data from the datatable to the worksheet.
            'Note: Without the Columns names.
            Dim iRow As Integer = 0

            For Each Row As DataRow In dtTable.Rows
                With XLWSheetDataTable.easy_getCell(iRow,0)
                    .setValue(CType(Row.Item("CustomerID"), String))
                    .setDataType(DataType.STRING)
                End With
                With XLWSheetDataTable.easy_getCell(iRow,1)
                    .setValue(CType(Row.Item("Freight"), String))
                    .setDataType(DataType.NUMERIC)
                End With
                With XLWSheetDataTable.easy_getCell(iRow,2)
                    .setValue(CType(Row.Item("ShipCountry"), String))
                    .setDataType(DataType.STRING)
                End With
                iRow = iRow + 1
            Next Row
              
            'Create the text file, add the worksheet to the text file.
            XLWbook.easy_WriteTXTFile(DocumentsPath + "\Text Files\TextReport.txt", xlWSheetData.getSheetName.ToString() , vbNewLine, vbTab)
            
            'Free up memory.
            oleAdtp.Dispose
            oleCon.Close
            oleCon.Dispose

            'Work well done!
            Export_To_TextFile = True

        Catch ex As Exception
            MessageBox.Show(ex.Message, p_stTitle, MessageBoxButtons.OK)
            Export_To_TextFile = False
        Finally
            'Free up memory.
            xlWSheetData.Dispose
            XLWbook.Dispose
            XLWSheetDataTable = Nothing
            xlWSheetData = Nothing
            XLWbook = Nothing
        End Try

    End Function

Convert Text File To MS Excel Workbook
The final case shows how easy it is to convert a text file to a MS Excel workbook. It’s extraordinary the fact that it requires only a few code lines. In my upcoming third article about EasyXLS focus will be set on how we can convert various file formats to various MS Excel file formats and vice versa. The following cases speaks for itself:

Function Convert_TXT_To_XLSB() As Boolean

        
        Try        
            'Create a workbook with one worksheet.
            Dim XLWbook As ExcelDocument = New ExcelDocument()

            'Shorten the code for using a built-in format.
            Dim XLFormat As New ExcelAutoFormat
            XLFormat.InitAs(Constants.Styles.AUTOFORMAT_3D_EFFECTS1)

            'Get the path to My Documents.
            Dim DocumentsPath As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

            'Set up source file.
            Dim SourceFile As String = DocumentsPath + "\Text Files\TextReport.txt"

            'Grab the data by populating a data set.
            Dim dsData As DataSet = XLWbook.easy_ReadTXTFile_AsDataSet(SourceFile, vbNewLine,vbTab)

            'Set up the target file.
            Dim TargetFile As String = DocumentsPath + "\Excel Files\Txt_Import.xlsb"

            'Create the workbook, add data to the worksheet, format the table and name the worksheet.
            XLWbook.easy_WriteXLSBFile_FromDataSet(TargetFile, dsData, XLFormat, "Import")

            'Clean up and free up memory.
            XLWbook.Dispose()
            XLWbook = Nothing
            
            'Work done!
            Convert_TXT_To_XLSB = True

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

    End Function

 

For more information please see:

Happy coding!
Dennis

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.