VB .NET Excel Library

IronXL library makes managing worksheets using C# code as easy as possible. The actions of creating & deleting worksheets, changing worksheet positions, and setting the active worksheet in an Excel file can be achieved without using Office Interop.

Create Worksheet

The CreateWorkSheet method allows creating a worksheet. It requires the worksheet name as the only parameter.

// Example C# code to create a worksheet using IronXL
var workbook = new IronXL.WorkBook();
var worksheet = workbook.CreateWorkSheet("NewSheet");

// Example C# code to create a worksheet using IronXL
var workbook = new IronXL.WorkBook();
var worksheet = workbook.CreateWorkSheet("NewSheet");

' Example C# code to create a worksheet using IronXL
Dim workbook = New IronXL.WorkBook()
Dim worksheet = workbook.CreateWorkSheet("NewSheet")

• This code snippet creates a new workbook and a new worksheet named "NewSheet" within that workbook.

Set Worksheet Position

The SetSheetPosition method can be used to change or move a worksheet's position. Two parameters are required: the worksheet name as a string and its index position as an integer.

// Example C# code to set a worksheet's position
workbook.SetSheetPosition("SheetName", 1);

// Example C# code to set a worksheet's position
workbook.SetSheetPosition("SheetName", 1);

CONVERTER NOT RUNNING

• This modifies the position of the worksheet named "SheetName" to the second position in the workbook (remember, indexing is zero-based).

Set Active Worksheet

Setting the active worksheet means setting which worksheet will be opened by default when the workbook is accessed. To achieve this, use the SetActiveTab method with the index position of the worksheet.

// Example C# code to set the active worksheet
workbook.SetActiveTab(0);

// Example C# code to set the active worksheet
workbook.SetActiveTab(0);

CONVERTER NOT RUNNING

• This sets the first worksheet as the active tab that opens by default.

Remove Worksheet

Removing a worksheet can also be done with IronXL. Use the RemoveWorkSheet method along with the index position of the worksheet. If the worksheet's position is unknown, the name of the worksheet can also be used to delete it.

// Example C# code to remove a worksheet by index
workbook.RemoveWorkSheet(0);

// Example C# code to remove a worksheet by name
workbook.RemoveWorkSheet("SheetName");

// Example C# code to remove a worksheet by index
workbook.RemoveWorkSheet(0);

// Example C# code to remove a worksheet by name
workbook.RemoveWorkSheet("SheetName");

' Example C# code to remove a worksheet by index
workbook.RemoveWorkSheet(0)

' Example C# code to remove a worksheet by name
workbook.RemoveWorkSheet("SheetName")

• The first example removes the first worksheet in the workbook by index.
• The second example removes the worksheet named "SheetName".

Please note that all the index positions mentioned above utilize zero-based indexing, meaning the first element has an index of 0.

The Load feature in IronXL allows you to easily load and manipulate existing Excel files in various formats (XLSX, XLS, CSV, etc.). By loading a workbook, you gain access to its worksheets and data, enabling you to extract values, loop through cell ranges, and perform calculations like sums or maximum values. You can access specific cells, convert their values to the appropriate data type (e.g., integers, decimals), and even use LINQ for advanced data operations. This feature simplifies working with Excel data in .NET, making it ideal for tasks like data analysis, report generation, and automation.

The WorkBook object is the key starting point for interacting with Excel files in IronXL. By using the WorkBook.Load("sample.xlsx") method, you open an existing Excel file, gaining access to its content and structure for further manipulation. This allows developers to work with pre-existing data, automate tasks, and update spreadsheets without having to create a new one from scratch.

Next, the WorkSheets[0] property is used to access the first worksheet in the workbook. In Excel, a workbook can contain multiple sheets, each representing a set of data organized into rows and columns. By selecting the first sheet, you're targeting the main area where your data is stored or where you'll perform operations like reading values or calculating results.

The range variable defines a specific group of cells, in this case, the range from A2 to A10. This is where IronXL allows you to interact with multiple cells at once, either by reading values, applying formatting, or performing calculations across ranges. In this example, it captures a column range, making it easy to work with sequential data.

The .Sum() method is used to calculate the sum of the numeric values in the selected range. This highlights how IronXL allows developers to perform quick, built-in calculations on ranges of data, eliminating the need for manual iteration and summing.

Finally, the Console.WriteLine() statement outputs the calculated sum to the console. This is a simple but powerful way to display the result of your operations, such as the sum of values in a range, directly within your application.

