Click here to Skip to main content
14,641,302 members
Rate this:
Please Sign up or sign in to vote.
See more:
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();

    }
}
Posted
Updated 9-Jun-18 4:47am
v3
Comments
Richard MacCutchan 9-Jun-18 9:04am
   
Where is the code that tests for less than 50? And less than 50 what?
Gerry Schmitz 9-Jun-18 10:55am
   
Maybe you should think about moving to database tables.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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

            }
        }
   
Comments
Richard Deeming 9-Jun-18 10:43am
   
If you want to update your question, click the green "Improve question" link and edit your question.

DO NOT post your update as a "solution".
shabzo1 9-Jun-18 13:45pm
   
I'm sorry .

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100