Sometimes we need to copy worksheets inside a workbook or from one workbook to another. For instance, we have an existing Excel template that we need to use for creating a report, we only need to create a copy of the template worksheet. Also, changing the position of the worksheet in a workbook is also required in certain cases. This article shows how to copy or move Excel worksheets using Spire.XLS in C#.
Introduction
Spire.XLS for .NET is a professional .NET Excel component which enables developers/programmers to fast generate, read, write and modify Excel document in their applications. Spire.XLS for .NET offers a method of Worksheet.CopyFrom() to enable developers not only copy worksheets within a workbook but also copy worksheets between different Excel Workbooks.
Duplicate Worksheets within a Workbook
The following are the steps to duplicate worksheets within an Excel workbook.
• Initialize an instance of Workbook
• Load an Excel file using LoadFromFile() method.
• Add a new blank sheet to the workbook using Add() method.
• Copy the original worksheet to the new sheet using CopyFrom() method.
• Use SaveToFile() method to save the changes to another file.
The following code snippet shows how to copy an Excel worksheet in a workbook.
using Spire.Xls;
namespace CopyExcelworksheet
{
class Program
{
static void Main(string[] args)
{
//Load the sample Excel
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
//Add worksheet and set its name
workbook.Worksheets.Add("Sheet1_Copy");
//copy worksheet to the new added worksheets
workbook.Worksheets[1].CopyFrom(workbook.Worksheets[0]);
workbook.SaveToFile("Duplicatesheet.xlsx", ExcelVersion.Version2013);
System.Diagnostics.Process.Start("Duplicatesheet.xlsx");
}
}
}
Copy Worksheets from One Workbook to Another
The following code snippet shows how to clone an Excel worksheet from different workbooks.
using Spire.Xls;
namespace CopyExcelworksheet
{
class Program
{
static void Main(string[] args)
{
//Load the sample Excel
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
Workbook workbook2 = new Workbook();
workbook2.LoadFromFile("New.xlsx");
Worksheet targetWorksheet = workbook2.Worksheets.Add("added");
targetWorksheet.CopyFrom(sheet);
workbook2.SaveToFile("CopySheetBetweenWorkbooks.xlsx", FileFormat.Version2013);
System.Diagnostics.Process.Start("CopySheetBetweenWorkbooks.xlsx");
}
}
}
Move Worksheets from One Position to Another
After you get a specific worksheet from the document, you can move it to the desired position by using Worksheet.MoveWorksheet() method.
using Spire.Xls;
namespace MoveExcelWorksheet
{
class Program
{
static void Main(string[] args)
{
//Load the sample Excel
Workbook workbook = new Workbook();
workbook.LoadFromFile("Duplicatesheet.xlsx");
// Locate the Worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Move Worksheet
worksheet.MoveWorksheet(1);
//Save and Launch
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}
Thanks for your reading.