My Profile
Sign Out
Tickets
Knowledge Base
Login
Create Ticket
Articles in this section
Category / Section
  2. ASP.NET Web Forms (Classic)
  3. XlsIO

How to perform Excel formula calculation in ASP.NET Web Forms XlsIO?

15 mins read

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

 

  1. 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.

 

  1. Refer the below dlls
  1. Syncfusion.Core.dll
  2. Syncfusion.Compression.Base.dll
  3. Syncfusion.XlsIO.Web.dll

 

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

 

C#

using Syncfusion.XlsIO;

 

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

 

C#

//Represents Excel application
static ExcelEngine excelEngine;
//Represents Excel workbook
static IWorkbook workbook;
static WebService1()
{
    //Step 1 : Instantiate the spreadsheet creation engine.
    excelEngine = new ExcelEngine();
    //Step 2 : Instantiate the excel application object.
    IApplication application = excelEngine.Excel;
 
    string filePath = HttpContext.Current.Server.MapPath("/App_Data/FederalTaxCalculatorTemplate.xlsx");
 
    // An existing workbook is opened.
    //[Equivalent to opening an existing workbook in MS Excel]
    workbook = excelEngine.Excel.Workbooks.Open(filePath);
}

 

  1. 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#

/// <summary>
/// Sets the provided values to the excel workbook
/// </summary>
/// <param name="inputValues">Array of input values to fill the workbook</param>
[WebMethod]
public void SetValues(double[] inputValues)
{
    List<string> calculatedList = new List<string>();
 
    lock (workbook)
    {
        IWorksheet sheet = workbook.Worksheets[0];
 
        //Filing Status
        sheet.ComboBoxes[0].SelectedIndex = Convert.ToInt16(inputValues[0]);
        //Over Age 65
        sheet["F8"].Number = Convert.ToDouble(inputValues[1]);
        //Personal Excemptions
        sheet["F9"].Number = Convert.ToDouble(inputValues[2]);
        //Dependant Children
        sheet["F10"].Number = Convert.ToDouble(inputValues[3]);
        //Gross Annual Income
        sheet["F12"].Number = Convert.ToDouble(inputValues[4]);
        //Adjustments
        sheet["F13"].Number = Convert.ToDouble(inputValues[5]);
        //Itemized Deductions
        sheet["F15"].Number = Convert.ToDouble(inputValues[6]);  
 
        sheet.EnableSheetCalculations();
    }
} 
 
/// <summary>
/// Returns workbook cell values
/// </summary>
/// <returns>String collection to display the values in UI</returns>
[WebMethod]
public List<string> GetValues()
{
    List<string> valuesList = new List<string>();
    lock (workbook)
    {
        IWorksheet sheet = workbook.Worksheets[0];
 
        sheet.EnableSheetCalculations();
        sheet.CalcEngine.UpdateCalcID();
        int selectedIndex = 0;
        if (sheet.ComboBoxes.Count > 0)
            selectedIndex = sheet.ComboBoxes[0].SelectedIndex - 1;
        //0 Filing Status
        valuesList.Add(Convert.ToString(selectedIndex));
        //1 Over Age 65
        valuesList.Add(Convert.ToString(sheet["F8"].Number));
        //2 Personal Excemptions
        valuesList.Add(Convert.ToString(sheet["F9"].Number));
        //3 Dependant Children
        valuesList.Add(Convert.ToString(sheet["F10"].Number));
        //4 Gross Annual Income
        valuesList.Add(Convert.ToString(sheet["F12"].CalculatedValue));
        //5 Adjustments
        valuesList.Add(Convert.ToString(sheet["F13"].Number));
        //6 Adjusted Gross Income
        valuesList.Add(Convert.ToString(sheet["F14"].CalculatedValue));
        //7 Itemized Deductions
        valuesList.Add(Convert.ToString(sheet["F15"].Number));  
        double grossIncome = Convert.ToDouble(sheet["F14"].CalculatedValue);
        if (grossIncome > 0)
        {
            //8 Standard Deductions
            valuesList.Add(Convert.ToString(sheet["F16"].CalculatedValue));
            //9 Excemption Dollars
            valuesList.Add(Convert.ToString(sheet["F17"].CalculatedValue));  
        }
        else
        {
            //8 Standard Deductions
            valuesList.Add(Convert.ToString(0));
            //9 Excemption Dollars
            valuesList.Add(Convert.ToString(0));  
        }
 
        //10 Taxable Income
        valuesList.Add(Convert.ToString(sheet["F18"].CalculatedValue));
        //11 Tax Estimate
        valuesList.Add(Convert.ToString(sheet["F21"].CalculatedValue));  
    }
    return valuesList;
}

 

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

 

 

