Excel Services Fix – "The workbook cannot be opened".

Error:

When I tried to open any excel file in SharePoint, I was facing below issue: Excel Services – "The workbook cannot be opened".

Cause:

After spending some time on checking configuration settings of Excel Service Application, I found root cause of the issue was permission to Service Account under which Excel Service is running. It does not have enough permission to Content Database of Web Application.

Resolution:

To fix the issue, follow below steps and execute PowerShell Commands.

1. Click Start, click All Programs.

2. Click Microsoft SharePoint 2010/2013 Products.

3. Click SharePoint 2010/2013 Management Shell.

4. At the Windows PowerShell command prompt (PS C:\>), type the following command, and then press ENTER:

$webApp = Get-SPWebApplication –Identity <URL of the Web application>

$webApp.GrantAccessToProcessIdentity(“<insert service account>”)

When you run these commands SharePoint does following actions:

1. Add Service Account user to Content Database of the Web Application. 

(Security > Users).

2. Assign "SPDataAccess" membership role to Service Account user. (Account > Membership)

3. Assign full control permission to the Service Account user on the Web Application.

You can get the “Service Account” details for Excel Service Application from Central Administration. Please follow below step:
1. Go to Central Administration > Security > Configure Service Accounts >
2. Select the application pool “Excel Services Application”.
3. You will find associated Service Account with application in other dropdown.

Reference: http://support.microsoft.com/kb/981293

If you have any questions you can reach out our SharePoint Consulting team here.

Add Site Column to multiple Content Types using PowerShell

Scenario
In large SharePoint Environment, we have hundreds of Site Content Types in site collection. And the requirement was to add new site column to all site content types of specific group.

Approach
Yes, first thing comes to mind is, lets add it to content type one by one through GUI. But it is not feasible approach while dealing with hundreds of content types as it becomes very tedious task.

So, I have decided to implement PowerShell script through which it can be added to all content types of specific group at once.

Code:

$site = Get-SPSite "<Site Collection Url>"
$web = $site.RootWeb

foreach ($ctype in $web.ContentTypes)
{
    if($ctype.Group -eq "<Site Content Type Group Name>")
    {     
        foreach($field in $web.Fields)
        {
            if($field.Group -eq "<Site Column Group Name>")
            {
                if($field.Title -eq "<Site Column Name>")
                {
                    if($ctype.Fields.ContainsField($field) -eq $false)
                    {
                        $link = new-object Microsoft.SharePoint.SPFieldLink $field
                        $ctype.FieldLinks.Add($link)
                        $ctype.Update($true)
                    }
                }
            }
        }      
    }
}

That's it, Done!!!

If you have any questions you can reach out our SharePoint Consulting team here.

SharePoint List View: Filter by Me and My SharePoint Groups

Scenario 
I have a SharePoint List containing task items. Tasks are assigned to a specific SharePoint Group or individual user. Each group has its own members. Requirement is, if current user is member of any of those groups, can see assigned tasks under My Tasks View.

Approach
There are few possible solutions, one of them is to modify Query filter via SharePoint Designer.

Filtering a list view by group membership
Step: 1
Open SharePoint designer then open site.  Select the Task List under Lists and Libraries. (For example, here we have selected "ResourceDocumentTypes").

Step: 2

When you select task list you will see a panel on right side named "Views". Select the view that needs to be modified. (For this example it’s "My Events")

Step: 3
Note: If you cannot edit file, you must select advanced mode.

In source view, search for <Query> and you will see View Query. If you have already modified view as filtered by [Me]. In that case, you need to modify the query which contains

<Query><Where><Eq><FieldRef Name="AssignedTo"/><Value Type="Integer"><UserID 
Type="Integer"/></Value></Eq></Where></Query>

With below code:

<Query>
<Where>
<Or>
<Membership Type="CurrentUserGroups"><FieldRef Name=" AssignedTo"/>
<Membership>
<Eq>
<FieldRef Name="AssignedTo"/><Value Type="Integer"><UserID Type="Integer"/></Value>
</Eq>
</Or>
</Where>
</Query>

Be sure to change the "FieldRef Name" to your own field, and click Save. Done!

Now, User can see all task items where “AssignedTo” field contains a SharePoint Group that logged in user is a member of and also assigned to him/her directly.

Results
Here is a list view showing all tasks assigned to different SharePoint Groups:

Below is filtered list view showing only tasks assigned to SharePoint Groups for which current user is a member.

If you have any questions you can reach out our SharePoint Consulting team here.

Office UI Fabric

Why Office UI Fabric?

It's the frontend toolkit for building UI of custom application same as Office and Office 365 products.

• Microsoft uses Fabric UI throughout Office to create functional and engaging user interfaces. 
• Now that Fabric UI is open source, we can find it on GitHub and apply the same Office Design Language to our own web applications.
• With Fabric UI, we can create an engaging customer experience and save development time. 
• Office UI Fabric is a responsive, mobile-first, front-end framework for developers, designed to make it simple to quickly create web experiences using the Office Design Language. 
• The framework is used internally on products within Office 365 — such as suite branding, OneDrive.com, Outlook.com, Delve and the Video Portal.

• With Office UI Fabric, you can apply simple CSS styles to make your web applications having same look and feel as Office products.The styling takes into account of typography, color, icons, animations, responsive grid layouts and localization.

• Typography: Typography includes CSS classes that you can use to implement the same text that is used across Office.
• Color: Use Office theme colors, neutral colors (different shades of gray), brand colors for different products & accent colors. All of these come in background, text, border and different shades.
• Icons: If you are familiar with Font Awesome, the Office UI Fabric includes icons that are specific to Office, including product logo.
• Animations: Want to mimic the same animations Office uses? just add a CSS class!
• Responsive grid: It follows the popular 12-grid format.
• Localization: It includes right-to-left support for those languages, such as Hebrew & Arabic, as well as language optimized fonts.

