Connecting Drupal Content Management System to SharePoint 2010 via ReST

SharePoint 2010 is an excellent document management system, which can also be used as intranet. In some cases, organizations may want something simpler to manage content yet need to link to elements of more sophisticated content held in documents, which may well be located in SharePoint. The easiest means to do this at a very basic level is to use the ReST protocol to pick up images, such as Charts in this example, which may probably be most conveniently hosted in Excel files.

The file below is held in a document library in SharePoint 2010 and updated by users via the browser.

Figures can be edited simply and the chart can be kept up to date.

Once the file is saved, (in this case, each cell edit in Excel is a save action), then the Chart will reflect the changed data.

 

In order to use the chart in an external content management system such as Drupal, the chart needs to be inserted as an image tag in a Drupal page as follows:

<img src=”http://win-sqpsjc4uhjv/sites/drupal/_vti_bin/ExcelRest.aspx/rest/Book1.xlsx/Model/Charts(‘Chart%201&#8217;)”>

The text format in Drupal must be set to Full HTML as follows:

The Chart image will then be displayed directly in the Drupal page. In this simplified version, the SharePoint site hosting the Excel file has been has been set up with read-only Anonymous access enabled. In the real world a more sophisticated configuration would be required to manage authentication between Drupal and SharePoint. This will be the subject of a blog article in the near future.

 

A soon as the source changes in SharePoint, only a page refresh is required to see the current data, as follows:

 

Advertisement

 

Adding months to a date in Excel

The straightforward way to add six months to a date in Excel is to use a formula of the form:

=DATE(YEAR(A29),MONTH(A2)+6,DAY(A2)) where your source date is in cell A2.

However, due to the varying length of months, if your source date happens to be, for example, 31/1/2011 and your formula adds 5 months, then the result of the calculation will be 1/7/2011. Perhaps not what you were expecting.

In order to get round this problem, Excel provides the EDATE function, which will correctly add months to a date and take into account varying month lengths. In our example, EDATE gives the correct result of 30/6/2011.

(If EDATE does not function in your version of Excel, check that you have the Analysis Toolpak installed).

 

Auto-number formula in Excel: 1.1, 1.2 etc

Requirement: column A in Excel contains a data validation drop-down menu which contains numbers from 1 to as many Objectives that have been inserted in another field somewhere. The requirement in column B is to create a numbered sequence via a formula which will automatically calculate in the form 1.2, 1.2, 1.3, 2.1, 2.2 etc. no matter how many numbers there are. This can be done in the following way:

=IF(OFFSET($B10,0,-1,1,1),IF(OFFSET($B10,0,-1,1,1)=

OFFSET($B10,-1,-1,1,1),$A10&”.”&

RIGHT(OFFSET($B10,-1,0,1,1),1)+1,$A10&”.1″),””)

The formula assumes that the count begins in row 10 and that there are no more than 9 subsets for any principal number.

 

Spell Checking Protected Sheets in Excel

Once a worksheet has been protected in Excel, to prevent formulas from being changed, for example, then a number of useful functionalities are no longer available. One of the most frustrating, for many people, is that spell checking is greyed out on the Excel ribbon.

In order to make spell checking available, the only work-around is to write a macro which will temporarily un-protect the sheet, run the spell check and then re-protect the sheet. The macro itself is simple, but where it starts to get complicated is when different files, perhaps in a corporate environment, have different passwords. In addition, users need the flexibility to spell check just a selection, an entire worksheet or indeed all worksheets in an Excel workbook.

The example Excel macro-enabled file (link at the bottom of this post) contains the code to create a custom menu-bar in the Add-ins section of the Excel ribbon. The menu will be loaded when the file is opened and removed when the file is closed. The code creates menu items to check a selection of the active sheet, an entire sheet and all sheets for two departments, Finance and Procurement. There are two example sheets in the file for Finance and Procurement, which have been locked with two different passwords “passf” for Finance and “passp”. Normally two different passwords would not be used in same file, this is done purely for the sake of the example.

Error handling provides users with a message if they try to run spell-checking on an Excel file using the wrong menu item (e.g. running a Finance spell check on a Procurement file or a Procurement spell check on a Finance file). The process could be streamlined if some consistent indication could be given of which department files originated from, e.g. using a named range in a hidden sheet set to Finance or Procurement.

Link to code example.

 

Excel Data Validation Holy Grail

