How to Write to an Excel File in VB.NET (The Easy Way)

Using Office Interop has traditionally been the defacto option for automating Microsoft Excel tasks, but it comes with significant drawbacks--performance issues, server-side complications, and the requirement that Microsoft Excel be installed on the machine.

This guide demonstrates a modern, more efficient approach to how you can create and write to an Excel file in VB.NET. We will use IronXL for .NET, a library built specifically to help developers create, read, and edit Excel spreadsheets without relying on Office Interop. You'll see how to handle common tasks like creating new files, writing data from collections, and modifying existing documents with just a few lines of intuitive code.

How Do I Get Started with Excel Automation in VB.NET?

To begin writing to Excel files in your VB.NET project, you first need to add the IronXL library. The process is straightforward and can be done directly within Visual Studio.

First, ensure your development environment is ready:

• Visual Studio 2022 or later.
• An active project targeting .NET Framework 4.6.2 or higher, or .NET Core/.NET 5+. IronXL is compatible with all modern .NET versions, up to and including .NET 9 and 10.

Next, install the IronXL NuGet package:

1. Open the NuGet Package Manager in Visual Studio.
2. Search for the package IronXL.Excel.
3. Click "Install".

Alternatively, you can install it via the Package Manager Console with the following command:

Install-Package IronXL.Excel

Navigating to the NuGet Package Manager in Visual Studio's menu to find the right package for VB.NET Excel integration.

Once installed, you're ready to start writing code. Just add Imports IronXL to the top of your VB.NET file to bring the library's classes into scope.

Searching for the IronXL package to install the library needed to write data to an Excel file using VB.NET.

How Can I Create a New Excel File and Write Data to It?

Creating a new Excel file from scratch is a fundamental task. With IronXL, you can generate a workbook, add worksheets, and populate cells with data from any source, such as a collection of objects. This is perfect for tasks like exporting application data or generating daily reports.

Let's look at a practical example. Imagine we have a list of product data that we want to export to an Excel spreadsheet.

Write a Collection to a New Excel Worksheet

The following code demonstrates how to take a List(Of Product) and write its contents into a newly created Excel file.

Imports IronXL
Imports System.Collections.Generic
Imports System.Linq

Public Class Product
    Public Property ID As Integer
    Public Property Name As String
    Public Property Price As Decimal
End Class

Module Program
    Sub Main(args As String())
        ' 1. Prepare a list of data to write to Excel.
        Dim products As New List(Of Product) From {
            New Product With {.ID = 1, .Name = "Laptop", .Price = 1200.50D},
            New Product With {.ID = 2, .Name = "Keyboard", .Price = 75.00D},
            New Product With {.ID = 3, .Name = "Mouse", .Price = 25.99D}
        }

        ' 2. Create a new Excel workbook and select the default worksheet.
        Dim workbook As WorkBook = WorkBook.Create()
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' 3. Write headers for our data.
        sheet("A1").Value = "Product ID"
        sheet("B1").Value = "Product Name"
        sheet("C1").Value = "Price"

        ' Style the header row.
        Dim headerRange = sheet.GetRange("A1:C1")
        headerRange.Style.Font.Bold = True
        headerRange.Style.SetBackgroundColor("#D3D3D3") ' Light Gray

        ' 4. Iterate through the product list and write data to subsequent rows.
        For i As Integer = 0 To products.Count - 1
            Dim product = products(i)
            Dim row = i + 2 ' Start from the second row
            sheet("A" & row).Value = product.ID
            sheet("B" & row).Value = product.Name
            sheet("C" & row).Value = product.Price
        Next

        ' 5. Apply currency formatting to the price column.
        Dim priceColumn = sheet.GetRange("C2:C" & products.Count + 1)
        priceColumn.Style.Format = "$#,##0.00"

        ' 6. Auto-size columns for better readability.
        sheet.Columns.AutoFit()

        ' 7. Save the newly created and populated workbook to a file.
        workbook.SaveAs("ProductReport.xlsx")

    End Sub
End Module

Imports IronXL
Imports System.Collections.Generic
Imports System.Linq

Public Class Product
    Public Property ID As Integer
    Public Property Name As String
    Public Property Price As Decimal
End Class

