Excel Services Cheat Sheet

You want (or maybe you need) to learn Excel Services in 3 minutes?

Ok, ok, I know it’s impossible, but anyway, if you have to cheat, here’s some help.

Ready? Go!

· Their name is Excel Calculation Services

· They are exposed by a Service Application, that I strongly suggest you create J

· You need the Enterprise Edition of SPS2010

· The ECS perform computations and rendering on the serve side, starting from an Excel 2007/2010 file that should be “compatible” and uploadedl into a SharePoint library

· What compatible means?

o Long story: http://office.microsoft.com/en-us/excel-help/differences-between-using-a-workbook-in-excel-and-excel-services-HA010021716.aspx?pid=CH101024611033. It’s for Office2007/SP2007, most of these limitations still apply

o Short Story: no VBA macros, no data validation, no external worksheet references, etc…

· You access computation results from a client application using SOAP Web Services or the brand new Rest Services API

· You access the sheet (or part of it) rendered by the server just by clicking the “View in browser” link on the ECB, or maybe the document link itself if the default library settings state that the default open behavior is “Open with Server Side Handler”

· When the document is viewed in the browser, all logics behind it (i.e. all formulas) are flattened and you only get the results of the computation

· If a user has just the ViewOnly permissions, he can just view the document in the browser or download a snapshot of it. Once again, the snapshot contains just data, without formulas

· If a user has, at least, the Read permissions, he can also download a copy of the document, which contains also formulas and logics

· From an administrative perspective, you – the Farm Admin guy – should probably:

o Define “trusted locations” (i.e. libraries where the ECS consider documents trusted and, as such, can manage them as discussed above)

o Define external data connections (upload Data Connection Files, specify how connections are performed, take care about authentication, etc…)

· If you are guessing what a data connection is… well… it’s a connection to some external data repository! More seriously speaking, y can define such connections with the Excel Client (or retrieve them from a central location that is, of course, your SharePoint environment). Data that flow through data connections can be displayed in a sheet, with al sort of cool features that Excel provide (pivot tables and reports, slices, etc…)

Mmmmmhhh… it took more that 3 minutes, probably J

Improving the CQWP Markup with SuppressWebPartChrome

Several SharePoint web parts, the ContentByQueryWebPart being one of those, expose a property named SuppressWebPartChrome.

When set to true, the web part code strips out the surrounding layout that constitutes the “chrome” of the web part itself.

An example will explain this much better.

Consider the following HTML markup, generated with the SuppressWebPartChrome property set to false (its default):

<table class=”s4-wpTopTable” border=”0″ cellpadding=”0″ cellspacing=”0″ width=”100%”>

     <tr>

          <td valign=”top”><div WebPartID=”00000000-0000-0000-0000-000000000000″ HasPers=”true” id=”WebPartWPQ3″ width=”100%” class=”ms-WPBody noindex” OnlyForMePart=”true” allowDelete=”false” style=”” ><div id=”cbqwpctl00_News_g_ded0dcdb_9aaf_44c3_98b5_6af1963c4f3b” class=”cbq-layout-main”><ul class=”dfwp-column dfwp-list”><li class=”dfwp-item”><div class=”item link-item”><a href=”http://erickson.sharepointjukebox.vmw/News/Pages/Test.aspx” title=””>Test</a></div></li></ul></div></div></td>

     </tr>

</table>

And compare it with the following markup, where the SuppressWebPartChrome property has been set to true:

<div id=”cbqwpctl00_News_g_ded0dcdb_9aaf_44c3_98b5_6af1963c4f3b” class=”cbq-layout-main”><ul class=”dfwp-column dfwp-list”><li class=”dfwp-item”><div class=”item link-item”><a href=”http://erickson.sharepointjukebox.vmw/News/Pages/Test.aspx” title=””>Test</a></div></li></ul></div

In the first snippet I have higlighted the HTML portion that gets stripped out.

