Persist Formulas from the Calculate.CalcEngine

[Administrator]

Hi ,

Im using TabBarSplitterControl which contains many TabBarPages.
Each TabBarPage Contains a GridDataBoundGrid.

Im also using Syncfusion.Calculate.CalcEngine engine for calculation across every GridDataBoundGrid.

After all my operations I need to persist formulas for each GridDataBoundGrid.

Is there any API exposed for getting the formulas for each GridDataBoundGrid ?

I have seen engine.FormulaInfoTable which stores the formuala but how can I get these tables for each grid?

NB: Im using only 1 Syncfusion.Calculate.CalcEngine for serveral grids.

Regards,
Sreekesh NK

[Administrator]

There is no direct support for serializing formulas from the CalcEngine. You would have to implement something yourself. Here is code the GridDataBoundGrid sample that we ship as part of the Calculate samples that loops through all the cells to find and show formulas.

		private void ShowFormulas(bool show, DataGrid grid)
		{
			DataTable dt = grid.DataSource as DataTable;

			if(show)
			{
				engine.IgnoreValueChanged = true;
				for(int row = 0; row < dt.Rows.Count; ++row)
				{
					for(int col = 0; col < dt.Columns.Count; ++col)
					{
						string s = this.engine.GetFormulaRowCol((ICalcData)grid, row+1, col+1); //one-based
						if(s.Length > 0)
							grid[row, col] = s;
					}
				}
				engine.IgnoreValueChanged = false;
			}
			else
			{
				for(int row = 0; row < dt.Rows.Count; ++row)
				{
					for(int col = 0; col < dt.Columns.Count; ++col)
					{
						string s = this.engine.GetFormulaRowCol((ICalcData)grid, row+1, col+1); //one-based
						if(s.Length > 0)
							grid[row, col] = s;
					}
				}
			}
			}

[Administrator]

Hi Clay,

1 .I has already seen this code in one of your examples but im sad to say that it never works in any of the examples.

2.Even if it works I really don’t understand the need for iterating the entire Data Table to get the formulas stored in each cell. The engine itself keeps a list of formula cells. So whats the purpose of iterating the entire table to find the formula cells.

The main issue is how to grab those formula cells from the engine for a particular Grid.



Regards,
Sreekesh NK


>There is no direct support for serializing formulas from the CalcEngine. You would have to implement something yourself. Here is code the GridDataBoundGrid sample that we ship as part of the Calculate samples that loops through all the cells to find and show formulas.

		private void ShowFormulas(bool show, DataGrid grid)
		{
			DataTable dt = grid.DataSource as DataTable;

			if(show)
			{
				engine.IgnoreValueChanged = true;
				for(int row = 0; row < dt.Rows.Count; ++row)
				{
					for(int col = 0; col < dt.Columns.Count; ++col)
					{
						string s = this.engine.GetFormulaRowCol((ICalcData)grid, row+1, col+1); //one-based
						if(s.Length > 0)
							grid[row, col] = s;
					}
				}
				engine.IgnoreValueChanged = false;
			}
			else
			{
				for(int row = 0; row < dt.Rows.Count; ++row)
				{
					for(int col = 0; col < dt.Columns.Count; ++col)
					{
						string s = this.engine.GetFormulaRowCol((ICalcData)grid, row+1, col+1); //one-based
						if(s.Length > 0)
							grid[row, col] = s;
					}
				}
			}
			}

[Administrator]

There is a single FormulaInfoTable for all the sheets. You access it through the engine.

Here is one way you can do it. Add a hashtable to track the ICalcDataObjects as keys and the SheetNames as values. Populate this hashtable when you call RegisterGridAsSheet.

private Hashtable parentObjectToSheetName = new Hashtable();


//...

				engine.RegisterGridAsSheet("DG1", this.dataGrid1, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid1, "DG1");
				engine.RegisterGridAsSheet("DG2", this.dataGrid2, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid2, "DG2");
				engine.RegisterGridAsSheet("DG3", this.dataGrid3, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid3, "DG3");
				engine.RegisterGridAsSheet("DG4", this.dataGrid4, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid4, "DG4");
				engine.RegisterGridAsSheet("DG5", this.dataGrid5, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid5, "DG5");
//...

