Commonly, there are two possibilities when copying excel worksheet: copy worksheet within an excel workbook and copy worksheet to other excel workbooks. This article will be divided into two sections to introduce how to copy excel worksheet in WPF applications using Spire.XLS for WPF.
Section 1: Copy Excel Worksheet within an excel workbook
By invoking the Worksheet.CopyFrom() method, we can easily copy an excel worksheet including its cell format, image or something else to another worksheet of the same workbook.
Detail steps as below:
Step 1: Initialize a new instance of Workbook class, load the excel workbook from file and get the worksheet which needs to be copied. Here we choose the first worksheet.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
Step 2: Add a new worksheet named copy of sheet1 to the excel workbook, and copy the first worksheet to the new added worksheet.
workbook.Worksheets.Add("copy of sheet1");
workbook.Worksheets[1].CopyFrom(worksheet);
Step 3: Save to the same workbook and launch the file.
workbook.SaveToFile("Sample.xlsx");
System.Diagnostics.Process.Start("Sample.xlsx");
Effective screenshot:
Section 2: Copy excel worksheet to other excel workbooks
If you need to copy a worksheet and save to another excel workbook, please refer to following code snippets:
Step 1: Load the excel workbook and get its first worksheet.
Workbook workbook1 = new Workbook();
workbook1.LoadFromFile("Sale.xlsx");
Worksheet worksheet = workbook1.Worksheets[0];
Step 2: Clone the first worksheet, then add to the excel workbook as a new worksheet.
Worksheet newsheet = (Worksheet)worksheet.Clone(worksheet.Parent); workbook1.Worksheets.Add(newsheet);
Step 3: Save to another workbook and launch the file.
workbook1.SaveToFile("NewWorkbook.xlsx");
System.Diagnostics.Process.Start("NewWorkbook.xlsx");
Besides, Spire.XLS also provides another method which allows us to copy an excel worksheet from one excel workbook to another existing workbook, like following:
workbook.Worksheets.AddCopy(worksheet);
Full codes:
Section 1:
using Spire.Xls;
using System.Windows;
namespace WpfApplication1
{
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
workbook.Worksheets.Add("copy of sheet1");
workbook.Worksheets[1].CopyFrom(worksheet);
workbook.SaveToFile("Sample.xlsx");
System.Diagnostics.Process.Start("Sample.xlsx");
}
}
}
Section 2:
using Spire.Xls;
using System.Windows;
namespace WpfApplication1
{
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void button2_Click(object sender, RoutedEventArgs e)
{
Workbook workbook1 = new Workbook();
workbook1.LoadFromFile("Sale.xlsx");
Worksheet worksheet = workbook1.Worksheets[0];
Worksheet newsheet = (Worksheet)worksheet.Clone(worksheet.Parent);
workbook1.Worksheets.Add(newsheet);
workbook1.SaveToFile("NewWorkbook.xlsx");
System.Diagnostics.Process.Start("NewWorkbook.xlsx");
}
}
}
