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

Last Reply 5 months ago By dharmendr

Posted 5 months ago

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(); 
              }
          }
Posted 5 months ago Modified on 5 months ago

Hi shabzo1,

Check with the below modified code. As per your condition the template worksheet starts from A121 : K121 and the row to start writing the data is 21(rowsToStart).

If there is less than or equals to 30 items in DataGridView then from row 52 to 120 will be deleted from excel as the template excel starts from row 121.

If there is more than 30 items on DataGridView then all empty rows deleted between the based on DataGridView count.

Example if there was 55 items then (55+21 = 76). Empty row deleted from A76 to 120.

protected void button1_Click(object sender, EventArgs e)
{
    int currentSheet = 1;
    Excel.Application xlApp = new Excel.Application();
    string filePath = @"C:\Users\dharmendra\Desktop\masterquote.xlsx";
    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:\Users\dharmendra\Desktop\";
    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 = 40;
    int rowsToStart = 21;

    for (int row = 0; row < dataGridView1.Rows.Count; 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("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();
            }

        }
        if ((row + 1) % rowsToDisplay == 0)
        {
            //currentSheet++;
            cell++;
        }
        else
        {
            cell++;
        }
    }
    int dgvcount = dataGridView1.Rows.Count;
    int rowToDeleteFrom = dgvcount + rowsToStart;
    if (dgvcount <= 30)
    {
        Excel.Range range = xlWorkSheet.get_Range("A52", "A120");
        Excel.Range entireRow = range.EntireRow;
        entireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
    }
    else
    {
        Excel.Range range = xlWorkSheet.get_Range("A" + rowToDeleteFrom, "A120");
        Excel.Range entireRow = range.EntireRow;
        entireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
    }
    if (System.IO.File.Exists(myfile))
    {
        System.IO.File.Delete(myfile);
    }

    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();
    }
}