//then to get the FormulaInfoTable, try code like this:

           GridSheetFamilyItem family = CalcEngine.GetSheetFamilyItem(this.dataGrid1);

            foreach (object key in engine.FormulaInfoTable.Keys)
            {
                FormulaInfo info = engine.FormulaInfoTable[key] as FormulaInfo;
                string cell = key.ToString();
                int loc = cell.LastIndexOf('!');
                string sheetToken = cell.Substring(0, loc+1);
                string sheetName = parentObjectToSheetName[family.TokenToParentObject[sheetToken]].ToString();
                cell = cell.Substring(loc+1);
                Console.WriteLine("sheet=[{0}] cell=[{1}] formula=[{2}]", sheetName, cell, info.FormulaText);
            }

[Administrator]

Hi Clay,

Ok thank you , i will try that but can u also tell me why the code sample of getting the formuals by giving the row ,column and the grid is not working?
[1 .I has already seen this code in one of your examples but im sad to say that it never works in any of the examples.]

Regards,
Sreekesh NK

>There is a single FormulaInfoTable for all the sheets. You access it through the engine.

Here is one way you can do it. Add a hashtable to track the ICalcDataObjects as keys and the SheetNames as values. Populate this hashtable when you call RegisterGridAsSheet.

private Hashtable parentObjectToSheetName = new Hashtable();


//...

				engine.RegisterGridAsSheet("DG1", this.dataGrid1, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid1, "DG1");
				engine.RegisterGridAsSheet("DG2", this.dataGrid2, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid2, "DG2");
				engine.RegisterGridAsSheet("DG3", this.dataGrid3, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid3, "DG3");
				engine.RegisterGridAsSheet("DG4", this.dataGrid4, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid4, "DG4");
				engine.RegisterGridAsSheet("DG5", this.dataGrid5, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid5, "DG5");
//...

//then to get the FormulaInfoTable, try code like this:

           GridSheetFamilyItem family = CalcEngine.GetSheetFamilyItem(this.dataGrid1);

            foreach (object key in engine.FormulaInfoTable.Keys)
            {
                FormulaInfo info = engine.FormulaInfoTable[key] as FormulaInfo;
                string cell = key.ToString();
                int loc = cell.LastIndexOf('!');
                string sheetToken = cell.Substring(0, loc+1);
                string sheetName = parentObjectToSheetName[family.TokenToParentObject[sheetToken]].ToString();
                cell = cell.Substring(loc+1);
                Console.WriteLine("sheet=[{0}] cell=[{1}] formula=[{2}]", sheetName, cell, info.FormulaText);
            }

[Administrator]

Hi Clay,

Any updates on My Qn?

And one more thing the code you have given is not working.

Regards
Sreekesh NK

>Hi Clay,

Ok thank you , i will try that but can u also tell me why the code sample of getting the formuals by giving the row ,column and the grid is not working?
[1 .I has already seen this code in one of your examples but im sad to say that it never works in any of the examples.]

Regards,
Sreekesh NK

>There is a single FormulaInfoTable for all the sheets. You access it through the engine.

Here is one way you can do it. Add a hashtable to track the ICalcDataObjects as keys and the SheetNames as values. Populate this hashtable when you call RegisterGridAsSheet.

private Hashtable parentObjectToSheetName = new Hashtable();


//...

				engine.RegisterGridAsSheet("DG1", this.dataGrid1, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid1, "DG1");
				engine.RegisterGridAsSheet("DG2", this.dataGrid2, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid2, "DG2");
				engine.RegisterGridAsSheet("DG3", this.dataGrid3, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid3, "DG3");
				engine.RegisterGridAsSheet("DG4", this.dataGrid4, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid4, "DG4");
				engine.RegisterGridAsSheet("DG5", this.dataGrid5, sheetfamilyID);
                parentObjectToSheetName.Add(this.dataGrid5, "DG5");
//...

//then to get the FormulaInfoTable, try code like this:

           GridSheetFamilyItem family = CalcEngine.GetSheetFamilyItem(this.dataGrid1);

            foreach (object key in engine.FormulaInfoTable.Keys)
            {
                FormulaInfo info = engine.FormulaInfoTable[key] as FormulaInfo;
                string cell = key.ToString();
                int loc = cell.LastIndexOf('!');
                string sheetToken = cell.Substring(0, loc+1);
                string sheetName = parentObjectToSheetName[family.TokenToParentObject[sheetToken]].ToString();
                cell = cell.Substring(loc+1);
                Console.WriteLine("sheet=[{0}] cell=[{1}] formula=[{2}]", sheetName, cell, info.FormulaText);
            }