The previous two posts have charted my experiences and the techniques I have put together in the context of Excel data validation. The ultimate objective was to create a pick-list which would contain both a standard set of default values and any user entered values together in a single list. This Holy Grail of data validation had to be achieved, in addition, without the use of macros.

The ultimate solution relies on a complex combination of formulas on a hidden sheet. Here is the step-by-step process described in detail:

1. Create a new worksheet which will ultimately be hidden from the user, my example is called Drops.

2. Create a dynamic named range called DropSource on the column which will contain your data validation.

=OFFSET(‘Data’!$A$2,0,0,COUNTA(‘Data’!$A:$A)-1,1)

 

3. In A2 of Drops, create an array formula using Control-Shift-Enter with the following form:

=INDEX(DropSource,MATCH(0,IF(ISBLANK(DropSource),1,COUNTIF(A$1:$A1,DropSource)),0))

 

4. Drag to copy this formula down for the number of rows equivalent to the maximum number of unique values you expect to have in your pick-list.

 

5. Create a dynamic range on column A (in my example called Good) to pick up only values and exclude #N/A

=OFFSET(Drops!$A$2,0,0,COUNTA(Drops!$A:$A)-COUNTIF(Drops!$A:$A,”=#N/A”),1)

 

6. Create your list of default values somewhere, for example in column B of Drops and give the range a name, in my example this range is called Fixed.

 

7. In column C, row 2, enter the following as an array formula

=IF(ROW(A1)<=COUNTA(Fixed),INDEX(Fixed,ROW(A1)),IF(ROW(A1)<=(COUNTA(Fixed)+COUNTA(Good)),INDEX(Good,ROW(A1)-COUNTA(Fixed)),”0″))

then copy it down the same number of row as you did in column A.

8. Next create a dynamic named range on this column, (in my example called GoodFinal), which will concatenate unique values entered in the column with data validation plus the list of default values and remove zeros.

=OFFSET(Drops!$C$2,0,0,COUNTA(Drops!$C:$C)-COUNTIF(Drops!$C:$C,”=0″),1)

 

9. In column D, row 2 enter the following array formula

=INDEX(GoodFinal,MATCH(0,IF(ISBLANK(GoodFinal),1,COUNTIF(I$1:$I1,GoodFinal)),0))

and copy it down the required number of rows.

10. Finally, you are ready to create a dynamic named range on this column, which will furnish the final list of values for your drop-down on Data!$A

=OFFSET(Drop!$D$2,0,0,COUNTA(Drops!$D:$D)-COUNTIF(Drops!$D:$D,”=#N/A”),1)

 

One final constraint is that you will need to enter a first value in your first cell which uses your drop-down, which could be an instruction, such as Enter values here.

 

 

Dynamic Drop-down Validation combining user entries and fixed values

Is there any technique which can be used in Excel to combine a list of default entries for a data validation drop-down list plus the values which have been added by the user where the data validation list is not constrained?

There is one means of achieving this which used a pivot table to create the range of values for the drop-down. The only drawback is that the pivot table needs to be refreshed before new values entered show up in the dropdown. This can be achieved most easily by including the Refresh all button in the Quick Launch menu bar.

In order to get bot the static and dynamic values to show up, we need to use a pivot table which consolidates two separate ranges. The first range will be a dynamic named range which looks up the entries that the user has entered in the column including any from the fixed list. Create a dynamic named range using the OFFSET formula as follows:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),2)

Note that the range must include two columns, but it is irrelevant what is in the adjacent column as it will not be used.

Next, create the list of fixed values, perhaps on another worksheet which can then be hidden from the user.

Make sure that you title the first column with the same name as used by the drop-down, so that the pivot table can consolidate both ranges.

To create the pivot table, use Alt D, P to launch the Pivot Table wizard and choose Multiple Consolidate ranges:

Add two ranges, the first will be the dynamic range created initially, the second must be a range covering the title and values in the fixed column, plus the column adjacent to the right, no matter what is in it.

The resulting pivot table will contain single entries from both ranges.

Create a dynamic range based on the pivot table, to pick up just the required values form the list:

=OFFSET(Sheet2!$H$5,0,0,COUNTA(Sheet2!$H:$H)-4,1)

Set the data validation on your column to this named range and it will combine entries from the fixed list plus any entries added by the user, once the pivot table has been refreshed.

 

Creating a data validation list which can include user defined choices beyond the present pick-list

Excel data validation is a good tool to employ to direct users to a pre-defined set of choices for a particular cell. You can also allow users to enter their own values. But what if you want to add a set of user-defined values on top of a set of default values, without any scope of entering other values apart from what is in the two lists?

This can be done with a few simple formulas in Excel.

First you need to create a place for users to enter their additional pick-list values. Let’s put these in Column A, from A2 until A11, (we will use the first row for our titles). In column B, we will use a formula to generate a numbered entry, which will follow-on incrementally from our fixed pick-list, in Column E.

We do not need to expect an unlimited number of User-Defined Values, and in this example we will limit the list to 10 entries. It is up to you to decide what is reasonable in your particular case. You may want to use sheet protection to limit editing outside certain areas of the sheet.

The formula used in Column B to generate the entry is:

=IF($A2=””,””,CONCATENATE(ROW()+4,”. “,$A2))

As you can see, the formula displays a generated value if the cell to its left is not empty. Entry 5 is then also added to the pick-list range in Column E.

To do this the cells below the first four fixed entries contain a formula:

=IF($B2=””,0,$B2)

which puts a zero in the cell if there no user-defined value present, with conditional formatting to make text white on white. I use a calculation to count how many items are in the pick-list, as follows:

=COUNTA(E2:E11)-COUNTIF(E2:E11,”=0″)

This formula counts values and subtracts entries which are set to 0 in Column E. This is necessary because otherwise Excel will consider that formulas in Column E are actually entries.

Finally, there is the formula for the Named Range which defines the choices in the pick-list for data validation:

=OFFSET(Sheet1!$E$2,0,0,Sheet1!$I2,1)

Offset will go as far as the last cell in Column E which has an entry.

 

Using Excel as a Data Collection form

The near universality of Excel means that it is often used as a form to capture data and input it to database systems. Excel files can be easily circulated and though InfoPath is a better tool for this job, the familiarity of Excel often leads to it being used instead of InfoPath forms.

To get anywhere near to the functionality available out-of-the-box using InfoPath, some pretty tortuous tricks need to be used in Excel, using data validation and named ranges. This post tries to capture some of the techniques I used recently at a client to make Excel a better tool for data capture.

In the scenario we faced, an international organization is using Excel as an input form to gather information from a range of partner organizations around the world. The institution has to work in six official languages, but for the most part, the interface in Excel has to exist in three principal languages, English, French and Spanish. Alongside the language choice, the organization collects information from three main sectors of activity; let’s call them Services, Industry and Agriculture, for the sake of argument. Now though a number of users are now moving to Office 2010 and the though the norm is still Office 2007, the institution has to cater to the lowest common denominator, which remains the Excel 2003 binary file format. In addition, a number of partner organizations cannot use macro-enabled spreadsheets and so the requirement was for an Excel interface which allowed for the easy switching of languages and that the contents of any drop-downs boxes should reflect the language choice which had been made, all done with only formulas and standard Excel functionality.

For the language choice, we set up a cell with data validation, based on a list containing the values English, Espagnol and Français as follows.

The cell was then given a Name, Language as follows:

Translations for headings are put in a separate worksheet called Translations, which will later be hidden from users. Headings in English are put in row 2, the equivalent translation in Spanish put in row 3 and that for French in row 4, as the mock-up below demonstrates:

A formula is used with nested IF statements to display the headings in the form, dependent on the language chosen.

The full formula is shown below:

=IF(Language=”English”,Translations!B2,IF(Language=”Espagnol”,Translations!B3,IF(Language=”Français”,Translations!B4)))

So far, so good and the technique provides a very simple interface which allows labels to be switched by the user at their own convenience.

However, the form must also contain drop-down boxes with suggested values under certain columns, and these choices must also change with reference to the language selected and also be constrained with reference to another drop-down we will call Sector.

First we will create the translations for the choices available in the drop-down box in the different languages:

The first column contains a Named Range called Agriculture covering the 7 rows of choices that are then provided in the next three columns in English, Spanish and French. For each cell in the Agriculture Named Range, a formula calculates the contents according to the Language chosen:

=IF(Language=”English”,B8,IF(Language=”Espagnol”,C8,IF(Language=”Français”,D8)))

In this way, the appropriate contents of the drop-down box are shown, relating to both Language and Sector selected.The same technique is used for the other two Sectors, Industry and Services.

These techniques are relatively simple but the end result is a more powerful multilingual form, with data validation but no use of macros.

 

SharePoint 2010, Excel Services and REST

REST can be used to connect to named ranges, charts, tables and pivot tables as well as individually defined cell contents using a simple URI via Excel Services. In the following Excel file, held in a normal document library in SharePoint 2010, each element can be referenced.

A single cell – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Ranges(‘Sheet1!b2’)

A Named Range – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Ranges(‘Range1’)

A Table – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Table(‘Table1’)

A Chart – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Charts(‘Chart%202’)

A Pivot Table – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/PivotTables(‘PivotTable1’)

However, as soon as an Excel file contains elements which are not compatible with Excel Services such as data validation or password protection, then REST no longer functions for that file and no elements can be accessed.

If no element is specified, and atom feed is returned listing all named elements which can be accessed using REST.

server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model

Macro-enabled Excel files, with the extension .xlsm, though not compatible with Excel Services, may still be displayed and may still be accessible via REST.

Unsupported Features in Excel Services

 Information Rights Management (IRM) protection.

 ActiveX controls.

 Embedded smart tags.

 PivotTables report with multiple consolidation ranges.

 External references (workbooks that contain links to other workbooks).

 Workbooks saved in formula view.

 XML expansion packs.

 XML maps.

 Data validation.

 Query tables, SharePoint lists, Web queries, and text queries.

 Workbooks that reference add-ins.

 Workbooks that use the RTD() function.

 Workbooks that use workbook and worksheet protection.

 Embedded pictures or clip art.

 Cell and sheet background pictures.

 AutoShapes and WordArt.

 Ink annotations.

 Organization charts and diagrams.

 DDE links.

Features That Are Not Displayed

Workbooks that contain one or more of the following features will load in Excel Services, but the features will not be displayed. The features won’t be removed from the file, so the next time you open the file in Excel 2007, they will be displayed again.

Split and frozen panes

Headers and footers

Page layout view

Cell patterns

Zoom

Microsoft SQL Server Analysis Services’ member properties in ScreenTips

Some cell formatting, such as diagonal borders and border types not supported by HTML

Excel Services displays all charts that were created using Excel 2007. However, depending on how the charts are designed, some minor visual differences occur when some charts are displayed using Excel Web Access. For example, Excel Web Access cannot show new chart effects such as shadows, bevels, and reflections.

Features with Limited Interactive Support

Certain features that are interactive in Excel 2007 are not completely interactive in Excel Web Access.

PivotChart reports

PivotChart reports are not interactive when displayed in a browser. You cannot filter a PivotChart report directly, but if you interact with the PivotTable report that supplies data for the PivotChart report, the PivotChart report updates accordingly. This means if you filter the PivotTable report, the PivotChart report updates to show the filtered data set.

PivotTable reports

You can sort, filter, expand, and collapse data in PivotTable reports in a browser, but you cannot use SQL Server Analysis Services actions, add or remove fields, or rearrange fields.

Go To

In a browser, you can use the named range object view or the provided navigation controls to move around a workbook. However, there is no Go To feature.

Zoom, minimize, and maximize

Users cannot zoom, minimize, or maximize worksheets when viewing them in the browser.

Switching to page layout view

Page layout view is a new view in Excel 2007. You cannot switch to this view when viewing a workbook using Excel Web Access. Page layout view is designed to facilitate printing. If you want to print a workbook, it is best to use Excel 2007.

Goal Seek and Scenario Manager

You cannot use Goal Seek or Scenario Manager when viewing a workbook using Excel Web Access.

Formulas

You cannot audit formulas using trace precedents, trace dependents, show formulas, and so on.

Changing a workbook’s calculation mode

You cannot change a workbook calculation mode using Excel Web Access.

Watch window

You cannot use the Watch window when using Excel Web Access.

 

Linking to SharePoint Lists as XML

Any SharePoint list can be accessed as XML. If you use a URL like the following:

http://anycompany.com/sites/yoursite/_vti_bin/owssvr.dll?Cmd=Display&List=%7B6DD52014%2D168C%2D4298%2D9F60%2D08234232A394%7D&XMLDATA=TRUE

and use Excel 2003 to open it, you can link to it like any other XML.

1. Choose Use the XML Source task pane:

   

2. Choose the Elements you want to map:

   

3. And then your List is linked to your Excel file:

   

4. Use the List Toolbar in Excel to synchronize changes: