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

Last Reply 3 months ago By dharmendr

Posted 3 months ago

How do i force a subtotal only on column j50 .vat onj51 and total j52.

If there is a continuation to sheet 2 then only subtotal on sheet 1 and on sheet 2 subtotal +vat = total  

        private void button1_Click_1(object sender, EventArgs e)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            string filePath = @"C:\MyCedarData\masterquote.xltx";
            xlWorkBook = xlApp.Workbooks.Add(filePath);
            
            string datestr = DateTime.Now.ToShortDateString();
            string filename = @"C:\MyCedarQuotes\Quote_" + txtName.Text.Replace(" ", "") + "_" + datestr + ".xls";
            string quote = txtName.Text.Replace(" ", "") + "_" + datestr;
            string salesman = txtEmail.Text.ToUpper();
            string customer = txtName.Text.ToUpper();
            string custname = txtCustName.Text.ToUpper();
            int currentSheet = 1;
            int cell = 0;
            int rowsToDisplay = 30;
            int rowsToStart = 19;
            for (int row = 0; row < dataGridView1.Rows.Count -1; 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(xlSheets[xlWorkBook.Sheets.Count], misValue, misValue, misValue);
                    xlWorkSheet.Name = "Sheet" + currentSheet.ToString();
                }
                    xlWorkSheet.get_Range("C10", "E10").Value2 = salesman;
                    xlWorkSheet.get_Range("C12", "E12").Value2 = customer;
                    xlWorkSheet.get_Range("C14", "E14").Value2 = custname;
                    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();
                        }
                    }
                    if ((row + 1) % rowsToDisplay == 0)
                    {
                        currentSheet++;
                        cell = 0;
                    }
                    else
                    {
                        cell++;
                    }
            }
            if (System.IO.File.Exists(filename))
            {
                System.IO.File.Delete(filename);
            }
            
             
            xlWorkBook.SaveAs(filename, 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 c:\\quote.xls");
        }

From the datagridview1 (dgv1) on form 2, I export the items to an excel template. The dgv1 can vary from 1 item to 59 items.

The excel template starts at row A19 TO J19. 30 Rows can thereafter be added to the template. The last row being A49 TO J49

If there are more items I want the subtotal at J50 AND the balance of the items is them exported to sheet2 at row A19. I NEED the subtotal to continue to sheet 2. 

If less items to sheet1 then subtotal on J50, VAT ON J51 and Total on J52.

Here is the link to see sheet1

 

Posted 3 months ago Modified on 2 months ago

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

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