Virtual Grids and vlookup

[Melinda Peirce]

  I am using the ImportFromExcelToVirtualGrid(workbook) function to open and view an excel workbook in a silverlight grid.  for the most part, everything is working beautifully, but I have one problem.
   I have a sheet in the workbook that contains lists of data used for building combobox lists and for some vlookup functionality.  the problem shows up when I don't view this sheet before viewing a sheet that attempts a vlookup against it.  This is a very specialized use, so I do know the name of the sheet, and I have attempted to make that sheet the active sheet first in code then change to the sheet I would prefer users see, but that doesn't seem to stick.  I would prefer to actually hide this sheet in the workbook, because these lists are it's only use.  But unless I actually show the page with the lookup values on the screen, the vlookup will not process correctly.

Any Ideas on how to solve this problem?

thanks,
Melinda Peirce

[Melinda Peirce]

I need to amend this.  Vlookup is not the only instance where this sort of thing occurs.  it seems that any cross sheet reference, may get messed up if the referenced sheet and cells has not been processed for the UI.  Perhaps my answer is not to use the virtual grids.  however the workbook I am trying to import is very VERY large, and I have concerns about trying to load that much data into the Silverlight app.  Is it possible to use a combination of virtual grids with non-virtual?
thanks,
Melinda

[Kathirvel P]

Hi Melinda,

Sorry for the inconvenience caused.

Regarding query 1:
GridControl will show the actual value and computed formula values even when the referring sheet is hidden or not viewed yet. It is not the problem in the GridControl. The problem is in the sample source. This can be resolved by changing the following lines of code at ExcelImportDemo sample source.

Index: GridCellImportingBehavior.cs

In Model_QueryCellInfo event:

Code Snipped[C#]

GridSheetFamilyItem family = GridFormulaEngine.GetSheetFamilyItem(gridModel);
string token = family.GridModelToToken[gridModel].ToString();
int index = int.Parse(token.Replace('!', ' '));

We have attached the modified sample for your reference.

Sample:

Regarding query 2:

You can have both virtual and non-virtual grid in a Silverlight application. This can be achieved by using the GridModelImportExtensions.ImportFromExcel() method inside the LoadWorkbook. This method import all value from excel to grid model at the time of importing itself. So there is no need to add behavior to the grid model imported using this method.

We have attached the code snipped for your reference.

Code Snipped [C#]

GridModelImportExtensions.ImportFromExcel(grid.Model, Workbook.Worksheets["Sheet2"]);

Please let us know if this helps.

Regards,
Kathirvel

Import_774629f3.zip