Delete empty rows from Excel based on condition using Microsoft Office Interop Library

Hi

Hi Dhamendr

My excel template worksheet starts from A121 : K121

column A WILL always have an item no.

datagridview can have 1 item to 100 items. so if i have <= 30 items on the datagridview that will be sent to the excel sheet , then i want to delete all empty rows UPWARDS from A121 TO A52.

Excel.Range range = xlWorkSheet.get_Range("A52", "A121"); 

But if there is more than 30 items on datagrid (upto 100) then delete empty rows upwards from a121 uo to rowtodelete from. like below .

example if there was 55items then (55items from A21 = A76). DELETE from A121 TO A77

if ((row + 1) % rowsToDisplay == 0)
{
    int rowToDeleteFrom = rowsToStart + have;//have is datagridview .rows .count
    Excel.Range range = xlWorkSheet.get_Range("A" + rowToDeleteFrom, "A121");
    Excel.Range entireRow = range.EntireRow;
    entireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);

    cell++;
}
else
{
    cell++;
}

      private void button1_Click(object sender, EventArgs e)
      {
          int currentSheet = 1;
          Excel.Application xlApp = new Excel.Application();
          string filePath = @"C:\MyProdecData\testquote1.xltx";
          Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(filePath);
          Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(currentSheet); ;
          object misValue = System.Reflection.Missing.Value;
          string datestr = DateTime.Now.ToString("dd.MM.yyyy");
          string quote = txtName.Text.Replace(" ", " ");
          string filename = @"C:\MyProdecQuotes\";
          string myfile = filename + quote + "_" + datestr + ".xlsx";
          string salesman = txtEmail.Text.ToUpper();
          string customer = txtName.Text.ToUpper();
          string custname = txtCustName.Text.ToUpper();
          string delivery = txtDel.Text;
 
          int cell = 0;
          int rowsToDisplay = 30;
          int rowsToStart = 21;
 
          for (int row = 0; row < dataGridView1.Rows.Count - 1; row++)
          {
 
              if (currentSheet <= xlWorkBook.Sheets.Count)
              {
                  xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(currentSheet);
              } 
 
              xlWorkSheet.get_Range("F18", "H18").Value2 = salesman;
              xlWorkSheet.get_Range("B13", "D14").Value2 = customer;
              xlWorkSheet.get_Range("B15", "D15").Value2 = custname;
              xlWorkSheet.get_Range("H13", "K15").Value2 = delivery;
              xlWorkSheet.get_Range("A13", "D14").Font.Bold = true;
              //xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
              xlWorkSheet.get_Range("I3", "J3").Value2 = DateTime.Now;
              xlWorkSheet.get_Range("I5", "K5").Value2 = quote + "_" + datestr;
              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().ToUpper();
                  }
                  else
                  {
                      xlWorkSheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Value.ToString().ToUpper();
                  }
 
              }
              int dgvcount = dataGridView1.Rows.Count;
              int rowToDeleteFrom = dgvcount + rowsToStart;
 
              if ((row + 1) % rowsToDisplay == 0)//if dgvcount more then 50
              {
                  Excel.Range range = xlWorkSheet.get_Range("A" + rowToDeleteFrom, "A121");
                  Excel.Range entireRow = range.EntireRow;
                  entireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
                   
                  cell++;
              }
              if (dgvcount < 50)
                  {
                      Excel.Range range = xlWorkSheet.get_Range("A52", "A121");
                      Excel.Range entireRow = range.EntireRow;
                      entireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
                   
                  cell++;
              }
              
              }
 
              if (System.IO.File.Exists(myfile))
              {
                  System.IO.File.Delete(myfile);
              }
 
              //xlWorkBook.SaveAs(myfile, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
              xlWorkBook.SaveAs(myfile, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);
              xlWorkBook.Saved = true;
              xlWorkBook.Close(true, misValue, misValue);
              if (xlApp != null)
              {
                  xlApp.Quit();
              }
 
              releaseObject(xlWorkSheet);
              releaseObject(xlWorkBook);
              releaseObject(xlApp);
 
              MessageBox.Show("Excel file created , CLICK on the Open quote button");
              {
                  btn2.Show(); 
              }
          }