[Beginning Database Design Solutions, Second Edition]

Title: Copy cells from one Excel workbook to another in C#

[Copy cells from one Excel workbook to another in C#]

This example includes two Excel files, Book1.xlsx and Book2.xlsx. At design time, I added them to the project and set their Copy to Output Directory properties to Copy Always. That way when you run the program, there are fresh copies of the files in the executable directory.

When the program starts, the following code executes.

private void Form1_Load(object sender, EventArgs e) { txtFromFile.Text = Path.Combine( Application.StartupPath, "Book1.xlsx"); txtToFile.Text = Path.Combine( Application.StartupPath, "Book2.xlsx"); }

This code uses Path.Combine to add the executable's startup path to the file names. It saves the resulting paths in the two path text boxes so they give the full paths to the two Excel files. In order to use Excel from a C# program, you need to add a reference to it. To do that, go to Solution Explorer, right-click References, and select Add Reference. On the COM tab, select "Microsoft Excel 14.0 Object Library" (or whatever version you have installed on your system). To make using Excel easier, the program includes the following using statement.

using Excel = Microsoft.Office.Interop.Excel;

When you click Copy, the following code executes.

private void btnCopy_Click(object sender, EventArgs e) { // Get the Excel application object. Excel.Application excel_app = new Excel.ApplicationClass(); // Make Excel visible (optional). //excel_app.Visible = true; // Open the from workbook read-only. Excel.Workbook from_workbook = excel_app.Workbooks.Open( txtFromFile.Text, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Get the first worksheet. Excel.Worksheet from_sheet = (Excel.Worksheet)from_workbook.Sheets[1]; // Get the from range. string from_cell1 = txtFromRange.Text.Split(':')[0]; string from_cell2 = txtFromRange.Text.Split(':')[1]; Excel.Range from_range = from_sheet.get_Range(from_cell1, from_cell2); // Open the to workbook read-write. Excel.Workbook to_workbook = excel_app.Workbooks.Open( txtToFile.Text, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Get the first worksheet. Excel.Worksheet to_sheet = (Excel.Worksheet)to_workbook.Sheets[1]; // Get the to range. string to_cell1 = txtToRange.Text.Split(':')[0]; string to_cell2 = txtToRange.Text.Split(':')[1]; Excel.Range to_range = to_sheet.get_Range(to_cell1, to_cell2); // Copy the data. to_range.Value2 = from_range.Value2; // Close the from workbook without saving changes. from_workbook.Close(false, Type.Missing, Type.Missing); // Close the to workbook while saving changes. to_workbook.Close(true, Type.Missing, Type.Missing); // Close the Excel server. excel_app.Quit(); MessageBox.Show("Done"); }

This code creates an Excel application server. It then opens the From Excel file in read-only mode and gets that workbook's first worksheet. (You can modify the program to use a different worksheet if you like.)

The code then splits the From range into two pieces separated by a colon. For example, if the range text box contains A2:C3, then the code splits that into A2 and C3. The program then uses the worksheet's get_Range method to get a Range object representing that range in the worksheet.

Next the program performs roughly the same steps to open the To workbook and get the appropriate range there. The only real difference is that this time it opens the file for read-write access.

With the two ranges in hand, the code simply sets to_range.Value2 = from_range.Value2 to copy the data. It then closes both workbooks, indicating that it should save the changes in the To workbook.

Finally, the code closes the Excel server.

Download the example to experiment with it and to see additional details.