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

Last Reply 22 days ago By arunkurmi

Posted 22 days ago

I am exporting data from datagridview to Excel with row total in windows form.

Everything is working fine for me but there is an issue. I am defining Cell Range manually for GrandTotal while exporting data. 

Example:

I have 35 rows to export in excel. then i would define the cell range like this:

wb.Worksheet(1).Cells("L36:L36").Value = Recievable;  

if there are 40 rows to export then it would be written like as

wb.Worksheet(1).Cells("L41:L41").Value = Recievable;  

I want that Row total must be automatically inserted at the end of row in Excel in Bold Font.

here is my code:

       private void mSExcelToolStripMenuItem_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 dGVExam.Columns)
               {
                   dt.Columns.Add(column.HeaderText); //, column.ValueType
               }
               foreach (DataGridViewRow row in dGVExam.Rows)
               {
                   if (dGVExam.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:S1").Style.Fill.BackgroundColor = XLColor.DarkGreen;
                   for (int i = 1; i <= dt.Rows.Count; i++)
                   {
                       Recievable += Convert.ToDecimal(dGVExam.Rows[i].Cells[11].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;
                       }
                       
                   }
                   wb.Worksheet(1).Cells("L41:L41").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);
           }
       }
Posted 22 days ago Modified on 21 days ago

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