How do I insert rows from datagridview to excel sheet

See more:

C#

MS-Excel

On button click all items on the datgridview is sent to an excel sheet, The template starts from A19 and ends at a49. if there is more I want to copy the template below the original and continue from A88. I am trying to avoid sheet 1 and sheet 2 etc, All should be on sheet 1.

What I have tried:

<pre>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:\MyCedarData\masterquote.xltx";
            xlWorkBook = xlApp.Workbooks.Add(filePath);

            string datestr = DateTime.Now.ToString("dd.MM.yyyy");
            string quote = txtName.Text.Replace(" ", " ");
            string filename = @"C:\MyCedarQuotes\Quote_";
            string myfile = filename + quote + "_" + datestr + ".xls";
            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;
            int rowsToStart2 = 188;
            decimal grandTotal = 0;
            decimal vatPer = 15;
            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();
                    }
                                                          
                }
                grandTotal += Convert.ToDecimal(dataGridView1.Rows[row].Cells[6].Value);
                if ((row + 1) % rowsToDisplay == 0)
                {
                    xlWorkSheet.get_Range("I50", "I50").Value2 = "Sub Total";
                    xlWorkSheet.get_Range("J50", "J50").Value2 = "=sum(J19:J49)";

                    currentSheet++;
                    cell = 0;
                }
                else if (row + 1 == dataGridView1.Rows.Count - 1)
                {
                    xlWorkSheet.get_Range("I50", "I50").Value2 = "Sub Total";
                    xlWorkSheet.get_Range("J50", "J50").Value2 = "=sum(J19:J49)";

                    xlWorkSheet.get_Range("I51", "I51").Value2 = "Transport";
                    xlWorkSheet.get_Range("J51", "J51").Value2 = 0;

                    xlWorkSheet.get_Range("I52", "I52").Value2 = "Excl Vat";
                    xlWorkSheet.get_Range("J52", "J52").Value2 = grandTotal;

                    decimal vatAmount = grandTotal * (vatPer / 100);

                    xlWorkSheet.get_Range("I53", "I53").Value2 = "Vat @15%";
                    xlWorkSheet.get_Range("J53", "J53").Value2 = vatAmount;

                    xlWorkSheet.get_Range("I54", "I54").Value2 = "Total";
                    xlWorkSheet.get_Range("J54", "J54").Value2 = grandTotal + vatAmount;
                }
                else
                {
                    cell++;
                }
            }