Published May 24, 2023
How to Convert Excel to Datagridview in C#
Introduction
Microsoft created the spreadsheet program Excel for Windows, macOS, Android, iOS, and iPadOS. It has calculating and computing capabilities, graphing tools, pivot tables, and the Visual Basic for Applications (VBA) macro programming language. The Microsoft 365 software suite includes Excel. To organize data manipulations, such as arithmetic operations, spreadsheets like Excel use a grid of cells organized in numbered rows and letter-named columns. It has a wide range of integrated functionalities to handle financial, engineering, and statistical requirements. Additionally, it offers a fully featured three-dimensional graphical display and can present data as line graphs, histograms, and charts. Data can be divided into sections to analyze how different factors affect it from various perspectives.
We 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 here.
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. In this tutorial, we will be using the Windows Forms Application for this tutorial to simplify things.
After choosing your project template, provide the name and location of the project.
Next, pick the following structure. This project will make use of .NET Framework 4.7.
After generating the project files, you can open the Form1.cs file in 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
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.
For our form, we have placed a DataGridView control 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 us 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 NamespaceThe above code helps us import 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 allow us to browse for the Excel file from a specific location. We have also added some custom code to filter and display only a limited number of files with a specific extension.
Then, we use the IronXL.WorkBook.LoadExcel method to load data from the Excel file. This method allows us 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, we can use the ToDataSet method 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.
We can then load the required table from the dataset into the DataGridView. To do this, we set the DataGridView's DataSource property to the newly created DataTable object from IronXL. This step allows us to populate the DataGridView with the data from the Excel file.
Additionally, we have added some code that will convert the Excel header name 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 here.
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 $749. 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 relevant source by clicking here.
