Copying Data from a Gridview and Pasting it to Excel and Again Back to Datagridview

Introduction

This tip help you to copy all the rows and columns of a datagridview to clipboard via CTRL+C or export to Excel via button and then we can paste it back to datagridview.

Background

I had an old application with user using datagridview for Dataentry. Sometimes, the user may have entered 100 rows and when he presses the submit button, the database may be down (hey, I forget to tell it's an Oracle 8i database running on Win2000 server). At that time, the user wants to copy the entered data to Excel and later paste it back to datagridview.

Using the Code

Step 1

Copying and pasting Data from Datagridview to Excel:

For copying data from the datagridview to Excel, I simply selected all the rows and columns and used the two techniques:

1. Simply selecting the rows and columns of datagridview and copying it using CTRL+C and used CTRL+V to paste to Excel (it's not my technique, it's windows default)
2. I had written a export to Excel code for exporting the data of the GridviewStep. For that, I created a class Dataexporter as given below:

using System.Text;
using Microsoft.Office.Interop;
namespace ATCHRM.Transactions
{
     public class DataExporter
    {
        public void exporttoexcel(System.Windows.Forms.DataGridView dataGridView1 )
        {
            try
            {
                // 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 = "Exported from gridview";

                // storing header part in Excel
                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
                }


                // storing Each row and column value to excel sheet
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        if (dataGridView1.Rows[i].Cells[j].Value == null)
                        {
                            worksheet.Cells[i + 2, j + 1] = 0;
                        }
                        else
                        {
                            worksheet.Cells[i + 2, j + 1] = 
                                dataGridView1.Rows[i].Cells[j].Value.ToString();
                        }

                    }
                }

                // save the application
                //   workbook.SaveAs("c:\\output.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();
            }
            catch (Exception)
            {                
              
            }
        }
    }
}

And inside the form where the datagridview (tbl_dailytime) resides, I kept a button "export to excel" where I initialised the dataexporter class and just called:

Transactions.DataExporter dtpexptr= new Transactions.DataExporter();
dtpexptr.exporttoexcel(this.tbl_DailyTime);

Step 2

Adding a Contextmenustrip to paste data to the Gridview. (Since CTRL+V will not work in Datagridview)

I added a contextmenustrip to the datagridview:

this.tbl_rolldata.ContextMenuStrip == this.ContextMenuStrip1
//ContextMenuStrip1
//

{
    this.ContextMenuStrip1.Items.AddRange
        (new System.Windows.Forms.ToolStripItem[] { this.PasteToolStripMenuItem });
    this.ContextMenuStrip1.Name = "ContextMenuStrip1";
    this.ContextMenuStrip1.Size = new System.Drawing.Size(113, 26);
    //
    //PasteToolStripMenuItem
    //
    this.PasteToolStripMenuItem.Name = "PasteToolStripMenuItem";
    this.PasteToolStripMenuItem.Size = new System.Drawing.Size(112, 22);
    this.PasteToolStripMenuItem.Text = "Paste";
}

Step 3

Adding code to enter paste data to dgridview.

I finally added the code to paste data in the datagridview.

private void PasteToolStripMenuItem_Click(System.Object sender, System.EventArgs e)
{
//declared a array to get the data from  clipboard linewise. Split the string when there is a newline
string[] Cliplines = Clipboard.GetData(DataFormats.Text).ToString().Split('\n');
            int i = 0;
//validates whether number of lines in excel and number of rows in data grid are same
            if (Cliplines.Length == tbl_rolldata.Rows.Count)
            {
                foreach (string str in Cliplines)
                {
//splits the stringlines with tab to get the cell values
                    string[] Clipword = Cliplines[i].Split('\t');
                    for (int j = 0; j < Clipword.Length; j++)
                    {

// if rowcount of datagridview greater than zero and  number of columns 
// less than  Clipword.Length set cell value to clipword value
                        if (tbl_rolldata.Rows.Count > i && j <Clipword.Length-1)
                        {
                            tbl_rolldata.Rows[i].Cells[j].Value = Clipword[j].Replace('\r', ' ');
                        }
                    }
                    i++;
                }
            }
            else
            {
                MessageBox.Show("The Number of Rows Mentioned and 
                Rows Copied  from Excel are not Matching  
                Please try again with same rownumber");
            }

Points of Interest

If required, we can make the copy also using the contextmenustrip or autocopy on some shortcut key combination.