How to Add Calculations in Word .DOCX Report Templates Using C#

July 21 2023

There may be times when your .NET applications need to programmatically generate Word documents that can be shared with other groups inside and/or outside your organization. This could include invoices, reports, or other sensitive documents.

Typically, the developer has to use the Microsoft Office API. However, there are several known major limitations to using it:

It cannot be used in web applications
You need MS Word to be installed on the system
The Word Object Model has limited method overloads and also a lack of examples

Ready to Get Started? Download GrapeCity Documents for Word now!

Additionally, wherever there are repeated data and numbers, calculations are usually needed. Word Reports are one of the most common document formats to prepare invoices, purchase orders, financial annual reports, budget reports, real estate or legal reports, and such documents that involve calculated data.

GrapeCity Documents for Word (GcWord) offers Word Report Templates that support processing Word templates designed in Microsoft Word using mustache syntax for data placeholders. The template layout can be created in Word and can contain static content as well as placeholder fields for dynamic values.

GcWord processes the template programmatically in .NET applications, binds with data, and generates bound Word .docx Reports with advanced layouts in seconds. There are several reporting features supported, like lists, tables, repeating multi-table rows, loops, and nested data, conditionally hiding data blocks, use of formatters to display data, and many more.

GcWord Word Report Templates also support adding calculations to GcWord report templates using the ‘calc' expression. This feature allows you to use template data fields and literals in VBA-like expressions. The 'calc’ expressions support arithmetic, logical and other common operators, text and data aggregate functions, and more.

Some of the advantages of using GcWord’s Word Report Templates with calculations are:

The process saves time as calculations are performed automatically
Programmatic calculations reduce the risk of manual errors
It becomes easier to create and send out the reports in the most common .docx format to clients
The documents with calculations are designed professionally with MS Word features

Let's see how to add calculations to Word Reports with Aggregate Functions

Add calculations to Word .docx Reports with Aggregate Functions in C#

Consider the following example where the Invoice report adds the total price of all items in the report:

Using GcWord, you can easily add a ‘calc’ expression to the Word Template along with other data fields defined using mustache syntax.

Steps

1. Open MS Word and design the template. To see how to add data fields to Word Template, please see this blog.

Here is how the template is designed:

2. Now, we are going to replace the expression ‘{{total}:format(C)}’ to use the ‘calc’ expression. The calc expression uses Aggregate function Sum over {{ps.Total}} field, which helps calculate the total sum of the Invoice report.

3. You can also use the Format function over the aggregate to display the calculation result in certain currency formats.

4. In C# code, prepare the integrated data source for the template.

using (var ds = new DataSet())
            {
                // Load the sample database, fetch a random supplier
                // and a random order from that supplier:
                ds.ReadXml(Path.Combine("Resources", "data", "GcNWind.xml"));

                // Database tables used by the invoice:
                var dtSuppliers = ds.Tables["Suppliers"];
                var dtOrders = ds.Tables["OrdersCustomersEmployees"];
                var dtOrdersDetails = ds.Tables["EmployeesProductsOrders"];

                // Collect order data:
                var random = new Random();

                var fetchedIndex = random.Next(dtSuppliers.Select().Count());
                var supplier =
                    dtSuppliers.Select()
                    .Skip(fetchedIndex).Take(1)
                    .Select(it => new
                    {
                        SupplierID = Convert.ToInt32(it["SupplierID"]),
                        CompanyName = it["CompanyName"].ToString(),
                        ContactName = it["ContactName"].ToString(),
                        ContactTitle = it["ContactTitle"].ToString(),
                        Address = it["Address"].ToString(),
                        City = it["City"].ToString(),
                        Region = it["Region"].ToString(),
                        PostalCode = it["PostalCode"].ToString(),
                        Country = it["Country"].ToString(),
                        Phone = it["Phone"].ToString(),
                        Fax = it["Fax"].ToString(),
                        HomePage = it["HomePage"].ToString()
                    }).FirstOrDefault();

                fetchedIndex = random.Next(dtOrders.Select().Count());
                var order = dtOrders.Select()
                    .Skip(fetchedIndex).Take(1)
                    .Select(it => new
                    {
                        OrderDate = it["OrderDate"],
                        OrderID = Convert.ToInt32(it["OrderID"]),
                        CompanyName = it["CompanyName"].ToString(),
                        Name = $"{it["FirstName"]} {it["LastName"]}",
                        Address = $"{it["ShipAddress"]},\n{it["ShipCity"]} {it["ShipRegion"]} {it["ShipPostalCode"]} {it["ShipCountry"]}",
                        Email = GetEmail(it["FirstName"].ToString(), it["LastName"].ToString(), it["CompanyName"].ToString()),
                    }).FirstOrDefault();

                var orderDetails = dtOrdersDetails.Select()
                    .Select(it => new
                    {
                        OrderID = Convert.ToInt32(it["OrderID"]),
                        ProductName = it["ProductName"].ToString(),
                        UnitPrice = Convert.ToDecimal(it["UnitPrice"]),
                        Quantity = Convert.ToDecimal(it["Quantity"]),
                        Total = Convert.ToDecimal(it["UnitPrice"]) * Convert.ToDecimal(it["Quantity"]),
                    })
                    .Where(it => it.OrderID == order.OrderID)
                    .OrderBy(it => it.ProductName).ToList();

                // Finally, prep the integrated data source for the template:
                var data = new
                {
                    o = order,
                    ps = orderDetails,
                    total = orderDetails.Sum(od_ => od_.Total),
                };

5. Load the template in the GcWordDocument object.

// Load the template DOCX, add the data source and process the template:
var doc = new GcWordDocument();
doc.Load(Path.Combine("Resources", "WordDocs", "TemplateInvoice.docx"));

6. Add the data source to the DataSources collection of GcWordDocument.DataTemplate object.

doc.DataTemplate.DataSources.Add("ds", data);

7. Process and save the template.

                doc.DataTemplate.Process();
                doc.Save("Invoice.docx");
}