Create a Web Application

 

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

 

  1. 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.

 

UI screen before calculation using XlsIO

Figure 1 – UI Screen Before Calculation

 

  1.  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.

  1. 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.

 

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

 

C#

using WebApplication1.localhost;

 

  1. Initialize the class WebService1 in the _default class.

 

C#

//Initializes web service
WebService1 service = new WebService1();

 

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

 

C#

/// <summary>
/// Fills UI controls with the values retrieved from Excel workbook
/// </summary>
private void FillControlsWithValues()
{
    string[] result = service.GetValues();
 
    DpnFilingStatus.SelectedIndex = Convert.ToUInt16(result[0]);
 
    txtAbove65.Text = result[1];
    txtPersonalExemp.Text = result[2];
    txtDependChild.Text = result[3];
 
    txtGrossIncome.Text = result[4];
    txtAdjustments.Text = result[5];
    txtAdjustedGrossIncome.Text = result[6];
 
    txtItemizedDeductions.Text = result[7];
    txtStandardDeductions.Text = result[8];
    txtExempDollars.Text = result[9];
    txtTaxableIncome.Text = result[10];
 
    txtIncomeTax.Text = result[11];
 
    txtGrossIncome.Focus();
}

 

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

 

C#

/// <summary>
/// Submits the input value into the workbook and 
/// displays the calculated values from the workbook
/// </summary>
/// <param name="sender">button object</param>
/// <param name="e">event argument</param>
protected void btnSubmit_Click(object sender, EventArgs e)
{
    double[] inputValues = new double[7];
 
    inputValues.SetValue(Convert.ToDouble(DpnFilingStatus.SelectedIndex + 1), 0);
    inputValues.SetValue(Convert.ToDouble(txtAbove65.Text), 1);
    inputValues.SetValue(Convert.ToDouble(txtPersonalExemp.Text), 2);
    inputValues.SetValue(Convert.ToDouble(txtDependChild.Text), 3);
 
    inputValues.SetValue(Convert.ToDouble(txtGrossIncome.Text), 4);
    inputValues.SetValue(Convert.ToDouble(txtAdjustments.Text), 5);
 
    inputValues.SetValue(Convert.ToDouble(txtItemizedDeductions.Text), 6);
 
    //Enter the input values in to the assigned cells
    service.FillValues(inputValues);
    //Fills UI controls with the values retrieved from Excel workbook
    FillControlsWithValues();
}

 

  1. 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.

UI screen after calculation using XlsIO

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.


Note:

A new version of Essential Studio for ASP.NET is available. Versions prior to the release of Essential Studio 2014, Volume 2 will now be referred to as a classic versions.The new ASP.NET suite is powered by Essential Studio for JavaScript providing client-side rendering of HTML 5-JavaScript controls, offering better performance, and better support for touch interactivity. The new version includes all the features of the old version, so migration is easy.

The Classic controls can be used in existing projects; however, if you are starting a new project, we recommend using the latest version of Essential Studio for ASP.NET. Although Syncfusion will continue to support all Classic Versions, we are happy to assist you in migrating to the newest edition.

For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls. If you have any queries or require clarifications, please let us know in the comments section below.

You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please  to leave a comment
Access denied
Access denied