Copy and Delete Excel sheet

Following sample shows how to copy and delete worksheets.

One worksheet is used as the template for other worksheets.

Screenshot

Sheet Copying/Deleting Screenshot

See the full code below, use RUN EXAMPLE to execute.

Upload your file (Drag files here)

1using System;
2using System.Collections.Generic;
3using System.Text;
4using GemBox.Spreadsheet;
5using GemBox.Spreadsheet.ConditionalFormatting;
6using GemBox.Spreadsheet.PivotTables;
7
8class Sample
9{
10    [STAThread]
11    static void Main(string[] args)
12    {
13        // If using Professional version, put your serial key below.
14        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
15
16        ExcelFile ef = ExcelFile.Load("TemplateUse.xlsx");
17
18        // Get template sheet.
19        ExcelWorksheet templateSheet = ef.Worksheets[0];
20
21        // Copy template sheet.
22        for (int i = 0; i < 4; i++)
23            ef.Worksheets.AddCopy("Invoice " + (i + 1), templateSheet);
24
25        // Delete template sheet.
26        ef.Worksheets.Remove(0);
27
28        DateTime startTime = DateTime.Now;
29
30        // Go to the first Monday from today.
31        while (startTime.DayOfWeek != DayOfWeek.Monday)
32            startTime = startTime.AddDays(1);
33
34        Random rnd = new Random();
35
36        // For each sheet.
37        for (int i = 0; i < 4; i++)
38        {
39            // Get sheet.
40            ExcelWorksheet ws = ef.Worksheets[i];
41
42            // Set some fields.
43            ws.Cells["J5"].SetValue(14 + i);
44            ws.Cells["J6"].SetValue(DateTime.Now);
45            ws.Cells["J6"].Style.NumberFormat = "m/dd/yyyy";
46
47            ws.Cells["D12"].Value = "ACME Corp";
48            ws.Cells["D13"].Value = "240 Old Country Road, Springfield, IL";
49            ws.Cells["D14"].Value = "USA";
50            ws.Cells["D15"].Value = "Joe Smith";
51
52            ws.Cells["E18"].Value = String.Format(startTime.ToShortDateString() + " until " + startTime.AddDays(11).ToShortDateString());
53
54            for (int j = 0; j < 10; j++)
55            {
56                ws.Cells[21 + j, 1].SetValue(startTime); // Set date.
57                ws.Cells[21 + j, 1].Style.NumberFormat = "dddd, mmmm dd, yyyy";
58                ws.Cells[21 + j, 4].SetValue(rnd.Next(6, 9)); // Work hours.
59
60                // Skip Saturday and Sunday.
61                startTime = startTime.AddDays(j == 4 ? 3 : 1);
62            }
63
64            // Skip Saturday and Sunday.
65            startTime = startTime.AddDays(2);
66
67            ws.Cells["B36"].Value = "Payment via check.";
68        }
69
70        ef.Save("Sheet Copying_Deleting.xls");
71    }
72}
1Imports System
2Imports System.Collections.Generic
3Imports System.Text
4Imports GemBox.Spreadsheet
5Imports GemBox.Spreadsheet.ConditionalFormatting
6Imports GemBox.Spreadsheet.PivotTables
7
8Module Samples
9
10    Sub Main()
11
12        ' If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
14
15        Dim ef As ExcelFile = ExcelFile.Load("TemplateUse.xlsx")
16
17        ' Get template sheet.
18        Dim templateSheet As ExcelWorksheet = ef.Worksheets(0)
19
20        ' Copy template sheet.
21        Dim i As Int32
22        For i = 0 To 3 Step 1
23            ef.Worksheets.AddCopy("Invoice " + (i + 1).ToString(), templateSheet)
24        Next
25
26        ' Delete template sheet.
27        ef.Worksheets.Remove(0)
28
29        Dim startTime As DateTime = DateTime.Now
30
31        ' Go to the first Monday from today.
32        While startTime.DayOfWeek <> DayOfWeek.Monday
33            startTime = startTime.AddDays(1)
34        End While
35
36        Dim rnd As Random = New Random()
37
38        ' For each sheet.
39        For i = 0 To 3 Step 1
40
41            ' Get sheet.
42            Dim ws As ExcelWorksheet = ef.Worksheets(i)
43
44            ' Set some fields.
45            ws.Cells("J5").SetValue(14 + i)
46            ws.Cells("J6").SetValue(DateTime.Now)
47            ws.Cells("J6").Style.NumberFormat = "m/dd/yyyy"
48
49            ws.Cells("D12").Value = "ACME Corp"
50            ws.Cells("D13").Value = "240 Old Country Road, Springfield, IL"
51            ws.Cells("D14").Value = "USA"
52            ws.Cells("D15").Value = "Joe Smith"
53
54            ws.Cells("E18").Value = String.Format(startTime.ToShortDateString() + " until " + startTime.AddDays(11).ToShortDateString())
55
56            Dim j As Int32
57            For j = 0 To 9 Step 1
58                ws.Cells(21 + j, 1).SetValue(startTime) ' Set date.
59                ws.Cells(21 + j, 1).Style.NumberFormat = "dddd, mmmm dd, yyyy"
60                ws.Cells(21 + j, 4).SetValue(rnd.Next(6, 9)) ' Work hours.
61
62                ' Skip Saturday and Sunday.
63                If j = 4 Then startTime = startTime.AddDays(3) Else startTime = startTime.AddDays(1)
64            Next
65
66            ' Skip Saturday and Sunday.
67            startTime = startTime.AddDays(2)
68
69            ws.Cells("B36").Value = "Payment via check."
70
71        Next
72
73        ef.Save("Sheet Copying_Deleting.xls")
74
75    End Sub
76
77End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.