Excel Template Use

The following example shows how you can 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 shows 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.

Excel file updated with GemBox.Spreadsheet
Screenshot of Excel file updated with GemBox.Spreadsheet
Upload your file (Drag file here) 
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%");

        int workingDays = %WorkingDays%;

        var startDate = DateTime.Now.AddDays(-workingDays);
        var endDate = DateTime.Now;

        var worksheet = workbook.Worksheets[0];

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

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

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

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

        workbook.Save("Template Use.%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%")

        Dim workingDays As Integer = %WorkingDays%

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

        Dim worksheet = workbook.Worksheets(0)

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

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

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

        ' Calculate formulas in worksheet.
        worksheet.Calculate()

        workbook.Save("Template Use.%OutputFileType%")
    End Sub
End Module

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.

Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.

Published: December 13, 2018 | Modified: September 3, 2020 | Author: Josip Kremenic