Excel Services - The workbook cannot be opened

After embedding excel file via Excel Web Access webpart, an error "The workbook cannot be opened." may occur if the service account running the Excel Service Application has not been granted access to the SharePoint web application yet properly.

The powershell script below will handle permission to SharePoint Web Application for the service account.

# Service Application that runs Excel Service Application is "Contoso\SPAppPool"
#
$web = Get-SPWebApplication -Identity "https://sp.contoso.edu"
$web.GrantAccessToProcessIdentity("Contoso\SPAppPool")

In fact, what happens as a result of executing this script at the database level is that

1. The Excel Service Account (Contoso\SPAppPool) is granted  custom database role, "SPDataAccess", which basically provides READ access to all SharePoint objects.
2. If there are multiple content databases under https://sp.contoso.edu Web Application, then the database role, "SPDataAccess" will be granted to the Service Account on all content databases.
   
   

Office Web Apps Server for SharePoint 2013

Here's a quick reminder about Office Web Apps for SharePoint 2013 (detail link)

There are basically three (3) steps to set up Office Web Apps.

• Step 1: Install pre-requisites
• Step 2: Install Office Web Apps and configure it
• Step 3: Have SharePoint 2013 server join Office Web Apps Server.

Below is Powershelll script to set up a Office Web Apps Server in a dev SharePoint environment. Office Web Apps server is configured as a single server with HTTPS.

Step 1: Install pre-requisites via PowerShell:

Add-WindowsFeature Web-Server,Web-Mgmt-Tools,Web-Mgmt-Console,Web-WebServer,Web-Common-Http,Web-Default-Doc,Web-Static-Content,Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Security,Web-Filtering,Web-Windows-Auth,Web-App-Dev,Web-Net-Ext45,Web-Asp-Net45,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Includes,InkandHandwritingServices,NET-Framework-Features,NET-Framework-Core,NET-HTTP-Activation,NET-Non-HTTP-Activ,NET-WCF-HTTP-Activation45

Step 2: Install Office Web Apps Server 2013 and Configure it via PowerShell.

# Install Office Web Apps Servers.
# Import SSL certificate via IIS
# (Script below uses an wildcard SSL with friendly name "*.contoso.edu")
# Then run the following on the Office Web Apps Server.
# "SQLWopi.contoso.edu" is the FQDN of the Office Web Server
# -ExternalUrl is only relevant if the server will be exposed to
# public-facing internet)
#
New-OfficeWebAppsFarm -InternalUrl "https://SQLWopi.contoso.edu" -ExternalUrl "https://SQLWopi.contoso.edu" -CertificateName "*.contoso.edu" -EditingEnabled

Step 3: Connect SharePoint 2013 server to Office Web Apps Server

# Run the following Powershell script on the SharePoint 2013 server.
#
New-SPWOPIBinding -ServerName "SQLWopi.contoso.edu"

Wrap Text in PRE tag

In order to wrap text in a <pre> tag, one can use the following:

<style>
  pre {
      white-space: pre-wrap;       /* Since CSS 2.1 */
      white-space: -moz-pre-wrap;  /* Mozilla, since 1999 */
      white-space: -pre-wrap;      /* Opera 4-6 */
      white-space: -o-pre-wrap;    /* Opera 7 */
      word-wrap: break-word;       /* Internet Explorer 5.5+ */
  }
</style>

Copy SharePoint List Items To Another Identical List

The following Powershell script could be useful while importing data into existing list. If such need arises, one can import data from spreadsheet into a new list and then copy its list items to the destination list.

Keep in mind though: ReadOnly fields, such as [CreatedDateTime] or [LastUpdatedDateTime] field, cannot be manipulated through the script below.

try
{
    $web = Get-SPWeb "http://site"

    $sList = $web.Lists["Movies2"]  #source-list
    $dList = $web.Lists["Movies"]   #destination-list

    Write-Host "Working on Web: "$web.Title -ForegroundColor Green

    if($sList)
    {
        Write-Host "    Working on List: " sList.Name -ForegroundColor Cyan

        $spSourceItems = $sList.Items
        $sourceSPFieldCollection = $sList.Fields

        
        foreach($item in $spSourceItems)
        {
            if($dList)
            {
                $newSPListItem = $dList.AddItem()

                #Copy all field data except Attachments
                foreach($spField in $sourceSPFieldCollection)
                {
                    if($spField.ReadOnlyField -ne $True -and $spField.InternalName -ne "Attachments")                   
                    {
                        $newSPListItem[$($spField.InternalName)] = $item[$($spField.InternalName)]
                    }
                }

                #Copy Attachments
                foreach($leafName in $item.Attachments)
                {
                    $spFile = $sList.ParentWeb.GetFile($($item.Attachments.UrlPrefix + $leafName))
                    $newSPListItem.Attachments.Add($leafName, $spFile.OpenBinary())
                }

                #Update new LisItem
                $newSPListItem.Update()
            }
            Write-Host "        Copying $($item["Name"]) completed"
        }
       
    }
    
}
catch
{
    Write-Host "Error: " $_.Exception.ToString() -ForegroundColor Red
}

