Is There a Quick Way to Get the Control That's Under the Mouse

C# - How to copy a single Excel worksheet from one workbook to another?

You could also use the Sheet.Copy() method.

Basically, Sheet.copy copies the sheet, and will automatically create a new workbook at the same time.

Try adding the following lines to your code, after your call to Worksheets.get_Item:

// Copies sheet and puts the copy into a new workbook
sheet.Copy(Type.Missing, Type.Missing);

// Sets the sheet variable to the copied sheet in the new workbook
sheet = app.Workbooks[2].Sheets[1];

Here's the reference for the Copy() function as well:
MSDN Link

Excel Interop: How to copy a worksheet to another workbook?

My fault was to load the workbooks in different Application objects. Using the same Application object solved the problem.

Thanks @HansPassant to point me there.

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

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.

How to copy excel worksheets Into another excel workbook without opening the excel file in c# winforms?

To copy a worksheet and all its contents and formatting without selecting and copying the contents of the worksheet itself you can make use of Worksheet.Copy. You'd use it like so:

Excel._Worksheet ws = (Excel._Worksheet)app.Workbooks[i].Worksheets[j];
Excel._Worksheet sheet = (Excel._Worksheet)app.Workbooks[1].Worksheets.Add(
                Type.Missing, Type.Missing, Type.Missing, Type.Missing
                );
ws.Copy(Before: sheet);

If, however, what you actually mean by your question is that you want to copy the contents of the workbooks into one common workbook without ever opening the file, then I don't believe that's possible. You need to open the file to access the data.

C# copy and append excel file contents to a new excel file

I know I'm late to answer this but still this could help someone.

You can have two functions here, one to open the files and the other to copy the contents.

In the first function, you can open as many files as you want through a for loop, something like this:

void OpenFiles()
{
    foreach (string strFile in sourceFiles) //sourceFiles is a list containing the file paths
    {
        bool b = false;
        Excel.Workbook bookSource = app.Workbooks._Open(strFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        Excel.Worksheet sheetSource = bookSource.Worksheets[1] as Excel.Worksheet;

        CopyData();

    }
    //finally save the file as your requirements and close all the open workbooks
}

void CopyData()
{
    Excel.Worksheet lastsheet = null;   //last sheet in the workbook
    int limit = 1000000; //variable to check if your sheet has exceeded
    try
    {
        var sheets = bookDest.Sheets;
        lastsheet = (Excel.Worksheet)bookDest.Sheets[sheets.Count];
        hc.ReleaseObject(sheets);
        drc = lastsheet.UsedRange.Rows.Count;       //no of rows used in result workbook
        src = sheetSource.UsedRange.Rows.Count;     //no of rows used in source workbook

        //if else loop to check if you have exceeded 1st sheet limit before start copying
        if ((drc + src) <= limit)
        {
            int sheetRowCount = sheetSource.UsedRange.Rows.Count;
            Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
            range.Copy(lastsheet.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
            _currentRowCount += range.Rows.Count;
        }
        else if ((drc >= limit && src >= limit) || drc >= limit || src >= limit || (drc + src) >= limit)
        {
                Excel.Worksheet newSheet = (Excel.Worksheet)bookDest.Worksheets.Add(After: lastsheet);
                newSheet.Name = "Result " + (cnt++);
                hc.ReleaseObject(lastsheet);
                lastsheet = newSheet;
                lastsheet.Activate();
                CopyHeader(lastsheet);

                //((Excel.Worksheet) this.app.ActiveWorkbook.Sheets[lastsheet]).Select();
                int sheetRowCount = sheetSource.UsedRange.Rows.Count;
                Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
                range.Copy(lastsheet.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
                _currentRowCount += range.Rows.Count;
            }
        }
        else
        {
            int sheetRowCount = sheetSource.UsedRange.Rows.Count;
            Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount), _columnEnd + sheetRowCount.ToString());
            range.Copy(lastsheet.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
            _currentRowCount += range.Rows.Count;
        }
    }
    catch (IndexOutOfRangeException)
    {
        MessageBox.Show("Some problem with the source file", "Copy error");
    }
    finally
    {
        ReleaseObject(lastsheet);
    }
}

I have provided the limit to exactly a million. If you feel this limit might hang your excel than you can reduce it.

You're invited for any code changes.

Thank you.

How to copy specific sheet name from excel and save it to a new location? Using C#

just try this in your code it will be works

foreach (Excel.Worksheet sheet in wbTarget.Worksheets)
            {
                //Save Particular sheet as file
                if (sheet.Name == "Sheet2")
                {
                    var newbook = excelApp.Workbooks.Add(1);
                    sheet.Copy(newbook.Sheets[1]);

                    newbook.SaveAs(@"C:\Users\LV98\Desktop\Test C#\" + sheet.Name);
                    newbook.Close();
                }
            }

complete code of your button click event. I tested the below code and it's working fine

protected void button1_Click(object sender, EventArgs e)
    {
        Excel.Application excelApp;

        string fileTemplate = "C:\\Users\\LV98\\Desktop\\Test C#\\template.xlsx";            
        excelApp = new Excel.Application();
        Excel.Workbook wbTarget;

        //Create target workbook
        wbTarget = excelApp.Workbooks.Open(fileTemplate);

        foreach (Excel.Worksheet sheet in wbTarget.Worksheets)
        {
            //Save Particular sheet as file
            if (sheet.Name == "Sheet2")
            {
                var newbook = excelApp.Workbooks.Add(1);
                sheet.Copy(newbook.Sheets[1]);

                newbook.SaveAs(@"C:\Users\LV98\Desktop\Test C#\" + sheet.Name);
                newbook.Close();
            }
        }
        wbTarget.Close(true);
        excelApp.AskToUpdateLinks = false;
        excelApp.Quit();
        GetExcelProcess(excelApp);
    }
    [DllImport("user32.dll")]
    static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);
    Process GetExcelProcess(Excel.Application excelApp)
    {
        int id;
        GetWindowThreadProcessId(excelApp.Hwnd, out id);
        return Process.GetProcessById(id);
    }

How to copy a sheet with a different name - C# and Excel Interop

You can achieve this in multiple ways - probably the easiest way is to copy after the last sheet and then rename it using the index:

Excel.Application xlApp = Marshal.GetActiveObject("Excel.Application") as Excel.Application;
Excel.Workbook xlWb = xlApp.ActiveWorkbook as Excel.Workbook;
Excel.Worksheet xlSht = xlWb.Sheets[1];
xlSht.Copy(Type.Missing, xlWb.Sheets[xlWb.Sheets.Count]); // copy
xlWb.Sheets[xlWb.Sheets.Count].Name = "NEW SHEET";        // rename

I believe this MSDN guide also answers your question.

If you want to get the index of a sheet, look up Worksheet.Index property.

---

Related Topics