Export DataGridView with column Total to Excel using ClosedXML in Windows Application

Hi smile,

I have modified your code please check it.

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using ClosedXML.Excel;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports ClosedXML.Excel

Code

C#

private void btnExport_Click(object sender, EventArgs e)
{
    try
    {
        decimal Recievable = 0;
        decimal Discount = 0;
        decimal Arrears = 0;
        decimal NetBal = 0;
        decimal Paid = 0;
        decimal RemBal = 0;
        DataTable dt = new DataTable();
        foreach (DataGridViewColumn column in dataGridView1.Columns)
        {
            dt.Columns.Add(column.HeaderText); //, column.ValueType
        }
        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            if (dataGridView1.Rows.Count - 1 > row.Index)
            {
                dt.Rows.Add();
                foreach (DataGridViewCell cell in row.Cells)
                {
                    dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
                }
            }
        }
        string folderPath = @"C:\Reports\";
        if (!Directory.Exists(folderPath))
        {
            Directory.CreateDirectory(folderPath);
        }
        using (XLWorkbook wb = new XLWorkbook())
        {
            string count = "";
            wb.Worksheets.Add(dt, "DefaulterStudents");
            wb.Worksheet(1).Cells("A1:S1").Style.Fill.BackgroundColor = XLColor.DarkGreen;
            for (int i = 1; i <= dt.Rows.Count; i++)
            {
                Recievable += Convert.ToDecimal(dataGridView1.Rows[i].Cells[2].Value);
                string cellRange = string.Format("A{0}:S{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;
                }

            }

            string cell = "L" + (dt.Rows.Count + 2) + ":L" + (dt.Rows.Count + 2);
            wb.Worksheet(1).Cells(cell).Value = Recievable;
            wb.Worksheet(1).Columns().AdjustToContents();
            wb.SaveAs(folderPath + "FeesCollectionHistory- " + DateTime.Now.ToShortDateString() + ".xlsx");
        }
        MessageBox.Show("Fees Collection History Data Exported Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }

    catch (Exception ex)
    {
        MessageBox.Show("Select All Checkbox to Export MS Excel.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    }
}

 VB.Net

Private Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs)
    Try
        Dim Recievable As Decimal = 0
        Dim Discount As Decimal = 0
        Dim Arrears As Decimal = 0
        Dim NetBal As Decimal = 0
        Dim Paid As Decimal = 0
        Dim RemBal As Decimal = 0
        Dim dt As DataTable = New DataTable()

        For Each column As DataGridViewColumn In dataGridView1.Columns
            dt.Columns.Add(column.HeaderText)
        Next

        For Each row As DataGridViewRow In dataGridView1.Rows
            If dataGridView1.Rows.Count - 1 > row.Index Then
                dt.Rows.Add()
                For Each cell As DataGridViewCell In row.Cells
                    dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value.ToString()
                Next
            End If
        Next

        Dim folderPath As String = "C:\Reports\"
        If Not Directory.Exists(folderPath) Then
            Directory.CreateDirectory(folderPath)
        End If

        Using wb As XLWorkbook = New XLWorkbook()
            Dim count As String = ""
            wb.Worksheets.Add(dt, "DefaulterStudents")
            wb.Worksheet(1).Cells("A1:S1").Style.Fill.BackgroundColor = XLColor.DarkGreen
            For i As Integer = 1 To dt.Rows.Count
                Recievable += Convert.ToDecimal(dataGridView1.Rows(i).Cells(2).Value)
                Dim cellRange As String = String.Format("A{0}:S{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

            Dim cell As String = "L" & (dt.Rows.Count + 2) & ":L" + (dt.Rows.Count + 2)
            wb.Worksheet(1).Cells(cell).Value = Recievable
            wb.Worksheet(1).Columns().AdjustToContents()
            wb.SaveAs(folderPath & "FeesCollectionHistory- " & DateTime.Now.ToShortDateString() & ".xlsx")
        End Using

        MessageBox.Show("Fees Collection History Data Exported Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
    Catch ex As Exception
        MessageBox.Show("Select All Checkbox to Export MS Excel.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    End Try
End Sub