how to copy cells from a specific range from one excel workbook to another workbook using c#

Question

I need help to copy some data from cells in one excel workbook to another excel workbook. I am having trouble specifying the c# code to look for data in a specified column that isn't the first column. The excel worksheet contains data about employees details such as name, number, department and email. I want the code to search the column and for the data that matches a certain position e.g. trainee instead of permanent. The code should be able to copy the people who are trainees and paste in another specified workbook.

I have tried to implement an if statement and if the cell contains the string 'trainee' it will paste the data into another workbook, this was successful only when the column was the first, which in the actual spreadsheet it wasn't. I have searched all over the internet but can't find a conclusive tutorial on how to manipulate excel with c# in a console app.

using Excel = Microsoft.Office.Interop.Excel;
class Program
{
     static void Main(string[] args)
    {
        var excelapp = new Excel.Application();
        excelapp.Workbooks.Add();
        string path = 
@"C:\Users\....xlsx";
        Excel.Workbook workbook = excelapp.Workbooks.Open(path);
        Excel.Worksheet workSheet = workbook.Worksheets.get_Item(1);
        var source = 
  workSheet.Range["h3:H10"].Insert(Excel.XlInsertShiftDirection.xlShiftDown);
        Excel.Range dest = workSheet.Range["F10"];
        workbook.SaveAs("Book1.xlsx");
    }
}

This current code will only copy the entire spreadsheet and paste into a new workbook. I only want the relevant data.

sin2akshay · Accepted Answer · 2019-08-21T13:14:27.113

Interop code is a bit difficult to understand and work with. I would suggest you to either use OleDb or some OpenXML alternative for working with Excel files.

From what I understood from your question is that the table looks something like this in excel and you want to copy rows having Position as 'Trainee' into a new WorkSheet.

| Name | Number | Department  | Email   | Position  |
|------|--------|-------------|---------|-----------|
| A    | 1      | DepartmentA | A@a.com | Permanent |
| B    | 2      | DepartmentB | B@b.com | Trainee   |
| C    | 3      | DepartmentB | C@c.com | Trainee   |

Using OleDB to get Excel Data:

var conStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended Properties='Excel 12.0;HDR={1};IMEX=1'";
var pathToExcel = @"C:\Users\....xlsx";;
var udt = new DataTable();
conStr = string.Format(conStr, pathToExcel, "Yes");
using (var connExcel = new OleDbConnection(conStr))
{
    var cmdExcel = new OleDbCommand();
    var oda = new OleDbDataAdapter();

    connExcel.Open();
    var dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    if (dtExcelSchema != null)
    {
        //Get the name of First Sheet
        var sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();


        cmdExcel.Connection = connExcel;
        cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
    }
    oda.SelectCommand = cmdExcel;
    oda.Fill(udt);
}

We put the data fetched from Excel to a datatable. Then you can just write a simple query to fetch whatever data you want,

//Getting array of DataRow with required data
var rows = udt.Select("[Position]='Trainee');
if (rows.Length > 0)
    DataTable finalDataTable = rows.CopyToDataTable();

Then you can convert DataTable to another WorkSheet or Excel sheet as required. There are many ways to do that, you can check other questions on SO. Example

You can also use ClosedXML. It is quite easy to work with Excels.

Yes you have understood my question exactly and I am sincerely grateful for the help! — hameedmd, Aug 13 '19 at 08:07
I cant get the code to work, none of the 'OleDb...' items work and my reference 'using system.data.oledb' also is highlighted, is this a nuget package error? @sin2akshay — hameedmd, Aug 13 '19 at 11:02
using System.Data; using System.Data.OleDb; using System.Linq; Add these references and rebuild your project, it should work. If it doesn't, please comment the error you are getting. — sin2akshay, Aug 14 '19 at 09:09
The following errors occur: 'the name 'Table_Name' does not exist in current context (for this i tried writing sheet 0 and it removed the error but to what extent this messes with the code im not certain), the next error: ' operator '>' cannot be applied to the operands of type 'DataRow[] ' and 'int' '. The line of code "DataTable finalDataTable = rows.CopyToDataTable();" also produces the error 'embedded statement cannot be a declaration or labeled statement'. @sin2akshay — hameedmd, Aug 16 '19 at 09:11
Update I managed to correct the last error as it was missing curly brackets as it was apart of an if statement. — hameedmd, Aug 16 '19 at 09:19
@hameedmd I have updated the answer. Please see if those errors are fixed. Changed TABLE_NAME to "TABLE_NAME" and rows to rows.Length — sin2akshay, Aug 21 '19 at 13:15

how to copy cells from a specific range from one excel workbook to another workbook using c#

1 Answers1