How to Convert Excel to Datatable in C# Without oledb

Microsoft Excel is a robust spreadsheet program created 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. Due to its versatility and extensive feature set, it is the go-to tool for organizing, assessing, and presenting data in both personal and professional situations. In this article, we are going to convert Excel to a 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 an object.
4. Convert the Excel sheet data into a DataTable using specific methods.
5. 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, making 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.

Always consider the needs of your application when choosing among these solutions. For further information about the IronXL library, visit this website.

Creating a New Project in Visual Studio

To open Visual Studio, click on File from the menu and select "New Project." Then, choose "Windows Forms Application."

After selecting the file location, 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 type of application chosen will dictate 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.

Next, add the necessary library and test the code.

Installing IronXL Library

To install the IronXL library, open the NuGet Package Manager Console and enter the following command:

Install-Package IronXL.Excel

Alternatively, you can use the NuGet Package Manager to search for the package "IronXL." This will display a list of NuGet packages related to IronXL, from which you can choose the one you need.

Convert Excel File to Data Table

The following code demonstrates how to read an Excel file and convert it into a DataTable after installing the package.

// Necessary namespaces for IronXL and application namespaces
using IronXL;
using System;
using System.Data;
using System.Windows.Forms;

namespace DataTableWindowsForm
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        /// <summary>
        /// Exports data from an Excel file specified by the user through a file dialog.
        /// </summary>
        /// <param name="filename">The name of the file to export data from.</param>
        private void ExportData(string filename)
        {
            string importFilePath = string.Empty;
            openFileDialog1.Filter = "Excel (*.xlsx)|*.xlsx";
            DialogResult result = openFileDialog1.ShowDialog();

            if (result == DialogResult.OK)
            {
                importFilePath = openFileDialog1.FileName;
            }

            if (!string.IsNullOrEmpty(importFilePath))
            {
                // Load the Excel document
                var excelDoc = WorkBook.Load(importFilePath);
                // Select the first worksheet
                var worksheet = excelDoc.WorkSheets[0];
                // Convert worksheet to a DataTable
                DataTable dt = worksheet.ToDataTable();
                // Bind DataTable to the DataGridView
                dataGridView1.DataSource = dt;
            }
        }
    }
}

// Necessary namespaces for IronXL and application namespaces
using IronXL;
using System;
using System.Data;
using System.Windows.Forms;

namespace DataTableWindowsForm
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        /// <summary>
        /// Exports data from an Excel file specified by the user through a file dialog.
        /// </summary>
        /// <param name="filename">The name of the file to export data from.</param>
        private void ExportData(string filename)
        {
            string importFilePath = string.Empty;
            openFileDialog1.Filter = "Excel (*.xlsx)|*.xlsx";
            DialogResult result = openFileDialog1.ShowDialog();

            if (result == DialogResult.OK)
            {
                importFilePath = openFileDialog1.FileName;
            }

            if (!string.IsNullOrEmpty(importFilePath))
            {
                // Load the Excel document
                var excelDoc = WorkBook.Load(importFilePath);
                // Select the first worksheet
                var worksheet = excelDoc.WorkSheets[0];
                // Convert worksheet to a DataTable
                DataTable dt = worksheet.ToDataTable();
                // Bind DataTable to the DataGridView
                dataGridView1.DataSource = dt;
            }
        }
    }
}

' Necessary namespaces for IronXL and application namespaces
Imports IronXL
Imports System
Imports System.Data
Imports System.Windows.Forms

Namespace DataTableWindowsForm
	Partial Public Class Form1
		Inherits Form

		Public Sub New()
			InitializeComponent()
		End Sub

		''' <summary>
		''' Exports data from an Excel file specified by the user through a file dialog.
		''' </summary>
		''' <param name="filename">The name of the file to export data from.</param>
		Private Sub ExportData(ByVal filename 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 Not String.IsNullOrEmpty(importFilePath) Then
				' Load the Excel document
				Dim excelDoc = WorkBook.Load(importFilePath)
				' Select the first worksheet
				Dim worksheet = excelDoc.WorkSheets(0)
				' Convert worksheet to a DataTable
				Dim dt As DataTable = worksheet.ToDataTable()
				' Bind DataTable to the DataGridView
				dataGridView1.DataSource = dt
			End If
		End Sub
	End Class
End Namespace

In the above code:

• The Windows Form loads the Excel files and converts them into a new DataTable.
• It uses an open file dialog to let the user choose an Excel file.
• The WorkBook.Load() method from IronXL is used to load the Excel file into a Workbook object.
• The first worksheet is selected using an index or name.
• The ToDataTable method is used to turn the worksheet data into a DataTable.
• The filled DataTable is set as the data source for a DataGridView control on the Windows Form.

At the bottom, a form and a button load the Excel file into the application.

When the user clicks the "Load Excel" button, it will prompt a dialog allowing the user to select a file.

It will then process the selected file, convert it into a DataTable, and load it into a DataGridView.

Learn more about using IronXL with DataTables.

Conclusion

IronXL is one of the most commonly used Excel add-ons that does not depend on any extra external libraries. Installing Microsoft Excel is unnecessary as it is a self-contained program. It can manipulate Excel files in various ways. In contrast, the Interop library requires other libraries to parse files and edit Word documents.

IronXL provides a comprehensive solution for any programming process using Microsoft Excel documents, including operations like calculations, sorting, merging, and file storage. It facilitates handling Excel data, reading, and writing files more efficiently.

Originally, IronXL was priced at $749. Users can receive software updates and support by paying a one-year membership fee. IronXL also offers protection against illegal redistribution for a fee. Click here to try IronXL with a free trial for more detailed pricing information. Visit the Iron Software home page for further information on other Iron software products.