Overview

Collection Tools

Pivot Collection Tools for the Command Line

Pivot Collection Tool for Microsoft Excel

Sample Code for Creating Just in Time Pivot Collections

Developer Info : Pivot Collection Tool for Microsoft Excel

Getting Started

In a nutshell, the Pivot Collection Tool for Excel enables you to represent a Pivot collection in a spreadsheet, and then export that spreadsheet to a format understood by Pivot.

Components

Because it is an Excel add-in, Pivot Collection Tool files are stored as Excel files. The tool consists of the following key components:

Excel Ribbon Tab – Installing the tool adds a new tab (“Pivot Collections”) to Excel’s Ribbon. Controls in that tab are used to associate images with items, set collection-wide properties, and preview or export the entire collection.
“Collection Items” and “Collection Properties” Worksheets – These worksheets (visible as tabs near the bottom of the Excel window) contain the items in the collection and properties affecting the entire collection, respectively.
Excel Table

Rows– Each row corresponds to an item in the collection.
Columns– Each column corresponds to a facet category (“Subject”, for example). For detailed information about facet categories, see Collection XML Schema.
Cells– Each cell contains that row/item’s value (or facet) for the column it is in (“flower” in the “Subject” column, for example).

All of the standard features of Excel are also at your disposal, including data import and transformation functionality. Using these features, data can be imported from text files, web pages, databases, etc., and then organized for display in Pivot.

Publishing

When you are finished editing your collection, use the "Publish" button in the Ribbon. Publishing will create the collection ".cxml" and Deep Zoom image files required for display in Pivot.

It is important to note that the Pivot Collection Tool cannot be used to load or edit existing ".cxml" collection files. As such, any edits made to the files generated during publishing cannot be imported back into the tool. It is recommended that any such edits be made on the Excel file instead, and the publishing step be repeated.

Tutorial

This tutorial will walk you through making a small Pivot collection using sample Windows photos. For reference, use the following figure to identify the various parts of the Pivot client.

Create a New Collection

To create a new collection: start Excel, click the “Pivot Collections” Ribbon tab, then press the “New Collection” button.

A new workbook will be created containing several columns:

Image Location- Location of the original image for an item. Publishing will generate Deep Zoom files from this image, but will not modify the original image. The higher resolution the image, the better.
Name- Name of the item. It will appear at the top of the info panel in Pivot.
Href- A URL associated with this item. Double-clicking the item in Pivot will navigate the user to this URL.
Description- Description of the item. The description will appear near the top of the info panel in Pivot.

Add Items

To add items to the collection:

Place your cursor into the first blank row and click the “Import Images” button in the Ribbon.
Navigate to the Sample Pictures folder (usually under C:\Users\username\Pictures\Sample Pictures).
Select all photos and click Open.

The table will expand to include all imported images, and the images will appear in the "Preview" column:

Add Basic Information

To edit item Names, Hrefs, or Descriptions, simply type in the appropriate columns. In the picture below, the spreadsheet has been edited to include names, links to Wikipedia articles, and Wikipedia descriptions:

Preview the Collection

There is now enough information in the spreadsheet to preview the collection in Pivot. To preview the collection, make sure your cursor is inside the table, then click the “Quick Preview” button in the Ribbon.

After pressing "Quick Preview," Pivot will open to display the information in the collection. Note that, during preview, the items have placeholder images rather than the original images. Click on any of the items in the collection to see if the info panel contents match your expectations:

Add Detailed Information

At this point, the collection could be published for use in Pivot. For instructional purposes, suppose you want to add some detailed information to the collection first.

Return to Excel to continue working on your collection. To make this a more interesting collection of photos, suppose you want to add “Rating,” “Date Taken” and “Subject” columns to the spreadsheet. To add columns, type their names in the first row of the columns to the right of the table.

Once you have added the columns, fill them with information for each item. See the following picture:

Adjust Visibility of Categories

Click “Quick Preview” again to see the new information in Pivot. By default, the newly-added facet categories will appear in both the filter panel and the info panel. Use the “Category Properties” panel in the Ribbon to modify visibility settings for each category.

The fields that can be modified are:

Filter Panel – Choose whether the category appears in the filter panel. If a category appears in the filter panel, it is also available as a sort option in the view.
Info Panel – Choose whether the category appears in the info panel, as part of the details of an item.
Keyword filtering – Choose whether the category is included in keyword filtering. Keyword filtering allows the user to enter keywords to use as filters in a collection. It is generally recommended that only facet categories that help identify the item (ex: actor name) be included in keyword filtering, as opposed to more tangential properties (ex: an actor’s list of co-stars).

