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 April 19, 2023

How to Export Datatable to Excel in ASP .NET

With the growing use of data analysis and management in businesses, the need for efficient and effective data processing has become increasingly important. One of the most widely used tools for data management is the DataTable, which provides a convenient way to store, manipulate, and analyze data. However, in order to share the data with others or present it in a visually appealing manner, it is often necessary to export DataTable to other formats. Excel is one such format that is widely used for data visualization and presentation. By exporting data from a DataTable to an Excel file, users can not only present the data in a visually appealing manner but also perform further analysis using the powerful tools provided by Excel.

In this article, we will explore the different ways to export DataTable to Excel file and the benefits of doing so.

1. IronXL

IronXL is a powerful .NET library that provides developers with the ability to read, write, and manipulate Excel files using ASP.NET. It offers a range of features that simplify the process of working with Excel files, such as data extraction, formatting, and validation.

IronXL can handle both XLS and XLSX file formats, making it a versatile tool for working with Excel files. It also supports a range of other file formats, such as CSV and TSV, allowing users to work with different data sources. In this article, we will explore the different features of IronXL and how it can help developers streamline the process of working with Excel files.

2. Prerequisites

Prerequisites for exporting data from DataTable to Excel using IronXL library:

In order to create an ASP.NET project, Visual Studio needs to be installed on your system. If you don't already have Visual Studio, you can download it from the Microsoft website.
Prior to creating an ASP.NET project, you need to ensure that ASP.NET is installed on your system. You can verify this by going to the Control Panel and checking for the "Turn Windows features on or off" option. Make sure that the "ASP.NET" option is enabled.
To be able to export data from DataTable to Excel file using the IronXL library, you must have it installed. You can install it by downloading the IronXL NuGet package from the NuGet package manager within Visual Studio.

3. Creating New ASP.NET Core Project

To use the IronXL library with Excel, you need to create a .NET project in Visual Studio. You can use any version of Visual Studio, but it's recommended to use the latest version. Depending on your requirements, you can choose from different project templates, such as Windows Forms. To make things easier, we'll use the Console Application for this tutorial.

After selecting the Project type, provide a name for the project and designate its location. Choose the desired Framework, such as .NET Core 6, for the project.

Once the solution is created, the program.cs file will be opened, enabling you to enter code and construct/run the application.

Finally, you can incorporate the library to test the code.

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 made ready to use.

5. Creating Excel file using DataTable DT Using IronXL

Exporting data from data tables to Excel file format is quite popular. Using IronXL, you can easily export DataTable data to Excel document. In below attached sample code example we will convert a DataTable to an Excel worksheet saved in the XLSX file format.

5.1. DataTable to Excel File

First you need to include the namespace of IronXL and other dependencies. Then you can start writing the following code as shown below.

using IronXL;
using System.Data;

DataTable dt = new DataTable();
dt.Columns.Add("DataSet", typeof(string));
dt.Rows.Add("Pen");
dt.Rows.Add("Paper");
dt.Rows.Add("Book");
dt.Rows.Add("Cat");
dt.Rows.Add("Panther");
dt.Rows.Add("Cell");
dt.Rows.Add("Bag");
dt.Rows.Add("Bed");
dt.Rows.Add("Fan");
dt.Rows.Add("Smoke");

WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
WorkSheet ws = wb.DefaultWorkSheet;
int rowCount = 1;

foreach (DataRow row in dt.Rows) {
  ws["A" + (rowCount)].Value = row[0].ToString();
  rowCount++;
}
wb.SaveAs("datatable.xlsx");

using IronXL;
using System.Data;

DataTable dt = new DataTable();
dt.Columns.Add("DataSet", typeof(string));
dt.Rows.Add("Pen");
dt.Rows.Add("Paper");
dt.Rows.Add("Book");
dt.Rows.Add("Cat");
dt.Rows.Add("Panther");
dt.Rows.Add("Cell");
dt.Rows.Add("Bag");
dt.Rows.Add("Bed");
dt.Rows.Add("Fan");
dt.Rows.Add("Smoke");

WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
WorkSheet ws = wb.DefaultWorkSheet;
int rowCount = 1;

foreach (DataRow row in dt.Rows) {
  ws["A" + (rowCount)].Value = row[0].ToString();
  rowCount++;
}
wb.SaveAs("datatable.xlsx");

