Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi When few rows of DataGridView are selected randomly means not sequentially then the exported excel file contains filled rows along with empty rows in between the filled rows. The empty rows are not desired.

How to remove these empty/blank rows from the exported file through coding.Please edit the code.Thanksssssssssssss

What I have tried:

private void btnExportToExcel_Click(object sender, EventArgs e)
        {
            DataGridViewRow row = new DataGridViewRow();
            // Creating an ArrayList 
            ArrayList myList = new ArrayList();

            for (int i = 0; i < CategoryGV.Rows.Count; i++)
            {
                row = CategoryGV.Rows[i];

                if (row.Cells["chkBox"].Value.ToString()=="1")
                {
                   // int id = Convert.ToInt16(row.Cells["id"].Value);
                    myList.Add(row.Index);    
                }

            }
            if(myList.Count > 0)
            {
                exportSelectedRowsToExcel(myList);
            }
            else
            {
                MessageBox.Show("No rows selected");
                Console.WriteLine(row.Cells["chkBox"].Value);
            }

        }



public void exportSelectedRowsToExcel(ArrayList arrRows)
        {
            // creating Excel Application  
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

            // creating new WorkBook within Excel application  
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

            // creating new Excelsheet in workbook  
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

            // see the excel sheet behind the program  
            app.Visible = true;

            // get the reference of first sheet. By default its name is Sheet1.  
            // store its reference to worksheet  
            worksheet = workbook.Sheets["Sheet1"];
            worksheet = workbook.ActiveSheet;

            // changing the name of active sheet  
            worksheet.Name = DateTime.Now.ToString("yyyyMMddHHmmssfff");

                

            // storing header part in Excel  
            for (int i = 1; i < CategoryGV.Columns.Count + 1; i++)
            {
                worksheet.Cells[1, i] = CategoryGV.Columns[i - 1].HeaderText;
            }
            // storing Each row and column value to excel sheet  
            
            foreach (int i in arrRows)
            {
                for (int j = 0; j < CategoryGV.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = 
                         Convert.ToString(CategoryGV.Rows[i].Cells[j].Value);
                }
            }

            
            // save the application 
            
            workbook.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\ExportedCategory.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            // Exit from the application  
            app.Quit();
        }
Posted
Updated 4-Dec-19 3:59am
Comments
ZurdoDev 4-Dec-19 8:58am    
As mentioned in the other one you wrote, debug your code and find out what is happening. Don't just ask people to figure it out for you.

1 solution

C#
foreach (int i in arrRows)
{
    for (int j = 0; j < CategoryGV.Columns.Count; j++)
    {
        worksheet.Cells[i + 2, j + 1] = 
             Convert.ToString(CategoryGV.Rows[i].Cells[j].Value);
    }
}

The values in arrRows are the row index values from the data grid. So if you have flagged rows 3, 7, 8 and 12 those are the row numbers you will add to your worksheet. However since you use those values to address the worksheet, rows 1,2,4,5,6,9,10 and 11 will be blank. You need to use a proper sequential index counter to address the rows where you store the values from the datagrid.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900