ADQuery.ReadOnly (Active Directory Query Utility)

using System;
using System.Collections;
using System.Data;
using System.DirectoryServices;
using System.DirectoryServices.ActiveDirectory;
namespace ADQuery
{
 public class ReadOnly
 {

  public enum DNObjectClass
  {
   user,
   group,
   computer
  }

  public enum DNReturnType
  {
   distinguishedName,
   ObjectGuid
  }

  public static string FriendlyDomainToLdapDomain(string friendlyDomainName)
  {
   string result = null;
   try
   {
    DirectoryContext context = new DirectoryContext(
      DirectoryContextType.Domain, friendlyDomainName);
    Domain domain = Domain.GetDomain(context);
    result = domain.Name;
   }
   catch (DirectoryServicesCOMException ex)
   {
    result = ex.Message.ToString();
   }
   return result;
  }

  public static ArrayList EnumerateDomains()
  {
   ArrayList arrayList = new ArrayList();
   Forest currentForest = Forest.GetCurrentForest();
   DomainCollection domains = currentForest.Domains;
   foreach (Domain domain in domains)
   {
    arrayList.Add(domain.Name);
   }
   return arrayList;
  }

  public static ArrayList EnumerateCatalogs()
  {
   ArrayList arrayList = new ArrayList();
   Forest currentForest = Forest.GetCurrentForest();
   foreach (GlobalCatalog globalCatalog in currentForest.GlobalCatalogs)
   {
    arrayList.Add(globalCatalog.Name);
   }
   return arrayList;
  }

  public static ArrayList EnumerateDomainControllers()
  {
   ArrayList arrayList = new ArrayList();
   Domain currentDomain = Domain.GetCurrentDomain();
   foreach (DomainController domainController in currentDomain.DomainControllers)
   {
    arrayList.Add(domainController.Name);
   }
   return arrayList;
  }

  public ArrayList EnumerateOU(string OuDn)
  {
   ArrayList arrayList = new ArrayList();
   try
   {
    DirectoryEntry directoryEntry = new DirectoryEntry("LDAP://" + OuDn);
    foreach (DirectoryEntry directoryEntry2 in directoryEntry.Children)
    {
     string text = directoryEntry2.Path.ToString();
     arrayList.Add(text.Remove(0, 7));
     directoryEntry2.Close();
     directoryEntry2.Dispose();
    }
    directoryEntry.Close();
    directoryEntry.Dispose();
   }
   catch (DirectoryServicesCOMException ex)
   {
    arrayList.Add("An Error Occurred: " + ex.Message.ToString());
   }
   return arrayList;
  }

  public static bool Exists(string objectPath)
  {
   bool result = false;
   if (DirectoryEntry.Exists("LDAP://" + objectPath))
   {
    result = true;
   }
   return result;
  }

  public ArrayList AttributeValuesMultiString(
    string attributeName, string objectDn, ArrayList valuesCollection, bool recursive)
  {
   DirectoryEntry directoryEntry = new DirectoryEntry(objectDn);
   PropertyValueCollection propertyValueCollection = directoryEntry.Properties[attributeName];
   IEnumerator enumerator = propertyValueCollection.GetEnumerator();
   while (enumerator.MoveNext())
   {
    if (enumerator.Current != null)
    {
     if (!valuesCollection.Contains(enumerator.Current.ToString()))
     {
      valuesCollection.Add(enumerator.Current.ToString());
      if (recursive)
      {
       this.AttributeValuesMultiString(attributeName, 
         "LDAP://" + enumerator.Current.ToString(), valuesCollection, true);
      }
     }
    }
   }
   directoryEntry.Close();
   directoryEntry.Dispose();
   return valuesCollection;
  }

  public string AttributeValuesSingleString(string attributeName, string objectDn)
  {
   DirectoryEntry directoryEntry = new DirectoryEntry(objectDn);
   string result = directoryEntry.Properties[attributeName].Value.ToString();
   directoryEntry.Close();
   directoryEntry.Dispose();
   return result;
  }