Imports IronXL
Imports System.Data

Private dt As New DataTable()
dt.Columns.Add("DataSet", GetType(String))
dt.Rows.Add("Pen")
dt.Rows.Add("Paper")
dt.Rows.Add("Book")
dt.Rows.Add("Cat")
dt.Rows.Add("Panther")
dt.Rows.Add("Cell")
dt.Rows.Add("Bag")
dt.Rows.Add("Bed")
dt.Rows.Add("Fan")
dt.Rows.Add("Smoke")

Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
Dim ws As WorkSheet = wb.DefaultWorkSheet
Dim rowCount As Integer = 1

For Each row As DataRow In dt.Rows
  ws("A" & (rowCount)).Value = row(0).ToString()
  rowCount += 1
Next row
wb.SaveAs("datatable.xlsx")

VB C#

Output

The below is the output of the above source code.

5.2. DataTable to Supported Excel file formats

Using IronXL you can also convert DataTable to different excel supported file formats like CSV file. We will use the below example, but this time save it as a CSV file. This is all you need to do to save the workbook in a different file format.

using System.Data;

DataTable dt = new DataTable();
dt.Columns.Add("DataSet", typeof(string));
dt.Rows.Add("Pen");
dt.Rows.Add("Paper");
dt.Rows.Add("Book");
dt.Rows.Add("Cat");
dt.Rows.Add("Panther");
dt.Rows.Add("Cell");
dt.Rows.Add("Bag");
dt.Rows.Add("Bed");
dt.Rows.Add("Fan");
dt.Rows.Add("Smoke");

WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
WorkSheet ws = wb.DefaultWorkSheet;
int rowCount = 1;

foreach (DataRow row in dt.Rows) {
  ws["A" + (rowCount)].Value = row[0].ToString();
  rowCount++; 
}
wb.SaveAsCsv("datatable.csv");

using System.Data;

DataTable dt = new DataTable();
dt.Columns.Add("DataSet", typeof(string));
dt.Rows.Add("Pen");
dt.Rows.Add("Paper");
dt.Rows.Add("Book");
dt.Rows.Add("Cat");
dt.Rows.Add("Panther");
dt.Rows.Add("Cell");
dt.Rows.Add("Bag");
dt.Rows.Add("Bed");
dt.Rows.Add("Fan");
dt.Rows.Add("Smoke");

WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
WorkSheet ws = wb.DefaultWorkSheet;
int rowCount = 1;

foreach (DataRow row in dt.Rows) {
  ws["A" + (rowCount)].Value = row[0].ToString();
  rowCount++; 
}
wb.SaveAsCsv("datatable.csv");

Imports System.Data

Private dt As New DataTable()
dt.Columns.Add("DataSet", GetType(String))
dt.Rows.Add("Pen")
dt.Rows.Add("Paper")
dt.Rows.Add("Book")
dt.Rows.Add("Cat")
dt.Rows.Add("Panther")
dt.Rows.Add("Cell")
dt.Rows.Add("Bag")
dt.Rows.Add("Bed")
dt.Rows.Add("Fan")
dt.Rows.Add("Smoke")

Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
Dim ws As WorkSheet = wb.DefaultWorkSheet
Dim rowCount As Integer = 1

For Each row As DataRow In dt.Rows
  ws("A" & (rowCount)).Value = row(0).ToString()
  rowCount += 1
Next row
wb.SaveAsCsv("datatable.csv")

VB C#

6. Conclusion

Exporting a DataTable to Excel is a crucial step in data analysis and management. Excel sheet is widely used for data visualization and presentation, and the IronXL library offers a range of features to simplify the process of working with Excel files in ASP.NET. This article covered the steps to create a new ASP.NET project, install IronXL, and use it to export data from a DataTable to an Excel file. Additionally, it demonstrated how to save the workbook in different file formats, such as CSV. By utilizing IronXL, developers can streamline the process of working with Excel files and create visually appealing presentations of their data.

To learn more about IronXL and exporting DataTable to different Excel formats visit the following link.

IronXL is free to install and use for non-commercial development purposes. A free trial is available for testing in production. Look at the pricing plans for more details about prices and licensing.

Users can also benefit from Iron Suite, a Suite of five professional libraries including IronXL, IronPDF, and more.