This is a huge improvement, especialloy because:

  • The resulting markup is smaller, with a lower network bandwidth consumption as the first consequence
  • You get rid of a surrounding table, which makes your markup more compliant to accessibility requirements (and, more generally, it just makes your markup better from a semantic perspective)

Needless to say, I suggest you to “think in advance” and apply this very early during the development lifecycle, since you may need to adjust your CSS or JS libraries accordingly!

FileRef vs EncodedAbsUrl

During the last days I found myself applying a lot of small post-migration patches, some of which can be accomplished with some (PowerShell) script, some of which I keep doing everything by hand.

The last one, and the one I’m talking about here, is related to a couple of custom DataFormWebParts that used the FileRef attribute of the SPListDataSource to render hyperlinks.

Now, it seems that in SharePoint 2007 these attributes return a site relative url with a leading slash (i.e.: /sites/somethingelse/etc), whereas I inspected the same attributes in a SharePoint 2010 environment and I got the site relative path without the leading slash. This, of course, causes the links to be considered relative to the current path, and the final effect is a lot of 404 all around.

The fix is super easy: I substituted the FileRef attribute with the EncodedAbsUrl attribute, which return exactly what I was expecting.

Sgunf 🙁

How to download a WSP file from the Farm Solution Store

I’m following several migration projects during the last months, especially from SharePoint 2007 to SharePoint 2010, and of course I’m trying to stay within the standard approach as much as I can.

By “standard” I mean (roughly):

  • Premigration check
  • Deploy code customizations
  • Apply farm and webapp configurations
  • Perform a content database attach

This can be smooth or can be painful, especially during the last phase.

But… well, things can start to go wrong early, and unfortnuately not only during the premigration check.

For example, more than often we found a ton of wsps deployed, then ask our customer to provide us the wsp (the package, not necessarily the source code) and this fails with a sad “I don’t know where they are”. Or, which may be even worse, we get a different version of the package, which may or may not work as ecpected.

That’s why I wrote these 5 lines of PowerShell code that let us download the exact wsp package that is deployed.

This script is supposed to run on a SharePoint 2007 farm (that’s why I cannot leverage the commandlets included in the SharePoint 2010 PowerShell SnapIn).

Here it is:

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint”)

$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local

$solution = $farm.Solutions[“nameofthewsp.wsp”]

$file = $solution.SolutionFile

$file.SaveAs(“c:\wsp\nameofthewsp.wsp”)

With some extra work, you could make this script iterate over all installed solution packages and, maybe, save them as a single zip file for convenience.

Discover unpublished files within a SharePoint web site

Here’s another small script that traverses a web hierarchy, seeking for files that are in a checked-out or unpublished (not approved) status.

function CheckPublishedFiles([Microsoft.SharePoint.SPFolder]$folder)

{

    foreach ($file in $folder.Files)

{

        $name = $file.Url

        try

        {

            $ok = (($file.Exists) -and ($file.OpenBinary() -ne $null));

            if ($ok)

           {

                $checkOutType = $file.CheckOutType

                if ($checkOutType -ne “None”)

                {

                    write-host “Checked Out: $name”

                }

                else

                {

                    $level = $file.Level                   

                    if ($level -ne “Published”)                   

                    {

                        write-host “Unpublished: $name”

                    }

                }

            }

        }

        catch

        {

            write-host “INVALID: $name”

        }

    }

    foreach ($subFolder in $folder.SubFolders)

    {

        CheckPublishedFiles $subFolder

    }

}

$web = get-spweb http://yourwebsite

$fld = $web.RootFolder

CheckPublishedFiles $fld

Discover corrupted files within a SharePoint web site

Although this is not exhaustive at all, since it uses a very simple mechanism to deem a file as corrupted (i.e. it tries to open it :-)), I found myself writing this little script and use it quite a lot of times.

Here it is:

Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue

function CheckFiles([Microsoft.SharePoint.SPFolder]$folder)

