ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Delete empty rows from Excel based on condition using Microsoft Office Interop Library<p>Hi <a class="username" rel="shabzo1"> shabzo1</a>,</p> <p>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). </p> <p>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.</p> <p>If there is more than 30 items on DataGridView then all empty rows deleted between the based on DataGridView count.</p> <p>Example if there was 55 items then (55+21 = 76). Empty row deleted from A76 to 120.</p> <pre class="brush: csharp">protected void button1_Click(object sender, EventArgs e) { &nbsp;&nbsp;&nbsp; int currentSheet = 1; &nbsp;&nbsp;&nbsp; Excel.Application xlApp = new Excel.Application(); &nbsp;&nbsp;&nbsp; string filePath = @&#34;C:\Users\dharmendra\Desktop\masterquote.xlsx&#34;; &nbsp;&nbsp;&nbsp; Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(filePath); &nbsp;&nbsp;&nbsp; Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(currentSheet); ; &nbsp;&nbsp;&nbsp; object misValue = System.Reflection.Missing.Value; &nbsp;&nbsp;&nbsp; string datestr = DateTime.Now.ToString(&#34;dd.MM.yyyy&#34;); &nbsp;&nbsp;&nbsp; string quote = txtName.Text.Replace(&#34; &#34;, &#34; &#34;); &nbsp;&nbsp;&nbsp; string filename = @&#34;C:\Users\dharmendra\Desktop\&#34;; &nbsp;&nbsp;&nbsp; string myfile = filename + quote + &#34;_&#34; + datestr + &#34;.xlsx&#34;; &nbsp;&nbsp;&nbsp; string salesman = txtEmail.Text.ToUpper(); &nbsp;&nbsp;&nbsp; string customer = txtName.Text.ToUpper(); &nbsp;&nbsp;&nbsp; string custname = txtCustName.Text.ToUpper(); &nbsp;&nbsp;&nbsp; string delivery = txtDel.Text; &nbsp;&nbsp;&nbsp; int cell = 0; &nbsp;&nbsp;&nbsp; int rowsToDisplay = 40; &nbsp;&nbsp;&nbsp; int rowsToStart = 21; &nbsp;&nbsp;&nbsp; for (int row = 0; row &lt; dataGridView1.Rows.Count; row++) &nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (currentSheet &lt;= xlWorkBook.Sheets.Count) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(currentSheet); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xlWorkSheet.get_Range(&#34;F18&#34;, &#34;H18&#34;).Value2 = salesman; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xlWorkSheet.get_Range(&#34;B13&#34;, &#34;D14&#34;).Value2 = customer; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xlWorkSheet.get_Range(&#34;B15&#34;, &#34;D15&#34;).Value2 = custname; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xlWorkSheet.get_Range(&#34;H13&#34;, &#34;K15&#34;).Value2 = delivery; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xlWorkSheet.get_Range(&#34;A13&#34;, &#34;D14&#34;).Font.Bold = true; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xlWorkSheet.get_Range(&#34;I3&#34;, &#34;J3&#34;).Value2 = DateTime.Now; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xlWorkSheet.get_Range(&#34;I5&#34;, &#34;K5&#34;).Value2 = quote + &#34;_&#34; + datestr; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for (int column = 0; column &lt; dataGridView1.Columns.Count; column++) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (column == 2 || column == 5) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xlWorkSheet.Cells[cell + rowsToStart, column + 1] = &#34;&#39;&#34; + dataGridView1.Rows[row].Cells[column].Value.ToString().ToUpper(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xlWorkSheet.Cells[cell + rowsToStart, column + 1] = dataGridView1.Rows[row].Cells[column].Value.ToString().ToUpper(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if ((row + 1) % rowsToDisplay == 0) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //currentSheet++; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cell++; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; else &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cell++; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp; int dgvcount = dataGridView1.Rows.Count; &nbsp;&nbsp;&nbsp; int rowToDeleteFrom = dgvcount + rowsToStart; &nbsp;&nbsp;&nbsp; if (dgvcount &lt;= 30) &nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Excel.Range range = xlWorkSheet.get_Range(&#34;A52&#34;, &#34;A120&#34;); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Excel.Range entireRow = range.EntireRow; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; entireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp); &nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp; else &nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Excel.Range range = xlWorkSheet.get_Range(&#34;A&#34; + rowToDeleteFrom, &#34;A120&#34;); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Excel.Range entireRow = range.EntireRow; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; entireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp); &nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp; if (System.IO.File.Exists(myfile)) &nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; System.IO.File.Delete(myfile); &nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp; 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); &nbsp;&nbsp;&nbsp; xlWorkBook.Saved = true; &nbsp;&nbsp;&nbsp; xlWorkBook.Close(true, misValue, misValue); &nbsp;&nbsp;&nbsp; if (xlApp != null) &nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xlApp.Quit(); &nbsp;&nbsp;&nbsp; } }</pre>https://www.aspforums.net:443/Threads/519594/Delete-empty-rows-from-Excel-based-on-condition-using-Microsoft-Office-Interop-Library/https://www.aspforums.net:443/Threads/519594/Delete-empty-rows-from-Excel-based-on-condition-using-Microsoft-Office-Interop-Library/Tue, 12 Jun 2018 05:28:13 GMT