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.

See the full code below, use Run Example to execute.

C#
VB.NET

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Text;
using GemBox.Spreadsheet;

class Sample
{
    [STAThread]
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile ef = ExcelFile.Load("Template.xlsx");

        int workingDays = 8;

        DateTime startDate = DateTime.Now.AddDays(-workingDays);
        DateTime endDate = DateTime.Now;
        
        ExcelWorksheet ws = ef.Worksheets[0];

        // Find cells with placeholder text and set their values.
        int row, column;
        if (ws.Cells.FindText("[Company Name]", true, true, out row, out column))
            ws.Cells[row, column].Value = "ACME Corp";
        if (ws.Cells.FindText("[Company Address]", true, true, out row, out column))
            ws.Cells[row, column].Value = "240 Old Country Road, Springfield, IL";
        if (ws.Cells.FindText("[Start Date]", true, true, out row, out column))
            ws.Cells[row, column].Value = startDate;
        if (ws.Cells.FindText("[End Date]", true, true, out row, out column))
            ws.Cells[row, column].Value = endDate;

        // Copy template row.
        row = 17;
        ws.Rows.InsertCopy(row + 1, workingDays - 1, ws.Rows[row]);

        // Fill inserted rows with sample data.
        var random = new Random();
        for (int i = 0; i < workingDays; i++)
        {
            ExcelRow currentRow = ws.Rows[row + i];
            currentRow.Cells[1].SetValue(startDate.AddDays(i));
            currentRow.Cells[2].SetValue(random.Next(1, 12));
        }

        // Calculate formulas in worksheet.
        ws.Calculate();

        ef.Save("Template Use.xlsx");
    }
}

Imports System.Text
Imports System.Drawing
Imports GemBox.Spreadsheet

Module Samples

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim ef As ExcelFile = ExcelFile.Load("Template.xlsx")

        Dim workingDays As Integer = 8

        Dim startDate As DateTime = DateTime.Now.AddDays(-workingDays)
        Dim endDate As DateTime = DateTime.Now

        Dim ws As ExcelWorksheet = ef.Worksheets(0)

        ' Find cells with placeholder text and set their values.
        Dim row As Integer, column As Integer
        If ws.Cells.FindText("[Company Name]", True, True, row, column) Then
            ws.Cells(row, column).Value = "ACME Corp"
        End If
        If ws.Cells.FindText("[Company Address]", True, True, row, column) Then
            ws.Cells(row, column).Value = "240 Old Country Road, Springfield, IL"
        End If
        If ws.Cells.FindText("[Start Date]", True, True, row, column) Then
            ws.Cells(row, column).Value = startDate
        End If
        If ws.Cells.FindText("[End Date]", True, True, row, column) Then
            ws.Cells(row, column).Value = endDate
        End If

        ' Copy template row.
        row = 17
        ws.Rows.InsertCopy(row + 1, workingDays - 1, ws.Rows(row))

        ' Fill inserted rows with sample data.
        Dim random As New Random()
        For i As Integer = 0 To workingDays - 1
            Dim currentRow As ExcelRow = ws.Rows(row + i)
            currentRow.Cells(1).SetValue(startDate.AddDays(i))
            currentRow.Cells(2).SetValue(random.Next(1, 12))
        Next

        ' Calculate formulas in worksheet.
        ws.Calculate()

        ef.Save("Template Use.xlsx")

    End Sub

End Module

Check next sample.