For more information on adjusting visibility, see Collection Design.

Assign Multiple Values in the Same Category

In the above example, no items were assigned multiple values in the same category. Suppose you want an item to have two values for the “Subject” category.

The tool supports two ways of assigning multiple values in the same category:

Separate the values with ‘||’ - Put multiple values into a single cell, but separate them with ‘||’ (two pipe characters). For example, put “flower || red” into the “Subject” column for the “Chrysanthemum” item.
Duplicate the column – Alternately, add another column to the table with the same name, but add a trailing number. When previewing or publishing, the tool will consolidate these columns into a single category with multiple values. In the example above, add a column called “Subject2” and place the second value there.

In either case, the display in the Pivot info panel is the same:

Note: the approach of separating values with '||' works only on facet categories of string type. Duplicating the column name works on facet categories of all types.

Set Facet Category Types and Formats

Pivot supports five facet category types: String, LongString, Number, DateTime and Link. For detailed information about facet categories types, see Collection XML Schema. The Pivot Collection Tool infers the type of data when you preview or publish a collection based on the values currently in the cells. Some of the behaviors available to help you format facet category presentation in Pivot are:

Setting date and time formats - Formats entered in Number, Date and Time categories in Excel will be preserved in Pivot. For example, to present a numeric facet that only has integer values, select the "Number" category in Format Cells dialog and set the number of decimal places to 0.
Choosing between Strings and LongStrings - Columns with text data will be shown with String type by default. To choose LongString formatting for a facet category, select all cells in the appropriate Excel column, choose 'General' or 'Text' category in the Number tab of Format Cells dialog, and check the 'Wrap Text' box on the Alignment tab of the same dialog.
Inserting hyperlinks - Pivot Collection Tool will automatically recognize columns with hyperlink data, and make use of both href and title from link cells. To specify the hyperlink href and title in Excel, you may press Ctrl-K when focused in a cell and set these values individually, or combine them programmatically by using Excel's HYPERLINK() formula.

In our example collection, suppose you want to display the "Date Taken" field in the form "weekday, month & date, year":

Right-click the "Date Taken" column header ("K", in this example).
Click "Format Cells...."
Under the "Date" category on the "Number" tab, select the format sample that reads "Wednesday, March 14, 2001".

Edit Collection-Wide Properties

One of the final steps in collection authoring is configuring collection-wide properties. These are available on the “Collection Properties” sheet of the workbook.

The following properties can be edited:

Collection Title
Icon
Brand Image
Additional Search Text
Copyright

For more information on these properties, see: Collection Design.

Publish!

Once you are finished editing the collection-wide properties, switch back to the “Collection Items” sheet. Make sure your cursor is inside the table, then click the “Publish” button in the Ribbon. Publish will create the collection ".cxml" and Deep Zoom image files for your collection and then load the collection in Pivot:

Sharing Your Collection

To share your collection with others, you'll need to place it on a website or file share. This section contains basic information about sharing a collection. For more detailed information, see: Collection Hosting.

To share your collection, you need to move the files created during the publish step to a location that your audience can access. When you navigate to the directory where you published the collection, you will see the following files and directories:

A ".cxml" file - This is the main file containing your collection. For example, "Photos.cxml".
A directory of image files - This directory contains the images in your collection. For example, "Photos_files". This directory contains:

A directory called "images" – This directory contains image files for your collection.
A number of ".xml" files and directories - These files and directories are generated every time a collection is published, but you'll only need the ones from the last time you published. Their names are generated randomly. For example, a file might be called "1c3a2fl3.w1s.xml" and a directory might be called "1c3a2fl3.w1s_files".

To share the collection with others, copy the ".cxml" file, the images directory, the most recently modified ".xml" file, and the most recently modified directory. The ".xml" file and the directory should have matching names. All other ".xml" files and directories can be deleted.

The easiest way to ensure that only the latest files are copied is to delete all files and directories with randomly-generated names, then re-publish from the Pivot Collection Tool. The tool will create a new ".xml" file and directory with the latest information, ready to be copied.

Once you have copied the files, share a link to the ".cxml" file to let your audience open it in Pivot!

Download

Feedback

Join our technical discussion to interact directly with the Pivot team. We hope you’ll join this community and share your work.