none
How to copy an Excel Worksheet from one workbook to another workbook with the associated C# code intact RRS feed

  • Question

  • I am trying to use a Worksheet template from a master Workbook by copying the Worksheet template into a new Workbook. I have provided code for several document level events within the Worksheet template (sheet1.cs) that need to follow the Worksheet when it is copied into the new Workbook. The code to copy the template Worksheet into the new Workbook is fully functional, but when the Worksheet is opened in the new Workbook, the events do not work (they are fully functional in the master Workbook).

    I am using Visual Studio 2017 Professional and Excel 2016.

    Is there a way to get the C# code (sheet1.cs code) to follow the Worksheet to the new Workbook?

    Thursday, August 23, 2018 12:06 AM

All replies

  • Hi Phil,

    There are two types of replication for Excel worksheets: Copying within the same document and copying between different documents.

    1. Use the Worksheet.copyfrom() method to copy a worksheet to another worksheet in the same Excel file:

    //Load Excel file
    Workbook workbook = new Workbook();
    workbook.LoadFromFile("DoughnutChart.xlsx");
    //Get sheet1
    Worksheet sheet1 = workbook.Worksheets[0];
    //Add new Worksheet
    Worksheet sheet2 = workbook.Worksheets.Add("Copy");
    //Copy
    sheet2.CopyFrom(sheet1);
    //Save file
    workbook.SaveToFile("DoughnutChart.xlsx");


    2. Use the Worksheetscollection.addcopy() method to copy a worksheet from an Excel file to another existing Excel file:

    //Load first Excel 
    Workbook workbook1 = new Workbook();
    workbook1.LoadFromFile("Vendor.xlsx");
    //Get sheet1
    Worksheet sheet1 = workbook1.Worksheets[0];
    //Load second Excel
    Workbook workbook2 = new Workbook();
    workbook2.LoadFromFile("Report.xlsx");
    //Copy
    Worksheet sheet2 = workbook2.Worksheets.AddCopy(sheet1);
    sheet2.Name = "Copy";
    //Save file
    workbook2.SaveToFile("Report.xlsx");



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Best Regards,

    Simon

    Thursday, August 23, 2018 1:50 AM
    Moderator
  • Just a supplement to Simon's answer, to use the code, you need to reference spire.xls.dll: https://www.nuget.org/packages/Spire.XLS/
    Friday, August 24, 2018 8:17 AM
  • Did you resolved your issue now ? Could you please update for this? 

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, August 27, 2018 4:16 AM
    Moderator
  • This thread has been around for a long time. Please remember to mark the replies as answers if they helped. Please help us close the thread.

    Thank you for understanding! If you have any question, or update, please feel free to let me know.

    I’m eager to receive your feedback.

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Wednesday, August 29, 2018 1:41 AM
    Moderator
  • I have attempted to use the code you provided above without success. I have also downloaded Spire.XLS version 8.8.5 as noted below and referenced it into my code, again without success. A snippet of my code follows:

    using Excel = Microsoft.Office.Interop.Excel;
    using Spire.Xls;

            private void CopyWorksheet()
            {
                // Test Copy worksheet with .cs code
                //Load first Excel 
                Excel.Workbook workbook1 = new Excel.Workbook();
                workbook1.LoadFromFile("Vendor.xlsx");
                //Get sheet1
                Excel.Worksheet sheet1 = workbook1.Worksheets[0];
                //Load second Excel
                Excel.Workbook workbook2 = new Excel.Workbook();
                workbook2.LoadFromFile("Report.xlsx");
                //Copy
                Excel.Worksheet sheet2 = workbook2.Worksheets.AddCopy(sheet1);
                sheet2.Name = "Copy";
                //Save file
                workbook2.SaveToFile("Report.xlsx");
            }

    I am getting the following error message:

    'Workbook does not contain a definition for 'LoadFromFile'. I get the same error message for "SaveToFile" and "AddCopy".

    Therefore, my questions are now:

    1. Am I missing a directive or a reference?

    2. Am I using the Spire.XLS incorrectly?

    Is there a way to copy the worksheet with the associated C# code (sheet1.cs) without using a third party solution?

    Thank you in advance for your help .

    Saturday, September 1, 2018 6:48 AM
  • Hi Phil,

    Sorry for late response. For your mentioned,

    "'Workbook does not contain a definition for 'LoadFromFile'. I get the same error message for "SaveToFile" and "AddCopy"."

    The problem is that incorrect reference to the object of Excel. You should remove "using Excel = Microsoft.Office.Interop.Excel;" code and just only use Spire.Xls. 

    Also, copy the worksheet using Spire.Xls which is a very simple method. However, you can use SaveAs and Copy function as well. 

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.



    Monday, September 3, 2018 5:27 AM
    Moderator
  • The fact that the "Microsoft.Office.Interop.Excel" namespace and the "Spire.Xls" namespace both have the same properties ("Workbook" and "Worksheet") made the process a little confusing. Thank you for the clarification.

    I have invoked the "Spire.XLS" directive throughout the code and the worksheet is being copied to the second workbook. However, the C# code associated with "sheet1" is not getting copied to the second workbook along with the "sheet1" object. So the proposed solution is not achieving the desired results.

    I have several worksheet templates with the required "Range Events" programmed into the worksheet template so that certain actions taken by the user will trigger the desired actions within the code automatically. All these worksheets are stored in a master Excel file. The worksheet templates get copied into the active workbook as needed.

    I have two options to accomplish this task.

    1. Program the events using VBA because I know the VBA associated with sheet1 copies with the sheet1 worksheet (this leaves code that can be discovered by a user). This is NOT the preferred method.

    2. Program the events using C#, but I need the C# code to copy to the new workbook with the worksheet object. This is the preferred method.  (The AddCopy did not work)

    Is there another solution to my problem? I would prefer a solution that is native to the Visual Studio environment, however I am willing to use 3rd party solutions if the Visual Studio environment is not able to support this process.

    I appreciate your help, thank you.

    Monday, September 3, 2018 7:38 PM
  • Hi Phil,

    Thanks for you feedback. I also understand your needs. However, we are testing it which copy feature of Excel by native C# as you expected. If there is any progress I will contact you the first time, please keep your attention to our forum. Thanks for you understanding.

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, September 4, 2018 1:27 PM
    Moderator
  • Hi Phil,

    I tried this solution with C# by a simple ConsoleApp to implement your request and it works for me. The Code as below:

    //using Spire.Xls;
    using Microsoft.Office.Interop.Excel;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    namespace ConsoleApp2
    {
        class Program
        {
          
            static void Main(string[] args)
            {
                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                Workbook curWorkBook = null;
                Workbook destWorkbook = null;
                Worksheet workSheet = null;
                Worksheet newWorksheet = null;
                Object defaultArg = Type.Missing;
                try
                {
                    // Copy the source sheet
                    curWorkBook = app.Workbooks.Open("D:\\Book1.xlsx", defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
                    workSheet = (Worksheet)curWorkBook.Sheets[1];
                    workSheet.UsedRange.Copy(defaultArg);
    
                    // Paste on destination sheet
                    destWorkbook = app.Workbooks.Open("D:\\Book2.xlsx", defaultArg, false, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
                    newWorksheet = (Worksheet)destWorkbook.Worksheets.Add(defaultArg, defaultArg, defaultArg, defaultArg);
                    newWorksheet.UsedRange._PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
                }
                catch (Exception exc)
                {
                    //.Windows.Forms.MessageBox.Show(exc.Message);
                }
                finally
                {
                    if (curWorkBook != null)
                    {
                        curWorkBook.Save();
                        curWorkBook.Close(defaultArg, defaultArg, defaultArg);
                    }
    
                    if (destWorkbook != null)
                    {
                        destWorkbook.Save();
                        destWorkbook.Close(defaultArg, defaultArg, defaultArg);
                    }
                }
                app.Quit();
    
            }
        }
    }
    

    However, such a solution requires that your target Excel must exist. This may not be the best solution, but hopefully it will provide you with some new ideas.

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Wednesday, September 5, 2018 7:07 AM
    Moderator
  • I have tried every variation on your most recent code suggestions above without success. The sheets continue to copy from one workbook to another, but the associated C# code that resides in the respective sheet1.cs still does not follow the sheet when it is copied to the new workbook.

    At this point, I am going to consider my original request to be impossible. I am going to rely on the VBA code associated with each sheet to accomplish the task at hand because the VBA code does follow the worksheet to the new workbook when it is copied.

    I greatly appreciate your time and efforts in helping solve this challenge. Thank you very much!

    Phil

    Sunday, September 9, 2018 10:03 PM