We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
|
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.
Create a Web Service
C# using Syncfusion.XlsIO;
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);
}
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;
}
Create a Web Application
Figure 1 – UI Screen Before Calculation
The rest of the fields are used to display calculated values from excel workbook.
C# using WebApplication1.localhost;
C# //Initializes web service WebService1 service = new WebService1();
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();
}
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();
}
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. |
This page will automatically be redirected to the sign-in page in 10 seconds.
You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.
