A friend of mine Erdem Avni Avni SELÇUK from Turkey showed a video sample which retrieves SharePoint Online list column information in Excel! Truly amazing stuff for SharePoint IT Professionals at least for me. So, thought of Sharing my demo code which simply lists all SharePoint list title and includes a header text and footer with image in word document. This is very ugly code but may give some dice for IT Pro’s for customizing and building the solution as desired for content managers.
This is our output – Modified the image so header and footer will be visible
Step 1: Create a a Office VSTO office 2013 / 2016 addin solution using Visual Studio – Start From here
Step 2: Add a new item to the solution and choose Ribbon – it’s an xml file! – Start From here
Step 3: Install CSOM SDK assemblies 🙂 🙂 🙂 – Below is the sample code
using (ClientContext context = new ClientContext("https://tenant.sharepoint.com"))
{
SecureString passWord = new SecureString();
foreach (char c in "P@ssword!*".ToCharArray()) passWord.AppendChar(c);
context.Credentials = new SharePointOnlineCredentials("admin@tenant.onmicrosoft.com", passWord);
ListCollection oList = context.Web.Lists;
context.Load(oList);
context.ExecuteQuery();
foreach(List list in oList)
{
Word.Range currrenttange = Globals.ThisAddIn.Application.Selection.Range;
currrenttange.Text = string.Concat(list.Title, Environment.NewLine);
}
foreach(Word.Section section in Globals.ThisAddIn.Application.Selection.Sections)
{
Word.Range headerrange = section.Headers[Word.WdHeaderFooterIndex.wdHeaderFooterPrimary].Range;
headerrange.Text = "List from SharePoint Site";
headerrange.Font.Color = Word.WdColor.wdColorDarkRed;
}
foreach(Word.Section section in Globals.ThisAddIn.Application.Selection.Sections)
{
Word.HeaderFooter footer = section.Footers[Word.WdHeaderFooterIndex.wdHeaderFooterPrimary];
footer.Shapes.AddPicture("https://tenant.sharepoint.com/MyCDN/2.png");
}
}
Note: I haven’t modified anything in XML file – For a demo I left as it is! I tested in MS word 2016. Once the Ribbon item is added you can use the below code as a sample by changing the URL, admin ID and password
using System;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Security;
using Office = Microsoft.Office.Core;
using Microsoft.SharePoint.Client;
using Microsoft.Online.SharePoint.TenantAdministration;
using Word = Microsoft.Office.Interop.Word;
// TODO: Follow these steps to enable the Ribbon (XML) item:
// 1: Copy the following code block into the ThisAddin, ThisWorkbook, or ThisDocument class.
// protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
// {
// return new Ribbon1();
// }
// 2. Create callback methods in the "Ribbon Callbacks" region of this class to handle user
// actions, such as clicking a button. Note: if you have exported this Ribbon from the Ribbon designer,
// move your code from the event handlers to the callback methods and modify the code to work with the
// Ribbon extensibility (RibbonX) programming model.
// 3. Assign attributes to the control tags in the Ribbon XML file to identify the appropriate callback methods in your code.
// For more information, see the Ribbon XML documentation in the Visual Studio Tools for Office Help.
namespace SPOAddin
{
[ComVisible(true)]
public class Ribbon1 : Office.IRibbonExtensibility
{
private Office.IRibbonUI ribbon;
public Ribbon1()
{
}
public void OnTextButton(Office.IRibbonControl control)
{
using (ClientContext context = new ClientContext("https://tenant.sharepoint.com"))
{
SecureString passWord = new SecureString();
foreach (char c in "Password!*".ToCharArray()) passWord.AppendChar(c);
context.Credentials = new SharePointOnlineCredentials("admin@tenant.onmicrosoft.com", passWord);
ListCollection oList = context.Web.Lists;
context.Load(oList);
context.ExecuteQuery();
foreach(List list in oList)
{
Word.Range currrenttange = Globals.ThisAddIn.Application.Selection.Range;
currrenttange.Text = string.Concat(list.Title, Environment.NewLine);
}
foreach(Word.Section section in Globals.ThisAddIn.Application.Selection.Sections)
{
Word.Range headerrange = section.Headers[Word.WdHeaderFooterIndex.wdHeaderFooterPrimary].Range;
headerrange.Text = "List from SharePoint Site";
headerrange.Font.Color = Word.WdColor.wdColorDarkRed;
}
foreach(Word.Section section in Globals.ThisAddIn.Application.Selection.Sections)
{
Word.HeaderFooter footer = section.Footers[Word.WdHeaderFooterIndex.wdHeaderFooterPrimary];
footer.Shapes.AddPicture("https://tenant.sharepoint.com/MyCDN/2.png");
}
}
}
public void OnTableButton(Office.IRibbonControl control)
{
object missing = System.Type.Missing;
Word.Range currentRange = Globals.ThisAddIn.Application.Selection.Range;
Word.Table newTable = Globals.ThisAddIn.Application.ActiveDocument.Tables.Add(
currentRange, 3, 4, ref missing, ref missing);
// Get all of the borders except for the diagonal borders.
Word.Border[] borders = new Word.Border[6];
borders[0] = newTable.Borders[Word.WdBorderType.wdBorderLeft];
borders[1] = newTable.Borders[Word.WdBorderType.wdBorderRight];
borders[2] = newTable.Borders[Word.WdBorderType.wdBorderTop];
borders[3] = newTable.Borders[Word.WdBorderType.wdBorderBottom];
borders[4] = newTable.Borders[Word.WdBorderType.wdBorderHorizontal];
borders[5] = newTable.Borders[Word.WdBorderType.wdBorderVertical];
// Format each of the borders.
foreach (Word.Border border in borders)
{
border.LineStyle = Word.WdLineStyle.wdLineStyleSingle;
border.Color = Word.WdColor.wdColorBlue;
}
}
#region IRibbonExtensibility Members
public string GetCustomUI(string ribbonID)
{
return GetResourceText("SPOAddin.Ribbon1.xml");
}
#endregion
#region Ribbon Callbacks
//Create callback methods here. For more information about adding callback methods, visit http://go.microsoft.com/fwlink/?LinkID=271226
public void Ribbon_Load(Office.IRibbonUI ribbonUI)
{
this.ribbon = ribbonUI;
}
#endregion
#region Helpers
private static string GetResourceText(string resourceName)
{
Assembly asm = Assembly.GetExecutingAssembly();
string[] resourceNames = asm.GetManifestResourceNames();
for (int i = 0; i < resourceNames.Length; ++i)
{
if (string.Compare(resourceName, resourceNames[i], StringComparison.OrdinalIgnoreCase) == 0)
{
using (StreamReader resourceReader = new StreamReader(asm.GetManifestResourceStream(resourceNames[i])))
{
if (resourceReader != null)
{
return resourceReader.ReadToEnd();
}
}
}
}
return null;
}
#endregion
}
}
Video Output