How to perform Excel formula calculation in web service using Essential XlsIO?

This topic gives an overview of how to create a web service that loads an excel template and displays calculated values using Essential XlsIO.

Summary

The following steps are to be followed to achieve this.

1. Create a Web Service project

1. Initialize XlsIO and perform excel formula calculations

2. Create a Web Application project

1. Refer the Web Service link and initialize the service in the Web Application
2. Update calculated values in the web application through web service

Create a Web Service

1. Create an empty web application and add Web Service (ASMX) item into the project.

2. Refer the below dlls

1. Syncfusion.Core.dll
2. Syncfusion.Compression.Base.dll
3. Syncfusion.XlsIO.Web.dll

3. Use the namespace Syncfusion.XlsIO in web service class (WebService1).

C#

4. Initialize ExcelEngine object and open the excel workbook template (FederalTaxCalculatorTemplate.xlsx) in web service class (WebService1).

C#

5. Add SetValues and GetValues methods in the class (WebService1). The SetValues method sets the values into worksheet. The GetValues method returns XlsIO calculated values from worksheet.

C#

6. Now, right-click at WebService1.asmx file from Solution Explorer and select View in Browser menu.

7. You can find a page (for ex. http://localhost:53774/WebService1.asmx) in your browser. Copy this link as this has to be used as a web service reference in the following Web Application project.

Create a Web Application

1. Create an empty web application and add a Web Form item named default.aspx into the project.

2. The scenario used here is to calculate Federal Tax Estimation. So, the web page must be designed accordingly. The following screen-shot shows the UI part of the application.

Figure 1 – UI Screen Before Calculation

3.  Here, the input fields are:
   

1. Tax Filing Status
2. No. of filers over age 65
3. No. of personal exemptions
4. No. of dependent children
5. Gross annual income
6. Adjustments
7. Itemized deduction

The rest of the fields are used to display calculated values from excel workbook.

4. Now, right-click WebApplication1 project from Solution Explorer and click Add->Service Reference->Advanced->Add Web Reference. Paste the web service link copied earlier in the URL field and click Add Reference button.

5. Following namespace must be used in _default class to initialize web service and access its public methods.

C#

6. Initialize the class WebService1 in the _default class.

C#

7. Get calculated values from worksheet to display in its respective UI fields. The following code snippet can be used to achieve this.

C#

8. Submit button sets the input values to the worksheet and returns its calculated values. The following code snippet illustrates that.

C#

9. The following screen-shot shows the output of the sample. For the given Gross Income 75000, the tax estimation is calculated by XlsIO and the calculated values are displayed in its respective fields as shown.

Figure 2 – UI Screen After Calculation

Note: The tax calculation is just to illustrate the calculation at API levels using Essential XlsIO and not exactly the scale of federal tax estimation.

The above sample can be downloaded here.