How to copy or move Excel worksheets in C#

Poster
Posted by in C# category on for Intermediate level | Points: 250 | Views : 359 red flag

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.

Page copy protected against web site content infringement by Copyscape

About the Author

Poster
Full Name: Ms poster
Member Level:
Member Status: Member
Member Since: 8/29/2007 10:18:25 AM
Country:


-- Hobby for DotNet

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)