Previously i had to select data from sqlite but there is no change to the data So i decided to import excel sheet to form1 datagridview with a qty column set to zeroOn change of qty i want those rows to copy to form 2 datagridviewThis is the excel file data to importprodcodeproductpackqtypricediscvatamttotal1234soap50150015 4567towel10270015 however i have managed to do this and will appreciate it if you would assist in solving how to get it to excel file with subtotals and vatplease che

Export DataGridView to Excel with Subtotal and vat in Windows Application

gmsahmod

SOLVED CLOSED ACTIVE ANSWERED
User: gmsahmod
Posted: on Feb 28, 2021 10:25 PM
Forum: Windows Basics
Answer: 1
Views: 1723

Previously i had to select data from sqlite but there is no change to the data. So i decided to import excel sheet to form1 datagridview with a qty column set to zero.

On change of qty i want those rows to copy to form 2 datagridview

This is the excel file data to import.

prodcode	product	pack	qty	price	disc	vatamt	total
1234	soap	5	0	15.00	15
4567	towel	1	0	27.00	15

however i have managed to do this and will appreciate it if you would assist in solving how to get it to excel file with subtotals and vat.

please check my code if it needs updating.

using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;
 
namespace Excelsior
{
    public partial class Form5 : Form
    {
        public Form5(DataTable dt1)
        {
            InitializeComponent();
            this.dataGridView1.DataSource = dt1;
        }
 
        private void Form5_Load(object sender, EventArgs e)
        {
            this.dataGridView1.Columns[0].Width = 150;
            this.dataGridView1.Columns[1].Width = 250;
            this.dataGridView1.Columns[2].Width = 40;
            this.dataGridView1.Columns[3].Width = 50;
            this.dataGridView1.Columns[4].Width = 58;
            this.dataGridView1.Columns[5].Width = 50;
            this.dataGridView1.Columns[6].Width = 50;
            this.dataGridView1.Columns[6].DefaultCellStyle.Format = "R0.00";
             
        }
        static string UppercaseWords(string value)
        {
            char[] a = value.ToLower().ToCharArray();
 
            for (int i = 0; i < a.Length; i++)
            {
                a[i] = i == 0 || a[i - 1] == ' ' ? char.ToUpper(a[i]) : a[i];
 
            }
 
            return new string(a);
        }
 
        private void button1_Click(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:\MyExcelsiorData\test2.xltx";
            xlWorkBook = xlApp.Workbooks.Add(filePath);
 
            string datestr = DateTime.Now.ToShortDateString();
            string datestr1 = DateTime.Now.ToShortTimeString();
            string fileName = @"C:\MyQuotes\Quote_" + txtName.Text.Replace(" ", "") + " " + datestr.Replace('/', '_')+ " " + datestr1.Replace(':', '_') + ".xlsx";
            //string fileName = @"C:\MyQuotes\Quote_" + txtName.Text.Replace(" ", "") + " " + datestr.Replace('/', '_') + " " + datestr1.Replace(':', '_') + ".xls";
            string quote = txtName.Text.Replace(" ", "") + " " + datestr.Replace('/', '_') + " " + datestr1.Replace(':', '_');
            string salesman = txtEmail.Text.ToUpper();
            string customer = txtName.Text.ToUpper();
            string contact = txtContact.Text.ToUpper();
            //string delivery = txtDel.Text.ToUpper();
            string address2 = txtDel2.Text.ToUpper();
            string address3 = txtDel3.Text.ToUpper();
            string note = txtNote.Text.ToUpper();
            string trans = comboBox1.SelectedItem.ToString();
 
 
            int currentSheet = 1;
            int cell = 0;
            int rowsToDisplay = 28;
            int rowsToStart = 20;
 
            //decimal grandTotal = 0;
            //decimal vatPer = 15;
            //double incVat = 1.5;
            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("E11", "E11").Value2 = salesman;
                xlWorkSheet.get_Range("A12", "A12").Value2 = customer;
                xlWorkSheet.get_Range("A13", "A13").Value2 = address2;
                xlWorkSheet.get_Range("A14", "A14").Value2 = address3;
                xlWorkSheet.get_Range("B11", "B11").Value2 = contact;
                
                xlWorkSheet.get_Range("D12", "D12").Font.Bold = true;
                
                xlWorkSheet.get_Range("B17", "B17").Value2 = DateTime.Now;
                xlWorkSheet.get_Range("E17", "G17").Value2 = quote;
                xlWorkSheet.get_Range("E13", "H13").Value2 = note;
                xlWorkSheet.get_Range("C17", "C17").Value2 = trans;
                
 
 
                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[6].Value/1.15);
                if ((row + 1) % rowsToDisplay == 0)
                {
                    xlWorkSheet.get_Range("F49", "F49").Value2 = "total";
                    xlWorkSheet.get_Range("G49", "G49").Value2 = "=SUM(G20:G48)/1.15";
                    currentSheet++;
                    cell = 0;
                }
                else if (row + 1 == dataGridView1.Rows.Count)
                {
                    
                    xlWorkSheet.get_Range("F49", "F49").Value2 = "total";
                    xlWorkSheet.get_Range("G49", "G49").Value2 = "=SUM(G20:G48)/1.15";
 
                    xlWorkSheet.get_Range("F50", "F50").Value2 = "Vat @15%";
                    xlWorkSheet.get_Range("G50", "G50").Value2 = "=SUM(F20:F48)+(SUM(F20:F48)*15%)";
 
                    xlWorkSheet.get_Range("F51", "F51").Value2 = "Total";
                    xlWorkSheet.get_Range("G51", "G51").Value2 = "=SUM(G49:G50)";
                }
                else
                {
                    cell++;
                }
            }
            if (System.IO.File.Exists(fileName))
            {
                System.IO.File.Delete(fileName);
 
            }
 
 
            xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            //xlWorkBook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, filename);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
 
