How to Convert Excel to Datatable in C# Without oledb

Introduction

Microsoft Excel is a robust spreadsheet program that was made by Microsoft. It's a widely used tool for data organization, analysis, and visualization. It is a part of the Office suite from Microsoft. Because of its various features, Excel is a versatile tool that may be used by professionals, individuals, organizations, and universities.

Excel is widely used in a wide range of sectors, including finance, accounting, business analysis, data analysis, research, education, and more. Because of its versatility and extensive feature set, it is the go-to tool for arranging, assessing, and presenting data in both personal and professional situations. In this article, we are going to convert Excel to datatable in C# without OLEDB connectivity.

How to Convert Excel to Datatable in C# without OLEDB

1. Create a new Visual Studio project
2. Install the required library.
3. Load the Excel file into the object.
4. Convert the Excel sheet data into datatable using the specific method
5. And use the datatable as required.

IronXL Library

An alternative to Microsoft Interop for handling Excel files in .NET programs is IronXL. While Microsoft Interop necessitates using the Interop assemblies to connect with Excel, IronXL provides a simpler, more efficient, and more potent way to manipulate Excel files programmatically in .NET environments.

The following are some advantages of using IronXL:

• Performance and Resource Economy: IronXL outperforms Microsoft Interop in terms of performance and resource efficiency as it is not reliant on the Excel program being installed on the PC.
• Readability and Simplicity: IronXL offers a simpler API that makes it easier to read, write, and manipulate Excel files without the drawbacks of Microsoft Interop.
• Compatibility and Dependency: IronXL removes dependencies and potential compatibility issues with different Office or Excel versions by not needing Microsoft Excel to be installed on the PC.
• Platform Independence: While Microsoft Interop is more closely linked to certain Microsoft Office versions, IronXL provides more flexibility and simplicity of deployment in a wider range of settings and platforms.

Because IronXL is faster, easier to use, and requires fewer third-party software installs, it is often a preferable choice for .NET developers who need to work with Excel files programmatically. However, factors like the project's specifics, the pre-existing infrastructure, and the user's familiarity with each library may affect their choice between IronXL and Microsoft Interop.

Remember the needs of your application at all times while choosing among these solutions. For further information about the IronXL library, click this website.

Creating a New Project in Visual Studio

To open the Visual Studio program, click on File from the File menu. After selecting "new project," pick "Window form application."

Once the file location has been selected, enter the project name in the appropriate text area. Next, click the Create button and choose the necessary .NET Framework, as seen in the sample below.

The chosen application will then determine how the Visual Studio project is structured. To add code, you may enter the program.cs file and construct or run the application using the console, Windows, or web application.

Following that, the library may be added and the code tested.

Installing IronXL Library

Install the IronXL library as it is required for the next patch. Finally, launch the NuGet Package Manager Console and enter the following command to complete this:

Install-Package IronXL.Excel

Alternatively, you can use the NuGet Package Manager to search for the package "IronXL". We may choose which NuGet package has to be downloaded from this list of all those connected to IronXL.

Convert Excel file to data table

The following code may be used to read an Excel file and turn it into a DataTable after the package has been installed.

using IronXL;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
namespace DataTableWindowsForm
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        /// <summary>
        /// In Export data method pass the file path as parameter
        /// </summary>
        /// <param name="filname"></param>
        private void ExportData(string filname)
        {
            string ImportFilePath = string.Empty;
            openFileDialog1.Filter = "Excel (*.xlsx)|*.xlsx";
            DialogResult Result = openFileDialog1.ShowDialog();
            if (Result == DialogResult.OK)
            {
                ImportFilePath = openFileDialog1.FileName;
            }
            if (ImportFilePath != string.Empty)
            {
                var exceldoc = IronXL.WorkBook.LoadExcel(ImportFilePath);
                var worksheet = exceldoc.WorkSheets [0];
                datatable dt = worksheet.ToDataTable();
                dataGridView1.DataSource = dt;
            }
        }
}
}

using IronXL;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
namespace DataTableWindowsForm
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        /// <summary>
        /// In Export data method pass the file path as parameter
        /// </summary>
        /// <param name="filname"></param>
        private void ExportData(string filname)
        {
            string ImportFilePath = string.Empty;
            openFileDialog1.Filter = "Excel (*.xlsx)|*.xlsx";
            DialogResult Result = openFileDialog1.ShowDialog();
            if (Result == DialogResult.OK)
            {
                ImportFilePath = openFileDialog1.FileName;
            }
            if (ImportFilePath != string.Empty)
            {
                var exceldoc = IronXL.WorkBook.LoadExcel(ImportFilePath);
                var worksheet = exceldoc.WorkSheets [0];
                datatable dt = worksheet.ToDataTable();
                dataGridView1.DataSource = dt;
            }
        }
}
}

Imports IronXL
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Namespace DataTableWindowsForm
	Partial Public Class Form1
		Inherits Form

		Public Sub New()
			InitializeComponent()
		End Sub
		''' <summary>
		''' In Export data method pass the file path as parameter
		''' </summary>
		''' <param name="filname"></param>
		Private Sub ExportData(ByVal filname As String)
			Dim ImportFilePath As String = String.Empty
			openFileDialog1.Filter = "Excel (*.xlsx)|*.xlsx"
			Dim Result As DialogResult = openFileDialog1.ShowDialog()
			If Result = System.Windows.Forms.DialogResult.OK Then
				ImportFilePath = openFileDialog1.FileName
			End If
			If ImportFilePath <> String.Empty Then
				Dim exceldoc = IronXL.WorkBook.LoadExcel(ImportFilePath)
				Dim worksheet = exceldoc.WorkSheets (0)
				Dim dt As datatable = worksheet.ToDataTable()
				dataGridView1.DataSource = dt
			End If
		End Sub
	End Class
End Namespace

From the above code, we are using the Windows form to load the Excel files and convert them into a new dataset, then we are loading them into Windows form datagrid. In the above code first, we use the open file dialog controller to load the xls file from the file system.

Then with the help of the IronXL workbook and Loadexcel file method, we pass the filename as a parameter which we can get from the open file dialog controller. It helps us to import Excel files into the workbook object. Then we select the Excel worksheet by using the index value or can use the Excel sheet name to select the worksheet. With the help of the available method ToDataTable, we can easily convert the Excel file data into the new datatable.

After converting the Excel file into the datatable we load them into datagrid controller which is available in the Windows form. From the below, we have a form and button a load the Excel file.

When the user clicks the Load Excel button it will prompt a dialog and allow us to load the file.

It will process the files convert them into the new datatable and load into the datagrid.

To know more about the IronXL datatable refer to the link here.

Conclusion

IronXL is among the most widely used Excel add-ons. It doesn't depend on any extra outside libraries. Installing Microsoft Excel is not required because it is a self-contained program. It uses different channels to function. In contrast, the Interop library requires other libraries to parse files to edit Word documents.

IronXL is a comprehensive solution for any programming process that uses Microsoft Excel documents. Among the possible operations are calculations, sorting strings or numbers, pruning, adding, finding and replacing, merging and unmerging, and file storage. Spreadsheet data may be created and evaluated, and new cell data types can also be created. It makes handling Excel data and reading and writing files easier.

IronXL was originally priced at $749. As an alternative, users can get software updates and support by paying a one-year membership fee. IronXL offers protection against illegal redistribution in exchange for a fee. Click here to experience IronXL with a free trial for more detailed price information. For further information on Iron software products, see here.