{

    foreach ($file in $folder.Files)

    {

        $name = $file.Url

        try

        {

            $ok = (($file.Exists) -and ($file.OpenBinary() -ne $null));

            if (!$ok)

            {

                write-host “$name”

            }

        }

        catch

        {

            write-host “$name”

        }

    }

    foreach ($subFolder in $folder.SubFolders)

    {

        CheckFiles $subFolder

    }

}

$web = get-spweb http://yourweburl 

$fld = $web.RootFolder

CheckFiles $fld

DocIcon without links

You may face this issue and have your customers complain after a migration to SharePoint 2010 from any previous SharePoint version: the document icon is now displayed just as an image, without any anchor tag to redirect the user to the document stream.

There are several wasy to fix this issue. The one I like best, since it’s well integrated and it leverages one of the most useful SharePoint 2010 customization approaches, is available for download here.

As a quick reference:

Project Description

DocIcon for SharePoint re-enables links from document icons in SharePoint 2010. This feature was in previous versions of SharePoint, but was removed in the 2010, breaking the user experience for most sites. This feature should be considered required for all 2007 site migrations.

How It Works

DocIcon for SharePoint 2010 installs an XSL file in the /layouts/xsl folder that overrides the standard behavior of the docIcon field type. The solution deploys this file using a standard SharePoint solution built in Visual Studio 2010.

Extract Crawled Properties Mappings

Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue

$searchapp = Get-SPEnterpriseSearchServiceApplication “Search Service Application Name Here”

$category = Get-SPEnterpriseSearchMetadataCategory –Identity “Category Name Here” -SearchApplication $searchapp

$properties = Get-SPEnterpriseSearchMetadataCrawledProperty -SearchApplication $searchapp -Category $category

foreach ($crawled in $properties)

{

$name = $crawled.Name

$managedProperties = $crawled.GetMappedManagedProperties()

foreach ($managed in $managedProperties)

{

$managedName = $managed.Name   

ac “c:\search.csv” “$name,$managedName”

}

}

I definitely love PowerShell 🙂

Measuring HTTP requests response time

Needless to say, this topic is complex, first of all because the total response time varies due to network congestion, server load, amount of bytes transfered just to name a few.

Anyway, if you just need to get a raw indication, you could just try out this small script:

function GetRequestTime([string]$url)

{

  $wc = new-object System.Net.WebClient

  $wc.Credentials = [System.Net.CredentialCache]::DefaultCredentials

  $start = get-date

  $output = $wc.DownloadString($url)

  $span = (get-date).Subtract($start)

  return $span.TotalMilliseconds

}

function ShootRequests([string]$url, [int]$count)

{

  $totalTime = 0

  1..$count |% { $totalTime += GetRequestTime($url)}

  return $totalTime / $count

}

What it does is firing for a request a configurable number of times and return the average response time. Here’s how you may invoke this function:

ShootRequests http://blog.claudiobrotto.com 50

Just a few notes, though:

  • It does not consider any client-side caching (you could easily overcome this issue by passing some query string parameter that makes the client consider it a brand new resource)
  • It does not download automatically any resource defined in the HTML of the page (i.e. if a page contains img tags that force the browser to download 10MB of content… this script just ignores that)
  • The rendering time is completely transparent too (you may have pages with a small network footprint but with a lot of JS code that slows doen the browser rendering)

CQWP, QueryOverride and filters

You probably know that one of the cool new features of the Content Query Web Part in SharePoint Server 2010 is the support to QueryString and PageField filters.

This was achievable also in MOSS 2007, but you had to rely on Parameter Bindings applied to the CQWP control, which made it a little more complicated since it requires some more knowledge about the “DataView family”.

Just a small note, though: if you keep using the QueryOverride mechanism (i.e., you specify a custom CAML query overwriting the default CQWP behavior), you completely lose the new filters functionality.

More generally, any time you apply some override (Query, View, etc…) the CQWP skips its automatic CAML query/filter/projection generation, and all of the plumbing it’s up to you.

With great power comes a great reponsibility!!