În mod obișnuit Excel-ul permite publicarea de date în SharePoint sub forma de liste, prin exportul tabelelor Excel în SharePoint (Figura 1) sau prin importul fișierelor Excel din SharePoint (Import Spreadsheet). În sens invers Excel poate prelua date din listele SharePoint prin Export (Figura 2)

Figura 1 – Export Table to SharePoint List

Figura 2 – Export to Excel

În momentul în care se exportă datele din SharePoint către Excel se poate stabili și o conexiune de tip consultare a datelor care permite adăugarea permanentă în Excel a noilor date introduse în listele SharePoint. Această funcționalitate permite centralizarea, consolidarea, prelucrarea și raportarea facilă a datelor introduse în listele SharePoint. În același fel datele pot fi exportate și către baze de date Access. Pentru a avea o conexiune permanentă între Excel și SharePoint trebuie bifate opțiunile Enable background refresh și Refresh data when opening the file.

Definiția conexiunii poate fi editată în așa fel încât se pot prelua date doar dintr-un anumit view. Exemplu de definiție a conexiunii:

<LIST>
<VIEWGUID>{6995ACC0-5E9D-4567-9CAF-D29FAB774237}</VIEWGUID>
<LISTNAME>{BCC35AE5-F9CF-4158-B542-1F7499069DC3}</LISTNAME>
<LISTWEB>https://intranet.uaic.ro/test/_vti_bin</LISTWEB>
<LISTSUBWEB></LISTSUBWEB>
<ROOTFOLDER>/test/Lists/Sales</ROOTFOLDER>
</LIST>

Fiecare View dintr-o listă SharePoint are un alt GUID care poate fi introdus în fișierul de conexiune în secțiunea Viewguid. Numele listei este și el sub formă de ID hexazecimal.

Conexiunea bidirecțională

Nu există o metodă ”oficială” pentru a realiza o conexiune bidirecțională între SharePoint 2013 și Excel 2013. Dar există o metodă mai veche care funcționează și în SharePoint 2013.

Pentru această funcționalitate trebuie să descărcați și să instalați add-on-ul pentru Excel și SharePoint 2007 de la adresa: Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists.

În fapt pachetul de instalare conține un fișier: SynchronizeWSSandExcel.xlam care implicit se găsește pe disc la adresa: C:\2007 Office System Developer Resources\Code Samples\XL2007SynchronizeWSSandExcel.

Fisierul SynchronizeWSSandExcel.xlam poate fi descărcat de la adresa: https://onedrive.live.com/?id=6EC38785F56DAE91%212853&cid=6EC38785F56DAE91

În articolul OfficeTalk: Publishing and Synchronizing Excel 2007 Tables to SharePoint Lists sunt detaliate metodele de activare ale Add-in-ului pentru Excel 2007, dar funcționalitățile respective nu pot fi activate în Excel 2013.

Pentru a beneficia de funcțiile de sincronizare trebuie să deschidem fișierul SynchronizeWSSandExcel.xlam și să activăm funcționalitatea macro-ului inclus.

După activarea Macrourilor vom putea beneficia de butonul de sincronizare în ribbon-ul Table Tools, Design. Observați diferența între figura 3 în care apare butonul de Publish and allow Sync și figura 1 în care nu apare acest buton.

Figura 3 – Butonul Publish and allow Sync.

În fișierul SynchronizeWSSandExcel.xlam descoperim un Macro  destul de interesant care are legătură cu un formular VBA de publicare a tabelului într-o listă Excel.

‘Callback for PublishRW onAction
Sub PublishSP(control As IRibbonControl)
LaunchPubForm
End Sub

Private Sub LaunchPubForm()
On Error GoTo noTable
ListName = ActiveCell.ListObject.Name
ExportRWList.Show
End
noTable:
MsgBox „You must be in a table to publish”
End Sub

Formularul VBA folosit pentru Publicare:

După publicare, coloanele din tabel apar în lista SharePoint, cu mențiunea că în view-ul default al listei apare și coloana Title care ar putea fi ascunsă ea neputând fi ștearsă. De asemenea, în Excel apar din SharePoint coloanele ID și Title.

Aceste coloane suplimentare pot fi ascunse după caz.

Un alt aspect este legat de inițierea sincronizării. Operațiunile de pe Ribbonul Table Tools nu includ un shortcut pentru operațiunea de sincronizare. Sincronizarea se poate realiza în schimb cu RClick pe tabel, secțiunea Table, opțiunea Syncronize with SharePoint.

“Aducerea” butonului de sincronizare pe Ribbon se poate face prin personalizarea acestuia și adăugarea opțiunii Syncronize List pe zona de toolbar ca un custom group.

O mică dilemă – Salvarea

Orice versiune aș încerca salvarea standard nu funcționeaza. Nici ca macro enabled nici într-un alt fel cunoscut de Excel. Mesajul de eroare:

Nici o versiune de Excel 2007/2013 nu este suportată în salvare, singura viabilă fiind cea descrisă în articolul Office talk de mai sus, adică: Excel 97-2003, din cauza fatpului că XLAM-ul este construit pentru versiunea 97/2003. Vestea bună este că după salvarea în format 97/2003 nu mai este nevoie să activăm manual excelul SynchronizeWSSandExcel.xlam.

O componentă interesantă a acestui add-in din Excel este legat de rezolvarea conflictelor. În cazul în care Lista a fost modificată înainte de sincronizare și în SharePoint și în Excel vom întâlni mesajul următor la prima sincronizare. Retry All My Changes va provoca o sincronizare corectă între datele modificate din SharePoint și cele din Excel.

Puțină optimizare

Cum facem ca fișierul XLAM să nu mai trebuiască deschis de fiecare dată când dorim să facem o publicare?

În articolul Add or remove add-ins ni se explică cum am putea activa XLAM-ul ca add-in permanent în Excel. Pe scurt: File, Options, Add-ins, Manage Excel Add-ins, Go. În fereastra Add-ins se apasă pe butonul Browse, se caută pe disc fisierul SynchronizeWSSandExcel.XLAM instalat la început și Ok, după care se activează Add-in-ul pentru a rămâne permanent în memoria Excel-ului.

Nu ștergeți sau redenumiți fișierul din locația lui. Eventual înainte de a adauga add-in-ul în Excel salvați-l în Program files sau într-o locație care să nu vă tenteze să-l stergeți.

Fisierul SynchronizeWSSandExcel.xlam poate fi descărcat de la adresa: https://onedrive.live.com/?id=6EC38785F56DAE91%212853&cid=6EC38785F56DAE91

Final

Testat fișierul exportat în formatul 97/2003 pe un Excel 2010 de pe alt calculator care nu are Add-in-ul instalat. Sincronizarea tabelului funcționează în mod corect.

Sper să fie util și… mulțumesc colegilor din Petrom pentru idee.