Copy and delete Excel sheets or cells

The following example shows how you can copy and delete worksheets using the GemBox.Spreadsheet library in your C# and VB.NET applications.

Copying and deleting Excel worksheets
Screenshot of copied Excel worksheets
Upload your file (Drag file here)
using System;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = ExcelFile.Load("%InputFileName%");

        // Get template sheet.
        var templateSheet = workbook.Worksheets[0];

        // Copy template sheet.
        for (int i = 0; i < 4; i++)
            workbook.Worksheets.AddCopy("Invoice " + (i + 1), templateSheet);

        // Delete template sheet.
        workbook.Worksheets.Remove(0);

        var random = new Random();

        // For each sheet.
        for (int i = 0; i < 4; i++)
        {
            // Get sheet.
            var worksheet = workbook.Worksheets[i];

            // Write sheet's cells.
            worksheet.Cells["C6"].Value = "ACME Corp";
            worksheet.Cells["C7"].Value = "240 Old Country Road, Springfield, IL";

            DateTime startDate = DateTime.Today;
            int itemsCount = random.Next(5, 20);
            worksheet.Cells["C11"].SetValue(startDate);
            worksheet.Cells["C12"].SetValue(startDate.AddDays(itemsCount - 1));

            // Copy template row.
            int row = 17;
            worksheet.Rows.InsertCopy(row + 1, itemsCount - 1, worksheet.Rows[row]);

            // Write row's cells.
            for (int j = 0; j < itemsCount; j++)
            {
                var currentRow = worksheet.Rows[row + j];
                currentRow.Cells[1].SetValue(startDate.AddDays(j));
                currentRow.Cells[2].SetValue(random.Next(6, 9));
            }
        }

        workbook.Save("Sheet Copying_Deleting.%OutputFileType%");
    }
}
Imports System
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook = ExcelFile.Load("%InputFileName%")

        ' Get template sheet.
        Dim templateSheet = workbook.Worksheets(0)

        ' Copy template sheet.
        For i = 0 To 3
            workbook.Worksheets.AddCopy("Invoice " + (i + 1).ToString(), templateSheet)
        Next

        ' Delete template sheet.
        workbook.Worksheets.Remove(0)

        Dim random As New Random()

        ' For each sheet.
        For i = 0 To 3

            ' Get sheet.
            Dim worksheet = workbook.Worksheets(i)

            ' Write sheet's cells.
            worksheet.Cells("C6").Value = "ACME Corp"
            worksheet.Cells("C7").Value = "240 Old Country Road, Springfield, IL"

            Dim startDate As DateTime = DateTime.Today
            Dim itemsCount As Integer = random.Next(5, 20)
            worksheet.Cells("C11").SetValue(startDate)
            worksheet.Cells("C12").SetValue(startDate.AddDays(itemsCount - 1))

            ' Copy template row.
            Dim row As Integer = 17
            worksheet.Rows.InsertCopy(row + 1, itemsCount - 1, worksheet.Rows(row))

            ' Write row's cells.
            For j = 0 To itemsCount - 1
                Dim currentRow = worksheet.Rows(row + j)
                currentRow.Cells(1).SetValue(startDate.AddDays(j))
                currentRow.Cells(2).SetValue(random.Next(6, 9))
            Next

        Next

        workbook.Save("Sheet Copying_Deleting.%OutputFileType%")

    End Sub
End Module

Besides copying and deleting the whole ExcelWorksheet, you can also copy and delete just the desired CellRange. The following example shows how you can copy and delete specific cell ranges.

Copying and deleting Excel cell ranges
Screenshot of copied Excel cell ranges
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = ExcelFile.Load("%#CellRanges.xlsx%");
        var worksheet = workbook.Worksheets[0];

        // Copy cells including all the data, like pictures, data validations, and conditional formattings.
        var range = worksheet.Cells.GetSubrange("B3:D12");
        range.CopyTo("F3");
        range.CopyTo("J3");

        // Copy cells with specified copy options.
        range = worksheet.Cells.GetSubrange("B7:D8");
        range.CopyTo("J15", new CopyOptions()
        {
            CopyTypes = CopyTypes.Values | CopyTypes.Formulas,
            Transpose = true
        });

        // Delete cells and shift remaining cells to the left.
        range = worksheet.Cells.GetSubrange("B14:D23");
        range.Remove(RemoveShiftDirection.Left);

        workbook.Save("CellRanges Copied and Deleted.%OutputFileType%");
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook = ExcelFile.Load("%#CellRanges.xlsx%")
        Dim worksheet = workbook.Worksheets(0)

        ' Copy cells including all the data, like pictures, data validations, and conditional formattings.
        Dim range = worksheet.Cells.GetSubrange("B3:D12")
        range.CopyTo("F3")
        range.CopyTo("J3")

        ' Copy cells with specified copy options.
        range = worksheet.Cells.GetSubrange("B7:D8")
        range.CopyTo("J15", New CopyOptions() With
        {
            .CopyTypes = CopyTypes.Values Or CopyTypes.Formulas,
            .Transpose = True
        })

        ' Delete cells and shift remains cells to the left.
        range = worksheet.Cells.GetSubrange("B14:D23")
        range.Remove(RemoveShiftDirection.Left)

        workbook.Save("CellRanges Copied and Deleted.%OutputFileType%")

    End Sub
End Module

See also


Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy