Export DataGridView with Calculation in Footer to Excel using C# and VB.Net in Windows Application

smile
 
on Oct 13, 2019 04:32 AM
Sample_114845.zip
3046 Views

How to export DataGridView with Calculation in Footer to Excel in Windows Form

I want to export data from datagridview to excel with Calculation in Footer in the same way like following link

Export GridView with Calculation in Footer to Excel in ASP.Net

how it can be done in windows form???

Download FREE API for Word, Excel and PDF in ASP.Net: Download
pandeyism
 
on Oct 14, 2019 01:11 AM

Hi smile,

Refer below sample.

For sum calculation in DataTable column refer below article.

Calculate Sum (Total) of DataTable Columns using C# and VB.Net

Namespaces

C#

using System.Data;
using System.IO;
using System.Windows.Forms;
using ClosedXML.Excel;

VB.Net

Imports System.Data
Imports System.IO
Imports System.Windows.Forms
Imports ClosedXML.Excel

Code

C#

private void Form1_Load(object sender, EventArgs e)
    {
        BindDataGrid();
    }

    private void BindDataGrid()
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[] { 
            new DataColumn("AdmissionNo", typeof(string)),
            new DataColumn("SName",typeof(string)),
            new DataColumn("FName",typeof(string)),
            new DataColumn("Year",typeof(int)),
            new DataColumn("ClassID", typeof(int)),
            new DataColumn("ClassName",typeof(string)),
            new DataColumn("SectionID",typeof(int)),
            new DataColumn("SectionName",typeof(string)),
            new DataColumn("FPhone", typeof(string)),
            new DataColumn("DueDate",typeof(string)),
            new DataColumn("Fees",typeof(int)),
            new DataColumn("Others",typeof(int)),
            new DataColumn("Recievable", typeof(int)),
            new DataColumn("Arrears",typeof(string)),
            new DataColumn("NetBal",typeof(int)),
            new DataColumn("Month",typeof(string)) });

        dt.Rows.Add("R-000001", "f1", "l1", 2019, 84, "Nursery", 265, "Red", "1111", "14-10-2019", 1300, 0, 1300, "0", 1300, "");
        dt.Rows.Add("R-000002", "f2", "l2", 2019, 84, "Nursery", 268, "Pink", "2222", "14-10-2019", 1300, 0, 1300, "0", 1300, "");

        DataTable sTable = dt;

        foreach (DataRow dr in sTable.Rows)
        {
            dr["NetBal"] = Convert.ToInt16(dr["Fees"]) + Convert.ToInt16(dr["Others"]) + Convert.ToInt16(dr["Recievable"]) + Convert.ToInt16(dr["Arrears"]);
        }
        dataGridView1.DataSource = sTable;
        dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0].Value = "Total";

        int total = Convert.ToInt32(dt.Compute("SUM(NetBal)", string.Empty));
        dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[14].Value = total;
    }

    private void button1_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        string folderPath = "C:\\Excel\\";
        if (!Directory.Exists(folderPath))
        {
            Directory.CreateDirectory(folderPath);
        }
        for (int i = 0; i < dataGridView1.Columns.Count; i++)
        {
            dt.Columns.Add(dataGridView1.Columns[i].Name);
        }
        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            dt.Rows.Add();
            for (int i = 0; i < row.Cells.Count; i++)
            {
                dt.Rows[row.Index][i] = row.Cells[i].Value;
            }
        }

        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(dt, "Customers");
            wb.SaveAs(folderPath + "DataGridViewExport.xlsx");
        }
    }

VB.Net

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    BindDataGrid()
End Sub

Private Sub BindDataGrid()
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("AdmissionNo", GetType(String)), New DataColumn("SName", GetType(String)), New DataColumn("FName", GetType(String)), New DataColumn("Year", GetType(Integer)), New DataColumn("ClassID", GetType(Integer)), New DataColumn("ClassName", GetType(String)), New DataColumn("SectionID", GetType(Integer)), New DataColumn("SectionName", GetType(String)), New DataColumn("FPhone", GetType(String)), New DataColumn("DueDate", GetType(String)), New DataColumn("Fees", GetType(Integer)), New DataColumn("Others", GetType(Integer)), New DataColumn("Recievable", GetType(Integer)), New DataColumn("Arrears", GetType(String)), New DataColumn("NetBal", GetType(Integer)), New DataColumn("Month", GetType(String))})
    dt.Rows.Add("R-000001", "f1", "l1", 2019, 84, "Nursery", 265, "Red", "1111", "14-10-2019", 1300, 0, 1300, "0", 1300, "")
    dt.Rows.Add("R-000002", "f2", "l2", 2019, 84, "Nursery", 268, "Pink", "2222", "14-10-2019", 1300, 0, 1300, "0", 1300, "")
    Dim sTable As DataTable = dt

    For Each dr As DataRow In sTable.Rows
        dr("NetBal") = Convert.ToInt16(dr("Fees")) + Convert.ToInt16(dr("Others")) + Convert.ToInt16(dr("Recievable")) + Convert.ToInt16(dr("Arrears"))
    Next

    dataGridView1.DataSource = sTable
    dataGridView1.Rows(dataGridView1.Rows.Count - 1).Cells(0).Value = "Total"
    Dim total As Integer = Convert.ToInt32(dt.Compute("SUM(NetBal)", String.Empty))
    dataGridView1.Rows(dataGridView1.Rows.Count - 1).Cells(14).Value = total
End Sub

Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click
    Dim dt As DataTable = New DataTable()
    Dim folderPath As String = "C:\Excel\"

    If Not Directory.Exists(folderPath) Then
        Directory.CreateDirectory(folderPath)
    End If

    For i As Integer = 0 To dataGridView1.Columns.Count - 1
        dt.Columns.Add(dataGridView1.Columns(i).Name)
    Next

    For Each row As DataGridViewRow In dataGridView1.Rows
        dt.Rows.Add()
        For i As Integer = 0 To row.Cells.Count - 1
            dt.Rows(row.Index)(i) = row.Cells(i).Value
        Next
    Next

    Using wb As XLWorkbook = New XLWorkbook()
        wb.Worksheets.Add(dt, "Customers")
        wb.SaveAs(folderPath & "DataGridViewExport.xlsx")
    End Using
End Sub

Screenshot

The Form

Excel