Copy and delete Excel sheet
Following example shows how to copy and delete worksheets.
One worksheet is used as the template for other worksheets.

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 startTime = DateTime.Now;
// Go to the first Monday from today.
while (startTime.DayOfWeek != DayOfWeek.Monday)
startTime = startTime.AddDays(1);
var random = new Random();
// For each sheet.
for (int i = 0; i < 4; i++)
{
// Get sheet.
var worksheet = workbook.Worksheets[i];
// Set some fields.
worksheet.Cells["J5"].SetValue(14 + i);
worksheet.Cells["J6"].SetValue(DateTime.Now);
worksheet.Cells["J6"].Style.NumberFormat = "m/dd/yyyy";
worksheet.Cells["D12"].Value = "ACME Corp";
worksheet.Cells["D13"].Value = "240 Old Country Road, Springfield, IL";
worksheet.Cells["D14"].Value = "USA";
worksheet.Cells["D15"].Value = "Joe Smith";
worksheet.Cells["E18"].Value = String.Format(startTime.ToShortDateString() + " until " + startTime.AddDays(11).ToShortDateString());
for (int j = 0; j < 10; j++)
{
worksheet.Cells[21 + j, 1].SetValue(startTime); // Set date.
worksheet.Cells[21 + j, 1].Style.NumberFormat = "dddd, mmmm dd, yyyy";
worksheet.Cells[21 + j, 4].SetValue(random.Next(6, 9)); // Work hours.
// Skip Saturday and Sunday.
startTime = startTime.AddDays(j == 4 ? 3 : 1);
}
// Skip Saturday and Sunday.
startTime = startTime.AddDays(2);
worksheet.Cells["B36"].Value = "Payment via check.";
}
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 Step 1
workbook.Worksheets.AddCopy("Invoice " + (i + 1).ToString(), templateSheet)
Next
' Delete template sheet.
workbook.Worksheets.Remove(0)
Dim startTime = DateTime.Now
' Go to the first Monday from today.
While startTime.DayOfWeek <> DayOfWeek.Monday
startTime = startTime.AddDays(1)
End While
Dim random As Random = New Random()
' For each sheet.
For i = 0 To 3 Step 1
' Get sheet.
Dim worksheet = workbook.Worksheets(i)
' Set some fields.
worksheet.Cells("J5").SetValue(14 + i)
worksheet.Cells("J6").SetValue(DateTime.Now)
worksheet.Cells("J6").Style.NumberFormat = "m/dd/yyyy"
worksheet.Cells("D12").Value = "ACME Corp"
worksheet.Cells("D13").Value = "240 Old Country Road, Springfield, IL"
worksheet.Cells("D14").Value = "USA"
worksheet.Cells("D15").Value = "Joe Smith"
worksheet.Cells("E18").Value = String.Format(startTime.ToShortDateString() + " until " + startTime.AddDays(11).ToShortDateString())
For j = 0 To 9 Step 1
worksheet.Cells(21 + j, 1).SetValue(startTime) ' Set date.
worksheet.Cells(21 + j, 1).Style.NumberFormat = "dddd, mmmm dd, yyyy"
worksheet.Cells(21 + j, 4).SetValue(random.Next(6, 9)) ' Work hours.
' Skip Saturday and Sunday.
If j = 4 Then startTime = startTime.AddDays(3) Else startTime = startTime.AddDays(1)
Next
' Skip Saturday and Sunday.
startTime = startTime.AddDays(2)
worksheet.Cells("B36").Value = "Payment via check."
Next
workbook.Save("Sheet Copying_Deleting.%OutputFileType%")
End Sub
End Module
Want more?
Like it?
Published: December 13, 2018 | Modified: November 20, 2020 | Author: Marko Kozlina