Module Program
    Sub Main(args As String())
        ' 1. Prepare a list of data to write to Excel.
        Dim products As New List(Of Product) From {
            New Product With {.ID = 1, .Name = "Laptop", .Price = 1200.50D},
            New Product With {.ID = 2, .Name = "Keyboard", .Price = 75.00D},
            New Product With {.ID = 3, .Name = "Mouse", .Price = 25.99D}
        }

        ' 2. Create a new Excel workbook and select the default worksheet.
        Dim workbook As WorkBook = WorkBook.Create()
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' 3. Write headers for our data.
        sheet("A1").Value = "Product ID"
        sheet("B1").Value = "Product Name"
        sheet("C1").Value = "Price"

        ' Style the header row.
        Dim headerRange = sheet.GetRange("A1:C1")
        headerRange.Style.Font.Bold = True
        headerRange.Style.SetBackgroundColor("#D3D3D3") ' Light Gray

        ' 4. Iterate through the product list and write data to subsequent rows.
        For i As Integer = 0 To products.Count - 1
            Dim product = products(i)
            Dim row = i + 2 ' Start from the second row
            sheet("A" & row).Value = product.ID
            sheet("B" & row).Value = product.Name
            sheet("C" & row).Value = product.Price
        Next

        ' 5. Apply currency formatting to the price column.
        Dim priceColumn = sheet.GetRange("C2:C" & products.Count + 1)
        priceColumn.Style.Format = "$#,##0.00"

        ' 6. Auto-size columns for better readability.
        sheet.Columns.AutoFit()

        ' 7. Save the newly created and populated workbook to a file.
        workbook.SaveAs("ProductReport.xlsx")

    End Sub
End Module

How It Works

This code example showcases the simplicity and power of the IronXL API for automating Excel input.

1. Data Preparation: We start with a simple Product class and a List(Of Product) which represents the data we want to export. This simulates pulling data from a database or another business logic layer.
2. Workbook and Worksheet Creation: WorkBook.Create() generates a new, empty Excel workbook in memory. Every new workbook automatically contains one worksheet, which we can access via the DefaultWorkSheet property. For more complex documents, you can add more worksheets using the CreateWorkSheet method.
3. Writing Data: We access cells using familiar A1-style notation (e.g., sheet("A1")). The .Value property is used to both set and get the content of a cell. IronXL automatically handles data type conversions for numbers, strings, and dates.
4. Styling and Formatting: IronXL provides a comprehensive styling API. In the example, we select a Range of cells and apply bold formatting and a background color to our header. We also apply a currency format to the price column using the Style.Format property, a feature essential for creating professional reports. For more styling options, explore the API documentation for the Style object.
5. Saving the File: Finally, workbook.SaveAs("ProductReport.xlsx") writes the in-memory workbook to the file system. IronXL supports various formats, including .xlsx, .xls, .csv, and .tsv, giving you flexibility in how you export your data.

How Can I Write Data to an Existing Excel File?

Often, you don't need to create a new file but rather add data to an existing one. This is common for logging, appending records to a dataset, or updating a report. IronXL makes this process just as easy as creating a new file.

The following example demonstrates how to open an existing spreadsheet, find the last used row, and append new data.

Imports IronXL
Imports System.Collections.Generic
Imports System.Linq

' (Assuming the Product class from the previous example is available)

Module Program
    Sub Main(args As String())
        ' Ensure the file from our previous example exists.
        If Not System.IO.File.Exists("ProductReport.xlsx") Then
            Console.WriteLine("Please run the first example to create ProductReport.xlsx")
            Return
        End If

        ' 1. Load the existing workbook from the file system.
        Dim workbook As WorkBook = WorkBook.Load("ProductReport.xlsx")
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' 2. Find the first empty row to append new data.
        ' The LastRowUsed property gives us the last row with data.
        Dim lastRow = sheet.Info.LastRowUsed
        Dim newRowIndex = lastRow.RowNumber + 1

        ' 3. Define the new data to be added.
        Dim newProducts As New List(Of Product) From {
            New Product With {.ID = 4, .Name = "Monitor", .Price = 350.00D},
            New Product With {.ID = 5, .Name = "Webcam", .Price = 99.99D}
        }

        ' 4. Loop through the new data and write it to the worksheet.
        For i As Integer = 0 To newProducts.Count - 1
            Dim product = newProducts(i)
            Dim currentRow = newRowIndex + i
            sheet("A" & currentRow).Value = product.ID
            sheet("B" & currentRow).Value = product.Name
            sheet("C" & currentRow).Value = product.Price
        Next

        ' 5. Re-apply formatting and auto-fit columns to include new data.
        Dim priceColumn = sheet.GetRange("C2:C" & sheet.Info.LastRowUsed.RowNumber)
        priceColumn.Style.Format = "$#,##0.00"
        sheet.Columns.AutoFit()

        ' 6. Save the changes back to the original file.
        workbook.Save()

        ' Or save as a new file to preserve the original.
        ' workbook.SaveAs("ProductReport_Updated.xlsx")
    End Sub
