How to Write to Excel File in VB.NET

Microsoft Office Excel is a powerful tool for managing and analyzing data. However, automating data input and analysis through code can streamline processes and save time. This article discusses how to programmatically write to an Excel file and create an Excel file using the programming language VB.NET.

1. IronXL

IronXL is a powerful and easy-to-use library for working with Excel files in VB.NET applications. With IronXL, developers can easily read, write, and manipulate Excel files without the need for Microsoft Excel to be installed on the server or client machines. IronXL is designed with VB.NET developers in mind, providing a simple and intuitive API that allows them to work with Excel files in a familiar programming environment. The library supports a wide range of Excel file formats, including XLS, XLSX, CSV, and TSV. It also offers advanced features like formatting, charting, and pivot tables, making it a versatile tool for handling Excel data in VB.NET applications.

2. Prerequisites

Before writing code for IronXL in VB.NET, some prerequisites need to be in place:

1. You must have Visual Studio 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. Make sure that the .NET Framework is installed on your system. IronXL requires .NET Framework 4.0 or later to function correctly.
3. To use IronXL in your VB.NET project, you need to install the IronXL library. This can be done by adding the IronXL NuGet package to your project through the NuGet Package Manager within Visual Studio.

By ensuring that these prerequisites are met, you will be able to successfully write code using IronXL in VB.NET.

3. Create a VB.NET Project in Visual Studio

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

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 for simplicity.

   Create a new Console Application in Visual Studio

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

   Configure the new project

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

   Select the latest .NET Core version for the project

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 simplest 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. Once the installation is complete, you can start using the IronXL library in your project.

The below screenshot shows how to open the NuGet Package Manager in Visual Studio.

Navigate to NuGet Package Manager

IronXL in search results:

Search for 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 command 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.

Install the IronXL package in the Package Manager Console tab

5. Write Data to Excel Workbook using IronXL

IronXL is a powerful library that enables users to read, create, and manipulate Excel documents using VB.NET.

With IronXL, you can easily create new Excel files or access an existing one and write data to it. This functionality can be extremely useful for a variety of tasks, such as generating reports, exporting data, or creating complex Excel spreadsheets.

In this context, a simple demonstration of how to create a new Excel file and write data to it, as well as how to open an existing Excel file and add data to it using IronXL in VB.NET.

5.1. Create an Excel File and Write Data in Excel

You can easily create a new Excel spreadsheet and write into it using IronXL for VB.NET. With IronXL, you can create an Excel Workbook (Excel spreadsheet) that contains multiple Excel worksheets.

In the following code snippet, we create a console-based application that creates a new Excel Excel sheet and writes "Hello World" in it.

Imports IronXL
Imports System
Imports System.Data

Module Program
    Sub Main(args As String())
        ' Create a new workbook
        Dim workBook As WorkBook = WorkBook.Create()
        ' Create a new sheet within the workbook
        Dim workSheet As WorkSheet = workBook.CreateWorkSheet("new_sheet")
        ' Write data to cells in the worksheet
        workSheet("A1").Value = "Hello World"
        workSheet("A1").Style.WrapText = True
        workSheet("A2").Value = "Created with IronXL"
        workSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double
        ' Save the workbook as an Excel file
        workBook.SaveAs("sample.xlsx")
    End Sub
End Module

Imports IronXL
Imports System
Imports System.Data

Module Program
    Sub Main(args As String())
        ' Create a new workbook
        Dim workBook As WorkBook = WorkBook.Create()
        ' Create a new sheet within the workbook
        Dim workSheet As WorkSheet = workBook.CreateWorkSheet("new_sheet")
        ' Write data to cells in the worksheet
        workSheet("A1").Value = "Hello World"
        workSheet("A1").Style.WrapText = True
        workSheet("A2").Value = "Created with IronXL"
        workSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double
        ' Save the workbook as an Excel file
        workBook.SaveAs("sample.xlsx")
    End Sub
End Module

The Excel file created from IronXL

5.2. Open an Existing Excel File and Write Data to Excel Worksheets

If you need to modify an existing Excel file, you can use IronXL to open and write data to an existing Excel worksheet. This can be useful if you want to update an existing Excel report, spreadsheet, or database. With IronXL, you can easily read, write, and modify Excel files in VB.NET.

The following code snippet focuses on opening an existing Excel file and writing data to it.

Imports IronXL
Imports System
Imports System.Data

Module Program
    Sub Main(args As String())
        ' Load an existing workbook
        Dim workBook As WorkBook = WorkBook.Load("multiple.xlsx")
        ' Access the first worksheet in the workbook
        Dim firstSheet As WorkSheet = workBook.DefaultWorkSheet
        ' Write new data to specific cells
        firstSheet("A8").Value = "Hello Developers"
        firstSheet("A8").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double
        firstSheet("A9").Value = "This is how you can write to an existing Excel file"
        firstSheet("A9").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double
        ' Save changes to the workbook
        workBook.SaveAs("sample.xlsx")
    End Sub
End Module

Imports IronXL
Imports System
Imports System.Data

Module Program
    Sub Main(args As String())
        ' Load an existing workbook
        Dim workBook As WorkBook = WorkBook.Load("multiple.xlsx")
        ' Access the first worksheet in the workbook
        Dim firstSheet As WorkSheet = workBook.DefaultWorkSheet
        ' Write new data to specific cells
        firstSheet("A8").Value = "Hello Developers"
        firstSheet("A8").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double
        firstSheet("A9").Value = "This is how you can write to an existing Excel file"
        firstSheet("A9").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double
        ' Save changes to the workbook
        workBook.SaveAs("sample.xlsx")
    End Sub
End Module

Modify the existing Excel file created from IronXL

6. Conclusion

The above code examples show how you can create an Excel file and save it in a different Excel file format. Writing data to Excel files programmatically using VB.NET can significantly streamline data processing and automate repetitive tasks. IronXL is a powerful and easy-to-use library for working with Excel files in VB.NET, providing developers with a simple and intuitive API to work with Excel files. By following the prerequisites, creating a VB.NET project in Visual Studio, and installing IronXL, users can create and modify Excel files using code. With IronXL, users can easily create new Excel files, open existing ones, and write data to them, making it a versatile tool for handling Excel data in VB.NET applications.

For further information on how to export data from an Excel document and write to an Excel file using IronXL, please refer to this link on how to write data into an Excel file. For code examples, please visit the following example to create an Excel file and its objects.

IronXL comes with a licensing fee that starts from $749, depending on the selected license plan. However, IronXL also offers a free trial period, allowing potential customers to test the software and its features free of cost, before committing to a purchase. During the free trial period, users can access all the features and functionalities of the software and determine if it meets their specific needs and requirements.

Additionally, users can take advantage of Iron Suite, which is a collection of 5 top-quality VB.NET Core libraries comprising IronXL, IronPDF, and others.