Edit and save Excel templates in C# and VB.NET

When dynamically creating spreadsheets like invoices, reports, timesheets, etc., it's more common to use an existing Excel template, edit it as you wish, and save it as a new file, rather than generating a new workbook from scratch with your C# or VB.NET code.

With GemBox.Spreadsheet you can load a template file with a predefined layout, formatting, and formulas as an input file, fill it with your data (text, images, and other information) and save the modified spreadsheet as an output file.

The following example shows how you can generate an invoice from a template workbook by inserting data into the spreadsheet's content.

Saved Excel file created by modifying or editing template workbook in C# and VB.NET — Screenshot of edited and saved Excel template workbook

C#
VB.NET

View on GitHub

using System;
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        int numberOfItems = %NumberOfItems%;
        var startDate = DateTime.Today.AddDays(-numberOfItems);
        var endDate = DateTime.Today;

        // Load an Excel template.
        var workbook = ExcelFile.Load("%#Template.xlsx%");

        // Get template sheet.
        var worksheet = workbook.Worksheets[0];

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

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

        // Fill copied rows with sample data.
        var random = new Random();
        for (int i = 0; i < numberOfItems; 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 a sheet.
        worksheet.Calculate();

        // Save the modified Excel template to output file.
        workbook.Save("Output.%OutputFileType%");
    }
}

Imports System
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        Dim numberOfItems As Integer = %NumberOfItems%
        Dim startDate = DateTime.Today.AddDays(-numberOfItems)
        Dim endDate = DateTime.Today

        ' Load an Excel template.
        Dim workbook = ExcelFile.Load("%#Template.xlsx%")

        ' Get template sheet.
        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]", row, column) Then
            worksheet.Cells(row, column).Value = "ACME Corp"
        End If
        If worksheet.Cells.FindText("[Company Address]", row, column) Then
            worksheet.Cells(row, column).Value = "240 Old Country Road, Springfield, IL"
        End If
        If worksheet.Cells.FindText("[Start Date]", row, column) Then
            worksheet.Cells(row, column).Value = startDate
        End If
        If worksheet.Cells.FindText("[End Date]", row, column) Then
            worksheet.Cells(row, column).Value = endDate
        End If

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

        ' Fill copied rows with sample data.
        Dim random As New Random()
        For i As Integer = 0 To numberOfItems - 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 a sheet.
        worksheet.Calculate()

        ' Save the modified Excel template to output file.
        workbook.Save("Output.%OutputFileType%")

    End Sub
End Module

Instead of using custom placeholder text, like [Company Name], you can use ExcelWorksheet.NamedRanges which are descriptive names that can represent cells.

Also, you can avoid the manual data insertion of the tabular data that's shown in the above example by inserting it using a DataTable object. For more information, see the DataTable to Sheet example.

With GemBox.Spreadsheet you can use any file of a supported input format as your template and generate the desired output files based on it.

But in general, to create a template with Microsoft Excel you would need to save the workbook as an XLTX, XLTM, or XLT file. Or you could take an existing template from Office templates & themes.

Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy

Published: December 13, 2018 | Modified: May 15, 2023 | Author: Josip Kremenic

Edit and save Excel templates in C# and VB.NET

See also

Next steps