Export DataGridView with grand Total to Excel file using ClosedXml in Windows Application

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on Oct 01, 2020 11:40 PM
Forum: Windows Basics
Answer: 1
Views: 4471
Sample Code: Download

I displayed the data in the datagridview with checkbox.It contains 5 rows. sceenshoot is attached.

Now I want to export this data in excel with Total in the last row.

it is working fine. but there is an issue. it is exporting 4 rows instead of 5 row. Why this is missing one row?? where is the error and how to fix it??

for this purpose, i wrote the following code

        private void mSExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                decimal Fee = 0;
                decimal Others = 0;
                decimal Recievable = 0;
                decimal Arrears = 0;
                decimal NetBal = 0;
                
                DataTable dt = new DataTable();
                foreach (DataGridViewColumn column in dGVStu.Columns)
                {
                    dt.Columns.Add(column.HeaderText); //, column.ValueType
                }
                foreach (DataGridViewRow row in dGVStu.Rows)
                {
                    if (dGVStu.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())
                {
                    wb.Worksheets.Add(dt, "DefaulterStudents");
                    wb.Worksheet(1).Cells("A1:R1").Style.Fill.BackgroundColor = XLColor.DarkGreen;
                    for (int i = 1; i <= dt.Rows.Count; i++)
                    {
                        Fee += Convert.ToDecimal(dGVStu.Rows[i].Cells[14].Value);
                        Others += Convert.ToDecimal(dGVStu.Rows[i].Cells[15].Value);
                        Recievable += Convert.ToDecimal(dGVStu.Rows[i].Cells[16].Value);
                        //Arrears += Convert.ToDecimal(dGVStu.Rows[i].Cells[15].Value);
                        Arrears += Convert.ToDecimal(dGVStu.Rows[i].Cells[17].Value == DBNull.Value ? "0" : dGVStu.Rows[i].Cells[17].Value.ToString());
                        NetBal += Convert.ToDecimal(dGVStu.Rows[i].Cells[18].Value);

                      
                        string cellRange = string.Format("A{0}:R{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 Fe = "O" + (dt.Rows.Count + 2) + ":O" + (dt.Rows.Count + 2);
                    wb.Worksheet(1).Cells(Fe).Style.Font.Bold = true;
                    wb.Worksheet(1).Cells(Fe).Value = Fee;

                    string Oth = "P" + (dt.Rows.Count + 2) + ":P" + (dt.Rows.Count + 2);
                    wb.Worksheet(1).Cells(Oth).Style.Font.Bold = true;
                    wb.Worksheet(1).Cells(Oth).Value = Others;

                    string Reci = "Q" + (dt.Rows.Count + 2) + ":Q" + (dt.Rows.Count + 2);
                    wb.Worksheet(1).Cells(Reci).Style.Font.Bold = true;
                    wb.Worksheet(1).Cells(Reci).Value = Recievable;

                    string Arr = "R" + (dt.Rows.Count + 2) + ":R" + (dt.Rows.Count + 2);
                    wb.Worksheet(1).Cells(Arr).Style.Font.Bold = true;
                    wb.Worksheet(1).Cells(Arr).Value = Arrears;

                    string Net = "S" + (dt.Rows.Count + 2) + ":S" + (dt.Rows.Count + 2);
                    wb.Worksheet(1).Cells(Net).Style.Font.Bold = true;
                    wb.Worksheet(1).Cells(Net).Value = NetBal;                   

                    wb.Worksheet(1).Columns().AdjustToContents();
                    wb.SaveAs(folderPath + "DefaulterStudents- " + DateTime.Now.ToShortDateString() + ".xlsx");
                }
                MessageBox.Show("Defaulter Student's Data Exported Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 8

Reply
ANSWER
User: dharmendr
Replied: on Oct 03, 2020 02:00 AM
Modified: on Oct 06, 2020 01:23 AM
Report

Refer the updated code.

In the code i am ignoring the CheckBox column while exportiong to Excel.

Namespaces

using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using ClosedXML.Excel;

VB.Net

Imports System.Data
Imports System.Diagnostics
Imports System.IO
Imports System.Linq
Imports ClosedXML.Excel

Code

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
        this.BindDataGridView();
    }

    private void BindDataGridView()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("AdmissionNo");
        dt.Columns.Add("ReferenceNo");
        dt.Columns.Add("SName");
        dt.Columns.Add("FName");
        dt.Columns.Add("ParentID");
        dt.Columns.Add("Year");
        dt.Columns.Add("ClassID");
        dt.Columns.Add("ClassName");
        dt.Columns.Add("SectionID");
        dt.Columns.Add("SectionName");
        dt.Columns.Add("FPhone");
        dt.Columns.Add("IssueDate");
        dt.Columns.Add("DueDate");
        dt.Columns.Add("Fees");
        dt.Columns.Add("Others");
        dt.Columns.Add("Recievable");
        dt.Columns.Add("Arrears");
        dt.Columns.Add("NetBal");

        dt.Rows.Add("R-000580", 0, "Meerab", "", 0, 2020, 83, "Play Group", 232, "A", null, "02-11-2020", "02-10-2020", 500, 0, 0, 0, 0);
        dt.Rows.Add("R-000581", 0, "Aiza Batool", "Zeshan Haider", 0, 2020, 83, "Play Group", 232, "A", "923092196747", "02-11-2020", "02-10-2020", 450, 0, 100, 10, 90);
        dt.Rows.Add("R-000629", 0, "Minahil Shabbir", "", 0, 2020, 83, "Play Group", 232, "A", null, "02-11-2020", "02-10-2020", 100, 0, 100, 0, 100);
        dt.Rows.Add("R-000659", 0, "Maham Babar", "Babar Hussain", 0, 2020, 83, "Play Group", 232, "A", "923244954683", "02-11-2020", "02-10-2020", 500, 5, 0, 0, 0);
        dt.Rows.Add("R-000660", 0, "Zainab Babar", "Babar Hussain", 0, 2020, 83, "Play Group", 232, "A", "923244954683", "02-11-2020", "02-10-2020", 500, 0, 0, 50, 450);

        dGVStu.DataSource = dt;
        dGVStu.AllowUserToAddRows = true;

        DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
        checkBoxColumn.HeaderText = "";
        checkBoxColumn.Width = 30;
        checkBoxColumn.Name = "checkBoxColumn";
        dGVStu.Columns.Insert(0, checkBoxColumn);
    }

    private void btnExportExcel_Click(object sender, EventArgs e)
    {
        try
        {
            decimal Fee = 0;
            decimal Others = 0;
            decimal Recievable = 0;
            decimal Arrears = 0;
            decimal NetBal = 0;
            DataTable dt = new DataTable();
            foreach (DataGridViewColumn column in dGVStu.Columns)
            {
                if (column.Index > 0)
                {
                    dt.Columns.Add(column.HeaderText);
                }
            }

            foreach (DataGridViewRow row in dGVStu.Rows)
            {
                if (dGVStu.Rows.Count - 1 > row.Index)
                {
                    dt.Rows.Add();
                    foreach (DataGridViewCell cell in row.Cells)
                    {
                        if (cell.ColumnIndex > 0)
                        {
                            dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex - 1] = cell.Value.ToString();
                        }
                    }
                }
            }

            string folderPath = @"C:\Reports\";
            if (!Directory.Exists(folderPath))
            {
                Directory.CreateDirectory(folderPath);
            }
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dt, "DefaulterStudents");
                wb.Worksheet(1).Cells("A1:R1").Style.Fill.BackgroundColor = XLColor.DarkGreen;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    Fee += Convert.ToDecimal(dGVStu.Rows[i].Cells[14].Value);
                    Others += Convert.ToDecimal(dGVStu.Rows[i].Cells[15].Value);
                    Recievable += Convert.ToDecimal(dGVStu.Rows[i].Cells[16].Value);
                    Arrears += Convert.ToDecimal(dGVStu.Rows[i].Cells[17].Value == DBNull.Value
                        ? "0" : dGVStu.Rows[i].Cells[17].Value.ToString());
                    NetBal += Convert.ToDecimal(dGVStu.Rows[i].Cells[18].Value);

                    string cellRange = string.Format("A{0}:R{0}", i + 2);
                    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 Fe = "N" + (dt.Rows.Count + 2) + ":N" + (dt.Rows.Count + 2);
                wb.Worksheet(1).Cells(Fe).Style.Font.Bold = true;
                wb.Worksheet(1).Cells(Fe).Value = Fee;
                wb.Worksheet(1).Cells(Fe).Style.Fill.BackgroundColor = XLColor.Red;

                string Oth = "O" + (dt.Rows.Count + 2) + ":O" + (dt.Rows.Count + 2);
                wb.Worksheet(1).Cells(Oth).Style.Font.Bold = true;
                wb.Worksheet(1).Cells(Oth).Value = Others;
                wb.Worksheet(1).Cells(Oth).Style.Fill.BackgroundColor = XLColor.Red;

                string Reci = "P" + (dt.Rows.Count + 2) + ":P" + (dt.Rows.Count + 2);
                wb.Worksheet(1).Cells(Reci).Style.Font.Bold = true;
                wb.Worksheet(1).Cells(Reci).Value = Recievable;
                wb.Worksheet(1).Cells(Reci).Style.Fill.BackgroundColor = XLColor.Red;

                string Arr = "Q" + (dt.Rows.Count + 2) + ":Q" + (dt.Rows.Count + 2);
                wb.Worksheet(1).Cells(Arr).Style.Font.Bold = true;
                wb.Worksheet(1).Cells(Arr).Value = Arrears;
                wb.Worksheet(1).Cells(Arr).Style.Fill.BackgroundColor = XLColor.Red;

                string Net = "R" + (dt.Rows.Count + 2) + ":R" + (dt.Rows.Count + 2);
                wb.Worksheet(1).Cells(Net).Style.Font.Bold = true;
                wb.Worksheet(1).Cells(Net).Value = NetBal;
                wb.Worksheet(1).Cells(Net).Style.Fill.BackgroundColor = XLColor.Red;

                wb.Worksheet(1).Columns().AdjustToContents();
                wb.Worksheet(1).Tables.FirstOrDefault().ShowAutoFilter = false;
                wb.SaveAs(folderPath + "DefaulterStudents-" + DateTime.Now.ToString("MM-dd-yyyy") + ".xlsx");
            }
            MessageBox.Show("Defaulter Student's Data Exported Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            // Opening the generated file from path.
            Process.Start(folderPath + "DefaulterStudents-" + DateTime.Now.ToString("MM-dd-yyyy") + ".xlsx");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString(), "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }
    }
}