End Module

Imports IronXL
Imports System.Collections.Generic
Imports System.Linq

' (Assuming the Product class from the previous example is available)

Module Program
    Sub Main(args As String())
        ' Ensure the file from our previous example exists.
        If Not System.IO.File.Exists("ProductReport.xlsx") Then
            Console.WriteLine("Please run the first example to create ProductReport.xlsx")
            Return
        End If

        ' 1. Load the existing workbook from the file system.
        Dim workbook As WorkBook = WorkBook.Load("ProductReport.xlsx")
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' 2. Find the first empty row to append new data.
        ' The LastRowUsed property gives us the last row with data.
        Dim lastRow = sheet.Info.LastRowUsed
        Dim newRowIndex = lastRow.RowNumber + 1

        ' 3. Define the new data to be added.
        Dim newProducts As New List(Of Product) From {
            New Product With {.ID = 4, .Name = "Monitor", .Price = 350.00D},
            New Product With {.ID = 5, .Name = "Webcam", .Price = 99.99D}
        }

        ' 4. Loop through the new data and write it to the worksheet.
        For i As Integer = 0 To newProducts.Count - 1
            Dim product = newProducts(i)
            Dim currentRow = newRowIndex + i
            sheet("A" & currentRow).Value = product.ID
            sheet("B" & currentRow).Value = product.Name
            sheet("C" & currentRow).Value = product.Price
        Next

        ' 5. Re-apply formatting and auto-fit columns to include new data.
        Dim priceColumn = sheet.GetRange("C2:C" & sheet.Info.LastRowUsed.RowNumber)
        priceColumn.Style.Format = "$#,##0.00"
        sheet.Columns.AutoFit()

        ' 6. Save the changes back to the original file.
        workbook.Save()

        ' Or save as a new file to preserve the original.
        ' workbook.SaveAs("ProductReport_Updated.xlsx")
    End Sub
End Module

Deeper Explanation

Modifying an existing file follows a logical load-edit-save pattern.

1. Loading the Workbook: Instead of WorkBook.Create(), we use WorkBook.Load("ProductReport.xlsx"). This opens the specified file and parses its contents into a WorkBook object, ready for manipulation.
2. Finding the Insertion Point: A key challenge in appending data is finding where to start writing. IronXL simplifies this with the sheet.Info.LastRowUsed property, which returns the last Row object containing data. We simply get its row number and add one to find the first empty row.
3. Appending Data: The process of writing the new data is identical to the previous example. We iterate through our new Product list and populate the cells in the newly identified empty rows.
4. Saving Changes: The workbook.Save() method overwrites the original file with the modified workbook from memory. If you need to preserve the original, simply use SaveAs() with a new file name, as shown in the commented-out line.

How Do I Write a DataTable to an Excel Worksheet?

For developers working with data from sources like SQL Server, a common task is to write the contents of a DataTable directly to an Excel sheet. IronXL streamlines this with a built-in method, eliminating the need to loop through rows and columns manually.

This example shows how to populate a DataTable and export it to a new Excel file in one step.

Imports IronXL
Imports System.Data

Module Program
    Sub Main(args As String())
        ' 1. Create and populate a DataTable. This often comes from a database query.
        Dim dt As New DataTable("EmployeeData")
        dt.Columns.Add("EmployeeID", GetType(Integer))
        dt.Columns.Add("FullName", GetType(String))
        dt.Columns.Add("Department", GetType(String))
        dt.Columns.Add("HireDate", GetType(Date))

        dt.Rows.Add(101, "John Smith", "Sales", New Date(2022, 5, 20))
        dt.Rows.Add(102, "Jane Doe", "Engineering", New Date(2021, 8, 15))
        dt.Rows.Add(103, "Peter Jones", "Marketing", New Date(2023, 1, 10))

        ' 2. Create a new workbook.
        Dim workbook As WorkBook = WorkBook.Create()
        Dim sheet As WorkSheet = workbook.CreateWorkSheet("Employees")

        ' 3. Write the DataTable to the worksheet starting at cell A1.
        ' The second parameter (True) indicates that column headers should be included.
        sheet.Write(dt, "A1", True)

        ' 4. Apply some styling for a more polished look.
        sheet.Columns.AutoFit()
        Dim headerRange = sheet.GetRange("A1:D1")
        headerRange.Style.Font.Bold = True
        headerRange.Style.SetBackgroundColor("#C5D9F1") ' Light Blue

        Dim dateColumn = sheet.GetRange("D2:D" & dt.Rows.Count + 1)
        dateColumn.Style.Format = "yyyy-mm-dd"

        ' 5. Save the workbook.
        workbook.SaveAs("EmployeeDatabaseExport.xlsx")
    End Sub
