Copy and delete Excel sheet

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

Excel worksheet copied with GemBox.Spreadsheet
Screenshot of Excel worksheet copied with GemBox.Spreadsheet
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

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