How to Export Datagridview To Excel in C#

This tutorial provides a comprehensive guide on how to export data from a DataGridView control in a Windows Forms Application to an Excel file using the IronXL library in C#. Our focus will be on creating a seamless experience for beginners looking to integrate Microsoft Excel functionality into their C# projects.

How to Export DataGridView to Excel in C#

1. Create a C# Windows Forms project in Visual Studio.
2. Install the Excel Library using NuGet Package Manager.
3. Create a DataGridView and a button in the Windows Form UI.
4. Attach a data source to the DataGridView.
5. Apply the data export logic in the button's click event handler.

Getting Started with IronXL

IronXL is a robust library that simplifies the process of working with Excel documents in .NET Framework applications. It allows you to export large Excel files, handle complex data sources, and provide extensive support for .NET Excel file manipulation without needing to have the Microsoft Excel Object Library. Let's set up our project.

Setting up Your Project

1. Create a New Windows Forms Application

Open Visual Studio and create a new Windows Forms Application. This will be the base for our project where we will implement the functionality to export DataGridView data to an Excel file.

2. Install IronXL

IronXL can be easily added to your project. In Visual Studio, navigate to the 'Manage NuGet Packages' option, search for IronXL, and install it. This action will handle all the dependencies required to export DataGridView to Excel in C#.

3. Prepare the User Interface

Add a DataGridView control to your form. This control will hold the data that we intend to export. Additionally, add a button to trigger the export process. You can label it as 'Export to Excel' or something similar.

Implementing the Export Functionality

In this section, we'll teach the specifics of how to program the functionality that allows you to export data from a DataGridView in a Windows Forms application to an Excel document using IronXL.

Building the Application

Firstly, you need to add a DataGridView control to your form. This control is the primary component where your data will be displayed before being exported. You can easily add it by dragging a DataGridView from the toolbox in Visual Studio onto your form. We can also add a button to export DataGridView data to Excel files.

The next step is loading the data into the DataGridView. This data could come from various sources, such as a DataTable or a DataSet. If you're starting, you might want to create a new DataSet or DataTable programmatically and populate it with sample data to see how it works. Alternatively, you can import data from an external source. The key here is to bind your data source to the DataGridView, which effectively links your data to the grid. Once your data is bound to the DataGridView, you will see it displayed in the grid on your form.

private void BindDataToDataGridView()
{
    // Create a new DataTable.
    DataTable dataTable = new DataTable();
    // Define columns for the DataTable.
    dataTable.Columns.Add("Employee ID", typeof(int));
    dataTable.Columns.Add("Name", typeof(string));
    dataTable.Columns.Add("Department", typeof(string));
    dataTable.Columns.Add("Joining Date", typeof(DateTime));
    // Add sample data to the DataTable.
    for (int i = 1; i <= 25; i++)
    {
        dataTable.Rows.Add(i, "Employee " + i, "Department " + (i % 5 + 1), DateTime.Now.AddDays(-i * 15));
    }
    // Bind the DataTable to the DataGridView.
    dataGridView1.DataSource = dataTable;
}

private void BindDataToDataGridView()
{
    // Create a new DataTable.
    DataTable dataTable = new DataTable();
    // Define columns for the DataTable.
    dataTable.Columns.Add("Employee ID", typeof(int));
    dataTable.Columns.Add("Name", typeof(string));
    dataTable.Columns.Add("Department", typeof(string));
    dataTable.Columns.Add("Joining Date", typeof(DateTime));
    // Add sample data to the DataTable.
    for (int i = 1; i <= 25; i++)
    {
        dataTable.Rows.Add(i, "Employee " + i, "Department " + (i % 5 + 1), DateTime.Now.AddDays(-i * 15));
    }
    // Bind the DataTable to the DataGridView.
    dataGridView1.DataSource = dataTable;
}

Private Sub BindDataToDataGridView()
	' Create a new DataTable.
	Dim dataTable As New DataTable()
	' Define columns for the DataTable.
	dataTable.Columns.Add("Employee ID", GetType(Integer))
	dataTable.Columns.Add("Name", GetType(String))
	dataTable.Columns.Add("Department", GetType(String))
	dataTable.Columns.Add("Joining Date", GetType(DateTime))
	' Add sample data to the DataTable.
	For i As Integer = 1 To 25
		dataTable.Rows.Add(i, "Employee " & i, "Department " & (i Mod 5 + 1), DateTime.Now.AddDays(-i * 15))
	Next i
	' Bind the DataTable to the DataGridView.
	dataGridView1.DataSource = dataTable
End Sub

Handling the Button Click Event

Our primary interaction point with the user will be a button within our Windows Form. This button, when clicked, initiates the export process. Here's how you set up this interaction:

• In your Windows Form, you should have a button dedicated to exporting the DataGridView data.
• This button has an event handler method in your C# code. This method is triggered when the user clicks the button.

private void btnExport_Click(object sender, EventArgs e)
{
    // Code to export data will go here
}

private void btnExport_Click(object sender, EventArgs e)
{
    // Code to export data will go here
}

Private Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs)
	' Code to export data will go here
End Sub

Exporting Data from DataGridView

To export the DataGridView data, we need to first reference the IronXL namespace:

using IronXL;

using IronXL;

Imports IronXL

Within the event handler, the first step is to initialize a new workbook and worksheet using IronXL. This is where our data from the DataGridView will be transferred.

• A WorkBook object represents the entire Excel file.
• A WorkSheet within the workbook is like an individual page or tab in an Excel file.

Here, we create a workbook and then add a worksheet to it. The worksheet is named "ExportedData", but you can choose any name that suits your application's context.

