C++: Copy Worksheets in Excel

Copying worksheets is very useful when you need to create similar worksheets or want to make changes to worksheets without affecting the original. This feature can save you a significant amount of time and effort, as it allows you to quickly reuse information such as data, formulas, formatting, and layouts from existing worksheets without having to create new worksheets from scratch. This article will explain how to copy worksheets in Excel in C++ using Spire.XLS for C++.

• Copy a Worksheet in the Same Workbook
• Copy a Worksheet to Another Workbook
• Copy Visible Worksheets to a New Workbook

Install Spire.XLS for C++

There are two ways to integrate Spire.XLS for C++ into your application. One way is to install it through NuGet, and the other way is to download the package from our website and copy the libraries into your program. Installation via NuGet is simpler and more recommended. You can find more details by visiting the following link.

Integrate Spire.XLS for C++ in a C++ Application

Copy a Worksheet in the Same Workbook in C++

You can copy a worksheet within the same workbook by adding a new worksheet to the workbook and then copying the worksheet to the new worksheet.

The following steps demonstrate how to copy a worksheet within the same workbook:

• Initialize an instance of the Workbook class.
• Load an Excel workbook using the Workbook->LoadFromFile(LPCWSTR_S name) method.
• Get a specific worksheet using the Workbook->GetWorksheets()->Get(int index) method.
• Add a new worksheet to the workbook using the Workbook->GetWorksheets()->Add(LPCWSTR_S name) method.
• Copy the specific worksheet to the new worksheet using the Worksheet->CopyFrom(Worksheet* worksheet) method.
• Save the result workbook to another file using the Workbook->SaveToFile(LPCWSTR_S fileName, ExcelVersion version) method.

#include "Spire.Xls.o.h";

using namespace Spire::Xls;
using namespace std;

int main()
{
    //Initialize an instance of the Workbook class
    Workbook* workbook = new Workbook();
    //Load an Excel workbook
    workbook->LoadFromFile(L"Input.xlsx");

    //Get the first worksheet
    Worksheet* sourceSheet = workbook->GetWorksheets()->Get(0);

    //Get the name of the first worksheet
    wstring sheetName = sourceSheet->GetName();

    //Add a new worksheet with a specific name to the workbook
    Worksheet* destSheet = workbook->GetWorksheets()->Add((sheetName + L"_Copy").c_str());

    //Copy the first worksheet to the new worksheet
    destSheet->CopyFrom(sourceSheet);

    //Save the result workbook to another file
    workbook->SaveToFile(L"CopyInSameWorkbook.xlsx", ExcelVersion::Version2016);
    workbook->Dispose();
    delete workbook;
}

Copy a Worksheet to Another Workbook in C++

To copy a worksheet from one workbook to another, you need to add a new worksheet to the destination workbook and then copy the worksheet from the source workbook to the new worksheet of the destination workbook. It’s worth noting that if you want to keep the source formatting of the source worksheet, you need to copy the theme of the source workbook to the destination workbook.

The following steps demonstrate how to copy a worksheet from one workbook to another and keep its source formatting:

• Initialize an instance of the Workbook class.
• Load the source workbook using the Workbook->LoadFromFile(LPCWSTR_S name) method.
• Get a specific worksheet using the Workbook->GetWorksheets()->Get(int index) method.
• Initialize an instance of the Workbook class.
• Load the destination workbook using the Workbook->LoadFromFile(LPCWSTR_S name) method.
• Add a new worksheet to the destination workbook using the Workbook->GetWorksheets()->Add(LPCWSTR_S name) method.
• Copy the specific worksheet of the source workbook to the new worksheet of the destination workbook using the Worksheet->CopyFrom(Worksheet* worksheet) method.
• Copy the theme from the source workbook to the destination workbook using the Workbook->CopyTheme (Workbook* srcWorkbook) method.
• Save the result workbook to another file using the Workbook->SaveToFile(LPCWSTR_S fileName, ExcelVersion version) method.

#include "Spire.Xls.o.h";

using namespace Spire::Xls;
using namespace std;

