Replace placeholders

The following example shows you how to use a workbook as a template by finding cells with placeholder values and replacing them.

C# example

using System;
using System.Collections.Generic;
using Independentsoft.Office.Spreadsheet;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Load a template workbook containing placeholder strings
            Workbook book = new Workbook("c:\\test\\template.xlsx");
            Worksheet ws = (Worksheet)book.Sheets[0];

            // Define placeholder replacements
            Dictionary<string, string> replacements = new Dictionary<string, string>
            {
                { "{{COMPANY}}", "Acme Corporation" },
                { "{{DATE}}", DateTime.Now.ToString("yyyy-MM-dd") },
                { "{{AUTHOR}}", "John Smith" },
                { "{{VERSION}}", "1.0" }
            };

            // Iterate all cells and replace placeholders
            IList<Cell> cells = ws.GetCells();
            foreach (Cell cell in cells)
            {
                if (cell.Value != null)
                {
                    string val = cell.Value.ToString();
                    foreach (var entry in replacements)
                    {
                        if (val == entry.Key)
                        {
                            ws[cell.Reference] = new Cell(entry.Value);
                            break;
                        }
                    }
                }
            }

            book.Save("c:\\test\\output.xlsx", true);
            Console.WriteLine("Placeholders replaced successfully.");
        }
    }
}

VB example

Imports System
Imports System.Collections.Generic
Imports Independentsoft.Office.Spreadsheet

Module Module1
    Sub Main(ByVal args As String())

        Dim book As New Workbook("c:\test\template.xlsx")
        Dim ws As Worksheet = DirectCast(book.Sheets(0), Worksheet)

        Dim replacements As New Dictionary(Of String, String)
        replacements.Add("{{COMPANY}}", "Acme Corporation")
        replacements.Add("{{DATE}}", DateTime.Now.ToString("yyyy-MM-dd"))
        replacements.Add("{{AUTHOR}}", "John Smith")
        replacements.Add("{{VERSION}}", "1.0")

        Dim cells As IList(Of Cell) = ws.GetCells()
        For Each cell As Cell In cells
            If cell.Value IsNot Nothing Then
                Dim val As String = cell.Value.ToString()
                For Each entry In replacements
                    If val = entry.Key Then
                        ws(cell.Reference) = New Cell(entry.Value)
                        Exit For
                    End If
                Next
            End If
        Next

        book.Save("c:\test\output.xlsx", True)
        Console.WriteLine("Placeholders replaced successfully.")

    End Sub
End Module

IndependentsoftProfessional software libraries for developers

Spreadsheet .NET > Tutorial > Replace placeholders

Need help? Ask our developers: