Share via

Introducing the Excel Services Windows 7 Gadget

  • Article

The past few posts have shown some of the basic REST functionality you can expect to get from the new Excel Services that will ship as part of SharePoint 2010. Before going into some of the more advanced features (well.. feature) of Excel Services REST, I thought I would show you a Windows Gadget we implemented for internal use. I will make the source code available either through the MSDN Code Library or through this blog in the very near future.

Now, gadgets are not hard to write, but they do require some knowledge of HTML and JavaScript. This Gadget is an absolutely great tool for people who have any sort of spreadsheet that contains any type of data that periodically changes and which is usually used as a sort of tracking application – instead of having to write the gadget yourself, you can just use the Excel workbook. You can think of any type of workbook – from the workbook that tracks your portfolio, to the one that displays information about your warehouse stock, to the one that tracks your project progress. This data is usually “locked” inside spreadsheets and one needs to manually open the workbook and actively look in. This gadget opens up an opportunity to use virtually any piece of information that resides within such workbooks and make it easily accessible right on your desktop – the gadget will make sure to periodically update the data so that with a simple glance, you can see exactly what’s going on!

Once installed, you add the gadget just like you would any other – run the “Desktop Gadget Gallery” in Windows 7 and double-click the Excel icon:

image

Once added to the desktop, you need to properly configure your gadget to show whatever information you want. In this example, we will show information about bug counts. For starters, you click the little wrench icon – that will get you to the settings screen:

image

Next, you type the name of the URL for the workbook in the “Workbook” field and hit Refresh. The Gadget will go and use REST discovery to figure out what elements exist in the workbook:

image

As you can see, the gadget lets you choose from any of the entities available in the workbook. For now, lets choose the “PersonalBugs” – it is what I like seeing on my desktop, so I can closely monitor my status.

image

As you can see, the chart is not really designed to be used in such a small space – clicking on the little arrow will “undock” the gadget (a term inherited from Vista) and will show the chart at full size:

image

Now, this looks much better. It can be even better though – if you noticed, in the first screenshot of the “Settings” screen, there are two combo boxes – one for the actual object you want to see and the other for the thumbnail. The nice thing is that you can set up the gadget to show you one element when it’s “minimized” and one when it’s “maximized”. In this example, we conveniently have a chart called “MiniPersonalChart” which contains the chart in a smaller format. Here is how you set the two objects up in the gadget:

image

Now, when the gadget is “minimized”, it will show the chart that’s better suited for smaller space (it has no header, legend or axis):

image

That’s it for this post! Next post will show how the gadget can show not only charts, but also show ranges!

Comments

  • Anonymous
    November 04, 2009
    The comment has been removed

  • Anonymous
    November 05, 2009
    Shahar, if I insert a rnage from Excel spreadsheet into my blog and see the cells, is it possible to change the cells in my blog and it will change it in the spreadsheet (wrtite back to the spreadsheet as oppsed to just reading from the spreadsheet)

  • Anonymous
    November 05, 2009
    You should be able to either do it, or get very close. i will make a post about this in the future - we'll see how close we can get. you can for sure put a workbook in your blog for editing - the only question is how integrated will it look and whether you can limit for a single range.

  • Anonymous
    November 12, 2009
    That is a pretty cool scenario and I look forward to using it when it is available.  But I looked at the 3rd party offering and that PUSHBI does a lot more than the ES REST + Gadget combo.   As an end user, I can subscribe to KPIs, it feeds data to a mobile device, it can use any reporting solution for the click through.   I don't have to upgrade my entire enterprise for hundreds of thousands of dollars...  :o) The ES REST scenario is very limited and really good need something simple and you have a technical business user.   Neat, but I wouldn't over sell it with the whole selfservicebi thing too...

  • Anonymous
    November 14, 2009
    Sounds like Jean-Phillippe works for the PushBI people.  REST + Gadget combo if you read and understand how it works allow the user to do exatly what you are describing - subscribe to any KPI in the published workbook.  Better yet, KPI in the gadget can be referenced by its plain name (member name from the cube), so any user of the master report can do it. The next posting in this blog also describes how just by hovering over a URL you can show additional detail about the data points.  Plus, support for hyperlinks enables linking to any detailed reports accesible via URI.  Excel provides HYPERLINK function that enables full URI parameterization. The possibilities for linking to additional content are limitless.  The mobile phone story is even simpler: REST is HTML visible anywhere on any device that doesn't require a special app. New version of Excel Services although very valuable by itself is just a part of SharePoint 2010.  It is very well worth the upgrade in my opinion.

  • Anonymous
    May 11, 2011
    I can't get the gadget to open my workbook.  I can open from IE.  But the Gadet says 'There was an error trying to fetch charts' Any clue why.

  • Anonymous
    July 12, 2011
    Hello everyone I need help why I can't connect to my excel file. There is an error message 'There was an error trying to fetch charts." My file location is C:UsersDonexDocumentsOffice Files2011 2nd Sem Target.xlsx. The content of my excel file is just a plain table with figures that I want to monitor in the side bar gadget. Please I need help on this. I have no indepth knowledge on programming. Thank you.

  • Anonymous
    August 04, 2011
    I'm with you Donex. I can't get it to work with a local file....

  • Anonymous
    November 29, 2011
    Anyone has been able to run this with a local xls or xlsx file?

  • Anonymous
    February 19, 2012
    You know what'd be cool? A Windows Phone Live Tile that displays an always-up-to-date chart via Excel Services. All possible today...anyone out there wanna build it? :)

  • Anonymous
    June 05, 2012
    Anyone to help or comment on if it is possible to make this work with LOCAL files. Any way out suggested or any other tool to accomplish the same. Any help would be much Appreciated.

  • Anonymous
    October 29, 2013
    The comment has been removed

  • Anonymous
    April 17, 2014
    The comment has been removed