Oh, that mysteriously broken VisibleSlicerItemsList

Excel 2010 has a new feature called Slicers and a bunch of new methods and objects associated with slicers (I actually am not an expert on Slicers themselves and still need to understand what they are for and how they operate but this leads me to my next point). And there is a SlicerCache object with a property called VisibleSlicerItemsList. I now have heard from several sources that using this property in C# is … ahem .... putting it nicely – quite confusing.

 

Typically, people would try to cast an object returned by aforementioned property to object[]. This causes an exception stating – “Unable to cast object of type 'System.Object[*] ' to type 'System.Object[]” . Truth to be told I’ve never heard of a type called System.Object[*] so I asked some people that do know and was informed that this merely indicates a non-zero bound array. So, the exception is basically telling us that one cannot cast a non-zero-bound array to a zero-bound array. Great! Now we know that Excel returns a single-dimensional one-bound (i.e. its lowest index is one and not zero) array. We also know that C# does not really have a good syntax to deal with such arrays. Hence we need to go lower. Meet System.Array type.

 

Armed with this knowledge we could write this code:

// <DOESNT WORK>

Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;

SlicerCache slicerCache = workbook.SlicerCaches["Slicer_Customer_Geography"];

System.Array a = (System.Array)(slicerCache.VisibleSlicerItemsList);

// <DOESNT WORK>

 

Unfortunately, this would also throw an exception if you are compiling for .NET Fx 4.0 and using C#’s new Embed Interop Types aka /link feature (I really hope you do that because of all the wonderful things you can do with type embedding). This has to do with the fact that using /link also activates C# 4.0’s auto-dynamic feature – meaning methods returning object type are automatically promoted to be dynamic ( seethe COM Interop section on that page). That's exactly our case here since VisibleSlicerItemsList is typed as generic object in the Excel's Primary Interop Assembly. Consequently the cast of dynamic to System.Array is handled by the Dynamic Binder which also does not currently play well with non-zero based arrays. The only way to avoid Dynamic Binder is to cast first to object and then cast back to System.Array:

// WORKS

Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;

SlicerCache slicerCache = workbook.SlicerCaches["Slicer_Customer_Geography"];

System.Array a = (System.Array)((object)slicerCache.VisibleSlicerItemsList);

// WORKS

Live becomes much easier once we do have a System.Array object in hand. Here is the simplest way to iterate over the collection:

foreach (object slicer in a)

    System.Windows.Forms.MessageBox.Show(slicer.ToString());

Or, if you prefer the more controlled access to the array – use Rank property to make sure there is only single dimension to the array, use GetLowerBound/GetUpperBound to determine the boundaries of each dimension and retrieve values using GetValue method.

Comments

• Anonymous
  August 17, 2010
  The comment has been removed

• Anonymous
  November 05, 2011
  Great!!! You saved me a lot of hours

• Anonymous
  September 05, 2015
  Hi I am trying to select a value in a slice (olap) but no luck any solutions in c# thx

• Anonymous
  November 01, 2015
  After hours of trying different results for changing the slicer parameters from .Net VSTO to Excel, this helped me find my solution.  Thanks!