Excel Template Use

The following example demonstrates how to create new workbooks using an existing Excel workbook as a template.

The procedure is simple:

  • load a file,
  • fill it in with your data,
  • and then save it as a new file.

The example also demonstrates how to use the InsertCopy method for copying an Excel row.

The template row can define the desired styling, number formatting, formulas, etc. When GemBox.Spreadsheet is inserting copied rows, it will automatically adjust the formulas of those rows and also any other formula in the workbook that is affected by that insertion.

Screenshot

Template Use 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.Drawing;
4using System.Text;
5using GemBox.Spreadsheet;
6
7class Sample
8{
9    [STAThread]
10    static void Main(string[] args)
11    {
12        // If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
14
15        ExcelFile ef = ExcelFile.Load("Template.xlsx");
16
17        int workingDays = 8;
18
19        DateTime startDate = DateTime.Now.AddDays(-workingDays);
20        DateTime endDate = DateTime.Now;
21        
22        ExcelWorksheet ws = ef.Worksheets[0];
23
24        // Find cells with placeholder text and set their values.
25        int row, column;
26        if (ws.Cells.FindText("[Company Name]", true, true, out row, out column))
27            ws.Cells[row, column].Value = "ACME Corp";
28        if (ws.Cells.FindText("[Company Address]", true, true, out row, out column))
29            ws.Cells[row, column].Value = "240 Old Country Road, Springfield, IL";
30        if (ws.Cells.FindText("[Start Date]", true, true, out row, out column))
31            ws.Cells[row, column].Value = startDate;
32        if (ws.Cells.FindText("[End Date]", true, true, out row, out column))
33            ws.Cells[row, column].Value = endDate;
34
35        // Copy template row.
36        row = 17;
37        ws.Rows.InsertCopy(row + 1, workingDays - 1, ws.Rows[row]);
38
39        // Fill inserted rows with sample data.
40        var random = new Random();
41        for (int i = 0; i < workingDays; i++)
42        {
43            ExcelRow currentRow = ws.Rows[row + i];
44            currentRow.Cells[1].SetValue(startDate.AddDays(i));
45            currentRow.Cells[2].SetValue(random.Next(1, 12));
46        }
47
48        // Calculate formulas in worksheet.
49        ws.Calculate();
50
51        ef.Save("Template Use.xls");
52    }
53}
1Imports System.Text
2Imports System.Drawing
3Imports GemBox.Spreadsheet
4
5Module Samples
6
7    Sub Main()
8
9        ' If using Professional version, put your serial key below.
10        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
11
12        Dim ef As ExcelFile = ExcelFile.Load("Template.xlsx")
13
14        Dim workingDays As Integer = 8
15
16        Dim startDate As DateTime = DateTime.Now.AddDays(-workingDays)
17        Dim endDate As DateTime = DateTime.Now
18
19        Dim ws As ExcelWorksheet = ef.Worksheets(0)
20
21        ' Find cells with placeholder text and set their values.
22        Dim row As Integer, column As Integer
23        If ws.Cells.FindText("[Company Name]", True, True, row, column) Then
24            ws.Cells(row, column).Value = "ACME Corp"
25        End If
26        If ws.Cells.FindText("[Company Address]", True, True, row, column) Then
27            ws.Cells(row, column).Value = "240 Old Country Road, Springfield, IL"
28        End If
29        If ws.Cells.FindText("[Start Date]", True, True, row, column) Then
30            ws.Cells(row, column).Value = startDate
31        End If
32        If ws.Cells.FindText("[End Date]", True, True, row, column) Then
33            ws.Cells(row, column).Value = endDate
34        End If
35
36        ' Copy template row.
37        row = 17
38        ws.Rows.InsertCopy(row + 1, workingDays - 1, ws.Rows(row))
39
40        ' Fill inserted rows with sample data.
41        Dim random As New Random()
42        For i As Integer = 0 To workingDays - 1
43            Dim currentRow As ExcelRow = ws.Rows(row + i)
44            currentRow.Cells(1).SetValue(startDate.AddDays(i))
45            currentRow.Cells(2).SetValue(random.Next(1, 12))
46        Next
47
48        ' Calculate formulas in worksheet.
49        ws.Calculate()
50
51        ef.Save("Template Use.xls")
52
53    End Sub
54
55End Module

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