SharePoint 2010 + InfoPath 2010 + Excel Services + REST API

Bei der Realisierung eines Urlaubsantragssystems auf Basis von SharePoint 2010 kann es mitunter zu einigen Problemen kommen.

Einige Fragen, die vor der Realisierung beantwortet werden müssen sind folgende:

     - Wo wird der Stand der Urlaubstage verwaltet
     - Wie wird die Berechnung der verbrauchten Urlaubstage durchgeführt
     - Wie sieht der Prozess für den Urlaubsantrag aus
     - Tragen die Mitarbeiter die genommenen Urlaubstage händisch ein, oder soll dies automatisch 
        berechnet werden ?

All diese Fragen führten dazu, dass für das Formulardesign InfoPath 2010 verwendet wurde, um eine schöne Oberfläche für das Urlaubsantrags-Formular zu bekommen.

Das Formular hatte einige Steuerelemente wie z.B. People picker, Textboxen und Datumsfelder für das Datum des 1. und des letzten Urlaubstages.

Und genau bei diesem Punkt fängt das Problem der Urlaubstagsberechnung an.
InfoPath kann zwar die Differenz von 2 Datumswerten berechnen, was allerdings fehlt ist die Berechnung unter Berücksichtigung der Wochenenden und Feiertage.

Hier kommen die Excel Services ins Spiel. Excel besitzt nämlich die Funktion NETWORKDAYS, die die Arbeitstage zwischen zwei Datumswerten berechnet.

Wie bekommt man nun die Datumswerte des InfoPath Formulars in den Excel Sheet, der dann dort die Berechnung der Arbeitstage ausführt und diese dann an das Formular retourniert ?

Die Antwort lautet REST API. Die REST API macht genau das möglich. Im weiteren Verlauf dieses Posts werde ich auf die Umsetzung dieser Funktionalität unter Zuhilfenahme der verschiedenen Technologien im Detail eingehen.

Also beginnen wir zunächst mit dem Excel Sheet.
Hierfür erstellen wir einen neuen Excel Sheet und verwenden die ersten 2 Zellen in der Spalte A für unsere Inputs.
Ich habe die Felder in diesem Fall "StartDate" und "EndDate" genannt.
In der 1. Zelle in Spalte B soll dann unser berechneter Wert stehen.
Dazu fügen wir die Formel "=NETWORKDAYS(StartDate; EndDate) in diese Spalte.
Ein Test zeigt, dass die Formel funktioniert:



Jetzt kann der Excel Sheet in eine SharePoint Library via "Save & Send" gepublished werden. Wichtig hierbei ist, dass die beiden Felder StartDate und EndDate bei den Parametern angehakt werden, um diese Felder über die REST url dann verfügbar machen zu können.

 

Ein wichtiger Faktor ist das Datumsformat. In meinem Fall war das Datumsformat English (UK).
Wichtig ist, dass dieses Format auf jeden Fall korrekt übergeben wird, sonst wird ein falscher Wert vom REST Service zurückgeliefert.

Um zu testen, ob REST funktioniert, wird dann folgende Url im Internet Explorer eingegeben:

http://<server>/<site>/_vti_bin/ExcelRest.aspx/<library>/<ExcelSheet>.xlsx/Model/Ranges('DateDiff')?Ranges('StartDate')=2012-01-13&Ranges('EndDate')=2012-01-17

DateDiff ist in unserem Fall die Zelle, die die Formel für die Berechnung enthält.
Haben wir alles richtig, gemacht, erscheint folgende Ausgabe im Internet Explorer:

 

Da das Format des Rückgabewertes zum jetzigen Zeitpunkt noch HTML ist und wir in InfoPath XML benötigen, fügen wir noch den Ausdruck $format=atom zu unserer Url hinzu, sodaß die Url dann folgendermaßen aussieht:

http://<server>/<site>/_vti_bin/ExcelRest.aspx/<library>/<ExcelSheet>.xlsx/Model/Ranges('DateDiff')?$format=atom&Ranges('StartDate')=2012-01-13&Ranges('EndDate')=2012-01-17

Da wie bereits erwähnt das Datumsformat sehr wichtig ist, stellen wir im InfoPath Formular gleich das richtige Datumsformat in dem Date Picker ein. Dies geschieht über die Properties des DatePickers und war in meinem Fall English (UK).

 

Nachdem das Design des InfoPath Formulars abgeschlossen ist, binden wir nun das REST Service in unser Formular mit ein.
Dazu fügen wir zuerst das REST Service als Datenquelle hinzu.













In das Url Feld fügen wir unsere Url von vorher (inklusive dem $format=atom Parameter) ein und bei "Automatic retreive" entfernen wir den Haken in der Checkbox.

Jetzt müssen die Datumswerte noch von den Feldwerten ersetzt werden.
Hierzu fügen wir für jedes Datumsfeld eine Regel hinzu.

Zunächst für das Startdatum:

 

Als Action ändern wir die REST Url:

Die Funktion substring-before(<feldname>, "T00:00:00") muss durchgeführt werden, da InfoPath diesen Zeitwert an den Datumswert anhängt und wir kein vernünftiges Ergebnis bekommen, wenn wir diesen Zeitstempel nicht entfernen.

Diese Rule wird auch für das EndDatum angelegt und die REST Url geändert:

 

Im nächsten Schritt wird für das EndDatum Feld noch eine Regel erstellt, die die neu erstellte REST Url an das REST Service sendet:

 

Als nächster Schritt wird das Ergebnis der Query - also die Anzahl der Urlaubstage - in einem Feld gespeichert:

Als Wert wird aus der Secondary Data Source das REST Service ausgewählt:

 

Damit ist die Funktionalität für die Berechnung der Urlaubstage implementiert.
Zusätzlich verwalte ich den aktuellen Urlaubsstand der Mitarbeiter noch in einer Liste und wenn der Urlaubsantrag approved wird, wird der Stand der Urlaubstage entsprechend korrigiert.