USING IRONXL

How to Export Datatable to Excel in VB.NET

DataTables handle large data sets with sorting, filtering, and search options. They can be exported to Excel for visual presentations, like charts and graphs, for richer data insight. HTML tables can also be converted to Excel workbooks.

1. IronXL

IronXL is a powerful library designed to facilitate the creation, reading, and manipulation of Excel files in VB.NET applications. With IronXL, developers can quickly and easily incorporate Excel worksheet functionality into their applications without requiring any specialized knowledge or expertise in Excel sheets. The library provides a range of features for working with Excel files, including the ability to read and write data, format cells, create charts and graphs, and much more. IronXL supports both XLS and XLSX file formats and can be used with a variety of Excel versions. Using IronXL you can also add column headers in .NET code.

2. Prerequisites

To successfully write code using IronXL in VB.NET, several prerequisites must be in place:

  1. Visual Studio must be installed on your system to create a VB.NET project. If you don't have Visual Studio, you can download it from the Microsoft website.
  2. The .NET Framework must be installed on your system, and IronXL requires version 4.0 or later to function correctly.
  3. You need to install the IronXL library to use it in your VB.NET project.

Ensuring that these prerequisites are met will enable you to write code successfully using IronXL in VB.NET.

3. Create a VB.NET Project in Visual Studio

To create VB.NET in Visual Studio, follow these steps to create a VB.NET project in Visual Studio:

  1. Open Visual Studio.
  2. Click on "Create a new project" on the start page, or go to "File" > "New" > "Project".
  3. In the "New Project" dialog box, select "Visual Basic" in the left pane.
  4. Choose the type of project you want to create, such as "Windows Forms App" or "Console App". This tutorial will use a Console App.

    How to Export Datatable to Excel in VB.NET, Figure 1: Create a new project in Visual Studio Create a new project in Visual Studio

  5. Enter a name for your project and choose a location to save it.

    How to Export Datatable to Excel in VB.NET, Figure 2: Configure your project Configure your project

  6. Then select the .NET Core version and click on "Create".

    How to Export Datatable to Excel in VB.NET, Figure 3: .NET Framework selection .NET Framework selection

  7. Visual Studio will create a new project with some default files and folders.

You are now ready to start writing your VB.NET code in Visual Studio.

4. Install IronXL

The IronXL Library can be downloaded and installed in different ways:

  • Using Visual Studio NuGet packages
  • Using the Visual Studio Command Line.

4.1 Using Visual Studio

To install the IronXL library, the first approach is to use the NuGet Package Manager in Visual Studio. Simply open the NuGet Package Manager and search for IronXL in the Browse tab. Once you have located IronXL in the search results, select it and proceed with the installation.

The below screenshot shows an example of how to navigate to the NuGet Package Manager in Visual Studio.

How to Export Datatable to Excel in VB.NET, Figure 4: Navigate to the NuGet Package Manager Navigate to the NuGet Package Manager

IronXL in search results:

How to Export Datatable to Excel in VB.NET, Figure 5: Search and install the IronXL package in NuGet Package Manager UI Search and install the IronXL package in NuGet Package Manager UI

4.2 Using the Visual Studio Command-Line

Many people prefer to install packages using the command line interface. To install IronXL using the command line, follow these steps:

  • In Visual Studio, go to Tools > NuGet Package Manager > Package Manager Console.
  • Enter the following line in the Package Manager Console tab: Install-Package IronXL.Excel

Now the package will download/install to the current project and be ready to use.

How to Export Datatable to Excel in VB.NET, Figure 6: Install IronXL package in Package Manager Console Install IronXL package in Package Manager Console

5. Export DataTable to Excel File using IronXL

The process of exporting a DataTable to an Excel file is a common task in data processing, and IronXL makes it easy to accomplish this in VB.NET. First, you need to create a new DataTable with the data you want to export. Then, you can use IronXL's Workbook object to create a new Excel workbook and add a worksheet to it. After that, you can populate the worksheet with the DataTable columns' data using IronXL's Range object.

5.1. DataTable To XLS files

Exporting a DataTable object to an XLS file allows you to save the table data in a format that is easily readable and shareable with others while preserving the structure and formatting of the original data.

The following code snippet shows how you can create DataTable rows and convert them into an Excel file.

Imports IronXL
Imports System
Imports System.Data

Module Program
    Sub Main(args As String())
        ' Create a new DataTable
        Dim table As New DataTable()

        ' Add a column to the DataTable
        table.Columns.Add("DataSet_Daily_Household", GetType(String))

        ' Add rows to the DataTable
        table.Rows.Add("Glass")
        table.Rows.Add("Plates")
        table.Rows.Add("Match Sticks")
        table.Rows.Add("Leather")
        table.Rows.Add("Soap")
        table.Rows.Add("Brush")
        table.Rows.Add("Comb")
        table.Rows.Add("Wires")
        table.Rows.Add("Pins")
        table.Rows.Add("And Many More")

        ' Create a new Excel workbook and set the format to XLS
        Dim dt As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)

        ' Select the default worksheet
        Dim dc As WorkSheet = dt.DefaultWorkSheet

        ' Iterate through DataTable rows and populate the Excel worksheet
        Dim rowCount As Integer = 1
        For Each row As DataRow In table.Rows
            dc("A" & (rowCount)).Value = row(0).ToString()
            rowCount += 1
        Next row

        ' Save the workbook as "sample.xls"
        dt.SaveAs("sample.xls")
    End Sub
End Module
Imports IronXL
Imports System
Imports System.Data

