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 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.Core.dll Syncfusion.Compression.Base.dll Syncfusion.XlsIO.Web.dll Use the namespace Syncfusion.XlsIO in web service class (WebService1). C# using Syncfusion.XlsIO; 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 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; } Now, right-click at WebService1.asmx file from Solution Explorer and select View in Browser menu. 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 SEQ Figure \* ARABIC 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# /// <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(); } 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(); } 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 SEQ Figure \* ARABIC 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!
Syncfusion® Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. It also, converts Excel documents to PDF files. Using this library, you can use formulas within and across worksheets. While working with formulas, sometimes you may encounter an 'Unexpected token' exception. This article explains how we can avoid the unexpected token exception while using formulas in XlsIO. What is unexpected token exception? An Unexpected token exception occurs when some arguments or tokens are misplaced or misused in a formula. In the Syncfusion® Excel (XlsIO) library, while parsing the formula, it is split into tokens. When a token is not valid, unexpected token exception will be thrown. The argument separator in a formula can be a reason for the exception. An unexpected token exception occurs if a different regional setting is used other than English and a comma is used in the formula. You must set the separator to the workbook before assigning the formula depending on your current UI culture. For example, consider that we need to add two numbers using formula as below. C# // Data to be used in formula worksheet["B1"].Number = 10; worksheet["B2"].Number = 20; // Formula to add two numbers worksheet["B3"].Formula = "=SUM(B1,B2)"; VB.NET ‘Data to be used in formula worksheet("B1").Number = 10 worksheet("B2").Number = 20 'Formula to add two numbers worksheet("B3").Formula = "=SUM(B1,B2)" In this case, we can avoid this exception by setting arguments separator as ‘;’ before using the formula. C# // Set separators for formula parsing workbook.SetSeparators(';', ','); VB.NET 'Set separators for formula parsing workbook.SetSeparators(";", ",") Steps to avoid the exception while using formulas in XlsIO, programmatically: Step 1: Create a new C# console application project. Create a new C# console application Step 2: Install the Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework application from NuGet.org. Install NuGet package Step 3: Include the following namespace in the Program.cs file. C# using Syncfusion.XlsIO; VB.NET Imports Syncfusion.XlsIO Step 4: The following C#, VB.NET code shows how to avoid the unexpected token exception while using formulas in XlsIO C# // Instantiate the spreadsheet creation engine using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; // Set separators for formula parsing workbook.SetSeparators(';', ','); worksheet["A1"].Text = "Value 1"; worksheet["A2"].Text = "Value 2"; worksheet["A3"].Text = "Sum"; worksheet["B1"].Number = 10; worksheet["B2"].Number = 20; // Formula to add two numbers worksheet["B3"].Formula = "=SUM(B1;B2)"; // Save and close the workbook worksheet.UsedRange.AutofitColumns(); workbook.SaveAs("Output.xlsx"); } VB.NET 'Instantiate the spreadsheet creation engine Using excelEngine As ExcelEngine = New ExcelEngine() Dim application As IApplication = excelEngine.Excel Dim workbook As IWorkbook = application.Workbooks.Create(1) Dim worksheet As IWorksheet = workbook.Worksheets(0) 'Set separators for formula parsing workbook.SetSeparators(";", ",") worksheet("A1").Text = "Value 1" worksheet("A2").Text = "Value 2" worksheet("A3").Text = "Sum" worksheet("B1").Number = 10 worksheet("B2").Number = 20 'Formula to add two numbers worksheet("B3").Formula = "=SUM(B1;B2)" 'Save and close the workbook worksheet.UsedRange.AutofitColumns() workbook.SaveAs("Output.xlsx") End Using A complete working example of how to avoid unexpected token exception while using formulas in XlsIO can be downloaded from Avoid Unexpected Token Exception.zip. By executing the program, you will get the output Excel file as shown below: Output Excel document Tip:If you face this exception in other similar cases, initially you should check whether all the tokens in the formula are used correctly. Also, separator in formula should match the current UI culture. If you are not sure about the separator to be used, set separators on your own before using the formula. To know more about formulas in Syncfusion Excel (XlsIO) library, please refer the documentation. Refer here to explore the rich set of Syncfusion® Excel (XlsIO) library features. See Also: I face different exceptions after upgraded XlsIO to the version 6.2 and later. Is there a solution available? How to avoid generic error occurred in GDI+ while saving EMF image in Azure? Why the “type initializer exception” thrown while running the application in production server? Why the “Formula string can’t be empty” exception was thrown? Note:Starting with v16.2.0.x, if you reference Syncfusion® assemblies from a trial setup or from the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering the Syncfusion® license key in your application to use the components without a trial message.ConclusionI hope you enjoyed learning about how can we avoid the 'Unexpected token' exception while using formula in XlsIO.You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion® components.If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!
Syncfusion® Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. It also converts Excel documents to PDF files. Using this library, you can copy and paste only the formula values of cells while ignoring the formula. Steps to paste only the formula value of cell, programmatically: Create a new C# console application project. Create a new C# console application project Install the Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework application from NuGet.org. Install NuGet package to the project You can copy a range of cells to another location using the CopyTo method of the IRange interface with different copy range options available under the enum ExcelCopyRangeOptions. C# // Copy source range to destination range worksheet.Range["C1:C5"].CopyTo(worksheet.Range["E1"], ExcelCopyRangeOptions.None); VB.NET 'Copy source range to destination range worksheet.Range("C1:C5").CopyTo(worksheet.Range("E1"), ExcelCopyRangeOptions.None) Include the following namespaces in the Program.cs file. C# using Syncfusion.XlsIO; VB.NET Imports Syncfusion.XlsIO Include the following code snippet in the main method of Program.cs file to copy and paste only the formula value of a cell. C# using (ExcelEngine excelEngine = new ExcelEngine()) { // Instantiate the Excel application object IApplication application = excelEngine.Excel; // Create a new Excel workbook IWorkbook workbook = application.Workbooks.Create(1); // Get the first worksheet in the workbook into IWorksheet IWorksheet worksheet = workbook.Worksheets[0]; // Assign data in the worksheet worksheet.Range["A1"].Number = 1; worksheet.Range["A2"].Number = 2; worksheet.Range["A3"].Number = 3; worksheet.Range["A4"].Number = 4; worksheet.Range["A5"].Number = 5; // Assign formulas in the worksheet worksheet.Range["C1"].Formula = "=A1+A1"; worksheet.Range["C2"].Formula = "=A2+A2"; worksheet.Range["C3"].Formula = "=A3+A3"; worksheet.Range["C4"].Formula = "=A4+A4"; worksheet.Range["C5"].Formula = "=A5+A5"; // Copy only the formula values from source range to destination range worksheet.Range["C1:C5"].CopyTo(worksheet.Range["E1"], ExcelCopyRangeOptions.None); // Save the Excel document workbook.SaveAs("Output.xlsx"); } VB.NET Using excelEngine As ExcelEngine = New ExcelEngine() 'Instantiate the Excel application object Dim application As IApplication = excelEngine.Excel 'Create a new Excel workbook Dim workbook As IWorkbook = application.Workbooks.Create(1) 'Get the first worksheet in the workbook into IWorksheet Dim worksheet As IWorksheet = workbook.Worksheets(0) 'Assign data in the worksheet worksheet.Range("A1").Number = 1 worksheet.Range("A2").Number = 2 worksheet.Range("A3").Number = 3 worksheet.Range("A4").Number = 4 worksheet.Range("A5").Number = 5 'Assign formulas in the worksheet worksheet.Range("C1").Formula = "=A1+A1" worksheet.Range("C2").Formula = "=A2+A2" worksheet.Range("C3").Formula = "=A3+A3" worksheet.Range("C4").Formula = "=A4+A4" worksheet.Range("C5").Formula = "=A5+A5" 'Copy only the formula values from source range to destination range worksheet.Range("C1:C5").CopyTo(worksheet.Range("E1"), ExcelCopyRangeOptions.None) 'Save the Excel document workbook.SaveAs("Output.xlsx") End Using A complete working sample of how to copy and paste only the formula values of cells while ignoring the formulas can be downloaded from Copy-Values.zip. By executing the program, you will get the output Excel document as follows. Output Excel document Take a moment to peruse the documentation where you will find other options like enable and disable calculation, reading and writing formula, calculated value, array of formula, incremental and external formula, named range and more. Click here to explore the rich set of Syncfusion® Excel (XlsIO) library features. Note:Starting with v16.2.0.x, if you reference Syncfusion® assemblies from a trial setup or from the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering the Syncfusion® license key in your application to use the components without a trial message. ConclusionI hope you enjoyed learning about paste only the formula value of Excel cell in C#, VB.NET.You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion® components.If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!
