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.
Unfortunately, activation email could not send to your email. Please try again.

Persist Formulas from the Calculate.CalcEngine

Thread ID:

Created:

Updated:

Platform:

Replies:

75493 Jul 29,2008 12:33 AM Jul 30,2008 04:45 PM Windows Forms 5
loading
Tags: GridControl
Administrator [Syncfusion]
Asked On July 29, 2008 12:33 AM

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 [Syncfusion]
Replied On July 29, 2008 05:37 AM

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 [Syncfusion]
Replied On July 29, 2008 03:14 PM

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 [Syncfusion]
Replied On July 29, 2008 07:36 PM

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 [Syncfusion]
Replied On July 29, 2008 08:49 PM

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 [Syncfusion]
Replied On July 30, 2008 04:45 PM

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);
}







CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

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.

;