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 May 24, 2023

How to Convert Excel to Datatable in C#

Excel is a widely used spreadsheet software that allows users to store, manipulate, and analyze data in a tabular format. However, when it comes to building applications that require the use of data stored in Excel files, it can be challenging to work with Excel file stream directly. This is where DataTable in C# comes in.

A DataTable is an in-memory representation of a table in a relational database, which allows efficient data manipulation and querying. By converting Excel object data into a DataTable, developers can leverage the full power of C# to process, analyze, export data and visualize the data. This Excel to DataTable conversion can be done easily using IronXL, which provides a simple and efficient way to read and write Excel files in C#

1. IronXL

IronXL is a powerful and user-friendly C# library designed to simplify the reading, writing, exporting data and manipulation of Excel worksheet. It provides developers with a seamless way to work with Excel spreadsheets and automate tasks within their C#

With IronXL, you can easily import, export, and modify data in Excel workbooks, worksheets, and cells. It supports both the older .XLS format and the newer .XLSX format, making it compatible with a wide range of Excel versions.

The library offers a straightforward API that enables developers to perform various operations on Excel files, such as creating new workbooks, reading and writing data to cells, applying formatting, handling formulas, and even extracting charts and images. IronXL also supports working with complex data structures like tables, ranges, and named ranges.

2. Prerequisites

To work with Excel files and convert them to DataTable using IronXL in C#, there are a few prerequisites that need to be in place:

Development Environment: Ensure that you have a compatible development environment set up on your machine, such as Visual Studio or any other C# IDE.
IronXL Library: Download and install the IronXL library. You can obtain the library from the official IronXL website or via NuGet Package Manager in Visual Studio. Include the IronXL namespace in your C# project to access its classes and methods.
.NET Framework or .NET Core: IronXL supports both the .NET Framework and .NET Core. Ensure that you have the appropriate version installed on your machine, depending on your project requirements.

3. Creating New Project in C#

To use the IronXL library for Excel-related tasks, the initial step is to create a .NET project in Visual Studio. While any version of Visual Studio can be used, it's recommended to use the latest one.

This tutorial will use the Console Application project template to showcase how to work with IronXL.

How to Convert Excel to DataTable in C#: Figure 1 - New Project

Once you have selected the project type, proceed to specify a name and location for the project. Furthermore, you have the option to choose the preferred framework for the project, such as .NET Core 6.

How to Convert Excel to DataTable in C#: Figure 2 - Project Configuration

After the solution is generated, you can access the Program.cs file, where you can input code and create/execute the application.

How to Convert Excel to DataTable in C#: Figure 3 - Program.cs

4. Installing IronXL

The IronXL library can be downloaded and installed in different ways. We will only discuss two of these in this article.

These are:

Using Visual Studio NuGet Packages
Using the Visual Studio Command Line

4.1 Using Visual Studio NuGet Packages

To install the IronXL library with NuGet Package Manager in Visual Studio, 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 use the IronXL library in your project.

The screenshot below shows how to open the NuGet Package Manager in Visual Studio.

How to Convert Excel to DataTable in C#: Figure 4 - NuGet Package Manager

IronXL in search results:

How to Convert Excel to DataTable in C#: Figure 5 - IronXL

4.2 Using the Visual Studio Command Line

Many developers prefer to install packages using the command line interface. To install IronXL using the command line, follow these steps:

Go to Tools > NuGet Package Manager > Package Manager Console in Visual Studio.
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 for use.

How to Convert Excel to DataTable in C#: Figure 6 - Install Package

5. Convert Excel Data to DataTable Using IronXL

To export Excel data from Excel file stream to DataTable in C# can be easily implemented using IronXL, an Excel Library. In this section, we will discuss how you can convert Excel worksheet data to DataTable using IronXL.

First, we need an Excel file to convert its first worksheet data into DataTable. For this purpose we will use a sample Excel file.

How to Convert Excel to DataTable in C#: Figure 7 - Sample Excel

Now lets look at our code samples.

using IronXL;
using System;
using System.Data;
//Workbook object
WorkBook workBook = WorkBook.Load("sample.xlsx");

WorkSheet workSheet = workBook.DefaultWorkSheet;
//var dt
DataTable dataTable = workSheet.ToDataTable(true);

foreach (DataRow row in dataTable.Rows)
{
    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        Console.Write(row[i]);
    }
    Console.WriteLine();
}

using IronXL;
using System;
using System.Data;
//Workbook object
WorkBook workBook = WorkBook.Load("sample.xlsx");

WorkSheet workSheet = workBook.DefaultWorkSheet;
//var dt
DataTable dataTable = workSheet.ToDataTable(true);

foreach (DataRow row in dataTable.Rows)
{
    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        Console.Write(row[i]);
    }
    Console.WriteLine();
}

Imports IronXL
Imports System
Imports System.Data
'Workbook object
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")

Private workSheet As WorkSheet = workBook.DefaultWorkSheet
'var dt
Private dataTable As DataTable = workSheet.ToDataTable(True)

For Each row As DataRow In dataTable.Rows
	For i As Integer = 0 To dataTable.Columns.Count - 1
		Console.Write(row(i))
	Next i
	Console.WriteLine()
Next row

VB C#

In above code, we are loading Excel file as workBook using WorkBook.Load method provided by IronXL and then saving its first worksheet data as workSheet. To convert this WorkSheet to DataTable we are using workSheet.ToDataTable(true) method that calls upon WorkSheet variable we previously created and exports data into DataTable and store it into new DataTable variable.

In the end we are printing DataTable data in console for Visualization.

How to Convert Excel to DataTable in C#: Figure 8 - Export Data to DataTable Output

6. Conclusion

The use of DataTable in C# provides a powerful and efficient solution for working with Excel data within applications. By leveraging libraries like IronXL, developers can easily convert Excel data into DataTable and unlock the full potential of C#

In this tutorial, we explored the capabilities of IronXL and its seamless integration with C#. We discussed the prerequisites for working with Excel files, such as having a compatible development environment, installing the IronXL library, and ensuring the appropriate .NET Framework or .NET Core version.

We also walked through the process of creating a new C# project in Visual Studio and installing IronXL either through the NuGet Package Manager or the Visual Studio Command Line. With IronXL successfully integrated into the project, we demonstrated exporting data to a DataTable using the library's easy-to-use API.

By loading an Excel file, accessing the desired worksheet, and utilizing the ToDataTable method provided by IronXL, we were able to extract the data and store it in a DataTable. Finally, we showcased how to visualize the DataTable by printing its contents in the console.

Using IronXL and DataTable in C#, developers can build robust applications that seamlessly interact with Excel data, enabling efficient data processing, analysis, and visualization. With the knowledge gained from this tutorial, you can now confidently incorporate Excel worksheets into your C# projects and take advantage of the extensive functionality provided by DataTable. For more tutorials on IronXL, please visit the link. If you need detailed tutorial on Excel C#