int main()
{
    //Initialize an instance of the Workbook class
    Workbook* sourceWorkbook = new Workbook();
    //Load the source Excel workbook
    sourceWorkbook->LoadFromFile(L"Input.xlsx");

    //Get the first worksheet of the source workbook
    Worksheet* sourceSheet = sourceWorkbook->GetWorksheets()->Get(0);
    //Get the name of the first worksheet
    wstring sheetName = sourceSheet->GetName();

    //Initialize an instance of the Workbook class
    Workbook* destWorkbook = new Workbook();
    //Load the destination Excel workbook
    destWorkbook->LoadFromFile(L"Sample.xlsx");

    //Add a new worksheet with a specific name to the destination workbook
    Worksheet* destSheet = destWorkbook->GetWorksheets()->Add((sheetName + L"_Copy").c_str());

    //Copy the first worksheet of the source workbook to the new worksheet of the destination workbook
    destSheet->CopyFrom(sourceSheet);

    //Copy the theme from the source workbook to the destination workbook
    destWorkbook->CopyTheme(sourceWorkbook);

    //Save the destination workbook to another file
    destWorkbook->SaveToFile(L"CopyToAnotherWorkbook.xlsx", ExcelVersion::Version2016);
    sourceWorkbook->Dispose();
    delete sourceWorkbook;
    destWorkbook->Dispose();
    delete destWorkbook;
}

Copy Visible Worksheets to a New Workbook in C++

If you only want to share visible worksheets rather than the entire workbook with others, you can copy the visible worksheets to a new workbook.

The following steps demonstrate how to copy visible worksheets from a workbook to a new workbook:

• Initialize an instance of the Workbook class.
• Load the source workbook using the Workbook->LoadFromFile(LPCWSTR_S name) method.
• Initialize an instance of the Workbook class to create a new workbook, then clear the default worksheets in the new workbook using the Workbook->GetWorksheets()->Clear() method.
• Iterate through all the worksheets in the source workbook.
• Check if the current worksheet is visible using the XlsWorksheetBase->GetVisibility() method.
• If the result is true, add a new worksheet to the new workbook using the Workbook->GetWorksheets()->Add(LPCWSTR_S name) method.
• Copy the worksheet from the source workbook to the new worksheet of the new workbook using the Worksheet->CopyFrom(Worksheet* worksheet) method.
• Copy the theme from the source workbook to the new workbook using the Workbook->CopyTheme (Workbook* srcWorkbook) method.
• Save the new workbook to another file using the Workbook->SaveToFile(LPCWSTR_S fileName, ExcelVersion version) method.

#include "Spire.Xls.o.h";

using namespace Spire::Xls;
using namespace std;

int main()
{
    //Initialize an instance of the Workbook class
    Workbook* sourceWorkbook = new Workbook();
    //Load the source Excel workbook
    sourceWorkbook->LoadFromFile(L"Input.xlsx");

    //Initialize an instance of the Workbook class to create a new workbook
    Workbook* newWorkbook = new Workbook();
    //Clear the default worksheets in the new workbook
    newWorkbook->GetWorksheets()->Clear();

    //Iterate through all the worksheets in the source workbook
    for (int i = 0; i < sourceWorkbook->GetWorksheets()->GetCount(); i++)
    {
        Worksheet* sourceSheet = sourceWorkbook->GetWorksheets()->Get(i);
        //Check if the current worksheet is visible
        if (sourceSheet->GetVisibility() == WorksheetVisibility::Visible)
        {
            //Get the name of the worksheet
            wstring sheetName = sourceSheet->GetName();
            //Add a new worksheet with a specific name to the new workbook
            Worksheet* destSheet = newWorkbook->GetWorksheets()->Add((sheetName + L"_Copy").c_str());
            //Copy the worksheet from the source workbook to the new worksheet of the new workbook
            destSheet->CopyFrom(sourceSheet);
        }
    }

    //Copy the theme from the source workbook to the new workbook
    newWorkbook->CopyTheme(sourceWorkbook);
     
    //Save the new workbook to another file
    newWorkbook->SaveToFile(L"CopyVisibleSheetsToNewWorkbook.xlsx", ExcelVersion::Version2016);
    sourceWorkbook->Dispose();
    delete sourceWorkbook;
    newWorkbook->Dispose();
    delete newWorkbook;
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.