WorkBook workbook = new WorkBook();
WorkSheet worksheet = workbook.CreateWorkSheet("ExportedData");

WorkBook workbook = new WorkBook();
WorkSheet worksheet = workbook.CreateWorkSheet("ExportedData");

Dim workbook As New WorkBook()
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("ExportedData")

Populating the Excel Sheet

The next step involves iterating through the rows and columns of your DataGridView and copying each cell's data into the corresponding cell in the Excel worksheet.

• The external loop progresses through each row, while the internal loop traverses through every column within the respective row.
• We assign each cell's value in the DataGridView to the corresponding cell in the Excel worksheet. This is done using the indices i (for rows) and j (for columns).

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    for (int j = 0; j < dataGridView1.Columns.Count; j++)
    {
        // Convert row and column index to Excel cell address format
        string cellAddress = ConvertToCellAddress(i, j);
        worksheet [cellAddress].Value = dataGridView1.Rows [i].Cells [j].Value.ToString();
    }
}

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    for (int j = 0; j < dataGridView1.Columns.Count; j++)
    {
        // Convert row and column index to Excel cell address format
        string cellAddress = ConvertToCellAddress(i, j);
        worksheet [cellAddress].Value = dataGridView1.Rows [i].Cells [j].Value.ToString();
    }
}

For i As Integer = 0 To dataGridView1.Rows.Count - 1
	For j As Integer = 0 To dataGridView1.Columns.Count - 1
		' Convert row and column index to Excel cell address format
		Dim cellAddress As String = ConvertToCellAddress(i, j)
		worksheet (cellAddress).Value = dataGridView1.Rows (i).Cells (j).Value.ToString()
	Next j
Next i

To convert the row and column indices to an Excel cell address, you can use a helper method like this:

private string ConvertToCellAddress(int row, int column)
{
    // Columns in Excel are labeled as A, B, C, ..., Z, AA, AB, ..., etc.
    // The following code converts a column index to this format.
    string columnLabel = "";
    while (column >= 0)
    {
        columnLabel = (char)('A' + column % 26) + columnLabel;
        column = column / 26 - 1;
    }
    // Rows in Excel are labeled as 1, 2, 3, ..., n
    // Adding 1 because Excel is 1-based and our loop is 0-based.
    string rowLabel = (row + 1).ToString();
    return columnLabel + rowLabel;
}

private string ConvertToCellAddress(int row, int column)
{
    // Columns in Excel are labeled as A, B, C, ..., Z, AA, AB, ..., etc.
    // The following code converts a column index to this format.
    string columnLabel = "";
    while (column >= 0)
    {
        columnLabel = (char)('A' + column % 26) + columnLabel;
        column = column / 26 - 1;
    }
    // Rows in Excel are labeled as 1, 2, 3, ..., n
    // Adding 1 because Excel is 1-based and our loop is 0-based.
    string rowLabel = (row + 1).ToString();
    return columnLabel + rowLabel;
}

Private Function ConvertToCellAddress(ByVal row As Integer, ByVal column As Integer) As String
	' Columns in Excel are labeled as A, B, C, ..., Z, AA, AB, ..., etc.
	' The following code converts a column index to this format.
	Dim columnLabel As String = ""
	Do While column >= 0
		columnLabel = ChrW(AscW("A"c) + column Mod 26) & columnLabel
		column = column \ 26 - 1
	Loop
	' Rows in Excel are labeled as 1, 2, 3, ..., n
	' Adding 1 because Excel is 1-based and our loop is 0-based.
	Dim rowLabel As String = (row + 1).ToString()
	Return columnLabel & rowLabel
End Function

Saving the Excel File

Once all the data is transferred, the next step is to save this workbook as an Excel file. This step creates an Excel file on your system in the specified path.

workbook.SaveAs("DataGridViewExport.xlsx");
MessageBox.Show("Data exported successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);

workbook.SaveAs("DataGridViewExport.xlsx");
MessageBox.Show("Data exported successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);

workbook.SaveAs("DataGridViewExport.xlsx")
MessageBox.Show("Data exported successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)

Error Handling

In any data export process, handling exceptions is vital to ensure the application's stability. Wrapping the export logic in a try-catch block ensures that any issues during the export (like file access permissions, data format issues, etc.) are caught and handled gracefully.

try
{
    // Export logic
}
catch (Exception ex)
{
    MessageBox.Show("An exception occurred: " + ex.Message);
}

try
{
    // Export logic
}
catch (Exception ex)
{
    MessageBox.Show("An exception occurred: " + ex.Message);
}

Try
	' Export logic
Catch ex As Exception
	MessageBox.Show("An exception occurred: " & ex.Message)
End Try

Output

Once we run the program, this interface will show up:

Here you can see that data is shown in the DataGridView. Now, click on the Export button. It will export the data from the DataGridView to the Excel file.

Here, you can see that a message box appears, and the data is exported to the Excel file. Here is the output Excel file:

Conclusion

By following this guide, you now have a basic yet powerful tool to export DataGridView data to an Excel file using C# and IronXL. This functionality is crucial for applications that require data analysis, reporting, or simply transferring data between different formats. Remember, experimenting with different features of the DataGridView control and IronXL library can lead to more customized and advanced implementations.

IronXL offers a free trial that you can use to explore its features. The licensing starts from $749, which is a worthwhile investment for professionals and organizations seeking a reliable and efficient solution for Excel file manipulation in .NET applications.

Your journey doesn't end here. Keep exploring more features of IronXL and other ways to enhance your Windows Forms applications. Happy coding!