With this approach, developers can easily automate data aggregation tasks or integrate them into larger workflows.

Click here to view the How-to Guide, including examples, sample code, and files

// Load the workbook from an existing Excel file
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Access the first worksheet in the workbook
WorkSheet workSheet = workBook.WorkSheets[0];

// Define a range of cells from A2 to A10
var range = workSheet["A2:A10"];

// Calculate the sum of the numeric values in the selected range
decimal sum = range.Sum();

// Output the calculated sum to the console
Console.WriteLine("The sum of the range A2:A10 is: " + sum);

// Load the workbook from an existing Excel file
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Access the first worksheet in the workbook
WorkSheet workSheet = workBook.WorkSheets[0];

// Define a range of cells from A2 to A10
var range = workSheet["A2:A10"];

// Calculate the sum of the numeric values in the selected range
decimal sum = range.Sum();

// Output the calculated sum to the console
Console.WriteLine("The sum of the range A2:A10 is: " + sum);

' Load the workbook from an existing Excel file
Dim workBook As WorkBook = WorkBook.Load("sample.xlsx")

' Access the first worksheet in the workbook
Dim workSheet As WorkSheet = workBook.WorkSheets(0)

' Define a range of cells from A2 to A10
Dim range = workSheet("A2:A10")

' Calculate the sum of the numeric values in the selected range
Dim sum As Decimal = range.Sum()

' Output the calculated sum to the console
Console.WriteLine("The sum of the range A2:A10 is: " & sum)

The Create Excel feature provides developers with a powerful way to generate and manage Excel files directly within .NET applications.

It streamlines tasks like creating new workbooks, adding worksheets, and inputting data while offering advanced styling options such as text wrapping, borders, and formatting. This feature is perfect for automating reports, exporting data, or creating dynamic spreadsheets. IronXL supports multiple file formats, including XLS, XLSX, CSV, and more, ensuring compatibility with various systems. With its user-friendly API, developers can focus on core functionality while saving time and reducing complexity in Excel file management.

The WorkBook object serves as the foundation of any Excel file. By initializing a new workbook, developers gain access to a blank Excel document, ready to be populated with data and formatted as needed.

Every Excel workbook requires at least one worksheet. The next step demonstrates how to create a new sheet within the workbook and assign it a custom name. Worksheets act as the primary structure for organizing data in rows and columns.

The snippet demonstrates how to add content by assigning the value "Hello World" to a specific cell, A1. This shows how easy it is to populate data into an Excel sheet programmatically, enabling developers to automate tasks like report generation and data export.

Additionally, the snippet applies styling to the cell by enabling the WrapText property. This ensures that lengthy content in the cell is displayed neatly across multiple lines, improving readability and preventing text from being cut off. Such styling options empower developers to create visually appealing and professional spreadsheets.

Lastly, the workbook is saved as an .xlsx file, ensuring all changes are preserved. This file can be opened in any Excel-compatible application, offering broad compatibility.

// Create a new workbook
WorkBook workBook = WorkBook.Create();

// Create a new worksheet with a specified name
WorkSheet workSheet = workBook.CreateWorkSheet("new_sheet");

// Set the value of cell A1
workSheet["A1"].Value = "Hello World";

// Enable text wrapping for cell A1 to improve readability
workSheet["A1"].Style.WrapText = true;

// Save the workbook to a specified file path
workBook.SaveAs("sample.xlsx");

// Create a new workbook
WorkBook workBook = WorkBook.Create();

// Create a new worksheet with a specified name
WorkSheet workSheet = workBook.CreateWorkSheet("new_sheet");

// Set the value of cell A1
workSheet["A1"].Value = "Hello World";

// Enable text wrapping for cell A1 to improve readability
workSheet["A1"].Style.WrapText = true;

// Save the workbook to a specified file path
workBook.SaveAs("sample.xlsx");

' Create a new workbook
Dim workBook As WorkBook = WorkBook.Create()

' Create a new worksheet with a specified name
Dim workSheet As WorkSheet = workBook.CreateWorkSheet("new_sheet")

' Set the value of cell A1
workSheet("A1").Value = "Hello World"

' Enable text wrapping for cell A1 to improve readability
workSheet("A1").Style.WrapText = True

' Save the workbook to a specified file path
workBook.SaveAs("sample.xlsx")

Click here to view the How-to Guide, including examples, sample code, and files

