Scalable strategy for Excel formula in web applications
By using XlsIO we can do the calculation in server side and view the result in the client side using the memory effectively. This topic gives an overview of how to create a web service that loads an excel template and displays calculated values using Essential XlsIO and their scalability.
Steps to achieve this
- Create a Web Service project
- Initialize XlsIO and perform excel formula calculations
- Create a Web Application project
- Refer the Web Service link and initialize the service in the Web Application
- Update calculated values in the web application through web service
Create a Web Service
- Create an empty web application and add Web Service (ASMX) item into the project.
- Refer the below dlls
- Syncfusion.Compression.Base.dll
- Syncfusion.XlsIO.Web.dll
- Use the namespace Syncfusion.XlsIO in web service class (WebService1).
C#
using Syncfusion.XlsIO;
Below code snippet demonstrates on how to create a web service program for calculating values.
- 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); }
- Add SetValues and GetValues and GetDefaultValues methods in the class (WebService1). The SetValues method sets the values into worksheet. The GetValues method returns XlsIO calculated values from the 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; } /// <summary> /// Get Default values /// </summary> /// <returns>list of default values</returns> [WebMethod] public List<string> GetDefaultValues() { List<string> valuesList = new List<string>(); //0 Filing Status valuesList.Add("1"); //1 Over Age 65 valuesList.Add("0"); //2 Personal Excemptions valuesList.Add("2"); //3 Dependant Children valuesList.Add("1"); //4 Gross Annual Income valuesList.Add("0"); //5 Adjustments valuesList.Add("0"); //6 Adjusted Gross Income valuesList.Add("0"); //7 Itemized Deductions valuesList.Add("0"); //8 Standard Deductions valuesList.Add("0"); //9 Excemption Dollars valuesList.Add("0"); //10 Taxable Income valuesList.Add("0"); //11 Tax Estimate valuesList.Add("0"); return valuesList; }
- Host the Web Service using IIS (Internet Information Services) and browse the web service and copy the link.
Create a Web Application
- Create an empty web application and add a Web Form item named default.aspx into the project.
- 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
- Here, the input fields are:
- Tax Filing Status
- No. of filers over age 65
- No. of personal exemptions
- No. of dependent children
- Gross annual income
- Adjustments
- Itemized deduction
The rest of the fields are used to display calculated values from excel workbook.
- 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.
- Following namespace must be used in _default class to initialize web service and access its public methods.
C#
using WebApplication1.localhost;
- Initialize the class WebService1 in the _default class.
C#
//Initializes web service WebService1 service = new WebService1();
- Get calculated values from worksheet to display in its respective UI fields. The following code snippet can be used to achieve this.
C#
protected void Page_Init(object sender, EventArgs e) { string[] result = service.GetDefaultValues(); FillControlsWithValues(result); } /// <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.SetValues(inputValues); string[] result = service.GetValues(); //Fills UI controls with the values retrieved from Excel workbook FillControlsWithValues(result); }
- Submit button sets the input values to the worksheet and returns its calculated values. The following code snippet illustrates that.
C#
/// <summary> /// Fills UI controls with the values retrieved from Excel workbook /// </summary> private void FillControlsWithValues(string[] result) { 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(); }
- 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.
Performance:
- The memory consumed in the server per user is 10MB. If 10 users access this service an approximate of 100MB is consumed in the server.
- The execution is faster as the service returns values from the server.
- The calculated values doesn’t overwrite the cell values and doesn’t affect the calculation of other users.