如果满足条件，如何删除行 [英] How to delete rows if condition is met
本文介绍了如果满足条件，如何删除行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我设法解决了一个问题，但我遇到了第二个问题。第一个条件检查是否有多个要显示的行并删除空白行。行开始于A21和结束于A121。第二个条件是如果少于50然后删除。它没有发生。
我尝试过：
< pre lang =c＃> int rowToDeleteFrom = rowsToStart + have;
if （（row + 1 ）％rowsToDisplay == 0 ）
{
// int rowToDeleteFrom = rowsToStart + have ; //具有datagridview .rows .count
Excel.Range range = xlWorkSheet.get_Range（ A + rowToDeleteFrom， A121）;
Excel.Range wholeRow = range.EntireRow;
wholeRow.Delete（Microsoft.Office.Interop.Excel.XlDirection.xlUp）;
cell ++;
}
else
{
cell ++;
}
编辑：解决方案1中添加的代码：
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 ++）
{
int have = dataGridView1.Rows.Count;
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 = < span class =code-digit> 0 ; column < dataGridView1.Columns.Count; column ++）
{
< span class =code-keyword> 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 rowToDeleteFrom = rowsToStart + have;
if （（row + 1 ）％rowsToDisplay == 0 ）
{
// int rowToDeleteFrom = rowsToStart + have ; //具有datagridview .rows .count
Excel.Range range = xlWorkSheet.get_Range（ A + rowToDeleteFrom， A121）;
Excel.Range wholeRow = range.EntireRow;
wholeRow.Delete（Microsoft.Office.Interop.Excel.XlDirection.xlUp）;
cell ++;
}
else
{
cell ++;
}
}
如果（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。放弃（）;
}
releaseObject（xlWorkSheet）;
releaseObject（xlWorkBook）;
releaseObject（xlApp）;
MessageBox.Show（ 创建Excel文件，单击打开引用按钮）;
{
btn2.Show（）;
}
}
解决方案
hi。对不起，这里是完整的代码。
< pre> 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 ++）
{
int have = dataGridView1.Rows.Count;
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 = < span class =code-digit> 0 ; column < dataGridView1.Columns.Count; column ++）
{
< span class =code-keyword> 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 rowToDeleteFrom = rowsToStart + have;
if （（row + 1 ）％rowsToDisplay == 0 ）
{
// int rowToDeleteFrom = rowsToStart + have ; //具有datagridview .rows .count
Excel.Range range = xlWorkSheet.get_Range（ A + rowToDeleteFrom， A121）;
Excel.Range wholeRow = range.EntireRow;
wholeRow.Delete（Microsoft.Office.Interop.Excel.XlDirection.xlUp）;
cell ++;
}
else
{
cell ++;
}
}
如果（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。放弃（）;
}
releaseObject（xlWorkSheet）;
releaseObject（xlWorkBook）;
releaseObject（xlApp）;
MessageBox.Show（ 创建Excel文件，单击打开引用按钮）;
{
btn2.Show（）;
}
}
I managed to solve one problem but i am stuck with the second condition. first condition checks if there is more than the rows to display and deletes blank rows. row starts at A21 AND ENDS AT A121. the second condition is if there is less than 50 then delete. it is not happening.
What I have tried:
int rowToDeleteFrom = rowsToStart + have;
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++;
}
EDIT: Code added from solution 1:
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++)
{
int have = dataGridView1.Rows.Count;
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 rowToDeleteFrom = rowsToStart + have;
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++;
}
}
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();
}
} 解决方案
hi. sorry here is the complete code.
<pre> 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++) { int have = dataGridView1.Rows.Count; 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 rowToDeleteFrom = rowsToStart + have; 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++; } } 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(); } }
这篇关于如果满足条件，如何删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