            //releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
            MessageBox.Show("Excel file created , you can find the file c:\\MyQuotes" + fileName);
        }
 
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
 
        }
 
    }
}

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

dharmend...

0 6

Reply
ANSWER
User: dharmendr
Replied: on Mar 02, 2021 06:14 AM
Report

gmsahmod says:
        private void button1_Click(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:\MyExcelsiorData\test2.xltx";

            xlWorkBook = xlApp.Workbooks.Add(filePath);

            string datestr = DateTime.Now.ToShortDateString();

            string datestr1 = DateTime.Now.ToShortTimeString();

            string fileName = @"C:\MyQuotes\Quote_" + txtName.Text.Replace(" ", "") + " " + datestr.Replace('/', '_')+ " " + datestr1.Replace(':', '_') + ".xlsx";

            //string fileName = @"C:\MyQuotes\Quote_" + txtName.Text.Replace(" ", "") + " " + datestr.Replace('/', '_') + " " + datestr1.Replace(':', '_') + ".xls";

            string quote = txtName.Text.Replace(" ", "") + " " + datestr.Replace('/', '_') + " " + datestr1.Replace(':', '_');

            string salesman = txtEmail.Text.ToUpper();

            string customer = txtName.Text.ToUpper();

            string contact = txtContact.Text.ToUpper();

            //string delivery = txtDel.Text.ToUpper();

            string address2 = txtDel2.Text.ToUpper();

            string address3 = txtDel3.Text.ToUpper();

            string note = txtNote.Text.ToUpper();

            string trans = comboBox1.SelectedItem.ToString();

            int currentSheet = 1;

            int cell = 0;

            int rowsToDisplay = 28;

            int rowsToStart = 20;

            //decimal grandTotal = 0;

            //decimal vatPer = 15;

            //double incVat = 1.5;

            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("E11", "E11").Value2 = salesman;

                xlWorkSheet.get_Range("A12", "A12").Value2 = customer;

                xlWorkSheet.get_Range("A13", "A13").Value2 = address2;

                xlWorkSheet.get_Range("A14", "A14").Value2 = address3;

                xlWorkSheet.get_Range("B11", "B11").Value2 = contact;



                xlWorkSheet.get_Range("D12", "D12").Font.Bold = true;



                xlWorkSheet.get_Range("B17", "B17").Value2 = DateTime.Now;

                xlWorkSheet.get_Range("E17", "G17").Value2 = quote;

                xlWorkSheet.get_Range("E13", "H13").Value2 = note;

                xlWorkSheet.get_Range("C17", "C17").Value2 = trans;



                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[6].Value/1.15);

                if ((row + 1) % rowsToDisplay == 0)

                {

                    xlWorkSheet.get_Range("F49", "F49").Value2 = "total";

                    xlWorkSheet.get_Range("G49", "G49").Value2 = "=SUM(G20:G48)/1.15";

                    currentSheet++;

                    cell = 0;

                }

                else if (row + 1 == dataGridView1.Rows.Count)

                {



                    xlWorkSheet.get_Range("F49", "F49").Value2 = "total";

                    xlWorkSheet.get_Range("G49", "G49").Value2 = "=SUM(G20:G48)/1.15";

                    xlWorkSheet.get_Range("F50", "F50").Value2 = "Vat @15%";

                    xlWorkSheet.get_Range("G50", "G50").Value2 = "=SUM(F20:F48)+(SUM(F20:F48)*15%)";

                    xlWorkSheet.get_Range("F51", "F51").Value2 = "Total";

                    xlWorkSheet.get_Range("G51", "G51").Value2 = "=SUM(G49:G50)";

                }

                else

                {

                    cell++;

                }

            }

            if (System.IO.File.Exists(fileName))

            {

                System.IO.File.Delete(fileName);

            }

            xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

            //xlWorkBook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, filename);

            xlWorkBook.Close(true, misValue, misValue);

            xlApp.Quit();

            //releaseObject(xlWorkSheet);

            releaseObject(xlWorkBook);

            releaseObject(xlApp);

            MessageBox.Show("Excel file created , you can find the file c:\\MyQuotes" + fileName);

        }

