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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Virtual Grids and vlookup

Thread ID:





104524 Jul 26,2012 04:23 PM UTC Aug 2,2012 11:06 AM UTC Silverlight 2
Tags: GridControl
Melinda Peirce
Asked On July 26, 2012 04:23 PM UTC

  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?

Melinda Peirce

Melinda Peirce
Replied On July 26, 2012 08:16 PM UTC

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?

Kathirvel P [Syncfusion]
Replied On August 2, 2012 11:06 AM UTC

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.


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.




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.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon 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.Close Icon