Sheet ID and CalcEngine Relation

[Administrator]

Hi ,

If i have 10 sheets registered to a Syncfusion.Calculate.CalcEngine using

engine.RegisterGridAsSheet(ref Name, ICalData model, sheetfamilyID);

How can i retrive the details back if i have the Sheet ID with me.

Thanks ,
Sreekesh NK

[Administrator]

The Sheet ID will not give you much. It is mainly used internally by the CalcEngine.

But if you get the SheetFamilyItem object as you register your ICalcData objects, then you can access several hashtables that expose most of the objects used by the engine. Here is code that gives some examples...

engine.RegisterGridAsSheet(ref Name, ICalData model, sheetfamilyID);

//pass in any ICalcData object to get the family object that holds
//information on all ICalcData objects with the same sheetFamilyID
GridSheetFamilyItem family = CalcEngine.GetSheetFamilyItem(this.dataGrid1);

//After getting family, you can access several hashtables. Here are a couple.
// family.ParentObjectToToken.Keys   - collection of all ICalcData objects
// family.SheetNameToParentObject - Keys are sheetnames and values are ICalcData objects


            

[Administrator]

Hi Clay,

Thank You , But how can i access the Formula Table from the family?
Syncfusion.Calculate.GridSheetFamilyItem family = Syncfusion.Calculate.CalcEngine.GetSheetFamilyItem(grid);

family.sheetFormulaInfoTable is a Non Public Member , so i cannot access it even though i can see it in Quick Watch Window.

Regards,
Sreekesh NK


>The Sheet ID will not give you much. It is mainly used internally by the CalcEngine.

But if you get the SheetFamilyItem object as you register your ICalcData objects, then you can access several hashtables that expose most of the objects used by the engine. Here is code that gives some examples...

engine.RegisterGridAsSheet(ref Name, ICalData model, sheetfamilyID);

//pass in any ICalcData object to get the family object that holds
//information on all ICalcData objects with the same sheetFamilyID
GridSheetFamilyItem family = CalcEngine.GetSheetFamilyItem(this.dataGrid1);

//After getting family, you can access several hashtables. Here are a couple.
// family.ParentObjectToToken.Keys   - collection of all ICalcData objects
// family.SheetNameToParentObject - Keys are sheetnames and values are ICalcData objects




            

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