Intro

By using Excel's existing internal calculator, ExcelWriter allows you to make calculations in your report.

In order to calculate values in some cells of a report, simply format those cells in the template file to have the desired formula.  Then, when the data is bound to the template and the report is open, the cells with formulas in them will be updated. 

In this example we have a sample loan calculator.  By simply binding the loan amount, annual interest rate, loan period and the start date of the loan, we can create a  report with a plan for how to pay off the loan in the given loan period. 

This sample requires OfficeWriter Enterprise Edition to be installed because the OfficeWriter Grouping and Nesting is only available in the Enterprise Edition of the product.

 

Code

public class LoanCalculator
    {
        //Variables needed for calculations:
        private double principal = 1000.00d;
        private double apr = 2.99d;
        private int numyears = 5;
        private DateTime date = DateTime.Now;

        // <summary>
        // Build the report with ExcelTemplate
        // </summary>
        public void GenerateReport()
        {

            //Create an instance of ExcelTemplate
            ExcelTemplate xlt = new ExcelTemplate();

            //Open the template workbook
            string templatePath = @"..\..\ExcelTemplateFiles\LoanCalculatorTemplate.xlsx";
            xlt.Open(templatePath);

            // Pass the data to the BindCellData method
            // and specify the data source name

            DataBindingProperties bindingProperties = xlt.CreateDataBindingProperties();
            xlt.BindCellData(principal, "Principal", bindingProperties);
            xlt.BindCellData(apr / 100, "APR", bindingProperties);
            xlt.BindCellData(numyears, "N", bindingProperties);
            xlt.BindCellData(date, "Date", bindingProperties);

            // Call the Process() method to populate the template with the data source values
            xlt.Process();

            // Save the report
            xlt.Save(@"..\..\ExcelOutputFiles\LoanCalculator_output.xlsx");
        }
    }

Downloads