End Module

Imports IronXL
Imports System.Data

Module Program
    Sub Main(args As String())
        ' 1. Create and populate a DataTable. This often comes from a database query.
        Dim dt As New DataTable("EmployeeData")
        dt.Columns.Add("EmployeeID", GetType(Integer))
        dt.Columns.Add("FullName", GetType(String))
        dt.Columns.Add("Department", GetType(String))
        dt.Columns.Add("HireDate", GetType(Date))

        dt.Rows.Add(101, "John Smith", "Sales", New Date(2022, 5, 20))
        dt.Rows.Add(102, "Jane Doe", "Engineering", New Date(2021, 8, 15))
        dt.Rows.Add(103, "Peter Jones", "Marketing", New Date(2023, 1, 10))

        ' 2. Create a new workbook.
        Dim workbook As WorkBook = WorkBook.Create()
        Dim sheet As WorkSheet = workbook.CreateWorkSheet("Employees")

        ' 3. Write the DataTable to the worksheet starting at cell A1.
        ' The second parameter (True) indicates that column headers should be included.
        sheet.Write(dt, "A1", True)

        ' 4. Apply some styling for a more polished look.
        sheet.Columns.AutoFit()
        Dim headerRange = sheet.GetRange("A1:D1")
        headerRange.Style.Font.Bold = True
        headerRange.Style.SetBackgroundColor("#C5D9F1") ' Light Blue

        Dim dateColumn = sheet.GetRange("D2:D" & dt.Rows.Count + 1)
        dateColumn.Style.Format = "yyyy-mm-dd"

        ' 5. Save the workbook.
        workbook.SaveAs("EmployeeDatabaseExport.xlsx")
    End Sub
End Module

The sheet.Write() method is incredibly efficient for this purpose. It intelligently maps the DataTable structure to the worksheet, including column headers if specified. This is a powerful feature for any data-driven application and a significant time-saver compared to manual iteration. For more details on data import and export, check out our tutorial on converting between different spreadsheet formats.

What Else Can I Do When Writing Excel Files?

Writing data is just the beginning. A robust VB.NET Excel library should offer a full suite of features to automate any spreadsheet task. IronXL provides extensive capabilities, including:

• Formulas: Programmatically set and compute formulas. You can assign a formula to a cell's Value (e.g., sheet("C1").Value = "=SUM(A1:B1)") and IronXL will calculate the result.
• Charts: Create various types of charts from worksheet data to visualize information.
• Data Validation: Enforce rules on cells, such as allowing only numbers within a specific range or values from a dropdown list.
• Conditional Formatting: Apply styling to cells based on their values, helping to highlight important data points.
• Security and Protection: Protect worksheets or entire workbooks with passwords to control access and prevent modifications.

These features allow for the complete automation of complex report generation and data analysis workflows directly within your .NET applications.

Start Writing to Excel Files in VB.NET Today

As demonstrated, writing data to Excel files in VB.NET is significantly simplified by using a modern library like IronXL. By avoiding the complexities and dependencies of Office Interop, you can build more reliable, performant, and easily deployable applications. Whether you need to create Excel files from scratch, append data to existing reports, or export a DataTable, IronXL provides an intuitive and powerful API to get the job done efficiently.

Start using IronXL in your project today with a free trial.

First Step:

Start for Free

You can download IronXL and try it out with a free trial license to see how easily it can be integrated into your projects.

For those with broader needs, remember that IronXL is also part of the Iron Suite for .NET. With a single license, you gain access to a comprehensive set of libraries for handling PDFs, barcodes, OCR, and more, providing exceptional value for any .NET developer.