Copy and Delete Excel sheet
GemBox.Spreadsheet is a .NET component that enables developers to read, write, convert and print spreadsheet files (XLSX, XLS, ODS, CSV, HTML, PDF or XPS) from their .NET applications using one simple API. GemBox.Spreadsheet Free is free of charge while GemBox.Spreadsheet Professional is a commercial version licensed per developer.
Following example shows how to copy and delete worksheets.
One worksheet is used as the template for other worksheets.
Screenshot
See the full code below, use RUN EXAMPLE to execute.
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.