Here is the resulting report. Note the Total is calculated using the ‘calc’ expression of GcWord Report Templates:

Let's see some other scenarios which are supported by ‘calc’ expressions.

Add calculations with other functions

There are several scenarios possible with 'calc' expressions.

Calculation with Calc Operators

The ‘calc’ expressions can be used with binary and unary operators to perform various operations on data. For example, to insert the remainder of the division of two data fields, the template '{{calc ds.a Mod ds.b)}}' can be used.

The following is the data and syntax to use for using the ‘calc' expression with various calc operators:

Data {{ds.a}}: 9241.27
Data {{ds.b}}: 18493.96
Data {{ds.ta}}: massa
Data {{ds.tb}}: sit
Add {{calc ds.a + ds.b}}: 27735.23
Subtract {{calc ds.a - ds.b}}: -9252.689999999999
Multiply {{calc ds.a * ds.b}}: 170907677.7292
Divide {{calc ds.a / ds.b}}: 0.4996912505488279
Modulus {{calc ds.a Mod ds.b}}: 9241
and more examples

Have a look at the complete demo - Calc Operators.

Calculation with Arithmetic Operations

The ‘calc’ expression can be used with arithmetic operators to perform arithmetic operations on data. For example, to insert the sum of two data fields, the template '{{calc ds.a + ds.b)}}' can be used.

Here are examples of how the ‘calc’ expression can be used with data fields and arithmetic operations:

a = 5877.39, b = 4637.03
{{ calc ds.a + ds.b }} : 10514.42
{{ calc ds.a - ds.b }} : 1240.3600000000006
{{ calc ds.a * ds.b }} : 27253633.7517
{{ calc ds.a / ds.b }} : 1.2674901822934077

Have a look at the complete demo - Arithmetic Operations.

Calculations with Aggregate functions

The 'calc' expression can be used with aggregate functions for data aggregation. Unlike other report template constructs, aggregates do not cause repetition of content in the generated document. Instead, they aggregate data and insert the (single) resulting value into the document.

For example, to insert the sum of a field's values in all records of a data source, the template '{{calc Sum(ds.value)}}' can be used. Aggregate functions' arguments can be expressions involving data, other functions, and constants.

The following are examples of how the ‘calc’ expression can be used with Aggregate Functions:

{{ calc Average(ds.value) }} : 30658.291666666668
{{ calc Count(ds.value) }} : 12
{{ calc First(ds.value) }} : 72304.89
{{ calc Last(ds.value) }} : 31347.48
{{ calc Max(ds.value) }} : 122548.25
{{ calc Min(ds.value) }} : 3012.81
{{ calc Sum(ds.value) }} : 367899.5

Have a look at the complete demo - Aggregate Functions.

Calculations with Text Functions

The ‘calc’ expression can be used with text functions to perform various operations on strings. For example, to insert the concatenation of three text data fields, the template '{{calc Concat(ds.a, ds.b, ds.c)}}' can be used.

The following are examples of how the ‘calc’ expression can be used with Text Functions:

Data {{ds.a}} : tempus
Data {{ds.b}} : tempus
Data {{ds.c}} : praesent
Data {{ds.n}} : 1
Asc {{calc Asc(ds.a)}} : 116
Concat {{calc Concat(ds.a, ds.b, ds.c)}} : tempustempuspraesent
Contains {{calc Contains(ds.a, "s")}} : True
EndsWith {{calc EndsWith(ds.a, "s")}} : True
and text functions covered