  public static ArrayList GetUsedAttributes(string objectDn)
  {
   DirectoryEntry directoryEntry = new DirectoryEntry("LDAP://" + objectDn);
   ArrayList arrayList = new ArrayList();
   foreach (string text in directoryEntry.Properties.PropertyNames)
   {
    string text2 = directoryEntry.Properties[text].Value.GetType().ToString();
    string text3 = string.Empty;
    if (!directoryEntry.Properties[text].Value.GetType().IsArray)
    {
     text3 = directoryEntry.Properties[text].Value.ToString();
    }
    if (text == "objectGUID")
    {
     Guid guid = new Guid((byte[])directoryEntry.Properties[text].Value);
     text3 = guid.ToString();
     text3 = text3 + "(" + directoryEntry.Guid.ToString() + ")";
    }
    if (text == "objectSID")
    {
     Guid guid = new Guid((byte[])directoryEntry.Properties[text].Value);
     text3 = guid.ToString();
     text3 = text3 + "(" + directoryEntry.Guid.ToString() + ")";
    }
    if (text == "memberOf")
    {
     ArrayList arrayList2 = new ArrayList((object[])directoryEntry.Properties[text].Value);
     ReadOnly readOnly = new ReadOnly();
     foreach (object current in arrayList2)
     {
      string text4 = (string)current;
      text3 = text3 + "" + text4 + "";
      text3 = text3 + " (objectGuid = " + readOnly.ConvertDnToGuid(text4) + ")";
     }
    }
    if (text == "member")
    {
     ArrayList arrayList2 = new ArrayList((object[])directoryEntry.Properties[text].Value);
     ReadOnly readOnly = new ReadOnly();
     foreach (object current in arrayList2)
     {
      string text4 = (string)current;
      text3 = text3 + "" + text4 + "";
      text3 = text3 + " (objectGuid = " + readOnly.ConvertDnToGuid(text4) + ")";
     }
    }
    arrayList.Add(string.Concat(new string[]
    {
     text,
     ", type = ",
     text2,
     ", value = ",
     text3,
     ""    
     }));
   }
   return arrayList;
  }

  public DataTable GetADUserAttributes(string objectDN)
  {
   DataTable result;
   if (!DirectoryEntry.Exists("LDAP://" + objectDN))
   {
    result = null;
   }
   else
   {
    try
    {
     DirectoryEntry directoryEntry = new DirectoryEntry("LDAP://" + objectDN);
     SearchResult searchResult = new DirectorySearcher(directoryEntry)
     {
      Filter = "(&(objectCategory=Person)(objectClass=user)))",
      SearchScope = SearchScope.Subtree
     }.FindOne();
     DataTable dataTable = new DataTable();
     dataTable.Columns.Add("CN");
     dataTable.Columns.Add("displayName");
     dataTable.Columns.Add("employeeID");
     dataTable.Columns.Add("givenName");
     dataTable.Columns.Add("sn");
     dataTable.Columns.Add("sAMAccountName");
     dataTable.Columns.Add("sAMAccountType");
     dataTable.Columns.Add("telephoneNumber");
     dataTable.Columns.Add("UserAccountControl");
     dataTable.Columns.Add("UserPrincipalName");
     dataTable.Columns.Add("extensionAttribute1");
     dataTable.Columns.Add("primaryGroupID");
     dataTable.Columns.Add("mail");
     dataTable.Columns.Add("whenChanged");
     dataTable.Columns.Add("whenCreated");
     dataTable.Columns.Add("ObjectGUID");
     DataRow dataRow = dataTable.NewRow();
     directoryEntry.Close();
     result = dataTable;
    }
    catch
    {
     throw new DirectoryServicesCOMException("There was an error searching for user in AD");
    }
   }
   return result;
  }

  public string GetDistinguishedName(string sAMAccountName, string LdapDomain)
  {
   string result = string.Empty;
   string text = "LDAP://" + LdapDomain;
   DirectoryEntry directoryEntry = new DirectoryEntry(text);
   DirectorySearcher directorySearcher = new DirectorySearcher(directoryEntry);
   directorySearcher.Filter = "(&(objectClass=user)(sAMAccountName=" + sAMAccountName + "))";
   SearchResult searchResult = directorySearcher.FindOne();
   if (searchResult == null)
   {
    throw new DirectoryServicesCOMException(string.Concat(new string[]
    {
     "Cannot locate distinguishedName for ",
     sAMAccountName,
     " in ",
     text,
     "."
    }));
   }
   DirectoryEntry directoryEntry2 = searchResult.GetDirectoryEntry();
   result = "LDAP://" + directoryEntry2.Properties["distinguishedName"].Value;
   directoryEntry.Close();
   directoryEntry.Dispose();
   directorySearcher.Dispose();
   return result;
  }

