How to Convert Excel to Datagridview in C#

IronXL Library Features

Using the IronXL library in C#, you can easily read and convert Microsoft Excel documents to CSV files. IronXL is a standalone .NET software library that can read various spreadsheet formats without requiring the installation of Microsoft Excel or depending on Microsoft Office Interop Excel.

IronXL is one of the leading Excel spreadsheet libraries for C#, compatible with both .NET Core and .NET Framework. It supports various .NET Frameworks, including Web Applications, Windows Forms applications, and Console Applications. Reading Excel files is fast and straightforward with IronXL. It supports a wide range of Excel file formats, including XLSX, XLS, CSV, TSV, XLST, XLSM, and others. You can import, edit, export data tables, export datasets, and perform many other operations. Exporting and saving files with different extensions is also possible.

With IronXL's intuitive C# API, you can effortlessly read, modify, and create Excel spreadsheet files in the .NET environment. It provides comprehensive support for .NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS, and Azure.

IronXL can perform calculations like Microsoft Excel can and supports several column data formats, such as text, integers, formulas, dates, currencies, and percentages.

To learn more, click on the IronXL website.

Creating a New Project in Visual Studio

Before using the IronXL framework, a .NET project must be created in Visual Studio. Any version of Visual Studio will work; however, using the most recent version is recommended. You can create various project templates or a Windows Forms-like application, depending on your requirements. This tutorial will be using the Windows Forms Application to simplify things.

Create a new project in Visual Studio

After choosing your project template, provide the name and location of the project.

Configure the project

Next, pick the following structure. This project will make use of .NET Framework 4.7.

.NET Framework selection

After generating the project files, you can open the Form1.cs file in the designer view to insert the program code, design the interface, and build/run the program. Then, you will need to download the required IronXL library for the solution. You can download the package by using the following command in the package manager:

Install-Package IronXL.Excel

Install the IronXL package in the Package Manager Console

Alternatively, you can use the NuGet Package Manager to search for and download the "IronXL" package. The NuGet Package Manager provides a convenient way to manage dependencies in your project.

Search and install the IronXL package in NuGet Package Manager UI

For this form, a DataGridView control is placed on the form to load the necessary Excel file into the DataGridView control.

Loading Data from an Excel File

IronXL is a powerful library for Excel that allows you to read Excel files with just a few lines of code. Below is an example code snippet that demonstrates how to import Excel data into a DataGridView:

using System;
using System.Windows.Forms;
using IronXL;

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

        private void button1_Click(object sender, EventArgs e)
        {
            string importFilePath = string.Empty;

            openFileDialog1.Filter = "Excel (*.xlsx)|*.xlsx";
            DialogResult result = openFileDialog1.ShowDialog();

            // Check if the user selected a file
            if (result == DialogResult.OK)
            {
                importFilePath = openFileDialog1.FileName;
            }

            // Proceed if the file path is not empty
            if (!string.IsNullOrEmpty(importFilePath))
            {
                // Load the Excel document
                WorkBook excelDoc = WorkBook.Load(importFilePath);

                // Convert the first sheet to a DataTable
                var dataTable = excelDoc.WorkSheets[0].ToDataTable();

                // Set the DataSource of dataGridView1 to the DataTable
                dataGridView1.DataSource = dataTable;
            }
        }
    }
}

using System;
using System.Windows.Forms;
using IronXL;

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

        private void button1_Click(object sender, EventArgs e)
        {
            string importFilePath = string.Empty;

            openFileDialog1.Filter = "Excel (*.xlsx)|*.xlsx";
            DialogResult result = openFileDialog1.ShowDialog();

            // Check if the user selected a file
            if (result == DialogResult.OK)
            {
                importFilePath = openFileDialog1.FileName;
            }

            // Proceed if the file path is not empty
            if (!string.IsNullOrEmpty(importFilePath))
            {
                // Load the Excel document
                WorkBook excelDoc = WorkBook.Load(importFilePath);

                // Convert the first sheet to a DataTable
                var dataTable = excelDoc.WorkSheets[0].ToDataTable();

                // Set the DataSource of dataGridView1 to the DataTable
                dataGridView1.DataSource = dataTable;
            }
        }
    }
}

Imports System
Imports System.Windows.Forms
Imports IronXL

Namespace DataTableWindowsForm
	Partial Public Class Form1
		Inherits Form

		Public Sub New()
			InitializeComponent()
		End Sub

		Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
			Dim importFilePath As String = String.Empty

			openFileDialog1.Filter = "Excel (*.xlsx)|*.xlsx"
			Dim result As DialogResult = openFileDialog1.ShowDialog()

			' Check if the user selected a file
			If result = System.Windows.Forms.DialogResult.OK Then
				importFilePath = openFileDialog1.FileName
			End If

			' Proceed if the file path is not empty
			If Not String.IsNullOrEmpty(importFilePath) Then
				' Load the Excel document
				Dim excelDoc As WorkBook = WorkBook.Load(importFilePath)

				' Convert the first sheet to a DataTable
				Dim dataTable = excelDoc.WorkSheets(0).ToDataTable()

				' Set the DataSource of dataGridView1 to the DataTable
				dataGridView1.DataSource = dataTable
			End If
		End Sub
	End Class
End Namespace

The above code imports data from Excel. To provide more context, let's assume we are designing a Windows Forms application with a DataGridView control and a button. The button will open a dialog to browse for the Excel file from a specific location. A custom code is added to filter and display only a limited number of files with a specific extension.

Open a dialog to select an Excel file

Then, the Load method is used to load data from the Excel file. This method allows loading data from various sources such as filenames, bytes, or streams. If the Excel file is password-protected, you can also pass the password as a parameter.

Once the Excel details are loaded into the IronXL object, the ToDataTable method is used to convert the Excel sheet data into a DataTable. Each sheet in the Excel file will be converted into a separate table.

Then, load the required table from the dataset into the DataGridView. To do this, the DataGridView's DataSource property is set to the newly created DataTable object from IronXL. This step allows the data population from the Excel file to the DataGridView.

Display data from the Excel file

Additionally, you may need to configure the DataGridView so it properly displays headers and the first row of data, depending on your specific requirements.

For more tutorials on using IronXL, click on this how-to guide.

Conclusion

IronXL is a highly popular Excel utility that does not rely on external libraries. It is an independent solution and does not require the installation of Microsoft Excel. It is compatible with various platforms.

With IronXL, you can accomplish a wide range of operations related to Microsoft Excel documents programmatically. You can perform tasks such as sorting strings or numbers, trimming and adding data, finding and replacing values, merging and unmerging cells, saving files, and more. It also allows you to validate spreadsheet data and define cell data types. Additionally, IronXL supports reading and writing CSV files.

IronXL is available for purchase with different licensing options. Customers also have the option to pay an annual membership fee for updates and product support. For an additional charge, IronXL provides unrestricted redistribution rights. To obtain more detailed pricing information, you can visit the licensing page.