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. In this article, we will explore how to add a nested IF function in an Excel document using the following code snippet. C# worksheet.Range["C" + row].Formula = "=IF(ISBLANK(A" + row + "), \"\", IF(OR(ISNUMBER(SEARCH(\"SMSF\", A" + row + ")), ISNUMBER(SEARCH(\"Trust\", A" + row + ")), ISNUMBER(SEARCH(\"Company\", A" + row + ")), ISNUMBER(SEARCH(\"PARTNERSHIP\", A" + row + "))), B" + row + ", \"\"))"; A complete working sample of how to add nested if function in Excel document using C# can be downloaded from here. See Also: Working with Formula using XlsIO Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheets or workbooks, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to a worksheet using Template Markers, and most importantly PDF and Image conversions with code examples. Refer here to explore the rich set of Syncfusion® Excel (XlsIO) library features. Conclusion I hope you enjoyed learning about how to add nested IF function in Excel document using C#. You can refer to our ASP.NET Core XIsIO feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our ASP.NET Core XIsIO example to understand how to create and manipulate data. 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! Note: Starting with v16.2.0.x, if you reference Syncfusion® assemblies from the trial setup or 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.
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. This article explains how to add Excel chart data labels with a formula in C# and VB.NET using XlsIO. Steps to add an Excel chart with a formula, 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 (versions after 18.2.0.44) as a reference to your .NET Framework application from NuGet.org. Install the XlsIO NuGet package Step 3: Include the following namespaces in the Program.cs file. C# using Syncfusion.XlsIO; VB.NET Imports Syncfusion.XlsIO Step 4: Use the following code snippet to set a formula as data label text in Excel using C# and VB.NET. C# // Create an instance of ExcelEngine using (ExcelEngine excelEngine = new ExcelEngine()) { // Instantiate the application object IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; // Create a workbook IWorkbook workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; // Enter values to the cells from A1 to C6 sheet.Range["A1"].Text = "Month"; sheet.Range["B1"].Text = "Product A"; sheet.Range["C1"].Text = "Product B"; sheet.Range["A2"].Text = "Jan"; sheet.Range["A3"].Text = "Feb"; sheet.Range["A4"].Text = "Mar"; sheet.Range["A5"].Text = "Apr"; sheet.Range["A6"].Text = "May"; sheet.Range["B2"].Number = 111; sheet.Range["B3"].Number = 361; sheet.Range["B4"].Number = 336; sheet.Range["B5"].Number = 40; sheet.Range["B6"].Number = 219; sheet.Range["C2"].Number = 182; sheet.Range["C3"].Number = 283; sheet.Range["C4"].Number = 406; sheet.Range["C5"].Number = 449; sheet.Range["C6"].Number = 337; // Create a chart IChartShape chart = sheet.Charts.Add(); chart.ChartType = ExcelChartType.Line; chart.DataRange = sheet.Range["A1:C6"]; // Set formula as data label text chart.Series[0].DataPoints[0].DataLabels.IsFormula = true; chart.Series[0].DataPoints[0].DataLabels.Text = "='Sheet1'!$A$2"; // Save the workbook workbook.SaveAs("Output.xlsx"); workbook.Close(); } VB.NET 'Create an instance of ExcelEngine Using excelEngine As ExcelEngine = New ExcelEngine() 'Instantiate the application object Dim application As IApplication = excelEngine.Excel application.DefaultVersion = ExcelVersion.Excel2016 'Create a workbook Dim workbook As IWorkbook = application.Workbooks.Create(1) Dim sheet As IWorksheet = workbook.Worksheets(0) 'Enter values to the cells from A1 to C6 sheet.Range("A1").Text = "Month" sheet.Range("B1").Text = "Product A" sheet.Range("C1").Text = "Product B" sheet.Range("A2").Text = "Jan" sheet.Range("A3").Text = "Feb" sheet.Range("A4").Text = "Mar" sheet.Range("A5").Text = "Apr" sheet.Range("A6").Text = "May" sheet.Range("B2").Number = 111 sheet.Range("B3").Number = 361 sheet.Range("B4").Number = 336 sheet.Range("B5").Number = 40 sheet.Range("B6").Number = 219 sheet.Range("C2").Number = 182 sheet.Range("C3").Number = 283 sheet.Range("C4").Number = 406 sheet.Range("C5").Number = 449 sheet.Range("C6").Number = 337 'Create a chart Dim chart As IChartShape = sheet.Charts.Add() chart.ChartType = ExcelChartType.Line chart.DataRange = sheet.Range("A1:C6") 'Set formula as data label text chart.Series(0).DataPoints(0).DataLabels.IsFormula = True chart.Series(0).DataPoints(0).DataLabels.Text = "='Sheet1'!$A$2" 'Save the workbook workbook.SaveAs("Output.xlsx") workbook.Close() End Using A complete working example to add Excel chart data labels with formula in C# and VB.NET using XlsIO can be downloaded from Add Excel chart data labels with formula.zip. By executing the program, you will get the output as below. Output Excel document Refer here to explore the rich set of Syncfusion® Excel (XlsIO) library features. See Also: How to convert Excel to PDF in C#, VB.NET How to Export and Save Excel Chart as Image How to convert Syncfusion’s WinForms chart to Excel chart using XlsIO How to copy and insert a chart in the same worksheet using C#,VB.NET? How to create Excel line with markers chart in C#, VB.NET? Format Excel chart data label in C#, VB.NET How can I change the font color of the title in chart? Add image to Excel header in C#, VB.NET How to create Excel combination chart in C#, VB.NET? 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 add Excel chart data labels with formula in C#, VB.NET using 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. This article explains how to add an Excel chart title with a formula in C#, VB.NET using XlsIO. Steps to add an Excel chart title with a formula 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 (versions after 18.2.0.44) as a reference to your .NET Framework application from NuGet.org. Install XlsIO NuGet package Step 3: Include the following namespaces in the Program.cs file. C# using Syncfusion.XlsIO; VB.NET Imports Syncfusion.XlsIO Step 4: Use the following code snippet to set a formula as the chart title in Excel using C# and VB.NET. C# // Create an instance of ExcelEngine using (ExcelEngine excelEngine = new ExcelEngine()) { // Instantiate the application object IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; // Create a workbook IWorkbook workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; // Enter values into the cells from A1 to C6 sheet.Range["A1"].Text = "Month"; sheet.Range["B1"].Text = "Product A"; sheet.Range["C1"].Text = "Product B"; sheet.Range["A2"].Text = "Jan"; sheet.Range["A3"].Text = "Feb"; sheet.Range["A4"].Text = "Mar"; sheet.Range["A5"].Text = "Apr"; sheet.Range["A6"].Text = "May"; sheet.Range["B2"].Number = 111; sheet.Range["B3"].Number = 361; sheet.Range["B4"].Number = 336; sheet.Range["B5"].Number = 40; sheet.Range["B6"].Number = 219; sheet.Range["C2"].Number = 182; sheet.Range["C3"].Number = 283; sheet.Range["C4"].Number = 406; sheet.Range["C5"].Number = 449; sheet.Range["C6"].Number = 337; // Create a chart IChartShape chart = sheet.Charts.Add(); chart.ChartType = ExcelChartType.Line; chart.DataRange = sheet.Range["A1:C6"]; // Set formula as chart title chart.ChartTitleArea.IsFormula = true; chart.ChartTitle = "='Sheet1'!$A$2"; // Save the workbook workbook.SaveAs("Output.xlsx"); workbook.Close(); } VB.NET 'Create an instance of ExcelEngine Using excelEngine As ExcelEngine = New ExcelEngine() 'Instantiate the application object Dim application As IApplication = excelEngine.Excel application.DefaultVersion = ExcelVersion.Excel2016 'Create a workbook Dim workbook As IWorkbook = application.Workbooks.Create(1) Dim sheet As IWorksheet = workbook.Worksheets(0) 'Enter values into the cells from A1 to C6 sheet.Range("A1").Text = "Month" sheet.Range("B1").Text = "Product A" sheet.Range("C1").Text = "Product B" sheet.Range("A2").Text = "Jan" sheet.Range("A3").Text = "Feb" sheet.Range("A4").Text = "Mar" sheet.Range("A5").Text = "Apr" sheet.Range("A6").Text = "May" sheet.Range("B2").Number = 111 sheet.Range("B3").Number = 361 sheet.Range("B4").Number = 336 sheet.Range("B5").Number = 40 sheet.Range("B6").Number = 219 sheet.Range("C2").Number = 182 sheet.Range("C3").Number = 283 sheet.Range("C4").Number = 406 sheet.Range("C5").Number = 449 sheet.Range("C6").Number = 337 'Create a chart Dim chart As IChartShape = sheet.Charts.Add() chart.ChartType = ExcelChartType.Line chart.DataRange = sheet.Range("A1:C6") 'Set formula as chart title chart.ChartTitleArea.IsFormula = True chart.ChartTitle = "='Sheet1'!$A$2" 'Save the workbook workbook.SaveAs("Output.xlsx") workbook.Close() End Using A complete working example of how to set a formula as the chart title in Excel using C# and VB.NET can be downloaded from Add Excel chart title with formula.zip. By executing the program, you will get the output as shown below. Output Excel document Refer here to explore the rich set of Syncfusion® Excel (XlsIO) library features. See Also: How to convert Excel to PDF in C#, VB.NET How to Export and Save Excel Chart as Image How to convert Syncfusion’s WinForms chart to Excel chart using XlsIO How to copy and insert a chart in the same worksheet using C#,VB.NET? How to create Excel line with markers chart in C#, VB.NET? Format Excel chart data label in C#, VB.NET How can I change the font color of the title in chart? Add image to Excel header in C#, VB.NET How to create Excel combination chart in C#, VB.NET? 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 Add Excel chart title with formula in C#, VB.NET using 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!
Description This Knowledge base explains about how to stop automatic calculation in Spreadsheet. Solution In Spreadsheet, the formulas are recalculated automatically whenever the value in the dependent cells are changed in each sheet. Setting calculation option to ‘manual’ prevents the recalculation of formula while changing its dependent cell. HTML <div id="Spreadsheet"></div> JS $(function () { $("#Spreadsheet").ejSpreadsheet({ sheets: [{ rows: [{ cells: [{ value: 1 }] }, { cells: [{ value: 2 }] }, { cells: [{ value: "=SUM(A1,A2)" }] }] }], loadComplete: "onLoadComplete" }); }); RAZOR @(Html.EJ().Spreadsheet<object>("Spreadsheet") .Sheets(sheet => { sheet.Rows(rows => { rows.Cells(cells => { cells.Value("1").Add(); }).Add(); rows.Cells(cells => { cells.Value("2").Add(); }).Add(); rows.Cells(cells => { cells.Value("=SUM(A1,A2)").Add(); }).Add(); }).Add(); }) .ClientSideEvents( events => events.LoadComplete("onLoadComplete") ) ) ASPX <ej:spreadsheet id="Spreadsheet1" runat="server"> <Sheets> <ej:Sheet> <Rows> <ej:Row> <Cells> <ej:Cell Value="1"> </ej:Cell> </Cells> </ej:Row> <ej:Row> <Cells> <ej:Cell Value="2"> </ej:Cell> </Cells> </ej:Row> <ej:Row> <Cells> <ej:Cell Value="=SUM(A1,A2)"> </ej:Cell> </Cells> </ej:Row> </Rows> </ej:Sheet> </Sheets> <ClientSideEvents LoadComplete="onLoadComplete" /> </ej:spreadsheet> function onLoadComplete(args) { if (!this.isImport) { //Suspending automatic calculation by passing false in isAutomatic parameter this.XLEdit.calcOption(false); //Updating Cell A1 value this.XLEdit.updateCellValue({ rowIndex: 0, colIndex: 0 }, "4"); } } The following screenshots displays the Spreadsheet after rendering it with above code snippet Figure: Spreadsheet after setting manual calculation option. Once you have changed the calculate options in Spreadsheet to Manual, you can calculate the formula in specific sheet or all the sheets using ‘calcNow()’ method.
Description This knowledge base explains the way to change the formula argument separator in the Spreadsheet based on the cultures. Solution It can be achieved by using “load” client-side event. HTML <div id="Spreadsheet"></div> JS $("#Spreadsheet").ejSpreadsheet({ load: "onLoad" }); RAZOR @(Html.EJ().Spreadsheet<object>("Spreadsheet") .ClientSideEvents(events => events.Load("onLoad")) ) ASPX <ej:spreadsheet id="Spreadsheet" runat="server"> <ClientSideEvents Load="onLoad" /> </ej:spreadsheet> function onLoad(args) { var calcEngine = this.getCalcEngine(); //To change the formula argument separator calcEngine.setParseArgumentSeparator(";"); } You can refer the below documentation link, https://help.syncfusion.com/api/js/ejspreadsheet#events:load
SfSpreadsheet provides support to get the list of dependent cells for a particular cell. You can get the list of cells dependent on the cell by looping the PrecedentCells of FormulaEngine, like below code example. C# public List<String> GetDependentCells(int rowindex,int columnindex) { var list = new List<string>(); var grid = Spreadsheet.ActiveGrid; var family = FormulaEngine.GetSheetFamilyItem(grid); var cellref = family.GetTokenForGridModel(grid) + GridRangeInfo.GetAlphaLabel(columnindex) + rowindex; //To get the dependent cell if (grid.FormulaEngine.PrecedentCells.ContainsKey(cellref) && grid.FormulaEngine.PrecedentCells[cellref] != null) { Calculatedependentcell(cellref,list); } return list; } public void Calculatedependentcell(string precedentCell,List<string> list) { var grid = Spreadsheet.ActiveGrid; if (grid.FormulaEngine.PrecedentCells[precedentCell] == null) return; var ht = (Hashtable)((Hashtable)grid.FormulaEngine.PrecedentCells[precedentCell]).Clone(); foreach (var o in ht.Keys) { var s1 = o as string; var row = grid.FormulaEngine.RowIndex(s1); var col = grid.FormulaEngine.ColumnIndex(s1); var cell = GridRangeInfo.GetAlphaLabel(col) + row; if (s1 != null) { if (ht.Keys.Count == 1) list.Add(s1); else { list.Add(s1); Calculatedependentcell(s1,list);//recursive call } } } } If you want to get the dependent cells of particular cell when its value changed, then subscribe the CellValueChanged event of Worksheet like below. Spreadsheet.ActiveSheet.CellValueChanged += ActiveSheet_CellValueChanged; private void ActiveSheet_CellValueChanged(object sender, CellValueChangedEventArgs e) { var list = GetDependentCells(e.Range.Row, e.Range.Column); } Sample link: WPF WinForms UWP ConclusionI hope you enjoyed learning about how to get the list of dependent cells for a particular cell in UWP Spreadsheet.You can refer to our UWP Spreadsheet feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. 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!
WPF Spreadsheet provides support to get the list of dependent cells for a particular cell. You can get the list of cells dependent on the cell by looping the PrecedentCells of FormulaEngine, like below code example. C# public List<String> GetDependentCells(int rowindex,int columnindex) { var list = new List<string>(); var grid = Spreadsheet.ActiveGrid; var family = FormulaEngine.GetSheetFamilyItem(grid); var cellref = family.GetTokenForGridModel(grid) + GridRangeInfo.GetAlphaLabel(columnindex) + rowindex; //To get the dependent cell if (grid.FormulaEngine.PrecedentCells.ContainsKey(cellref) && grid.FormulaEngine.PrecedentCells[cellref] != null) { Calculatedependentcell(cellref,list); } return list; } public void Calculatedependentcell(string precedentCell,List<string> list) { var grid = Spreadsheet.ActiveGrid; if (grid.FormulaEngine.PrecedentCells[precedentCell] == null) return; var ht = (Hashtable)((Hashtable)grid.FormulaEngine.PrecedentCells[precedentCell]).Clone(); foreach (var o in ht.Keys) { var s1 = o as string; var row = grid.FormulaEngine.RowIndex(s1); var col = grid.FormulaEngine.ColumnIndex(s1); var cell = GridRangeInfo.GetAlphaLabel(col) + row; if (s1 != null) { if (ht.Keys.Count == 1) list.Add(s1); else { list.Add(s1); Calculatedependentcell(s1,list);//recursive call } } } } If you want to get the dependent cells of particular cell when its value changed, then subscribe the CellValueChanged event of Worksheet like below. Spreadsheet.ActiveSheet.CellValueChanged += ActiveSheet_CellValueChanged; private void ActiveSheet_CellValueChanged(object sender, CellValueChangedEventArgs e) { var list = GetDependentCells(e.Range.Row, e.Range.Column); } Sample link: WPF WinForms UWP ConclusionI hope you enjoyed learning about how to to get the list of dependent cells for a particular cell in WPF Spreadsheet.You can refer to our WPF Spreadsheet feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. 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 Essential® XlsIO is a .NET Excel library used to create, read, and edit Excel documents. Using this library, you can start creating an Excel document in Xamarin. Steps to create an Excel file programmatically: Create a new C# Xamarin.Forms application project. Select a project template and required platforms to deploy the application. In this application the portable assemblies to be shared across multiple platforms, the .NET Standard code sharing strategy has been selected. For more details about code sharing refer here. Note: If .NET Standard is not available in the code sharing strategy, the Portable Class Library (PCL) can be selected. Install the Syncfusion.Xamarin.XlsIO NuGet package as a reference to the .NET Standard project in your Xamarin applications from NuGet.org. Add new Forms XAML page in portable project If there is no XAML page is defined in the App class. Otherwise proceed to the next step. To add the new XAML page, right click on the project and select Add > New Item and add a Forms XAML Page from the list. Name it as MainXamlPage. In App class of portable project (App.cs), replace the existing constructor of App class with the code snippet given below which invokes the MainXamlPage. public App() { // The root page of your application MainPage = new MainXamlPage(); } In the MainXamlPage.xaml add new button as shown below. <ContentPage xmlns="http://xamarin.com/schemas/2014/forms" xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml" x:Class="GettingStarted. MainXamlPage"> <StackLayout VerticalOptions="Center"> <Button Text="Generate Document" Clicked="OnButtonClicked" HorizontalOptions="Center"/> </StackLayout> </ContentPage> Include the following namespace in the MainXamlPage.xaml.cs file. using Syncfusion.XlsIO; Include the below code snippet in the click event of the button in MainXamlPage.xaml.cs, to create an Excel file and save it in a stream. void OnButtonClicked(object sender, EventArgs args) { // Create an instance of ExcelEngine. using (ExcelEngine excelEngine = new ExcelEngine()) { // Set the default application version as Excel 2013. excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013; // Create a workbook with a worksheet IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); // Access the first worksheet from the workbook instance. IWorksheet worksheet = workbook.Worksheets[0]; // Adding text to a cell worksheet.Range["A1"].Text = "Hello World"; // Save the workbook to stream in xlsx format. MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); workbook.Close(); // Save the stream as a file in the device and invoke it for viewing Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("GettingStared.xlsx", "application/msexcel", stream); } } Download the helper files from this link and add them into the mentioned project. These helper files allow you to save the stream as a physical file and open the file for viewing. Project File Name Summary Protable project ISave.cs Represent the base interface for save operation iOS Project SaveIOS.cs Save implementation for iOS device PreviewControllerDS.cs Helper class for viewing the Excel file in iOS device Android project SaveAndroid.cs Save implementation for Android device WinPhone project SaveWinPhone.cs Save implementation for Windows phone device UWP project SaveWindows.cs Save implementation for UWP device. Windows(8.1) project SaveWindows81.cs Save implementation for WinRT device. Compile and execute the application. Now this application creates a simple Excel document. By executing the program, you will get the Excel file as follows. A complete working example of how to create Excel File in Xamarin can be downloaded from Create-Excel-file.zip. Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions etc. with code examples. Refer here to explore the rich set of Syncfusion® Essential XlsIO features. An online sample link to generate Excel file. See Also: Create a Excel file in ASP.NET MVC Create a Excel file in ASP.NET Core Create a Excel file in Windows Forms Create a Excel file in WPF 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 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 to create an Excel file in Xamarin.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!
Dependent cells for a particular cell SfSpreadsheet provides support to get the list of dependent cells for a particular cell. You can get the list of cells dependent on the cell by looping the PrecedentCells of FormulaEngine, like below code example. C# public List<String> GetDependentCells(int rowindex,int columnindex) { var list = new List<string>(); var grid = Spreadsheet.ActiveGrid; var family = FormulaEngine.GetSheetFamilyItem(grid); var cellref = family.GetTokenForGridModel(grid) + GridRangeInfo.GetAlphaLabel(columnindex) + rowindex; //To get the dependent cell if (grid.FormulaEngine.PrecedentCells.ContainsKey(cellref) && grid.FormulaEngine.PrecedentCells[cellref] != null) { Calculatedependentcell(cellref,list); } return list; } public void Calculatedependentcell(string precedentCell,List<string> list) { var grid = Spreadsheet.ActiveGrid; if (grid.FormulaEngine.PrecedentCells[precedentCell] == null) return; var ht = (Hashtable)((Hashtable)grid.FormulaEngine.PrecedentCells[precedentCell]).Clone(); foreach (var o in ht.Keys) { var s1 = o as string; var row = grid.FormulaEngine.RowIndex(s1); var col = grid.FormulaEngine.ColumnIndex(s1); var cell = GridRangeInfo.GetAlphaLabel(col) + row; if (s1 != null) { if (ht.Keys.Count == 1) list.Add(s1); else { list.Add(s1); Calculatedependentcell(s1,list);//recursive call } } } } If you want to get the dependent cells of particular cell when its value changed, then subscribe the CellValueChanged event of Worksheet like below. Spreadsheet.ActiveSheet.CellValueChanged += ActiveSheet_CellValueChanged; private void ActiveSheet_CellValueChanged(object sender, CellValueChangedEventArgs e) { var list = GetDependentCells(e.Range.Row, e.Range.Column); } Sample: WPF WinForms UWP
Show the formula in a cell By default, the formula will be shown in a cell even if it is in edit mode. In order to show the formula in a cell when cell is activated, the ForceEditWhenActivated property can be set as False. C# this.gridControl1.ActivateCurrentCellBehavior = GridCellActivateAction.DblClickOnCell; GridFormulaCellRenderer.ForceEditWhenActivated = false; VB Me.gridControl1.ActivateCurrentCellBehavior = GridCellActivateAction.DblClickOnCell GridFormulaCellRenderer.ForceEditWhenActivated = False Screen shot Samples: C#: ActivateFormulaCell_CS VB: ActivateFormulaCell_VB Reference link: https://help.syncfusion.com/windowsforms/grid-control/formula-support
VLookup formula To use VLOOKUP formula by assigning the CellType property as “FormulaCell” to the specified grid cells. Refer the below code to enable the formula cell and the syntax for using the VLOOKUP formula. C#: // Sets the entire Grid cells as FormulaCell. this.gridControl1.BaseStylesMap["Standard"].StyleInfo.CellType = GridCellTypeName.FormulaCell; // Sets the specific cell as FormulaCell. this.gridControl1[5, 5].CellType = GridCellTypeName.FormulaCell; // Sets the specific row as FormulaCell. this.gridControl1.Model.RowStyles[7].CellType = GridCellTypeName.FormulaCell; // Sets the specific column as FormulaCell. this.gridControl1.Model.ColStyles[4].CellType = GridCellTypeName.FormulaCell; VB: ' Sets the entire Grid cells as FormulaCell. Me.gridControl1.BaseStylesMap("Standard").StyleInfo.CellType = GridCellTypeName.FormulaCell ' Sets the specific cell as FormulaCell. Me.gridControl1(5, 5).CellType = GridCellTypeName.FormulaCell ' Sets the specific row as FormulaCell. Me.gridControl1.Model.RowStyles(7).CellType = GridCellTypeName.FormulaCell ' Sets the specific column as FormulaCell. Me.gridControl1.Model.ColStyles(4).CellType = GridCellTypeName.FormulaCell CellValue can be assigned by using VLOOKUP formula and the syntax for the VLOOKUP is,VLOOKUP(lookup_value,table_array,row_index_num,range_lookup) C#: // The value "Data3" is looked up in the grid. this.gridControl1[2, 4].CellValue = "=vlookup(\"Data3\",A1:C4,3,false)"; VB: ' The value "Data3" is looked up in the grid. Me.gridControl1(2, 4).CellValue = "=vlookup(""Data3"",A1:C4,3,false)" Samples: C#: VLOOKUP VB: VLOOKUP
In order to export user-defined formula library function from Grid to Excel, the following steps has to be followed. Step 1: Create an Excel Add-In file (*.xla) including the user-defined function as illustrated in the following steps. Create a new empty workbook and click Save As from the File menu. In the Save As dialog box, select the location to save the workbook. Enter the file name for the Add-In workbook, and then select the type as Excel Add-In (*.xla). Click Save, and then press ALT+ F11 to open the VBA Editor. Now, you can create the custom function through the VBA Editor by inserting a module. Step 2: Now, bind add-in file path and custom method name with GridExcelConverterControl through AddCustomFunction method. The following screenshot illustrates how to create the user-defined function through the VBA editor. The XLA file will not be visible in Excel because Add-Ins are never visible. All changes must be done to the VBA Editor only. VBA Coding Public Function Age(DOB As Date) Dim cd1 As Date cd1 = Format(Now, "dd/MM/yyyy") Dim d1 As Integer d1 = Year(cd1) Dim d2 As Integer d2 = Year(DOB) If DOB = 0 Then Age = "" Else Age = d1 - d2 End If End Function Code Snippet C# string xla = "D:\Fun.xlam"; Syncfusion.GridExcelConverter.GridExcelConverterControl gecc = new Syncfusion.GridExcelConverter.GridExcelConverterControl(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "Files(*.xls)|*.xls"; saveFileDialog1.DefaultExt = ".xls"; if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK) { gecc.AddCustomFunction(xla, "Age", saveFileDialog1.FileName); gecc.GridToExcel(this.gridControl1, saveFileDialog1.FileName); Process.Start(saveFileDialog1.FileName); } VB Dim xla As String = "D:\Fun.xlam" Dim gecc As New Syncfusion.GridExcelConverter.GridExcelConverterControl() Dim saveFileDialog1 As New SaveFileDialog() saveFileDialog1.Filter = "Files(*.xls)|*.xls" saveFileDialog1.DefaultExt = ".xls" If saveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then gecc.AddCustomFunction(xla, "Age", saveFileDialog1.FileName) gecc.GridToExcel(Me.gridControl1, saveFileDialog1.FileName) Process.Start(saveFileDialog1.FileName) End If Screenshot Sample Link: C# CustomFunction_In_Excel_CS VB CustomFunction_in_Excel_VB
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!
Show the formulas instead of values in grid This can be done by handling the GridControl's KeyDown and DrawCellDisplayText event. A flag can be used to synchronize the KeyDown event and the DrawCellDisplayText for displaying the formula. C# this.gridControl1.KeyDown += gridControl1_KeyDown; //set the cellType as FormulaCell this.gridControl1.TableStyle.CellType = GridCellTypeName.FormulaCell; this.gridControl1.DrawCellDisplayText += gridControl1_DrawCellDisplayText; void gridControl1_DrawCellDisplayText(object sender, GridDrawCellDisplayTextEventArgs e) { if (this.gridControl1[e.Style.CellIdentity.RowIndex, e.Style.CellIdentity.ColIndex].CellType == "FormulaCell" & flag) { //draw the display text for the cell e.DisplayText = e.Style.CellValue.ToString(); } } void gridControl1_KeyDown(object sender, KeyEventArgs e) { bool keydown = (e.Modifiers & Keys.Control) != Keys.None; Keys Key = e.KeyCode & Keys.KeyCode; //check whether the pressed key is Control with tilde or not if (Key == Keys.Oemtilde && keydown) { flag = !flag; this.gridControl1.Refresh(); this.gridControl1.Model.Refresh(); } } VB Private Me.gridControl1.KeyDown += AddressOf gridControl1_KeyDown 'set the cellType as FormulaCell Me.gridControl1.TableStyle.CellType = GridCellTypeName.FormulaCell AddHandler Me.gridControl1.DrawCellDisplayText, AddressOf gridControl1_DrawCellDisplayText void gridControl1_DrawCellDisplayText(Object sender, GridDrawCellDisplayTextEventArgs e) If Me.gridControl1(e.Style.CellIdentity.RowIndex, e.Style.CellIdentity.ColIndex).CellType Is "FormulaCell" & flag Then 'draw the display text for the cell e.DisplayText = e.Style.CellValue.ToString() End If void gridControl1_KeyDown(Object sender, KeyEventArgs e) Dim keydown As Boolean = (e.Modifiers And Keys.Control) <> Keys.None Dim Key As Keys = e.KeyCode And Keys.KeyCode 'check whether the pressed key is Control with tilde or not If Key = Keys.Oemtilde AndAlso keydown Then flag = Not flag Me.gridControl1.Refresh() Me.gridControl1.Model.Refresh() End If Samples: C#: Showing_Formulas VB: Showing_Formulas
The selected ranges of formula cells are protected with the help of the ReadOnly Property in GridControl. To protect a selected range of cells by using Mouse or Keyboard, Click Tools->Protection-> Protect Cells. To unprotect a selected range of cells by using Mouse or Keyboard, Click Tools->Protection->Unprotect Cells. C# GridStyleInfo headerStyle = new GridStyleInfo(); private void menuItem7_Click(object sender, System.EventArgs e) { this.gridControl1.IgnoreReadOnly=false; //To protect selected Formula Cells. this.gridControl1.CurrentCell.MoveTo(-1,-1); headerStyle.ReadOnly =true; headerStyle.BackColor=Color.LightPink; foreach(GridRangeInfo r in this.gridControl1.Selections.Ranges) { this.gridControl1.ChangeCells(r, headerStyle,Syncfusion.Styles.StyleModifyType.Override); } this.gridControl1.Selections.Clear(); } private void menuItem8_Click(object sender, System.EventArgs e) { //To unprotect Selected Formula Cells. this.gridControl1.CurrentCell.MoveTo(-1,-1); this.gridControl1.IgnoreReadOnly=true; headerStyle.ReadOnly = false; headerStyle.BackColor=Color.White; //.FromKnownColor(System.Drawing.KnownColor.White); foreach(GridRangeInfo r in this.gridControl1.Selections.Ranges) { this.gridControl1.ChangeCells(r, headerStyle,Syncfusion.Styles.StyleModifyType.Override); } this.gridControl1.IgnoreReadOnly=false; this.gridControl1.Selections.Clear(); } VB Private headerStyle As New GridStyleInfo() Private Sub menuItem7_Click(ByVal sender As Object, ByVal e As System.EventArgs) Me.gridControl1.IgnoreReadOnly=False 'To protect selected Formula Cells. Me.gridControl1.CurrentCell.MoveTo(-1,-1) headerStyle.ReadOnly =True headerStyle.BackColor=Color.LightPink For Each r As GridRangeInfo In Me.gridControl1.Selections.Ranges Me.gridControl1.ChangeCells(r, headerStyle,Syncfusion.Styles.StyleModifyType.Override) Next r Me.gridControl1.Selections.Clear() End Sub Private Sub menuItem8_Click(ByVal sender As Object, ByVal e As System.EventArgs) 'To unprotect Selected Formula Cells. Me.gridControl1.CurrentCell.MoveTo(-1,-1) Me.gridControl1.IgnoreReadOnly=True headerStyle.ReadOnly = False headerStyle.BackColor=Color.White '.FromKnownColor(System.Drawing.KnownColor.White); For Each r As GridRangeInfo In Me.gridControl1.Selections.Ranges Me.gridControl1.ChangeCells(r, headerStyle,Syncfusion.Styles.StyleModifyType.Override) Next r Me.gridControl1.IgnoreReadOnly=False Me.gridControl1.Selections.Clear() End Sub After applying the properties, the grid looks as follows. Figure SEQ Figure \* ARABIC 1: Protect the selected range of formula cell Samples: C#: Protect/Unprotect Cells VB: Protect/Unprotect Cells
Add and remove function Refer to the following steps to replace a function in the GridFormulaEngine Library. To remove a function, use the RemoveFunction method in the GridFormulaEngine class. C# //Remove the SUM function engine.RemoveFunction("SUM"); VB 'Remove the SUM function engine.RemoveFunction("SUM") To add a function, use the AddFunction method in the GridFormulaEngine class. C# //Add the SUM Function engine.AddFunction("SUM",new GridFormulaEngine.LibraryFunction(computeMySUM)); gridControl1[3,3].Text = "= SUM(100+700)"; /// <summary> /// Get the octal value for the specified base10 value /// </summary> private string base8ToInt(string args) { string s = args; //To match the integer in the args MatchCollection mc = Regex.Matches(args,@"(\d)+"); foreach(Match m in mc) { //To convert the octal to base10 values s = Regex.Replace(s,m.Value,Convert.ToInt32(m.Value ,8).ToString()); } return s; } /// <summary> /// Get the string notation for the specified range of cells /// </summary> private string ParseRangeOfValues(string args) { //To match the cellranges //The group <cellRangeBegin> matches the beginning of the range. //The group <cellRangeEnd> matches the ending of the range. Match m1 = Regex.Match(args,@"(?<cellRangeBegin>((\w)+))(\s*):(\s*)(?<cellRangeEnd>((\w)+))"); if(m1.Success) { string start = m1.Groups["cellRangeBegin"].Value; string end = m1.Groups["cellRangeEnd"].Value; int rowFrom; int rowTo; int colFrom; int colTo; //To match the starting range for separating the column and row //The group <column1> matches the column name //The group <row1> matches the row no Match mrange1 = Regex.Match( start,@"(?<column1>(([a-zA-Z])))(?<row1>(\d+))"); //To match the ending range for separating the column and row //The group <column1> matches the column name //The group <row1> matches the row no Match mrange2 = Regex.Match( end,@"(?<column1>(([a-zA-Z])))(?<row1>(\d+))"); start = ""; if(mrange1.Success && mrange2.Success) { //To convert the string row to integer row no rowFrom = Convert.ToInt32(mrange1.Groups["row1"].Value); rowTo = Convert.ToInt32( mrange2.Groups["row1"].Value); //To convert the string column to integer col no colFrom = mrange1.Groups["column1"].Value.ToUpper()[0] - 'A' + 1; colTo = mrange2.Groups["column1"].Value.ToUpper()[0] - 'A' + 1; for(int i = rowFrom ;i <= rowTo;i++) { for(int j = colFrom;j <= colTo;j++) { //To match the 8 and 9 digits to avoid the error if(Regex.Match(gridControl1[i,j].Text,"[89]").Success) return "Only Octal digits are Allowed for Sum function"; //To convert the octal to decimal start += Convert.ToInt32(gridControl1[i,j].Text,8).ToString() + "+"; } } start += "0ab"; start = "bn" + start; //To convert into required notation. start = Regex.Replace(start,@"\+","an"); //To replace the first 'an' to n start = Regex.Replace(start,@"bn(?<num>(\d+))an","bn${num}n"); return start; } } return ""; } /// <summary> /// compute the sum for specified args /// </summary> private string computeMySUM(string args) { // to match the octal notation if( !Regex.Match(args,"[89]").Success ) { string s = args; //to match the range (:) Match m1 = Regex.Match(args,":"); if( !m1.Success) { //to convert the octal to decimal s = this.base8ToInt(args); } else { //to parse the specified range of the cells s = this.ParseRangeOfValues(args); if(s.Equals("Only Octal digits are Allowed for Sum function")) return s; } string inum = engine.ComputeSum(s); //to convert decimal to octal return covertToBase8(int.Parse(inum)); } else return "Only Octal digits are Allowed for Sum Function"; } private string covertToBase8(int s) { string sum = ""; while(s >0) { sum += s % 8 ; s = (int)s/8; } int num = 0; // to reverse the sum value for(int i = 0 ;i<sum.Length;i++) { num += Convert.ToInt32(sum[i].ToString()) * (int)Math.Pow(10.0,(double)i) ; } sum = num.ToString(); return sum ; } VB 'Add the SUM Function engine.AddFunction("SUM",New GridFormulaEngine.LibraryFunction(AddressOf computeMySUM)) gridControl1(3,3).Text = "= SUM(100+700)" ''' <summary> ''' Get the octal value for the specified base10 value ''' </summary> private String base8ToInt(String args) Dim s As String = args 'To match the integer in the args Dim mc As MatchCollection = Regex.Matches(args,"(\d)+") For Each m As Match In mc 'To convert the octal to base10 values s = Regex.Replace(s,m.Value,Convert.ToInt32(m.Value,8).ToString()) Next m Return s ''' <summary> ''' Get the string notation for the specified range of cells ''' </summary> private String ParseRangeOfValues(String args) 'To match the cellranges 'The group <cellRangeBegin> matches the beginning of the range. 'The group <cellRangeEnd> matches the ending of the range. Dim m1 As Match = Regex.Match(args,"(?<cellRangeBegin>((\w)+))(\s*):(\s*)(?<cellRangeEnd>((\w)+))") If m1.Success Then Dim start As String = m1.Groups("cellRangeBegin").Value Dim [end] As String = m1.Groups("cellRangeEnd").Value Dim rowFrom As Integer Dim rowTo As Integer Dim colFrom As Integer Dim colTo As Integer 'To match the starting range for seperating the column and row 'The group <column1> match the column name 'The group <row1> matches the row no Dim mrange1 As Match = Regex.Match(start,"(?<column1>(([a-zA-Z])))(?<row1>(\d+))") 'To match the ending range for seperating the column and row 'The group <column1> match the column name 'The group <row1> matches the row no Dim mrange2 As Match = Regex.Match([end],"(?<column1>(([a-zA-Z])))(?<row1>(\d+))") start = "" If mrange1.Success AndAlso mrange2.Success Then 'To convert the string row to integer row no rowFrom = Convert.ToInt32(mrange1.Groups("row1").Value) rowTo = Convert.ToInt32(mrange2.Groups("row1").Value) 'To convert the string column to integer col no colFrom = mrange1.Groups("column1").Value.ToUpper()(0) - AscW("A"c) + 1 colTo = mrange2.Groups("column1").Value.ToUpper()(0) - AscW("A"c) + 1 For i As Integer = rowFrom To rowTo For j As Integer = colFrom To colTo 'To match 8 and 9 digits for avoiding the error If Regex.Match(gridControl1(i,j).Text,"[89]").Success Then Return "Only Octal digits are Allowed for Sum function" End If 'To convert the octal to decimal start &= Convert.ToInt32(gridControl1(i,j).Text,8).ToString() & "+" Next j Next i start &= "0ab" start = "bn" & start 'To convert into required notation. start = Regex.Replace(start,"\+","an") 'To replace the first 'an' to n start = Regex.Replace(start,"bn(?<num>(\d+))an","bn${num}n") Return start End If End If Return "" ''' <summary> ''' compute the sum for specified args ''' </summary> private String computeMySUM(String args) ' to match the octal notation If Not Regex.Match(args,"[89]").Success Then Dim s As String = args 'To match the range (:) Dim m1 As Match = Regex.Match(args,":") If Not m1.Success Then 'to convert the octal to decimal s = Me.base8ToInt(args) Else 'to parse the specified range of the cells s = Me.ParseRangeOfValues(args) If s.Equals("Only Octal digits are Allowed for Sum function") Then Return s End If End If Dim inum As String = engine.ComputeSum(s) 'to convert decimal to octal Return covertToBase8(Integer.Parse(inum)) Else Return "Only Octal digits are Allowed for Sum Function" End If private String covertToBase8(Integer s) Dim sum As String = "" Do While s >0 sum &= s Mod 8 s = CInt(Fix(s))/8 Loop Dim num As Integer = 0 ' to reverse the sum value For i As Integer = 0 To sum.Length - 1 num += Convert.ToInt32(sum.Chars(i).ToString()) * CInt(Fix(Math.Pow(10.0,CDbl(i)))) Next i sum = num.ToString() Return sum Samples: C#: Formula_library_function VB: Formula_library_function Reference link: https://help.syncfusion.com/windowsforms/grid-control/formula-support
Switch between two cultureinfo Refer to the following steps to switch between two CultureInfo. Pass the current culture name in the CultureInfo class. As, the System.Globalization.CultureInfo() represents information about a specific culture including the names of the culture. Assign the new CultureInfo to the Grid's CultureInfo. C# private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) { string culName = ""; switch (this.comboBox1.SelectedIndex) { case 0: culName = "en-GB"; break; //English united states case 1: culName = "en-US"; break; //German culture case 2: culName = "de-DE"; break; } //clear the grid this.gridControl1.RowCount = 0; this.gridControl1.RowCount = 10; this.gridControl1.ColCount = 5; //Passing the new culture name and setting the CultureInfo System.Globalization.CultureInfo ci = null; if (culName.Length == 0) { ci = System.Globalization.CultureInfo.CurrentCulture; } else { ci = new System.Globalization.CultureInfo(culName); } //Assigning the new culture to Grid's CultureInfo this.gridControl1.TableStyle.CultureInfo = ci; Application.CurrentCulture = ci; GridFormulaEngine.ParseDecimalSeparator = Application.CurrentCulture.NumberFormat.NumberDecimalSeparator.ToCharArray()[0]; GridFormulaEngine.ParseArgumentSeparator = Application.CurrentCulture.TextInfo.ListSeparator.ToCharArray()[0]; this.label1.Text = "Current Culture for the grid: " + ci.Name; } VB Private Sub comboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Dim culName As String = "" Select Case Me.comboBox1.SelectedIndex Case 0 culName = "en-GB" 'English united states Case 1 culName = "en-US" 'German culture Case 2 culName = "de-DE" End Select 'clear the grid Me.gridControl1.RowCount = 0 Me.gridControl1.RowCount = 10 Me.gridControl1.ColCount = 5 'Passing the new culture name and setting the CultureInfo Dim ci As System.Globalization.CultureInfo = Nothing If culName.Length = 0 Then ci = System.Globalization.CultureInfo.CurrentCulture Else ci = New System.Globalization.CultureInfo(culName) End If 'Assigning the new culture to Grid's CultureInfo Me.gridControl1.TableStyle.CultureInfo = ci Application.CurrentCulture = ci GridFormulaEngine.ParseDecimalSeparator = Application.CurrentCulture.NumberFormat.NumberDecimalSeparator.ToCharArray()(0) GridFormulaEngine.ParseArgumentSeparator = Application.CurrentCulture.TextInfo.ListSeparator.ToCharArray()(0) Me.label1.Text = "Current Culture for the grid: " & ci.Name End Sub Note:In German language, the sum of the two variables are separated by the semicolon. Example: Sum(a;b) Samples: C#: Cultureinfo VB: Cultureinfo