IronXL offers a simple and flexible way for developers to work with Excel files, allowing easy import and export across various formats. It supports file types like XLS, XLSX, CSV, JSON, XML, and HTML, making it ideal for different application needs. Developers can convert Excel data into formats suitable for web integration, APIs, or further data processing with just a few lines of code.

For example, exporting Excel files as HTML or JSON makes it easy to embed data into web applications, while converting them to binary, byte arrays, or streams helps integrate with databases or other systems. Converting Excel content into a DataSet simplifies connecting to SQL databases or populating UI components like data grids. This reduces manual coding, enhances productivity, and makes IronXL a valuable tool for modern, data-driven applications.

5 Steps to Export Excel Files in Multiple Formats

// Load an existing Excel file into a WorkBook object. This acts as the central hub for data manipulation.
WorkBook workBook = WorkBook.Load("Sample.xlsx");

// Save the workbook as an .xlsx file, ensuring compatibility with modern Excel applications.
workBook.SaveAs("exported_file.xlsx");

// Convert the workbook into JSON format, making it suitable for API responses or lightweight data exchanges.
workBook.SaveAsJson("exported_file.json");

// Convert the workbook into a DataSet object, which is a .NET-native structure for handling tabular data.
// This enables seamless integration with databases or UI components like data grids.
DataSet dataSet = workBook.ToDataSet();

// Generate an HTML file from the workbook, enabling developers to display Excel data directly on web pages.
workBook.ExportToHtml("exported_file.html");

// Load an existing Excel file into a WorkBook object. This acts as the central hub for data manipulation.
WorkBook workBook = WorkBook.Load("Sample.xlsx");

// Save the workbook as an .xlsx file, ensuring compatibility with modern Excel applications.
workBook.SaveAs("exported_file.xlsx");

// Convert the workbook into JSON format, making it suitable for API responses or lightweight data exchanges.
workBook.SaveAsJson("exported_file.json");

// Convert the workbook into a DataSet object, which is a .NET-native structure for handling tabular data.
// This enables seamless integration with databases or UI components like data grids.
DataSet dataSet = workBook.ToDataSet();

// Generate an HTML file from the workbook, enabling developers to display Excel data directly on web pages.
workBook.ExportToHtml("exported_file.html");

' Load an existing Excel file into a WorkBook object. This acts as the central hub for data manipulation.
Dim workBook As WorkBook = WorkBook.Load("Sample.xlsx")

' Save the workbook as an .xlsx file, ensuring compatibility with modern Excel applications.
workBook.SaveAs("exported_file.xlsx")

' Convert the workbook into JSON format, making it suitable for API responses or lightweight data exchanges.
workBook.SaveAsJson("exported_file.json")

' Convert the workbook into a DataSet object, which is a .NET-native structure for handling tabular data.
' This enables seamless integration with databases or UI components like data grids.
Dim dataSet As DataSet = workBook.ToDataSet()

' Generate an HTML file from the workbook, enabling developers to display Excel data directly on web pages.
workBook.ExportToHtml("exported_file.html")

This code demonstrates the versatility of IronXL in handling and exporting Excel data across multiple formats. The process begins by loading an existing Excel file, Sample.xlsx, into a WorkBook object. This object acts as the central hub for manipulating and exporting data. Using the SaveAs method, the workbook is saved as an .xlsx file, ensuring compatibility with modern Excel applications.

Next, the SaveAsJson method converts the workbook into JSON format, making it suitable for API responses or lightweight data exchanges. The ToDataSet method enables seamless integration with databases or UI components by converting the workbook into a DataSet object, which is a .NET-native structure for handling tabular data.

Finally, the ExportToHtml method generates an HTML file, enabling developers to display the Excel data directly on web pages. These capabilities streamline data processing tasks, making IronXL an exceptional tool for developers working with Excel files.

Click here to view the How-to Guide, including examples, sample code, and files

Convert any XLSX, XLS, XLSM, XLTX, CSV and TSV file to a System.Data.DataTable for full interoperability with System.Data.SQL or to populate a DataGrid. This DataTable object can contain a collection of tables, relationships, and constraints.

using System;
using System.Data;
using System.IO;
using ExcelDataReader; // Include ExcelDataReader for reading Excel files
using System.Data.SqlClient; // Include for SQL interoperability

class Program
{
    static void Main()
    {
        // Provide the file path for the input file
        string filePath = "path_to_your_file.xlsx";

        // Call the method to convert the file
        DataTable dataTable = ConvertToDataTable(filePath);

        // Process the DataTable or populate a data grid
    }