  public string GetObjectDistinguishedName(
    ReadOnly.DNObjectClass dnObjectClass, ReadOnly.DNReturnType dnReturnType, 
    string objectName, string LdapDomain)
  {
   string result = string.Empty;
   string path = "LDAP://" + LdapDomain;
   DirectoryEntry directoryEntry = new DirectoryEntry(path);
   DirectorySearcher directorySearcher = new DirectorySearcher(directoryEntry);
   switch (dnObjectClass)
   {
   case ReadOnly.DNObjectClass.user:
    directorySearcher.Filter = string.Concat(new string[]
    {
     "(&(objectClass=user)(|(cn=",
     objectName,
     ")(sAMAccountName=",
     objectName,
     ")))"
    });
    break;
   case ReadOnly.DNObjectClass.group:
    directorySearcher.Filter = string.Concat(new string[]
    {
     "(&(objectClass=group)(|(cn=",
     objectName,
     ")(dn=",
     objectName,
     ")))"
    });
    break;
   case ReadOnly.DNObjectClass.computer:
    directorySearcher.Filter = string.Concat(new string[]
    {
     "(&(objectClass=computer)(|(cn=",
     objectName,
     ")(dn=",
     objectName,
     ")))"
    });
    break;
   }
   SearchResult searchResult = directorySearcher.FindOne();
   if (searchResult == null)
   {
    throw new NullReferenceException(string.Concat(new string[]
    {
     "unable to locate the distinguishedName for the object ",
     objectName,
     " in the ",
     LdapDomain,
     " domain"
    }));
   }
   DirectoryEntry directoryEntry2 = searchResult.GetDirectoryEntry();
   switch (dnReturnType)
   {
   case ReadOnly.DNReturnType.distinguishedName:
    result = "LDAP://" + directoryEntry2.Properties["distinguishedName"].Value;
    break;
   case ReadOnly.DNReturnType.ObjectGuid:
    result = directoryEntry2.Guid.ToString();
    break;
   }
   directoryEntry.Close();
   directoryEntry.Dispose();
   directorySearcher.Dispose();
   return result;
  }

  public string ConvertDnToGuid(string objectDN)
  {
   string result = string.Empty;
   if (DirectoryEntry.Exists("LDAP://" + objectDN))
   {
    DirectoryEntry directoryEntry = new DirectoryEntry("LDAP://" + objectDN);
    result = directoryEntry.Guid.ToString();
    directoryEntry.Close();
    directoryEntry.Dispose();
   }
   return result;
  }

  public static string ConvertGuidToOctetString(string objectGuid)
  {
   Guid guid = new Guid(objectGuid);
   byte[] array = guid.ToByteArray();
   string text = "";
   byte[] array2 = array;
   for (int i = 0; i < array2.Length; i++)
   {
    byte b = array2[i];
    text = text + "\\" + b.ToString("x2");
   }
   return text;
  }

  public ArrayList LogonUserGroups()
  {
   return null;
  }

  public static string ConvertGuidToDn(string guid)
  {
   DirectoryEntry directoryEntry = new DirectoryEntry();
   string nativeGuid = directoryEntry.NativeGuid;
   directoryEntry.Close();
   directoryEntry.Dispose();
   return null;
  }
 }
}

Turn on or off SharePoint Developer Dashboard

SharePoint developer dashboard can be turned on or off via Powershell script as follows
Make sure that "Usage and Health Data Collection" service application has started first under Manage Service Applications.

Add-PSSnapin "Microsoft.SharePoint.PowerShell"
$contentService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService
$dashboard = $contentService.DeveloperDashboardSettings
$dashboard.DisplayLevel = "On"  # "Off" will turn off dashboard
$dashboard.Update()

After the dashboard is turned on, you will see a perfmon icon in the right upper corner of SharePoint site next to "SHARE", "FOLLOW", 'EDIT" menu items.

***In case the "Usage and Health Data Collection Proxy" has not started, use the following Powershell to start it.

$SAP = Get-SPServiceApplicationProxy | 
 where-object { $_.TypeName -eq "Usage and Health Data Collection Proxy" }