Fun with PowerPivot Previews

DISCLAIMER: Clearly this is just done for fun and not supported by Microsoft at any level !

I’m sure we all know how great the PowerPivot views are that allow us to preview the contents of Excel files without having to open each one. But have you ever wondered how they are stored and how you might be able to manipulate the previews for a bit of Friday afternoon fun?

OOB PowerPivot Gallery View:

Well here’s how you can modify the preview images shown in the PowerPivot views to include any images you might want to display to the PowerPivot users. Firstly, all the images are stored in the vti_Snapshots property of the SPListItem for each file in the library. This property can be inspected with the following PowerShell:

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$w = Get-SPweb http://bc01/BI
$l = $w.Lists["PowerPivot"]
$i = $l.items[0] ##Get the first item in the library
$ss = $i.Properties["vti_Snapshots"]
$ss

What you’ll see from the output of this is all the preview images have been base64 encoded and concatenated into a single property. To output this property to a file and to allow us to start manipulating it, use the following PowerShell:

Out-File -FilePath c:\users\brian\snapshot.txt -InputObject $ss -Encoding ascii

This will write the contents of the vti_Snapshots property to a text file we can then edit. If you open the text file you’ll notice it is a comma separated file. I’ve split the contents of the file over several lines to make the content easier to read:

You’ll notice there is a reference to each of the worksheets contained in this Excel file. Additionally there is a base64 encoded string that represents each preview image for each sheet. This has been highlighted in red in the screen shot.

Now it’s time to customise the preview image. Firstly, copy on of these base64 encoded strings (a preview image), ensuring you don’t copy any trailing commas, onto the clipboard. Then using a publicly available web site, convert the string into an image file. There are many such sites that allow you to do this but I’ve been using http://www.motobit.com/util/base64-decoder-encoder.asp

If you’re using the above site, paste the base64 string into the form (1), select decode (2), and select export to binary file(3). Finally change the filename to preview.jpg (4) and press ‘Convert the data source’ (5)

This will convert the base64 encoded image back into binary file and download it to your browser. Save the downloaded file and you’ll then have:

Now edit this image file in the software of your choosing, I’ve simply inserted my head shot over the top of the existing preview image:

Now we are going to reverse the whole process. Using the same website we used to decode the base64 string, we’ll encode the binary file (preview.jpg). The options to take should be fairly obvious:

This will result in the manipulated image being base64 encoded and returned to you as a very long string. If the returned string contains any carriage returns, remove these with your favourite text editor and then copy the new base64 encoded string into the snapshot.txt file created earlier ensuring you only overwrite the original base64 encoded string we copied from this file previously, i.e. overwrite the first preview image.. Once you’ve overwrite the original image in the snapshot.txt (and removed any carriage returns you might have inserted as I have done) save this.

New base64 encoded string inserted back into snapshot.txt and carriage returns removed:

 

Now the final step.

Use the following PowerShell to update the vti_Snapshots property of the SPListItem we originally took the preview images from:

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$w = Get-SPweb http://bc01/BI
$l = $w.Lists["PowerPivot"]
$i = $l.items[0] ## Get the first item in the library
$ss = Get-Content -Path c:\users\brian\snapshot.txt
$i.Properties["vti_Snapshots"] = $ss.ToString()
$i.systemupdate(0)

The final result:

I can’t think of any practical use for this but open to suggestions.

Enjoy!