Module Program
    Sub Main(args As String())
        ' Create a new DataTable
        Dim table As New DataTable()

        ' Add a column to the DataTable
        table.Columns.Add("DataSet_Daily_Household", GetType(String))

        ' Add rows to the DataTable
        table.Rows.Add("Glass")
        table.Rows.Add("Plates")
        table.Rows.Add("Match Sticks")
        table.Rows.Add("Leather")
        table.Rows.Add("Soap")
        table.Rows.Add("Brush")
        table.Rows.Add("Comb")
        table.Rows.Add("Wires")
        table.Rows.Add("Pins")
        table.Rows.Add("And Many More")

        ' Create a new Excel workbook and set the format to XLS
        Dim dt As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)

        ' Select the default worksheet
        Dim dc As WorkSheet = dt.DefaultWorkSheet

        ' Iterate through DataTable rows and populate the Excel worksheet
        Dim rowCount As Integer = 1
        For Each row As DataRow In table.Rows
            dc("A" & (rowCount)).Value = row(0).ToString()
            rowCount += 1
        Next row

        ' Save the workbook as "sample.xls"
        dt.SaveAs("sample.xls")
    End Sub
End Module
VB .NET

How to Export Datatable to Excel in VB.NET, Figure 7: The Excel file generated from the code snippet above The Excel file generated from the code snippet above

5.2. DataTable To XLSX files

Similarly, for converting DataTable rows and columns to XLSX file format, just change one line of code in the above example.

Replace this line of code:

dt.SaveAs("sample.xls")
dt.SaveAs("sample.xls")
VB .NET

With this:

dt.SaveAs("sample.xlsx")
dt.SaveAs("sample.xlsx")
VB .NET

5.3. DataTable To CSV file

You can also convert DataTable rows and columns to CSV file format by changing one line of code in the above example from SaveAs to SaveAsCsv.

Replace this line of code:

dt.SaveAs("sample.xls")
dt.SaveAs("sample.xls")
VB .NET

With this:

dt.SaveAsCsv("sample.csv")
dt.SaveAsCsv("sample.csv")
VB .NET

How to Export Datatable to Excel in VB.NET, Figure 8: The CSV file generated from the code above The CSV file generated from the code above

6. Conclusion

Exporting a DataTable to an Excel file in VB.NET is a common requirement in data processing and analysis. The IronXL library provides developers with a powerful tool to facilitate the creation, reading, and manipulation of Excel files in VB.NET applications, including the ability to export DataTables to Excel files. By exporting DataTables to Excel files, users can leverage the powerful features provided by Excel, such as charts, graphs, and pivot tables, to present data in a visually appealing and organized manner. To use IronXL, you need to ensure that you have met the prerequisites, such as having Visual Studio and the .NET Framework installed and installing the IronXL library. Finally, this tutorial demonstrated how to export DataTables to Excel files in both XLS and CSV file formats using IronXL.

To learn more about exporting DataTables to Excel files using IronXL, please visit this provided tutorial.

Furthermore, users can utilize Iron Suite, a bundle of five high-quality ASP.NET Core libraries that include IronXL, IronPDF, and other components.

Frequently Asked Questions

What is IronXL?

IronXL is a powerful library designed to facilitate the creation, reading, and manipulation of Excel files in VB.NET applications. It allows developers to incorporate Excel functionality without specialized expertise in Excel.

What prerequisites are needed to use IronXL in VB.NET?

To use IronXL in VB.NET, you must have Visual Studio installed, the .NET Framework version 4.0 or later, and the IronXL library installed in your project.

How can I create a VB.NET project in Visual Studio?

To create a VB.NET project in Visual Studio, open the software, click 'Create a new project', select 'Visual Basic', choose a project type, name your project, and select the .NET Core version.

How do I install the IronXL library?

IronXL can be installed using the NuGet Package Manager in Visual Studio or via the Visual Studio Command Line by entering 'Install-Package IronXL.Excel' in the Package Manager Console.

How can I export a DataTable to an Excel file using IronXL?

To export a DataTable to an Excel file using IronXL, create a DataTable, use IronXL's Workbook object to create an Excel workbook, add a worksheet, and populate it with DataTable data using IronXL's Range object.

Can IronXL export DataTables to different file formats?

Yes, IronXL allows exporting DataTables to XLS, XLSX, and CSV file formats by modifying the SaveAs method in the code.

Does IronXL support different Excel file formats?

IronXL supports both XLS and XLSX file formats, allowing developers to work with a variety of Excel versions.

What features does IronXL provide for Excel file manipulation?

IronXL provides features such as reading and writing data, formatting cells, creating charts and graphs, and converting between spreadsheet file types.

Where can I find more tutorials on using IronXL?

For more tutorials on using IronXL, including how to export DataTables to Excel files, visit the provided IronXL tutorials on the official website.

What is the Iron Suite?

The Iron Suite is a bundle of five high-quality ASP.NET Core libraries, including IronXL and IronPDF, designed to enhance application features and functionalities.

Regan Pun
Software Engineer
Regan graduated from the University of Reading, with a BA in Electronic Engineering. Before joining Iron Software, his previous job roles had him laser-focused on single tasks; and what he most enjoys at Iron Software is the spectrum of work he gets to undertake, whether it’s adding value to sales, technical support, product development or marketing. He enjoys understanding the way developers are using the Iron Software library, and using that knowledge to continually improve documentation and develop the products.
< PREVIOUS
How to Export to Excel in ASP.NET Core
NEXT >
C# Fastest way to Export DataTable to Excel