• There are many Components available of Office UI Fabric. These Components include suggested HTML, CSS, and JavaScript where necessary to show the intended behavior:
  Buttons, Drop-down lists, Radio buttons, Check boxes, Form fields, Search boxes, Toggle switches, Text fields, Lists, Dialogs (with overlay), Callouts, Navigation bars, Pivots, Personas, Tables.

Sample Source Code:

• Follow below steps and use below HTML & CSS code to include "To Do List" section in SharePoint Page.
• Add a reference to fabric.css in the <head> of your page. Make sure the path is correct.
• If you're using components, add a reference to fabric.components.css after the fabric.css reference.
• Add below HTML code in HTML file and css code in separate .css file and upload both files in Site Assets folder.
• Add Content Editor web-part on page and set the HTML file url in Content Editor link.

Html code

<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Fabric UI Demo</title>
<!--You can also download following css files from GitHub--> <link rel="stylesheet" href="https://appsforoffice.microsoft.com/fabric/2.2.0/fabric.min.css"> <link rel="stylesheet" href="https://appsforoffice.microsoft.com/fabric/2.2.0/fabric.components.min.css"> <link href="http://brvm159/sites/abc/fd/SiteAssets/Democs.css" type="text/css" rel="stylesheet" /> </head> <body> <div class="ms-TextField"> <input class="ms-TextField-field"> <span class="ms-TextField-description">This should be your first and last name.</span> </div> <div class="ms-Grid-col ms-u-sm6 ms-u-md4 ms-u-lg2"> <button class="ms-Button"> <span class="ms-Button-icon"><i class="ms-Icon ms-Icon--plus"></i></span> <span class="ms-Button-label">Add ToDo</span> <span class="ms-Button-description">Description of the action this button takes</span> </button> </div> <div class="ListHeader ms-font-xxl ms-fontWeight-light">Todo List</div> <div class="TodoList"> <ul class="ms-List"> <div class="ms-ListItem is-unread is-selectable is-selected"> <span class="ms-ListItem-primaryText">Jacob</span> <span class="ms-ListItem-secondaryText">Meeting notes</span> <span class="ms-ListItem-tertiaryText">Today we discussed the importance of SSIS in regards to SharePoint.</span> <span class="ms-ListItem-metaText">2:42p</span> <div class="ms-ListItem-selectionTarget"></div> <div class="ms-ListItem-actions"> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--mail"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--trash"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--flag"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--pinLeft"></i></div> </div> </div> <div class="ms-ListItem is-unread is-selectable"> <span class="ms-ListItem-primaryText">Bill</span> <span class="ms-ListItem-secondaryText">Meeting notes</span> <span class="ms-ListItem-tertiaryText">Today we discussed the importance of SSRS in regards to SharePoint.</span> <span class="ms-ListItem-metaText">2:42p</span> <div class="ms-ListItem-selectionTarget"></div> <div class="ms-ListItem-actions"> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--mail"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--trash"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--flag"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--pinLeft"></i></div> </div> </div> <div class="ms-ListItem is-selectable"> <span class="ms-ListItem-primaryText">Brad</span> <span class="ms-ListItem-secondaryText">Meeting notes</span> <span class="ms-ListItem-tertiaryText">Today we discussed the importance of Fabric UI in regards to SharePoint Add-In.</span> <span class="ms-ListItem-metaText">2:42p</span> <div class="ms-ListItem-selectionTarget"></div> <div class="ms-ListItem-actions"> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--mail"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--trash"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--flag"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--pinLeft"></i></div> </div> </div> <div class="ms-ListItem is-selectable"> <span class="ms-ListItem-primaryText">Andy</span> <span class="ms-ListItem-secondaryText">Meeting notes</span> <span class="ms-ListItem-tertiaryText">Today we discussed the importance of Materilize Framework.</span> <span class="ms-ListItem-metaText">2:42p</span> <div class="ms-ListItem-selectionTarget"></div> <div class="ms-ListItem-actions"> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--mail"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--trash"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--flag"></i></div> <div class="ms-ListItem-action"><i class="ms-Icon ms-Icon--pinLeft"></i></div> </div> </div> </ul> </div> </body> </html>

CSS Code

html,

body {

 margin: 0;

}

.TodoBar {

 position: fixed; /* This bar should always be fixed to the top */

 top: 0;

 left: 0;

 right: 0;

 height: 60px;

 z-index: 2;

}

.TodoList {

 width: 100%;

 height: auto;

 z-index: 1;

 min-height: 200px;

}

.ListHeader {

 width: 100%;

 height: auto;

 margin-top: 60px;

 padding-top: 10px;

 padding-bottom: 10px;

 text-align: center;

}

Pros and Cons

Pros

• Can have same look and feel as Office products.
• Integrates with other frameworks, like Bootstrap. 
• Built with LESS for powerful customization. 
• Uniquely named classes to prevent conflicts. 
• All about styling instead of JavaScript, so you can focus on your look and feel. 
• Language support (including right-to-left behavior) to simplify localization.

Cons

• Not as responsive as bootstrap.
• Documentation on GitHub for the Office UI Fabric is not nearly as complete as for Bootstrap.

Alternatives of Fabric UI ?

Yes. Bootstrap, Material Design, Foundation, Semantic-UI etc.

There are many front end CSS frameworks to choose and the selection greatly depends on the requirements of the project.

If you have any questions you can reach out our SharePoint Consulting team here.