    /// <summary>
    /// Converts a supported spreadsheet or delimited file into a DataTable.
    /// </summary>
    /// <param name="filePath">Path to the input file</param>
    /// <returns>DataTable containing the data from the spreadsheet or delimited file</returns>
    static DataTable ConvertToDataTable(string filePath)
    {
        // Validate file existence
        if (!File.Exists(filePath))
        {
            throw new FileNotFoundException("The specified file was not found.");
        }

        // Initialize DataTable to be returned
        DataTable dataTable = new DataTable();

        // Determine file extension for appropriate handling
        string fileExtension = Path.GetExtension(filePath).ToLower();

        // Handle based on file type
        switch (fileExtension)
        {
            // Excel format (XLSX, XLS, etc.)
            case ".xlsx":
            case ".xls":
            case ".xlsm":
            case ".xltx":
                using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
                {
                    using (var reader = ExcelReaderFactory.CreateReader(stream))
                    {
                        // Use reader to work through Excel structure
                        var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration()
                        {
                            ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                            {
                                UseHeaderRow = true // Use the first row as column headers
                            }
                        });

                        // Copy the first table of the dataset to the dataTable
                        dataTable = dataSet.Tables[0];
                    }
                }
                break;

            // CSV and TSV formats
            case ".csv":
            case ".tsv":
                char delimiter = fileExtension == ".csv" ? ',' : '\t';
                // Read all lines from the file
                var lines = File.ReadAllLines(filePath);
                if (lines.Length > 0)
                {
                    // Process header line and add columns
                    string[] headers = lines[0].Split(delimiter);
                    foreach (string header in headers)
                    {
                        dataTable.Columns.Add(header);
                    }

                    // Read the rest of the file, adding rows to the DataTable
                    for (int i = 1; i < lines.Length; i++)
                    {
                        string[] cells = lines[i].Split(delimiter);
                        dataTable.Rows.Add(cells);
                    }
                }
                break;

            default:
                throw new NotSupportedException("File extension is not supported for conversion.");
        }

        return dataTable;
    }
}

using System;
using System.Data;
using System.IO;
using ExcelDataReader; // Include ExcelDataReader for reading Excel files
using System.Data.SqlClient; // Include for SQL interoperability

class Program
{
    static void Main()
    {
        // Provide the file path for the input file
        string filePath = "path_to_your_file.xlsx";

        // Call the method to convert the file
        DataTable dataTable = ConvertToDataTable(filePath);

        // Process the DataTable or populate a data grid
    }

    /// <summary>
    /// Converts a supported spreadsheet or delimited file into a DataTable.
    /// </summary>
    /// <param name="filePath">Path to the input file</param>
    /// <returns>DataTable containing the data from the spreadsheet or delimited file</returns>
    static DataTable ConvertToDataTable(string filePath)
    {
        // Validate file existence
        if (!File.Exists(filePath))
        {
            throw new FileNotFoundException("The specified file was not found.");
        }

        // Initialize DataTable to be returned
        DataTable dataTable = new DataTable();

        // Determine file extension for appropriate handling
        string fileExtension = Path.GetExtension(filePath).ToLower();

        // Handle based on file type
        switch (fileExtension)
        {
            // Excel format (XLSX, XLS, etc.)
            case ".xlsx":
            case ".xls":
            case ".xlsm":
            case ".xltx":
                using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
                {
                    using (var reader = ExcelReaderFactory.CreateReader(stream))
                    {
                        // Use reader to work through Excel structure
                        var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration()
                        {
                            ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                            {
                                UseHeaderRow = true // Use the first row as column headers
                            }
                        });

                        // Copy the first table of the dataset to the dataTable
                        dataTable = dataSet.Tables[0];
                    }
                }
                break;

            // CSV and TSV formats
            case ".csv":
            case ".tsv":
                char delimiter = fileExtension == ".csv" ? ',' : '\t';
                // Read all lines from the file
                var lines = File.ReadAllLines(filePath);
                if (lines.Length > 0)
                {
                    // Process header line and add columns
                    string[] headers = lines[0].Split(delimiter);
                    foreach (string header in headers)
                    {
                        dataTable.Columns.Add(header);
                    }

                    // Read the rest of the file, adding rows to the DataTable
                    for (int i = 1; i < lines.Length; i++)
                    {
                        string[] cells = lines[i].Split(delimiter);
                        dataTable.Rows.Add(cells);
                    }
                }
                break;

            default:
                throw new NotSupportedException("File extension is not supported for conversion.");
        }

