Export DataGridView to excel with Subtotal and Grand Total in different sheet using C# and VB.Net in Windows Forms Application

Hi shabzo,

Refer the below sample.

Database

For this sample I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

C#

public Form1()
{
    InitializeComponent();
    this.BindDataGridView();
}
private void BindDataGridView()
{
    SqlConnection con;
    string str = "data source=.;initial catalog=Northwind;user id=sa;password=password;";
    con = new SqlConnection(str);
    con.Open();
    SqlDataAdapter dscmd = new SqlDataAdapter("SELECT TOP 33 CustomerID,ShipName,ShipAddress,ShipCity,ShipCountry,Freight FROM Orders", con);
    DataTable dt = new DataTable();
    dscmd.Fill(dt);
    this.dataGridView1.DataSource = dt;
}
private void btnExportExcel_Click(object sender, EventArgs e)
{
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    string sourcePath = @"C:\Users\dharmendra\Desktop\masterquote.xlsx";
    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(sourcePath);

    string datestr = DateTime.Now.ToShortDateString();
    string destinationPath = @"C:\Users\dharmendra\Desktop\Quote_" + "Name_" + datestr.Replace('/', '_') + ".xls";
    string quote = "Test quote" + "_" + datestr;
    string salesman = "Test salesman".ToUpper();
    string customer = "Test customer".ToUpper();

    int currentSheet = 1;
    int cell = 0;
    int rowsToDisplay = 30;
    int rowsToStart = 19;

    decimal grandTotal = 0;
    decimal vatPer = 14;
    for (int row = 0; row < dataGridView1.Rows.Count; row++)
    {
        if (currentSheet <= xlWorkBook.Sheets.Count)
        {
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(currentSheet);
        }
        else
        {
            var xlSheets = xlWorkBook.Sheets as Excel.Sheets;
            xlWorkSheet = (Excel.Worksheet)xlSheets.Add(misValue, xlSheets[xlWorkBook.Sheets.Count]);
            xlWorkSheet.Name = "Sheet" + currentSheet.ToString();
        }
        xlWorkSheet.get_Range("C10", "E10").Value2 = salesman;
        xlWorkSheet.get_Range("C12", "E12").Value2 = customer;
        xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
        xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
        xlWorkSheet.get_Range("H12", "I12").Value2 = DateTime.Now;
        xlWorkSheet.get_Range("H10", "I10").Value2 = quote;

        for (int column = 0; column < dataGridView1.Columns.Count; column++)
        {
            if (column == 2 || column == 5)
            {
                xlWorkSheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Value.ToString();
            }
            else
            {
                xlWorkSheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Value.ToString();
            }
        }
        grandTotal += Convert.ToDecimal(dataGridView1.Rows[row].Cells[5].Value);
        if ((row + 1) % rowsToDisplay == 0)
        {
            xlWorkSheet.get_Range("E50", "E50").Value2 = "Sub Total";
            xlWorkSheet.get_Range("F50", "F50").Value2 = "=SUM(F19:F48)";
            currentSheet++;
            cell = 0;
        }
        else if (row + 1 == dataGridView1.Rows.Count)
        {
            xlWorkSheet.get_Range("E50", "E50").Value2 = "Sub Total";
            xlWorkSheet.get_Range("F50", "F50").Value2 = "=SUM(F19:F48)";

            xlWorkSheet.get_Range("E51", "E51").Value2 = "Transport";
            xlWorkSheet.get_Range("F51", "F51").Value2 = 0;

            xlWorkSheet.get_Range("E52", "E52").Value2 = "Excl Vat";
            xlWorkSheet.get_Range("F52", "F52").Value2 = grandTotal;

            decimal vatAmount = grandTotal * (vatPer / 100);

            xlWorkSheet.get_Range("E53", "E53").Value2 = "Vat @14%";
            xlWorkSheet.get_Range("F53", "F53").Value2 = vatAmount;

            xlWorkSheet.get_Range("E54", "E54").Value2 = "Total";
            xlWorkSheet.get_Range("F54", "F54").Value2 = grandTotal + vatAmount;
        }
        else
        {
            cell++;
        }
    }
    if (System.IO.File.Exists(destinationPath))
    {
        System.IO.File.Delete(destinationPath);
    }

    xlWorkBook.SaveAs(destinationPath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();
    //releaseObject(xlWorkSheet);
    //releaseObject(xlWorkBook);
    //releaseObject(xlApp);
    MessageBox.Show("Excel file created , you can find the file in " + destinationPath);
}

VB.Net

Public Sub New()
    InitializeComponent()
    Me.BindDataGridView()
End Sub
Private Sub BindDataGridView()
    Dim con As SqlConnection
    Dim str As String = "data source=.;initial catalog=Northwind;user id=sa;password=password;"
    con = New SqlConnection(str)
    con.Open()
    Dim dscmd As New SqlDataAdapter("SELECT TOP 33 CustomerID,ShipName,ShipAddress,ShipCity,ShipCountry,Freight FROM Orders", con)
    Dim dt As New DataTable()
    dscmd.Fill(dt)
    Me.dataGridView1.DataSource = dt
End Sub
Private Sub btnExportExcel_Click(sender As Object, e As EventArgs) Handles btnExportExcel.Click
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value

    Dim sourcePath As String = "C:\Users\dharmendra\Desktop\masterquote.xlsx"
    xlApp = New Excel.Application()
    xlWorkBook = xlApp.Workbooks.Add(sourcePath)

    Dim datestr As String = DateTime.Now.ToShortDateString()
    Dim destinationPath As String = "C:\Users\dharmendra\Desktop\Quote_" + "Name_" + datestr.Replace("/"c, "_"c) + ".xls"
    Dim quote As String = Convert.ToString("Test quote" + "_") & datestr
    Dim salesman As String = "Test salesman".ToUpper()
    Dim customer As String = "Test customer".ToUpper()

    Dim currentSheet As Integer = 1
    Dim cell As Integer = 0
    Dim rowsToDisplay As Integer = 30
    Dim rowsToStart As Integer = 19

    Dim grandTotal As Decimal = 0
    Dim vatPer As Decimal = 14
    For row As Integer = 0 To dataGridView1.Rows.Count - 1
        If currentSheet <= xlWorkBook.Sheets.Count Then
            xlWorkSheet = xlWorkBook.Worksheets.Item(currentSheet)
        Else
            Dim xlSheets = TryCast(xlWorkBook.Sheets, Excel.Sheets)
            xlWorkSheet = DirectCast(xlSheets.Add(misValue, xlSheets(xlWorkBook.Sheets.Count)), Excel.Worksheet)
            xlWorkSheet.Name = "Sheet" + currentSheet.ToString()
        End If
        xlWorkSheet.Range("C10", "E10").Value2 = salesman
        xlWorkSheet.Range("C12", "E12").Value2 = customer
        xlWorkSheet.Range("C12", "E12").Font.Bold = True
        xlWorkSheet.Range("C12", "E12").Font.Bold = True
        xlWorkSheet.Range("H12", "I12").Value2 = DateTime.Now
        xlWorkSheet.Range("H10", "I10").Value2 = quote

        For column As Integer = 0 To dataGridView1.Columns.Count - 1
            If column = 2 OrElse column = 5 Then
                xlWorkSheet.Cells(cell + rowsToStart, column + 1) = dataGridView1.Rows(row).Cells(column).Value.ToString()
            Else
                xlWorkSheet.Cells(cell + rowsToStart, column + 1) = dataGridView1.Rows(row).Cells(column).Value.ToString()
            End If
        Next
        grandTotal += Convert.ToDecimal(dataGridView1.Rows(row).Cells(5).Value)
        If (row + 1) Mod rowsToDisplay = 0 Then
            xlWorkSheet.Range("E50", "E50").Value2 = "Sub Total"
            xlWorkSheet.Range("F50", "F50").Value2 = "=SUM(F19:F48)"
            currentSheet += 1
            cell = 0
        ElseIf row + 1 = dataGridView1.Rows.Count Then
            xlWorkSheet.Range("E50", "E50").Value2 = "Sub Total"
            xlWorkSheet.Range("F50", "F50").Value2 = "=SUM(F19:F48)"

            xlWorkSheet.Range("E51", "E51").Value2 = "Transport"
            xlWorkSheet.Range("F51", "F51").Value2 = 0

            xlWorkSheet.Range("E52", "E52").Value2 = "Excl Vat"
            xlWorkSheet.Range("F52", "F52").Value2 = grandTotal

            Dim vatAmount As Decimal = grandTotal * (vatPer / 100)

            xlWorkSheet.Range("E53", "E53").Value2 = "Vat @14%"
            xlWorkSheet.Range("F53", "F53").Value2 = vatAmount

            xlWorkSheet.Range("E54", "E54").Value2 = "Total"
            xlWorkSheet.Range("F54", "F54").Value2 = grandTotal + vatAmount
        Else
            cell += 1
        End If
    Next
    If System.IO.File.Exists(destinationPath) Then
        System.IO.File.Delete(destinationPath)
    End If

    xlWorkBook.SaveAs(destinationPath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, _
        Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
    xlWorkBook.Close(True, misValue, misValue)
    xlApp.Quit()
    MessageBox.Show(Convert.ToString("Excel file created , you can find the file in ") & destinationPath)
End Sub

Screenshot