Datagridview - copy selected cells to new DataTable

Question

I need to export data from DataGridView into Excel, but only selected cells. DataGridView is bounded to DataTable. I've seen a lot of examples of how to copy selected rows, but can't find on how to copy only selected cells into new DataTable. This is what I've tried so far:

  //First, copy structure of DataTable into new one
  DataTable dt = ((DataTable)dgv.DataSource).Clone();

  //Then insert data into new datatable
  foreach (DataGridViewCell cell in dgv.SelectedCells)
  {
    //We get Index of column and row from bounded DataTable 
     int col_indx = ((DataTable)dgv.DataSource).Columns.IndexOf(dgv.Columns[cell.ColumnIndex].Name);
     int row_indx = ((DataTable)dgv.DataSource).Rows.IndexOf(((DataRowView)dgv.Rows[cell.RowIndex].DataBoundItem).Row);

  //First check if row already exists
  if (dt.Columns.Contains(dgv.Columns[cell.ColumnIndex].Name))
            {
                DataRow newrow = dt.NewRow();
                newrow[col_indx] = cell.Value;
                dt.Rows.InsertAt(newrow, row_indx);
            }
            else
            {
               dt.Rows[row_indx][cell_indx] = cell.Value;
            }    

  }
   dt.AcceptChanges();

This part inserts data into new DataTable, but on separate rows If I select some cells from same row. How can I fix this ?

EDIT:

 for (int i = 0; i < dgv.Rows.Count; i++)
 {
     dt.Rows.Add();
     for (int j = 0; j < dgv.ColumnCount; j++)
     {
        if (dgv.Rows[i].Cells[j].Selected == true)
        {
           dt.Rows[i][j] = dgv.Rows[i].Cells[j].Value;
        }
     }
  }
   dt.AcceptChanges();

This is closest thing I could achieve. It copies selected data into new DataTable, but unfortunally It preserves empty rows too. So when I select cells in Datagridview from e.g. 3th row, the output in Excel is going to be with first 2 rows empty. I want to avoid that, but how ?...In other words, what I select in Datagridview must start in new Datatable from row 1 and so on...

Answer 1

You add a row for every row. You must add a row for each selected row. Try this

 int k=0;
 bool addRow;
 for (int i = 0; i < dgv.Rows.Count; i++)
 {
     addRow=true;
     for (int j = 0; j < dgv.ColumnCount; j++)
     {
        if (dgv.Rows[i].Cells[j].Selected == true)
        {
            if(addRow){
                dt.Rows.Add();
                addRow=false;
                k++;
            }
            dt.Rows[k-1][j] = dgv.Rows[i].Cells[j].Value;
        }
     }
  }
   dt.AcceptChanges();

Answer 2

You are looping through each selected cells, and creating/inserting a new row at the specified row_indx without checking whether the cells belongs to the same row or not, and whether the dt already has a row inserted at row_indx.

I would suggest to check whether you already have a row inserted at row_indx, if it has then grab that row and add the selected cell to it, if it doesn't have the row then create a new row and add the selected cell.

Answer 3

Problem solved. It was not easy though. First I had to create a new DataTable with same structure as bounded DataTable. Then copy all selected cells into that DataTable. And in the end I created a new DataTable with only rows that are not empty, using CopyToDataTable method as from this answer of Levitikon. Here is full code:

//First, copy structure of DataTable into new one
DataTable dt = ((DataTable)dgv.DataSource).Clone();

//Add all selected cells into this DataTable
for (int i = 0; i < dgv.Rows.Count; i++)
 {
     dt.Rows.Add();
     for (int j = 0; j < dgv.ColumnCount; j++)
     {
        if (dgv.Rows[i].Cells[j].Selected == true)
        {
           dt.Rows[i][j] = dgv.Rows[i].Cells[j].Value;
        }
     }
  }
   dt.AcceptChanges();

 // Then create a new DataTable without blank rows
 DataTable final_dt = dt.Rows.Cast<DataRow>()
 .Where(row => !row.ItemArray.All(field => field is DBNull ||
 string.IsNullOrWhiteSpace(field.ToString()))).CopyToDataTable();

Now everything I select in DataGridView get's added in new DataTable, and once I export this DataTable in Excel all data starts with row 0 as desired. If anyone knows better/shorter solution please let me know.