        return dataTable;
    }
}

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.IO
Imports ExcelDataReader ' Include ExcelDataReader for reading Excel files
Imports System.Data.SqlClient ' Include for SQL interoperability

Friend Class Program
	Shared Sub Main()
		' Provide the file path for the input file
		Dim filePath As String = "path_to_your_file.xlsx"

		' Call the method to convert the file
		Dim dataTable As DataTable = ConvertToDataTable(filePath)

		' Process the DataTable or populate a data grid
	End Sub

	''' <summary>
	''' Converts a supported spreadsheet or delimited file into a DataTable.
	''' </summary>
	''' <param name="filePath">Path to the input file</param>
	''' <returns>DataTable containing the data from the spreadsheet or delimited file</returns>
	Private Shared Function ConvertToDataTable(ByVal filePath As String) As DataTable
		' Validate file existence
		If Not File.Exists(filePath) Then
			Throw New FileNotFoundException("The specified file was not found.")
		End If

		' Initialize DataTable to be returned
		Dim dataTable As New DataTable()

		' Determine file extension for appropriate handling
		Dim fileExtension As String = Path.GetExtension(filePath).ToLower()

		' Handle based on file type
		Select Case fileExtension
			' Excel format (XLSX, XLS, etc.)
			Case ".xlsx", ".xls", ".xlsm", ".xltx"
				Using stream = File.Open(filePath, FileMode.Open, FileAccess.Read)
					Using reader = ExcelReaderFactory.CreateReader(stream)
						' Use reader to work through Excel structure
'INSTANT VB TODO TASK: Underscore 'discards' are not converted by Instant VB:
'ORIGINAL LINE: var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true } });
						Dim dataSet = reader.AsDataSet(New ExcelDataSetConfiguration() With {
							.ConfigureDataTable = Function(underscore) New ExcelDataTableConfiguration() With {.UseHeaderRow = True}
						})

						' Copy the first table of the dataset to the dataTable
						dataTable = dataSet.Tables(0)
					End Using
				End Using

			' CSV and TSV formats
			Case ".csv", ".tsv"
				Dim delimiter As Char = If(fileExtension = ".csv", ","c, ControlChars.Tab)
				' Read all lines from the file
				Dim lines = File.ReadAllLines(filePath)
				If lines.Length > 0 Then
					' Process header line and add columns
					Dim headers() As String = lines(0).Split(delimiter)
					For Each header As String In headers
						dataTable.Columns.Add(header)
					Next header

					' Read the rest of the file, adding rows to the DataTable
					For i As Integer = 1 To lines.Length - 1
						Dim cells() As String = lines(i).Split(delimiter)
						dataTable.Rows.Add(cells)
					Next i
				End If

			Case Else
				Throw New NotSupportedException("File extension is not supported for conversion.")
		End Select

		Return dataTable
	End Function
End Class

Convert XLSX, XLS, XLSM, XLTX, CSV and TSV file to a System.Data.DataTable for full interoperability with System.Data.SQL or to populate a DataGrid.

Input true to ToDataTable method to set the first row as column names of the table. The DataTable can populate a DataGrid.

using IronXL;

public class ExcelToDataTable
{
    public static DataTable ConvertExcelToDataTable(string filePath)
    {
        WorkBook workbook = WorkBook.Load(filePath);
        WorkSheet sheet = workbook.DefaultWorkSheet;
        DataTable dataTable = sheet.ToDataTable(true);
        return dataTable;
    }
}

using IronXL;

public class ExcelToDataTable
{
    public static DataTable ConvertExcelToDataTable(string filePath)
    {
        WorkBook workbook = WorkBook.Load(filePath);
        WorkSheet sheet = workbook.DefaultWorkSheet;
        DataTable dataTable = sheet.ToDataTable(true);
        return dataTable;
    }
}

Imports IronXL

Public Class ExcelToDataTable
	Public Shared Function ConvertExcelToDataTable(ByVal filePath As String) As DataTable
		Dim workbook As WorkBook = WorkBook.Load(filePath)
		Dim sheet As WorkSheet = workbook.DefaultWorkSheet
		Dim dataTable As DataTable = sheet.ToDataTable(True)
		Return dataTable
	End Function
End Class