VB.Net

Public Class Form1

    Public Sub New()
        InitializeComponent()
        Me.BindDataGridView()
    End Sub

    Private Sub BindDataGridView()
        Dim dt As DataTable = New DataTable()
        dt.Columns.Add("AdmissionNo")
        dt.Columns.Add("ReferenceNo")
        dt.Columns.Add("SName")
        dt.Columns.Add("FName")
        dt.Columns.Add("ParentID")
        dt.Columns.Add("Year")
        dt.Columns.Add("ClassID")
        dt.Columns.Add("ClassName")
        dt.Columns.Add("SectionID")
        dt.Columns.Add("SectionName")
        dt.Columns.Add("FPhone")
        dt.Columns.Add("IssueDate")
        dt.Columns.Add("DueDate")
        dt.Columns.Add("Fees")
        dt.Columns.Add("Others")
        dt.Columns.Add("Recievable")
        dt.Columns.Add("Arrears")
        dt.Columns.Add("NetBal")
        dt.Rows.Add("R-000580", 0, "Meerab", "", 0, 2020, 83, "Play Group", 232, "A", Nothing, "02-11-2020", "02-10-2020", 500, 0, 0, 0, 0)
        dt.Rows.Add("R-000581", 0, "Aiza Batool", "Zeshan Haider", 0, 2020, 83, "Play Group", 232, "A", "923092196747", "02-11-2020", "02-10-2020", 450, 0, 100, 10, 90)
        dt.Rows.Add("R-000629", 0, "Minahil Shabbir", "", 0, 2020, 83, "Play Group", 232, "A", Nothing, "02-11-2020", "02-10-2020", 100, 0, 100, 0, 100)
        dt.Rows.Add("R-000659", 0, "Maham Babar", "Babar Hussain", 0, 2020, 83, "Play Group", 232, "A", "923244954683", "02-11-2020", "02-10-2020", 500, 5, 0, 0, 0)
        dt.Rows.Add("R-000660", 0, "Zainab Babar", "Babar Hussain", 0, 2020, 83, "Play Group", 232, "A", "923244954683", "02-11-2020", "02-10-2020", 500, 0, 0, 50, 450)
        Me.dGVStu.DataSource = dt
        dGVStu.AllowUserToAddRows = True

        Dim checkBoxColumn As DataGridViewCheckBoxColumn = New DataGridViewCheckBoxColumn()
        checkBoxColumn.HeaderText = ""
        checkBoxColumn.Width = 30
        checkBoxColumn.Name = "checkBoxColumn"
        dGVStu.Columns.Insert(0, checkBoxColumn)
    End Sub

    Private Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExportExcel.Click
        Try
            Dim Fee As Decimal = 0
            Dim Others As Decimal = 0
            Dim Recievable As Decimal = 0
            Dim Arrears As Decimal = 0
            Dim NetBal As Decimal = 0
            Dim dt As DataTable = New DataTable()

            For Each column As DataGridViewColumn In dGVStu.Columns
                If column.Index > 0 Then
                    dt.Columns.Add(column.HeaderText)
                End If
            Next

            For Each row As DataGridViewRow In dGVStu.Rows
                If dGVStu.Rows.Count - 1 > row.Index Then
                    dt.Rows.Add()
                    For Each cell As DataGridViewCell In row.Cells
                        If cell.ColumnIndex > 0 Then
                            dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex - 1) = cell.Value.ToString()
                        End If
                    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()
                wb.Worksheets.Add(dt, "DefaulterStudents")
                wb.Worksheet(1).Cells("A1:R1").Style.Fill.BackgroundColor = XLColor.DarkGreen

                For i As Integer = 0 To dt.Rows.Count - 1
                    Fee += Convert.ToDecimal(dGVStu.Rows(i).Cells(14).Value)
                    Others += Convert.ToDecimal(dGVStu.Rows(i).Cells(15).Value)
                    Recievable += Convert.ToDecimal(dGVStu.Rows(i).Cells(16).Value)
                    Arrears += Convert.ToDecimal(If(IsDBNull(dGVStu.Rows(i).Cells(17).Value), "0", dGVStu.Rows(i).Cells(17).Value.ToString()))
                    NetBal += Convert.ToDecimal(dGVStu.Rows(i).Cells(18).Value)
                    Dim cellRange As String = String.Format("A{0}:R{0}", i + 2)

                    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 Fe As String = "N" & (dt.Rows.Count + 2) & ":N" & (dt.Rows.Count + 2)
                wb.Worksheet(1).Cells(Fe).Style.Font.Bold = True
                wb.Worksheet(1).Cells(Fe).Value = Fee
                wb.Worksheet(1).Cells(Fe).Style.Fill.BackgroundColor = XLColor.Red
                Dim Oth As String = "O" & (dt.Rows.Count + 2) & ":O" & (dt.Rows.Count + 2)
                wb.Worksheet(1).Cells(Oth).Style.Font.Bold = True
                wb.Worksheet(1).Cells(Oth).Value = Others
                wb.Worksheet(1).Cells(Oth).Style.Fill.BackgroundColor = XLColor.Red
                Dim Reci As String = "P" & (dt.Rows.Count + 2) & ":P" & (dt.Rows.Count + 2)
                wb.Worksheet(1).Cells(Reci).Style.Font.Bold = True
                wb.Worksheet(1).Cells(Reci).Value = Recievable
                wb.Worksheet(1).Cells(Reci).Style.Fill.BackgroundColor = XLColor.Red
                Dim Arr As String = "Q" & (dt.Rows.Count + 2) & ":Q" & (dt.Rows.Count + 2)
                wb.Worksheet(1).Cells(Arr).Style.Font.Bold = True
                wb.Worksheet(1).Cells(Arr).Value = Arrears
                wb.Worksheet(1).Cells(Arr).Style.Fill.BackgroundColor = XLColor.Red
                Dim Net As String = "R" & (dt.Rows.Count + 2) & ":R" & (dt.Rows.Count + 2)
                wb.Worksheet(1).Cells(Net).Style.Font.Bold = True
                wb.Worksheet(1).Cells(Net).Value = NetBal
                wb.Worksheet(1).Cells(Net).Style.Fill.BackgroundColor = XLColor.Red
                wb.Worksheet(1).Columns().AdjustToContents()
                wb.Worksheet(1).Tables.FirstOrDefault().ShowAutoFilter = False
                wb.SaveAs(folderPath & "DefaulterStudents-" & DateTime.Now.ToString("MM-dd-yyyy") & ".xlsx")
            End Using
            MessageBox.Show("Defaulter Student's Data Exported Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
            ' Opening the generated file from path.
            Process.Start(folderPath & "DefaulterStudents-" & DateTime.Now.ToString("MM-dd-yyyy") & ".xlsx")
        Catch ex As Exception
            MessageBox.Show(ex.ToString(), "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub
End Class

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Export DataGridView with grand Total to Excel file using ClosedXml in Windows Application

Company

Explore

Follow us

Contact Us