In this article, I will explain you, How to export data of DataGridView to Excel file. In this article, I will use OpenXML and ClosedXml libraries for export data. We can also export data of DataGridView using Foreach loop by fetching each row of it. To do this task, first of all Download two assemblies i.e :
Now, Add windows form into your project. Add a DataGridView and single button on it.
using System.Windows.Forms;
using System.IO;
using System.Data;
using System.Reflection;
using ClosedXML.Excel;
namespace Export_DataTable_Excel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
this.BindData();
}
private void BindData()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("StudentId", typeof(int)),
new DataColumn("StudentName", typeof(string)),
new DataColumn("StudentCity",typeof(string)) });
dt.Rows.Add(1, "Jacob lefore", "US");
dt.Rows.Add(2, "Ammey smith", "UK");
dt.Rows.Add(3, "Bill Smith", "Fr");
dt.Rows.Add(4, "Robert", "Ru");
this.dataGridView1.DataSource = dt;
}
private void ExportExcel_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
foreach (DataGridViewColumn column in dataGridView1.Columns)
{
dt.Columns.Add(column.HeaderText, column.ValueType);
}
foreach (DataGridViewRow row in dataGridView1.Rows)
{
dt.Rows.Add();
foreach (DataGridViewCell cell in row.Cells)
{
dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
}
}
//Exporting to Excel
string folderPath = "C:\\my\\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
using (XLWorkbook xlfile = new XLWorkbook())
{
xlfile.Worksheets.Add(dt, "students");
xlfile.SaveAs(folderPath + "data.xlsx");
}
}
}
}
Code Generates the following output
