using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published March 25, 2023

How to Write to Excel File in VB.NET

Microsoft Office Excel is a widely used tool for managing and analyzing data in various industries. It offers a multitude of features and functionalities that allow users to create and manipulate complex Excel spreadsheets with ease. However, sometimes it is necessary to automate data input or perform data analysis using code. In such cases, the ability to write to an Excel file programmatically becomes crucial. By writing to an Excel file, users can streamline data processing and automate repetitive tasks, ultimately saving time and effort. In this article, we will explore the different ways to write to an Excel file and create Excel file programmatically 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, there are some prerequisites that need to be in place:

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.
Make sure that the .NET Framework is installed on your system. IronXL requires .NET Framework 4.0 or later to function correctly.
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 VB.NET Project in Visual Studio

To create VB.NET in Visual Studio, here are following steps to create a VB.NET project in Visual Studio:

Open Visual Studio.
Click on "Create a new project" on the start page, or go to "File" > "New" > "Project".
In the "New Project" dialog box, select "Visual Basic" in the left pane.
Choose the type of project you want to create, such as "Windows Forms App" or "Console App" here we will use Console App.
Enter a name for your project and choose a location to save it.
Then select the .NET core version and click on "Create".
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.

These are:

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

4.1 Using Visual Studio

To install the IronXL library, we can 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 we can open the NuGet Package Manager in Visual Studio.

IronXL in search results:

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.

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 spreadsheet.

In this context, we will discuss 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 Excel File and Write Data to Excel

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

In this following code snippet we will create a console based Excel application object and write in it.

Imports IronXL
Imports System
Imports System.Data
Module Program
'private sub
    Sub Main(args As String())
'new workbook
        Dim workBook As WorkBook = WorkBook.Create()
        Dim xlsWorkBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
        Dim workSheet As WorkSheet = workBook.CreateWorkSheet("new_sheet")
        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
        workBook.SaveAs("sample.xlsx")
    End Sub
End Module

Imports IronXL
Imports System
Imports System.Data
Module Program
'private sub
    Sub Main(args As String())
'new workbook
        Dim workBook As WorkBook = WorkBook.Create()
        Dim xlsWorkBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
        Dim workSheet As WorkSheet = workBook.CreateWorkSheet("new_sheet")
        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
        workBook.SaveAs("sample.xlsx")
    End Sub
End Module

VB.NET

5.2. Open Existing Excel File and Write Data to Excel Worksheet

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, Excel spreadsheet or Excel database. With IronXL, you can easily read, write and modify Excel files in VB.NET.

In the following code snippet, we will focus 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())
        Dim workBook As WorkBook = WorkBook.Load("multiple.xlsx")
        Dim workSheet As WorkSheet = workBook.WorkSheets(0)
' first worksheet
        Dim firstSheet As WorkSheet = workBook.DefaultWorkSheet
        firstSheet("A8").Value = "Hello Developers"
        firstSheet("A8").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double
        firstSheet("A9").Value = "This is how you can write to existing Excel file"
        firstSheet("A9").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double
        workBook.SaveAs("sample.xlsx")
    End Sub
End Module

Imports IronXL
Imports System
Imports System.Data
Module Program
    Sub Main(args As String())
        Dim workBook As WorkBook = WorkBook.Load("multiple.xlsx")
        Dim workSheet As WorkSheet = workBook.WorkSheets(0)
' first worksheet
        Dim firstSheet As WorkSheet = workBook.DefaultWorkSheet
        firstSheet("A8").Value = "Hello Developers"
        firstSheet("A8").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double
        firstSheet("A9").Value = "This is how you can write to existing Excel file"
        firstSheet("A9").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double
        workBook.SaveAs("sample.xlsx")
    End Sub
End Module

VB.NET

6. Conclusion

Above code example shows 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 the link provided. For Code examples please visit the following link.

IronXL comes with a licensing fee that starts from $499, depending on the selected license plan. However, IronXL also offers a 30-day 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.