How to Import Excel to GridView in ASP .NET C# with IronXL
Let's be honest: trying to import Excel to GridView in ASP .NET C# used to be a headache. Between fighting with OLEDB connection strings and worrying about whether the right drivers are installed on your server, a "simple" data import can quickly turn into a long afternoon of debugging.
It doesn't have to be that way. Using IronXL, you can skip the complex configurations and bridge the gap between your spreadsheets and your UI in just a few lines of code. This guide will show you how to pull Excel data directly into a DataTable and bind it to a GridView, making your data-handling seamless and, dare we say, enjoyable.
Try it yourself: Grab a free trial of IronXL to follow along with the code below.
What Is the Fastest Way to Load Excel Data into a GridView?
The fastest method to load data from Excel files into a GridView involves using IronXL's ToDataTable method. This approach eliminates complex connection strings and driver dependencies that traditional methods require. Simply load the workbook, select the worksheet, convert to DataTable, and assign it as the GridView's data source.
using IronXL;
using System.Data;
// Load the Excel file
WorkBook workBook = WorkBook.Load("data.xlsx");
// Access the first sheet
WorkSheet sheet = workBook.DefaultWorkSheet;
// Convert to DataTable for GridView binding
DataTable dataTable = sheet.ToDataTable(true);using IronXL;
using System.Data;
// Load the Excel file
WorkBook workBook = WorkBook.Load("data.xlsx");
// Access the first sheet
WorkSheet sheet = workBook.DefaultWorkSheet;
// Convert to DataTable for GridView binding
DataTable dataTable = sheet.ToDataTable(true);Imports IronXL
Imports System.Data
' Load the Excel file
Dim workBook As WorkBook = WorkBook.Load("data.xlsx")
' Access the first sheet
Dim sheet As WorkSheet = workBook.DefaultWorkSheet
' Convert to DataTable for GridView binding
Dim dataTable As DataTable = sheet.ToDataTable(True)💡 Pro Tip: When using sheet.ToDataTable(true), IronXL automatically treats your first row as headers. This saves you the trouble of manually naming columns in your code!
The ToDataTable method accepts a boolean parameter that specifies whether the first row contains header information. When set to true, column names are assigned from the first row of your Excel worksheet. IronXL supports both XLS and XLSX files seamlessly, handling cell data and formatting automatically.
How Do You Create an ASP.NET Project to Import Excel Data?
Setting up your ASP.NET project requires adding IronXL through NuGet Package Manager. In Visual Studio, create a new ASP.NET Web Forms Application, then install the IronXL library. The installation process adds all necessary references to your project automatically.
Install-Package IronXL.Excel
After installation, add the IronXL namespace to your code-behind file along with System.Data for DataTable support. Your license key can be applied at application startup or directly in the page load event.
How Can You Import Excel Files to GridView in ASP.NET C#?
The following code demonstrates a complete implementation for uploading an Excel file and displaying its contents in a GridView control. The page includes a FileUpload control, an upload button, and a GridView to display the imported data.
ASPX Markup:
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" /><asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" /><asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" />Code-Behind (C#):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using IronXL;
namespace ExcelImportTest
{
public partial class _Default : System.Web.UI.Page
{
// Add this property if it does not exist
protected global::System.Web.UI.WebControls.GridView GridView1;
protected global::System.Web.UI.WebControls.FileUpload FileUpload1;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
try
{
string path = Path.Combine(Server.MapPath("~/Uploads/"), FileUpload1.FileName);
FileUpload1.SaveAs(path);
WorkBook workBook = WorkBook.Load(path);
WorkSheet sheet = workBook.WorkSheets[0];
// 1. Manually create a DataTable
DataTable dt = new DataTable();
// 2. Create Columns based on the first row of Excel
var firstRow = sheet.Rows.First();
foreach (var cell in firstRow)
{
dt.Columns.Add(cell.Value.ToString());
}
// 3. Manually add the data rows (skipping the header row)
foreach (var row in sheet.Rows.Skip(1))
{
DataRow dr = dt.NewRow();
int colIndex = 0;
foreach (var cell in row)
{
dr[colIndex] = cell.Value?.ToString();
colIndex++;
}
dt.Rows.Add(dr);
}
// 4. Force the GridView to refresh
GridView1.DataSource = dt;
GridView1.DataBind();
Session["CurrentData"] = dt;
}
catch (Exception ex)
{
Response.Write("Manual Load Error: " + ex.Message);
}
}
}
}
}using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using IronXL;
namespace ExcelImportTest
{
public partial class _Default : System.Web.UI.Page
{
// Add this property if it does not exist
protected global::System.Web.UI.WebControls.GridView GridView1;
protected global::System.Web.UI.WebControls.FileUpload FileUpload1;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
try
{
string path = Path.Combine(Server.MapPath("~/Uploads/"), FileUpload1.FileName);
FileUpload1.SaveAs(path);
WorkBook workBook = WorkBook.Load(path);
WorkSheet sheet = workBook.WorkSheets[0];
// 1. Manually create a DataTable
DataTable dt = new DataTable();
// 2. Create Columns based on the first row of Excel
var firstRow = sheet.Rows.First();
foreach (var cell in firstRow)
{
dt.Columns.Add(cell.Value.ToString());
}
// 3. Manually add the data rows (skipping the header row)
foreach (var row in sheet.Rows.Skip(1))
{
DataRow dr = dt.NewRow();
int colIndex = 0;
foreach (var cell in row)
{
dr[colIndex] = cell.Value?.ToString();
colIndex++;
}
dt.Rows.Add(dr);
}
// 4. Force the GridView to refresh
GridView1.DataSource = dt;
GridView1.DataBind();
Session["CurrentData"] = dt;
}
catch (Exception ex)
{
Response.Write("Manual Load Error: " + ex.Message);
}
}
}
}
}Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.IO
Imports IronXL
Namespace ExcelImportTest
Partial Public Class _Default
Inherits System.Web.UI.Page
' Add this property if it does not exist
Protected GridView1 As Global.System.Web.UI.WebControls.GridView
Protected FileUpload1 As Global.System.Web.UI.WebControls.FileUpload
Protected Sub Page_Load(sender As Object, e As EventArgs)
End Sub
Protected Sub btnUpload_Click(sender As Object, e As EventArgs)
If FileUpload1.HasFile Then
Try
Dim path As String = Path.Combine(Server.MapPath("~/Uploads/"), FileUpload1.FileName)
FileUpload1.SaveAs(path)
Dim workBook As WorkBook = WorkBook.Load(path)
Dim sheet As WorkSheet = workBook.WorkSheets(0)
' 1. Manually create a DataTable
Dim dt As New DataTable()
' 2. Create Columns based on the first row of Excel
Dim firstRow = sheet.Rows.First()
For Each cell In firstRow
dt.Columns.Add(cell.Value.ToString())
Next
' 3. Manually add the data rows (skipping the header row)
For Each row In sheet.Rows.Skip(1)
Dim dr As DataRow = dt.NewRow()
Dim colIndex As Integer = 0
For Each cell In row
dr(colIndex) = If(cell.Value?.ToString(), Nothing)
colIndex += 1
Next
dt.Rows.Add(dr)
Next
' 4. Force the GridView to refresh
GridView1.DataSource = dt
GridView1.DataBind()
Session("CurrentData") = dt
Catch ex As Exception
Response.Write("Manual Load Error: " & ex.Message)
End Try
End If
End Sub
End Class
End NamespaceOutput
This implementation handles the complete import process. When users click the upload button, the file saves to a server folder, IronXL loads the workbook and accesses the default worksheet, then the DataTable populates the grid. The AutoGenerateColumns property creates columns automatically based on the Excel sheet structure.
For projects requiring access to specific sheets rather than the first sheet, use the GetWorkSheet method with the sheet name as a string parameter.
How Do You Access Specific Excel Sheets and Columns?
IronXL provides flexible methods to access particular worksheets and cell ranges within your Excel file. When working with workbooks containing multiple tables across different sheets, specify exactly which data to import.
// Access worksheet by name
WorkSheet sheet = workBook.GetWorkSheet("SalesData");
// Access specific cell range
var range = sheet["A1:D10"];
// Get dataset from entire workbook (all sheets)
DataSet dataSet = workBook.ToDataSet();// Access worksheet by name
WorkSheet sheet = workBook.GetWorkSheet("SalesData");
// Access specific cell range
var range = sheet["A1:D10"];
// Get dataset from entire workbook (all sheets)
DataSet dataSet = workBook.ToDataSet();' Access worksheet by name
Dim sheet As WorkSheet = workBook.GetWorkSheet("SalesData")
' Access specific cell range
Dim range = sheet("A1:D10")
' Get dataset from entire workbook (all sheets)
Dim dataSet As DataSet = workBook.ToDataSet()Output
The ToDataSet method converts all worksheets into a DataSet containing multiple DataTable objects. Each DataTable corresponds to one Excel sheet, making it simple to populate multiple grids or process data from various sheets independently. You can iterate through rows and columns to access individual cell values or apply formatting as needed.
How Can You Export GridView Data Back to Excel?
Exporting data from your GridView back to an Excel file completes the round-trip workflow. IronXL makes this process straightforward by accepting DataTable input and creating properly formatted spreadsheet files.
protected void btnExport_Click(object sender, EventArgs e)
{
// Create new workbook and worksheet
WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.CreateWorkSheet("ExportedData");
// Get DataTable from GridView
DataTable dt = (DataTable)GridView1.DataSource;
// Fill worksheet with header row
for (int col = 0; col < dt.Columns.Count; col++)
{
workSheet.SetCellValue(0, col, dt.Columns[col].ColumnName);
}
// Fill data rows
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int col = 0; col < dt.Columns.Count; col++)
{
workSheet.SetCellValue(row + 1, col, dt.Rows[row][col].ToString());
}
}
// Save to server and provide download
string exportPath = Server.MapPath("~/Exports/export.xlsx");
workBook.SaveAs(exportPath);
}protected void btnExport_Click(object sender, EventArgs e)
{
// Create new workbook and worksheet
WorkBook workBook = WorkBook.Create();
WorkSheet workSheet = workBook.CreateWorkSheet("ExportedData");
// Get DataTable from GridView
DataTable dt = (DataTable)GridView1.DataSource;
// Fill worksheet with header row
for (int col = 0; col < dt.Columns.Count; col++)
{
workSheet.SetCellValue(0, col, dt.Columns[col].ColumnName);
}
// Fill data rows
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int col = 0; col < dt.Columns.Count; col++)
{
workSheet.SetCellValue(row + 1, col, dt.Rows[row][col].ToString());
}
}
// Save to server and provide download
string exportPath = Server.MapPath("~/Exports/export.xlsx");
workBook.SaveAs(exportPath);
}Protected Sub btnExport_Click(sender As Object, e As EventArgs)
' Create new workbook and worksheet
Dim workBook As WorkBook = WorkBook.Create()
Dim workSheet As WorkSheet = workBook.CreateWorkSheet("ExportedData")
' Get DataTable from GridView
Dim dt As DataTable = DirectCast(GridView1.DataSource, DataTable)
' Fill worksheet with header row
For col As Integer = 0 To dt.Columns.Count - 1
workSheet.SetCellValue(0, col, dt.Columns(col).ColumnName)
Next
' Fill data rows
For row As Integer = 0 To dt.Rows.Count - 1
For col As Integer = 0 To dt.Columns.Count - 1
workSheet.SetCellValue(row + 1, col, dt.Rows(row)(col).ToString())
Next
Next
' Save to server and provide download
Dim exportPath As String = Server.MapPath("~/Exports/export.xlsx")
workBook.SaveAs(exportPath)
End SubOutput
This export method creates a new workbook, transfers the DataTable contents including column headers, and saves the result. The process preserves your data structure while generating valid XLSX files that open correctly in Microsoft Excel and other spreadsheet applications.
Conclusion
IronXL streamlines the process of working with Excel files in ASP.NET applications. The library's intuitive API handles XLS file and XLSX files formats, imports Excel data to Data Table objects for easy GridView binding, and supports exporting data back to spreadsheet format. For clarification on advanced features like cell formatting, formulas, or working with large datasets, explore the IronXL documentation.
Ready to implement Excel-GridView integration in your web application? Purchase a license to unlock full production capabilities, or contact our team for assistance with your specific requirements.
Get started for FREE
Get started for FREE
Book Free Live Demo
No credit card or account creation required
