How to export DataGridView to Excel?

6 mins read

Syncfusion Essential XlsIO is a .NET Excel library used to create, read, and edit Excel documents. Using this library, you can export DataGridView to an Excel document in C# and VB.NET.

Microsoft DataGridView control is a new control which has numerous features than DataGrid control like sorting, etc. This article explains how the data can be exported from DataGridView to Excel with its column header and cell formatting using XlsIO.

Steps to export DataGridView to an Excel file programmatically:

Create a new Windows Forms application.

Create Windows Forms application in Visual Studio

Create a console application

Install Syncfusion.XlsIO.WinForms NuGet package as reference to your .NET Framework applications from the NuGet.org.

Add XlsIO reference to the project

Install NuGet package

Initialize the DataGridView object and set data source in Form1.cs.
Include the following namespace to create new DataGridView object.

using System.Windows.Forms;

Include the following namespace for XlsIO.

using Syncfusion.XlsIO;

After the initialization set valid data sources to the DataGridView’s DataSource property. Here the DataTable object has been used for the DataSource property.

//Initialize DataGridView control
DataGridView dataGridView = new DataGridView();
 
//Assign data source
dataGridView.DataSource = GetDataTable();

Add a new button to export DataGridView to Excel.

private Button btnCreate;
private Label label;
private GroupBox groupBox;
 
private void InitializeComponent()
{
  btnCreate = new Button();
  label = new Label();
  groupBox = new GroupBox();
 
  //Button
  btnCreate.Location = new System.Drawing.Point(339, 280);
  btnCreate.Size = new System.Drawing.Size(115, 26);
  btnCreate.Text = "Create";
  btnCreate.Click += new EventHandler(btnCreate_Click);
   
  //Label
  label.Location = new System.Drawing.Point(0, 50);
  label.Size = new System.Drawing.Size(426, 48);
  label.Text = "Click the button to view an Excel spreadsheet generated by Essential XlsIO. Please note that MS Excel Viewer or MS Excel is required to view the resultant document.";
  label.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
       
  //Group Box
  groupBox.Location = new System.Drawing.Point(12, 100);
  groupBox.Size = new System.Drawing.Size(442, 151);
  groupBox.Text = "DataGridView";
 
  //DataGridView to Excel 
  ClientSize = new System.Drawing.Size(466, 333);
  Controls.Add(groupBox);
  Controls.Add(label);
  Controls.Add(btnCreate);
  Text = "DataGridView to Excel";
}

Use the following code snippet to create a new Excel workbook.

//Initialize the Excel Engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Initialize Application
  IApplication application = excelEngine.Excel;
 
  //Set default version for application
  application.DefaultVersion = ExcelVersion.Xlsx;
 
  //Create a new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
 
  //Accessing first worksheet in the workbook
  IWorksheet worksheet = workbook.Worksheets[0];
}

To export data from DataGridView object to Excel worksheet use the ImportDataGridView() method to export the data from DataGridView to Excel worksheet with its column header and cell formatting.

//Import data from DataGridView control
worksheet.ImportDataGridView(dataGridView, 1, 1, true, true);

Use the following complete code in btnCreate_Click to export data from DataGridView to Excel using XlsIO.

//Initialize the Excel Engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Initialize DataGridView control
  DataGridView dataGridView = new DataGridView();
 
  //Assign data source
  dataGridView.DataSource = GetDataTable();
 
  //Add control to group box
  groupBox.Controls.Add(dataGridView);
 
  //Assign the datagridview size
  dataGridView.Size = new System.Drawing.Size(441, 150);
 
  //Initialize Application
  IApplication application = excelEngine.Excel;
 
  //Set default version for application
  application.DefaultVersion = ExcelVersion.Xlsx;
 
  //Create a new workbook
  IWorkbook workbook = application.Workbooks.Create(1);
 
  //Accessing first worksheet in the workbook
  IWorksheet worksheet = workbook.Worksheets[0];
 
  //Import data from DataGridView control
  worksheet.ImportDataGridView(dataGridView, 1, 1, true, true);
 
  //Save the workbook
  workbook.SaveAs("Output.xlsx");
}

A complete working example to export DataGridView to Excel in C# is present on this GitHub page.

We recommend you to walkthrough the documentation for importing data from other grid controls, data objects and exporting operations available with XlsIO for various data objects (DataTable, CLR Objects, etc.,).

An online sample link to import from DataGrid.

Note:

Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer to link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.

Did you find this information helpful?

Yes

Comments (0)

How to export DataGridView to Excel?

Steps to export DataGridView to an Excel file programmatically:

Access denied