How to Convert Excel to Datagridview in C#

This blog will compare and contrast how to interact with Microsoft Excel documents programmatically in .NET technologies, either in the CSV format or the standard Excel format, using one of the most popular libraries, IronXL.

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 for this tutorial 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 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 code 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.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Window;
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(); 

            if (Result == DialogResult.OK)
            {
                ImportFilePath = openFileDialog1.FileName;
            }
            if(ImportFilePath!=string.Empty)
            {
                var exceldoc=IronXL.WorkBook.LoadExcel(ImportFilePath);
                var Edataset = exceldoc.ToDataSet().Tables [0];
                for (int i = 0; i < Edataset.Columns.Count; i++)
                {
                    Edataset.Columns [i].ColumnName = Edataset.Rows [0].ItemArray [i].ToString();
                    //dataGridView1.Rows [i].HeaderCell.Value = Edataset.Rows [0].ItemArray [i];
                }
                dataGridView1.DataSource = Edataset;
                dataGridView1.Rows.RemoveAt(0);
            }
        }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Window;
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(); 

            if (Result == DialogResult.OK)
            {
                ImportFilePath = openFileDialog1.FileName;
            }
            if(ImportFilePath!=string.Empty)
            {
                var exceldoc=IronXL.WorkBook.LoadExcel(ImportFilePath);
                var Edataset = exceldoc.ToDataSet().Tables [0];
                for (int i = 0; i < Edataset.Columns.Count; i++)
                {
                    Edataset.Columns [i].ColumnName = Edataset.Rows [0].ItemArray [i].ToString();
                    //dataGridView1.Rows [i].HeaderCell.Value = Edataset.Rows [0].ItemArray [i];
                }
                dataGridView1.DataSource = Edataset;
                dataGridView1.Rows.RemoveAt(0);
            }
        }
    }
}

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Windows.Forms
Imports System.Windows.Forms.VisualStyles.VisualStyleElement.Window
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()

			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 Edataset = exceldoc.ToDataSet().Tables (0)
				For i As Integer = 0 To Edataset.Columns.Count - 1
					Edataset.Columns (i).ColumnName = Edataset.Rows (0).ItemArray (i).ToString()
					'dataGridView1.Rows [i].HeaderCell.Value = Edataset.Rows [0].ItemArray [i];
				Next i
				dataGridView1.DataSource = Edataset
				dataGridView1.Rows.RemoveAt(0)
			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 LoadExcel method is used to load data from the Excel file. This method allows to load data from various sources such as filenames, bytes, or streams. If the Excel file is password-protected, we can also pass the password as a parameter.

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

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, the Excel header name is also assigned to the DataGridView header. Furthermore, we removed any additional information column from the DataTable that contains the Excel column as row data. This ensures that only the relevant data is displayed in the DataGridView.

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 for $599. 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.