Simple Template

You can use expressions with braces {{ }} in any cell of any sheet of the template workbook and Excel will find their values at run-time. How? ClosedXML.Report adds a hidden worksheet in a report workbook and transfers values of all fields for the current record. Then it names all these data cells.

Excel formulas, in which variables are added, must be escaped &. As an example: &=CONCATENATE({{Addr1}}, " ", {{Addr2}}). Pay attention that escaped formulas must follow international, non-localized syntax which includes English names of functions, comma as a value separator, etc. For example, in the Russian version of Excel you could use formula =СУММ(A1:B2; E1:F2) for ordinary cells, but the escaped formula should be &=SUM(A1:B2, E1:F2).

Cells with field formulas can be formatted in any known way, including conditional formatting.

Here is a simple example:

...
        var template = new XLTemplate(@".\Templates\template.xlsx");
        var cust = db.Customers.GetById(10);

        template.AddVariable(cust);
        // OR
        template.AddVariable("Company", cust.Company);
        template.AddVariable("Addr1", cust.Addr1);
        template.AddVariable("Addr2", cust.Addr2);
...

public class Customer
{
	public double CustNo { get; set; }
	public string Company { get; set; }
	public string Addr1 { get; set; }
	public string Addr2 { get; set; }
	public string City { get; set; }
	public string State { get; set; }
	public string Zip { get; set; }
	public string Country { get; set; }
	public string Phone { get; set; }
	public string Fax { get; set; }
	public double? TaxRate { get; set; }
	public string Contact { get; set; }
}