Replace with below code.

private void button1_Click(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:\MyExcelsiorData\test2.xltx";
    xlWorkBook = xlApp.Workbooks.Add(filePath);

    string datestr = DateTime.Now.ToShortDateString();
    string datestr1 = DateTime.Now.ToShortTimeString();
    string fileName = @"C:\MyQuotes\Quote_" + txtName.Text.Replace(" ", "") + " " + datestr.Replace('/', '_') + " " + datestr1.Replace(':', '_') + ".xlsx";
    //string fileName = @"C:\MyQuotes\Quote_" + txtName.Text.Replace(" ", "") + " " + datestr.Replace('/', '_') + " " + datestr1.Replace(':', '_') + ".xls";
    string quote = txtName.Text.Replace(" ", "") + " " + datestr.Replace('/', '_') + " " + datestr1.Replace(':', '_');
    string salesman = txtEmail.Text.ToUpper();
    string customer = txtName.Text.ToUpper();
    string contact = txtContact.Text.ToUpper();
    //string delivery = txtDel.Text.ToUpper();
    string address2 = txtDel2.Text.ToUpper();
    string address3 = txtDel3.Text.ToUpper();
    string note = txtNote.Text.ToUpper();
    string trans = comboBox1.SelectedItem.ToString();


    int currentSheet = 1;
    int cell = 0;
    int rowsToDisplay = 28;
    int rowsToStart = 20;

    //decimal grandTotal = 0;
    //decimal vatPer = 15;
    //double incVat = 1.5;
    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(xlSheets[xlWorkBook.Sheets.Count], misValue, misValue, misValue);
            xlWorkSheet.Name = "Sheet" + currentSheet.ToString();
        }
        xlWorkSheet.get_Range("E11", "E11").Value2 = salesman;
        xlWorkSheet.get_Range("A12", "A12").Value2 = customer;
        xlWorkSheet.get_Range("A13", "A13").Value2 = address2;
        xlWorkSheet.get_Range("A14", "A14").Value2 = address3;
        xlWorkSheet.get_Range("B11", "B11").Value2 = contact;
        xlWorkSheet.get_Range("D12", "D12").Font.Bold = true;
        xlWorkSheet.get_Range("B17", "B17").Value2 = DateTime.Now;
        xlWorkSheet.get_Range("E17", "G17").Value2 = quote;
        xlWorkSheet.get_Range("E13", "H13").Value2 = note;
        xlWorkSheet.get_Range("C17", "C17").Value2 = trans;

        for (int column = 0; column < dataGridView1.Columns.Count; column++)
        {
            if (column == 2 || column == 5)
            {
                if (dataGridView1.Rows[row].Cells[column].Value == null)
                {
                    xlWorkSheet.Cells[cell + rowsToStart, column + 1] = "";
                }
                else
                {
                    xlWorkSheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Value.ToString();
                }
            }
            else
            {
                if (dataGridView1.Rows[row].Cells[column].Value == null)
                {
                    xlWorkSheet.Cells[cell + rowsToStart, column + 1] = "";
                }
                else
                {
                    xlWorkSheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Value.ToString();
                }
            }
        }

        if ((row + 1) % rowsToDisplay == 0)
        {
            xlWorkSheet.get_Range("F49", "F49").Value2 = "total";
            xlWorkSheet.get_Range("G49", "G49").Value2 = "=SUM(G20:G48)/1.15";
            currentSheet++;
            cell = 0;
        }
        else if (row + 1 == dataGridView1.Rows.Count)
        {
            xlWorkSheet.get_Range("F49", "F49").Value2 = "total";
            xlWorkSheet.get_Range("G49", "G49").Value2 = "=SUM(G20:G48)/1.15";

            xlWorkSheet.get_Range("F50", "F50").Value2 = "Vat @15%";
            xlWorkSheet.get_Range("G50", "G50").Value2 = "=SUM(F20:F48)+(SUM(F20:F48)*15%)";

            xlWorkSheet.get_Range("F51", "F51").Value2 = "Total";
            xlWorkSheet.get_Range("G51", "G51").Value2 = "=SUM(G49:G50)";
        }
        else
        {
            cell++;
        }
    }
    if (System.IO.File.Exists(fileName))
    {
        System.IO.File.Delete(fileName);
    }

    xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook, 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:\\MyQuotes" + fileName);
}

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

Export DataGridView to Excel with Subtotal and vat in Windows Application

Company

Explore

Follow us

Contact Us