Have a look at the complete demo - Text Functions.

Calculations with Date/Time Functions

The ‘calc’ expression can also be used with date and time functions that can be used with the ‘calc' report templates feature. The following example shows the usage of the 'calc’ expression with various date and time functions:

{{ calc Now() }} : 7/13/2023 2:29:12 PM
{{ calc Today() }} : 7/13/2023 12:00:00 AM
{{ calc UtcNow() }} : 7/13/2023 2:29:12 PM
{{ calc AddTicks(Now(), 100) }} : 7/13/2023 2:29:12 PM
{{ calc AddMilliSeconds(Now(), 60 * 1000) }} : 7/13/2023 2:30:12 PM
{{ calc AddSeconds(Now(), 60) }} : 7/13/2023 2:30:12 PM
{{ calc AddMinutes(Now(), 1) }} : 7/13/2023 2:30:12 PM
{{ calc AddHours(Now(), 2) }} : 7/13/2023 4:29:12 PM
{{ calc AddDays(Today(), 7) }} : 7/20/2023 12:00:00 AM
{{ calc AddMonths(Today(), 4) }} : 11/13/2023 12:00:00 AM
{{ calc AddYears(Today(), 10) }} : 7/13/2033 12:00:00 AM
and more functions covered

Have a look at the complete demo - Date/Time Functions.

Calculations with Math Functions

The ‘calc’ expression can also be used with Math functions to perform mathematical operations. A few of them are as follows:

{{ ds.value }} : 3.141592653589793
{{ calc Abs(-ds.value) }} : 3.141592653589793
{{ calc Round(ds.value, 2) }} : 3.14
{{ calc Acos(ds.value) }} : NaN
{{ calc Asin(ds.value) }} : NaN
{{ calc Atan(ds.value) }} : 1.2626272556789118
{{ calc Atan2(ds.value, 1) }} : 1.2626272556789118
and many more Math functions covered

Take a look at the complete demo - Math Functions.

Calculations with Conversion Functions

The ‘calc’ expression can be used with other Conversion Functions, for example:

{{ calc CBool("True") }} : True
{{ calc CBool("False") }} : False
{{ calc CByte("127") }} : 127
{{ calc CChar("䉉") }} : 䉉
{{ calc CDate("1-jan-2001") }} : 1/1/2001 12:00:00 AM
{{ calc CDbl("123.456") }} : 123.456
{{ calc CDec("123.456") }} : 123.456
{{ calc CInt("123") }} : 123
{{ calc CLng("123456789") }} : 12345678
and many more

Here is the complete demo - Conversion Functions.

Calculations with Logical Functions

The ‘calc’ expression can be used with logical functions, for example:

{{ calc Iif(ds.value, "Iif result true", "Iif result false") }} : Iif result true
{{ calc IsNull("Nothing") }} :

Have a look at the complete demo - Logical Functions.

Calculations with Format Function

The ‘calc’ expression can be used with optional format strings and locale arguments.

The following are some date formatting examples:

Date with default formatting: {{calc Format(dsDate.value)}} - 4/12/2023
Date formatted as ('D', 'en-US'):{{calc Format(dsDate.value, \"D\", \"en-US\")}} - Wednesday, April 12, 2023
Date formatted as ('D', 'fr-FR'): {{calc Format(dsDate.value, \"D\", \"fr-FR\")}} - mercredi 12 avril 2023
Date formatted as ('D', 'ja-JP'): {{calc Format(dsDate.value, \"D\", \"ja-JP\")}} - 2023年4月12日水曜日

Here are more Format Function examples - Calc Format.

Calculations with Collection State Functions

The ‘calc’ expression can be used with collection-based state functions: IsFirst, IsLast, and Index, which can be applied to iterated ranges. These functions return the state of the current iteration over a collection. In your Word reports, you can implement scenarios like these:

Show only even elements of the collection
Show the number of elements and the current element
Check if a word is the first or last element in the collection
Insert a word at a certain location

The following are a few examples of using the 'calc' expression with Collection based functions:

"Record {{calc Index(ds)+1}} of {{calc Count(ds)}}: {{ds.name}}{{if IsFirst(ds)}} (first){{else}}{{if IsLast(ds)}} (last){{endif}}{{endif}}");
Result
- Record 1 of 20: Australasian Mediterranean Sea (first)
- Record 2 of 20: Philippine Sea
- Record 3 of 20: Coral Sea
- Record 4 of 20: South China Sea
- Record 5 of 20: Sargasso Sea
- Record 6 of 20: Caribbean Sea
- Record 7 of 20: Mediterranean Sea
- and more