Sincronizare date între Excel 2013 și SharePoint 2013

Î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)

image
Figura 1 – Export Table to SharePoint List

Export to Excel
Figura 2 – Export to Excel

În imagemomentul î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.

Î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.

imagePentru 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.

image
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:

image

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.

image image

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.

image

“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:

image

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.

imageO 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?

image Î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.

Puteți descărca fișierul SynchronizeWSSandExcel.XLAM și de la această adresă.

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.

Prezentarea de la Using Microsoft Academic Program

Manifest:
Este important să știi, dar este esențial să înțelegi!

Duminică 10/11/2013 am participat la Using Microsoft Academic Program organizată de Microsoft Students Partners Iasi și am povestit despre experiențele mele cu produsele Microsoft și în special despre cum am migrat Portalul FEAA de la SharePoint 2010 la SharePoint 2013, precum și despre implicațiile acestei operațiuni.

Mai multe detalii găsiți în slide-urile atașate: http://sdrv.ms/HKx4r2

Viziune proprie despre arhitectura SharePoint Server

Viziunea proprie despre arhitectura SharePoint în cadrul unei organizații. Totul poate fi perfect până la nivelul People, de aceea de obicei în figurile de arhitectura lipsește acest nivel. :)

Etapele sau pașii mari ai migrării (criptat :):

1. Copy Backup SQL Server
2. Instalare SP2013
http://blogs.msdn.com/b/uksharepoint/archive/2013/03/18/scripted-installation-of-sharepoint-2013-and-office-web-apps-server-from-the-field-part-2.aspx
de pus si patch-urile
(1)   KB2767999 http://support.microsoft.com/kb/2767999 – ubersrvsp2013-kb2767999-fullfile-x64-glb.exe
(2)   KB2726992 http://support.microsoft.com/kb/2726992 – 462705_intl_x64_zip.exe
====
Comenzi configurare BD
New-SPConfigurationDatabase –DatabaseName A_SharePoint_Config -DatabaseServer bdsrv-02 –AdministrationContentDatabaseName A_SharePoint_Admin_Content

====
Configurare state service
New-SPStateServiceApplication -Name "State Service Application"
Get-SPStateServiceApplication | New-SPStateServiceApplicationProxy -defaultproxygroup
Get-SPStateServiceApplication | New-SPStateServiceDatabase -Name "A_State_Service_DB"
Get-spdatabase | where-object {$_.type -eq"Microsoft.Office.Server.Administration.StateDatabase"} | initialize-spstateservicedatabase
New-SPUsageApplication -Name "Usage and Health Data Collection"
$proxy = Get-SPServiceApplicationProxy | where {$_.TypeName-eq"Usage and Health Data Collection Proxy"}
$proxy.Provision()

=====
Wizard pentru alte servicii
=====
Sters site implicit (SharePoint – 80) si creat MySite Host
=====

3. Instalare Language pack Romanian

4. Restore baza de date in new SQL Server

5. Creare Web app in clasic mode
New-SPWebApplication –name "P_ID" –Port 80 –ApplicationPool "A_ID_Pool" -HostHeader "id.feaa.uaic.ro" –ApplicationPoolAccount (Get-SPManagedAccount "FEAA\Admin.pool")

6. Test baza de date
Test-SPContentDatabase -Name P_Doctorat_Content -WebApplication http://v-portal:100/ > TestUpgrade.txt

7. Atasare baza de date
Mount-SPContentDatabase -Name P_ID_Content -WebApplication http://id.feaa.uaic.ro

8. Convert-SPWebApplication –Identity http://id.feaa.uaic.ro –To Claims -RetainPermissions -Force

9. Creare Web app in clasic mode
New-SPWebApplication –name "P_ID" –Port 80 –ApplicationPool "A_ID_Pool" -HostHeader "id.feaa.uaic.ro" –ApplicationPoolAccount (Get-SPManagedAccount "FEAA\Admin.pool")
New-SPWebApplication –name "P_Doctorat" –Port 80 –ApplicationPool "A_Doctorat_Pool" -HostHeader "doctorat.feaa.uaic.ro" –ApplicationPoolAccount (Get-SPManagedAccount "FEAA\Admin.pool")
New-SPWebApplication –name "P_Portal" –Port 80 –ApplicationPool "A_Portal_Pool" -HostHeader "portal.feaa.uaic.ro" –ApplicationPoolAccount (Get-SPManagedAccount "FEAA\Admin.pool")

Test-SPContentDatabase -Name P_PortalFEAA_Content -WebApplication http://portal.feaa.uaic.ro/ > C:\Temp\TestUpgrade.txt

10. Atasare baza de date
Mount-SPContentDatabase -Name P_ID_Content -WebApplication http://id.feaa.uaic.ro
Mount-SPContentDatabase -Name P_Doctorat_Content -WebApplication http://doctorat.feaa.uaic.ro
Mount-SPContentDatabase -Name P_PortalFEAA_Content -WebApplication http://portal.feaa.uaic.ro

11. Convert-SPWebApplication –Identity http://id.feaa.uaic.ro –To Claims -RetainPermissions -Force

Convert-SPWebApplication –Identity http://doctorat.feaa.uaic.ro –To Claims -RetainPermissions -Force
Convert-SPWebApplication –Identity http://portal.feaa.uaic.ro –To Claims -RetainPermissions -Force

http://portal.feaa.uaic.ro/_layouts/siteupgradestatus.aspx

12. Integrarea cu Office Web App de pe alt server.

New-SPWOPIBinding -ServerName ca.feaa.uaic.ro

Resources:

Sper să vi se fi părut interesant!

Blog la WordPress.com.

SUS ↑