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