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 30, 2023

How to Export Datatable to Excel in VB.NET

DataTables are a popular tool for storing and manipulating large sets of data. They provide a convenient way to sort, filter, and search through data in a table format. However, presenting the data in a visually appealing and organized manner is equally important. Excel is a widely used tool for data visualization and presentation, which is why exporting a DataTable to an Excel file is a common requirement. Export data from DataTable to Excel file, users can take advantage of the powerful features provided by Excel, such as charts, graphs, and pivot tables, to present the data in a more meaningful and insightful way. You can also convert HTML table 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 sheet. 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, there are several prerequisites that must be in place:

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.
The .NET Framework must be installed on your system, and IronXL requires version 4.0 or later to function correctly.
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 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.

The below screenshot shows an example of 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. 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.

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())
Dim table As New DataTable()
table.Columns.Add("DataSet_Daily_ Household", GetType(String))
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")
Dim dt As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
Dim dc As WorkSheet = wb.DefaultWorkSheet
Dim rowCount As Integer = 1
For Each row As DataRow In table.Rows
dc("A" & (rowCount)).Value = row(0).ToString()
rowCount += 1
Next row
dt.SaveAs("sample.xls")
End Sub
End Module

Imports IronXL
Imports System
Imports System.Data

Module Program
Sub Main(args As String())
Dim table As New DataTable()
table.Columns.Add("DataSet_Daily_ Household", GetType(String))
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")
Dim dt As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
Dim dc As WorkSheet = wb.DefaultWorkSheet
Dim rowCount As Integer = 1
For Each row As DataRow In table.Rows
dc("A" & (rowCount)).Value = row(0).ToString()
rowCount += 1
Next row
dt.SaveAs("sample.xls")
End Sub
End Module

VB.NET

5.2. DataTable To XLSX files

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

Just replace this line of code.

wb.SaveAs("sample.xls")

wb.SaveAs("sample.xls")

VB.NET

With this and you are all done.

wb.SaveAsCsv("sample.xlsx")

wb.SaveAsCsv("sample.xlsx")

VB.NET

5.3. DataTable To CSV file

You can also convert DataTable rows and columns in CSV file format just by Changing one line of code in the above example.

Just replace this line of code.

wb.SaveAs("sample.xls")

wb.SaveAs("sample.xls")

VB.NET

With this and you are all done.

wb.SaveAsCsv("sample.csv")

wb.SaveAsCsv("sample.csv")

VB.NET

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, we 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 link.

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