Copy Worksheet Between Excel Workbook using Aspose.Cells

Introduction

Transferring data between Excel workbooks is a common task in .NET applications, especially for generating reports or managing templates. Fortunately, using Aspose.Cells for .NET makes this process straightforward and efficient. In this tutorial, we’ll guide you through the steps to copy a worksheet from one workbook to another, providing you with powerful control over your data management.

Prerequisites

Before we get started, ensure you have the following tools:

1. Aspose.Cells for .NET Library: Download the library here.
2. Visual Studio or Similar IDE: You’ll use this to write and execute your .NET code.
3. Aspose License: To bypass evaluation limitations, you can apply for a free trial or obtain a temporary license.

Import Packages

Begin by importing the necessary namespaces into your project:

using System.IO;
using Aspose.Cells;
using System;

These namespaces will give you access to the classes needed to manipulate Excel workbooks and worksheets effectively.

Step 1: Set the Directory Path

First, define the directory to store your Excel workbooks. This will simplify file access later on.

// Set the path to your documents directory.
string dataDir = "Your Document Directory";

Replace "Your Document Directory" with your actual path.

Step 2: Create the First Workbook

Let’s create a new workbook and add a worksheet to it.

// Create a new Workbook.
Workbook excelWorkbook0 = new Workbook();
// Access the first worksheet in the workbook.
Worksheet ws0 = excelWorkbook0.Worksheets[0];

Step 3: Add Header Data

Populate the worksheet with header rows to represent your dataset clearly.

// Populate header rows (A1:A4).
for (int i = 0; i < 5; i++)
{
    ws0.Cells[i, 0].PutValue($"Header Row {i}");
}

Step 4: Populate Detail Data Rows

Add detailed content to provide context for your worksheet.

// Populate detail rows (A5:A999).
for (int i = 5; i < 1000; i++)
{
    ws0.Cells[i, 0].PutValue($"Detail Row {i}");
}

Step 5: Configure Printing Setup

Set up the page configuration to repeat header rows on printed pages, which is especially useful for large reports.

// Configure page setup to repeat header rows on each page.
PageSetup pageSetup = ws0.PageSetup;
pageSetup.PrintTitleRows = "$1:$5";

Step 6: Create the Second Workbook

Next, create the second workbook that will receive the copied worksheet.

// Create another Workbook.
Workbook excelWorkbook1 = new Workbook();
// Access the first worksheet in the workbook.
Worksheet ws1 = excelWorkbook1.Worksheets[0];

Step 7: Rename the Destination Worksheet

Rename the worksheet in the second workbook for easy identification.

// Rename the worksheet.
ws1.Name = "MySheet";

Step 8: Copy Data to the Destination Worksheet

Utilize the Copy method to transfer the entire worksheet from the first workbook to the second.

// Copy data from the first worksheet of the first workbook into the first worksheet of the second workbook.
ws1.Copy(ws0);

Step 9: Save the Final Workbook

Finally, save the modified workbook.

// Save the second workbook.
excelWorkbook1.Save(dataDir + "CopyWorksheetFromWorkbookToOther_out.xls");

Conclusion

And there you have it! You can easily copy a worksheet from one workbook to another using Aspose.Cells for .NET. This method is ideal for large datasets, template creation, and report generation.

FAQ’s

Can I copy multiple worksheets at once?

Yes, you can iterate through multiple worksheets and copy them individually to another workbook.

Does Aspose.Cells retain formatting during copying?

Absolutely! The Copy method preserves all formatting and styles.

How can I access specific cells in the copied worksheet?

You can access specific cells using the Cells property within the worksheet.

What if I only want to copy values without formatting?

You can implement a custom method to copy values cell-by-cell if preferred.

Can I test this feature without a license?

Yes, Aspose offers a free trial to explore its features.