In this article I will explain with an example, how to export DataGridView data to Excel file with formatting i.e. Colors and Styles in Windows Forms (WinForms) Applications using C# and VB.Net.
DataGridView cannot be exported directly to Excel file and hence need to generate a DataTable and export the DataTable to Excel file.
The DataTable will be exported to a formatted Excel file using ClosedXml library which is a wrapper of OpenXml.
Database
I have made use of the following table Customers with the schema as follows.
![Export DataGridView to Excel with Formatting using C# and VB.Net]()
I have already inserted few records in the table.
![Export DataGridView to Excel with Formatting using C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Download DocumentFormat.OpenXml and ClosedXml Libraries
You can download the libraries using the following download locations.
Note: The DLL files of both OpenXml and ClosedXml are present in the attached sample.
Form Controls
I have added a DataGridView and a Button to the Windows Form.
![Export DataGridView to Excel with Formatting using C# and VB.Net]()
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using ClosedXML.Excel;
using System.Data.SqlClient;
VB.Net
Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
Imports System.Data.SqlClient
Populating DataGridView
Inside the Form Load event, the DataGridView is populated with records from Customers table.
C#
private void Form1_Load(object sender, EventArgs e)
{
this.BindDataGridView();
}
private void BindDataGridView()
{
string constring = @"Data Source=.\SQL2014;Initial Catalog=AjaxSamples;Integrated Security=true";
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", con))
{
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
}
}
}
}
VB.Net
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Me.BindDataGridView()
End Sub
Private Sub BindDataGridView()
Dim constring As String = "Data Source=.\SQL2014;Initial Catalog=AjaxSamples;Integrated Security=true"
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("SELECT * FROM Customers", con)
cmd.CommandType = CommandType.Text
Using sda As New SqlDataAdapter(cmd)
Using dt As New DataTable()
sda.Fill(dt)
dataGridView1.DataSource = dt
End Using
End Using
End Using
End Using
End Sub
![Export DataGridView to Excel with Formatting using C# and VB.Net]()
Exporting DataGridView data to Excel with formatting
Inside the Button Click event handler, first a DataTable is created with columns same as that of the DataGridView and a loop is executed over the DataGridView rows and all the data is added to the DataTable.
Then a Workbook object is created to which the DataTable is added as Worksheet using the Add method which accepts DataTable and the name of the Sheet as parameters.
Once the DataTable is added as a Worksheet to the Workbook, the formatting is done by first setting the Header row background color and then applying background colors to the rows and the alternating rows of the Excel file.
Finally the WorkBook is saved to the specified location on the disk.
C#
private void btnExportExcel_Click(object sender, EventArgs e)
{
//Creating DataTable.
DataTable dt = new DataTable();
//Adding the Columns.
foreach (DataGridViewColumn column in dataGridView1.Columns)
{
dt.Columns.Add(column.HeaderText, column.ValueType);
}
//Adding the Rows.
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:\\Excel\\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Customers");
//Set the color of Header Row.
//A resembles First Column while C resembles Third column.
wb.Worksheet(1).Cells("A1:C1").Style.Fill.BackgroundColor = XLColor.DarkGreen;
for (int i = 1; i <= dt.Rows.Count; i++)
{
//A resembles First Column while C resembles Third column.
//Header row is at Position 1 and hence First row starts from Index 2.
string cellRange = string.Format("A{0}:C{0}", i + 1);
if (i % 2 != 0)
{
wb.Worksheet(1).Cells(cellRange).Style.Fill.BackgroundColor = XLColor.GreenYellow;
}
else
{
wb.Worksheet(1).Cells(cellRange).Style.Fill.BackgroundColor = XLColor.Yellow;
}
}
//Adjust widths of Columns.
wb.Worksheet(1).Columns().AdjustToContents();
//Save the Excel file.
wb.SaveAs(folderPath + "DataGridViewExport.xlsx");
}
}
VB.Net
Private Sub btnExportExcel_Click(sender As Object, e As EventArgs) Handles btnExportExcel.Click
'Creating DataTable.
Dim dt As New DataTable()
'Adding the Columns.
For Each column As DataGridViewColumn In dataGridView1.Columns
dt.Columns.Add(column.HeaderText, column.ValueType)
Next
'Adding the Rows.
For Each row As DataGridViewRow In dataGridView1.Rows
dt.Rows.Add()
For Each cell As DataGridViewCell In row.Cells
dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value.ToString()
Next
Next
'Exporting to Excel.
Dim folderPath As String = "C:\Excel\"
If Not Directory.Exists(folderPath) Then
Directory.CreateDirectory(folderPath)
End If
Using wb As New XLWorkbook()
wb.Worksheets.Add(dt, "Customers")
'Set the color of Header Row.
'A resembles First Column while C resembles Third column.
wb.Worksheet(1).Cells("A1:C1").Style.Fill.BackgroundColor = XLColor.DarkGreen
For i As Integer = 1 To dt.Rows.Count
'A resembles First Column while C resembles Third column.
'Header row is at Position 1 and hence First row starts from Index 2.
Dim cellRange As String = String.Format("A{0}:C{0}", i + 1)
If i Mod 2 <> 0 Then
wb.Worksheet(1).Cells(cellRange).Style.Fill.BackgroundColor = XLColor.GreenYellow
Else
wb.Worksheet(1).Cells(cellRange).Style.Fill.BackgroundColor = XLColor.Yellow
End If
Next
'Adjust widths of Columns.
wb.Worksheet(1).Columns().AdjustToContents()
'Save the Excel file.
wb.SaveAs(folderPath & "DataGridViewExport.xlsx")
End Using
End Sub
Screenshot
![Export DataGridView to Excel with Formatting using C# and VB.Net]()
Downloads
