SharePoint 2010 Excel Services error: The workbook cannot be opened.

In our SharePoint 2010 portal, we have recieved an error "The workbook cannot be opened" when we try to open Excel files in web pages or when we try to use the Excel Web Access Web Parts.

The logs are not of much help as they donot contain details about the cause for the error.

Error info from SharePoint log:

Utils.ExecuteWebEventHandler: An exception occured Microsoft.Office.Excel.WebUI.ExcelWebRendererException: The workbook cannot be opened. ---> Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSessionException: The workbook cannot be opened. at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethodCore(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod) at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethod(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod) at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.OpenWorkbook(String u... e9b4f581-c314-4f43-b3e8-60257ead66a0


...rl, OpenWorkbookFlags flags, CommandParameter commandParameter, CultureCommandParameter cultureParameter, String subscriberId, OpenWorkbookOutFlags& outFlags, OpenWorkbookOutParameters& outParameters) at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ExcelServerLoadBalancer.<>c__DisplayClass3.b__2(ServerSession newServerSession) at Microsoft.Office.Excel.Server.CalculationServer.Proxy.LoadBalancer.ExecuteNewSessionMethod(CreateServerSessionMethod createServerSessionMethod, CoreNewSessionMethod newSessionMethod, String workbookUrl) at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ExcelServerLoadBalancer.OpenWorkbook(String url, OpenWorkbookFlags flags, CommandParameter commandParameter, CultureCommandParameter cultureParameter, String subscriberId, O... e9b4f581-c314-4f43-b3e8-60257ead66a0
...penWorkbookOutFlags& outFlags, OpenWorkbookOutParameters& outParameters, ServerSession& serverSession) at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ExcelServerSession.OpenWorkbook(String url, OpenWorkbookFlags flags, CommandParameter commandParameter, CultureCommandParameter cultureParameter, OpenWorkbookOutFlags& outFlags, OpenWorkbookOutParameters& outParameters, ServerSession& serverSession) at Microsoft.Office.Excel.WebUI.EwaState.OpenWorkbookAndUpdateState(String sessionId, CommandParameter commandParameter, OpenWorkbookFlags flags, String plainTextWorkbookUrl) at Microsoft.Office.Excel.WebUI.EwaState.OpenWorkbook() --- End of inner exception stack trace --- at Microsoft.Office.Excel.WebUI.EwaState.OpenWorkbook() at Microsoft.Office.Excel.WebUI.Util... e9b4f581-c314-4f43-b3e8-60257ead66a0
...s.ExecuteWebEventHandler(CoreWebEventHandler coreWebEventHandler, CoreWebEventHandler baseClassHandler, Object param, IMainControl mainControl) e9b4f581-c314-4f43-b3e8-60257ead66a0
InternalEwr.RenderWebPart: Finished rendering internal EWR e9b4f581-c314-4f43-b3e8-60257ead66a0

Resolution:

Finally the problem was resolved after deleting and creating Excel Services Application again in Cental Admin

Navigate to SharePoint 2010 Central Admin > Application Management
Under the Service Applications section, click the "Manage service applications" link


Select the Excel Services Application and delete it.

The confirmation screen looks like the one below:

Create a new Service Application by clicking on the "New" button:

Create a new Excel Service Application and choose Application Pool as required:

Do an IISRESET finally

The maximum number of Web Parts for all zones on this page has been exceeded

Error: The maximum number of Web Parts for all zones on this page has been exceeded

I came across this message today when I was working on a page that has more than 50 web parts on it.

Later after a bit of research I learnt that

• The limit is customizable in web.config file,
• 50 (default) - http://technet.microsoft.com/en-us/library/cc262787.aspx
• 100 (recommended max) - http://technet.microsoft.com/en-us/library/cc287743.aspx

 However, no documentation  has been found about

• The maximum number of web parts per web part zone
• The maximum number of web part zones per page

SharePoint 2010 - Table of Contents webpart 20 sites limit

Today while working with Table Of Contents Web Part in a 2010 site, I have noticed that the web part was not displaying all the sites.

From my research online, I have understood about the 50 item limit that SharePoint imposes. There are many posts explaining that issue such as this post

My case was different because the Table of Contents was showing only 20 sites.

Later I have found out that I need to change a setting in Site Settings of the site that contains all the sub sites that I wanted to show.

Navigate to Site Settings > Navigation

Change the value from 20 to [whatever number you need] in the "Current Navigation" section

After changing this, the site map started to show all the sites (in my case about 40)

Error: SharePoint folders that are created programatically are hidden.

I created few folders programatically from a SharePoint Workflow.

They have been created with no exceptions but I was unable to see them from the UI. Assuming that the folders are not created, I tried to created them through the UI. Then I got an error message "Folder already exists".

The above behaviour was with Custom Lists whereas these folders show up quite well in Document Libraries.

So I did a bit of research and learnt some thing about SharePoint folders.

There are few ways to create folders.

One of them is to use

SPWeb.Folders.Add(folderUrl);

The above problem was as a result of using this code.

Hers is another way to create folders in a list which works with both custom lists and document libraries (I have not tested the rest yet)

/// 

        /// Check whether the folder path exists. If it does not, build the folder structure
        /// 
        /// 
/// 
/// 
/// 
        private static String EnsureFolderStructure(SPWeb web, SPList list, String folderPath)
        {
            String folderUrl = String.Empty;
            try
            {
                if (!DoesFolderExist(web, folderPath))
                {
                    String partialFolderUrl = String.Empty;
                    String[] folderNames = folderPath.Split('/');
                    int ctr = 0;
                    SPListItem folder = null;
                    foreach (String folderName in folderNames)
                    {
                        partialFolderUrl += String.Format("/{0}", folderName);
                        folderUrl = String.Format("{0}/{1}{2}", list.ParentWeb.Url, list.RootFolder.Url, partialFolderUrl);
                        if (!DoesFolderExist(web, folderUrl))
                        {
                            //Folder does not exist. Create it
                            if (ctr == 0)
                                //Important: Leave the folderUrl blank if this folder is create in the root list.
                                folder = list.Items.Add("", SPFileSystemObjectType.Folder, folderName);
                            else
                                folder = list.Items.Add(folderUrl.Replace("/" + folderName, ""), SPFileSystemObjectType.Folder, folderName);
                            folder["Title"] = folderName;
                            folder.Update();

                            list.Update(true);
                            web.Update();
                        }
                        ctr++;
                    }
                }
                else
                {
                    folderUrl = String.Format("{0}/{1}/{2}", list.ParentWeb.Url, list.RootFolder.Url, folderPath);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return folderUrl;
        }
        /// 

        /// Check whether a folder exists
        /// 
        /// 
/// 
/// 
        public static Boolean DoesFolderExist(SPWeb web, String folderUrl)
        {
            try
            {
                return web.GetFolder(folderUrl).Exists;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

Hide the SharePoint 2010 ribbon in a list form

If you have a to disable / hide the SharePoint 2010 ribbon in list forms (such as New / Edit / Display forms), one of the easiest ways is to do it through CSS.

As SharePoint 2010 provides great flexibility with list forms, we can easily modify whatever is required in a particular form for a particular list.

Click "Edit List" icon in the ribbon and it will fire up SharePoint Designer. Open the form where the ribbon needs to be hidden, right click it and choose "Edit File in Avdanced Mode".

The below is div in which the ribbon resides.

<div id=”s4-ribbonrow” class=”s4-pr s4-ribbonrowhidetitle”>
... 
</div>

Now to hide the ribbon, add this to the page.

<style type="text/css">
#s4-ribbonrow{ display:none; }
</style>

Ribbon gone..

Creating/Modifying list forms in SharePoint 2010

SharePoint 2010 provides the ability for users to create and edit list forms (New, Edit etc..) easily.
When you click on "Edit List" option in the list ribbon, SharePoint Designer opens up and provides  us with options to add/edit views, forms and workflows.

In order to create a new form, click on the "New" button next to forms and you will see a dialog that allows you to provide a name for the form, the type of the form (New/Edit/Display). Also you can set the newly created form as the default form for the selected type. You can also select content type to use.

Create a new form, choose the fields that you require to show in the form and hit ok.

All set. The newly created form should appear when you attempt to create a list item. (If the newly created form is for new items)

Error: "ERROR_NO_RESULTS_FOUND" when you use Search.asmx with FullText SQL query

I have encountered this bug that really irritated me today.
I was using Search.asmx with FullText SQL query to get some results and then display them to users using XSL through JavaScript using jQuery.

SELECT LastName,Company,AccountName,UserProfile_GUID, JobTitle,Size, Rank, Path, Title, Description, Write FROM SCOPE() WHERE ("DAV:contentclass"='urn:content-class:SPSPeople') AND (LastName LIKE 'C%')

worked

whereas

SELECT LastName,Company,AccountName,UserProfile_GUID, JobTitle,Size, Rank, Path, Title, Description, Write FROM SCOPE() WHERE ("DAV:contentclass"='urn:content-class:SPSPeople') AND (Company LIKE 'C%') 

did not work and it threw an error "ERROR_NO_RESULTS_FOUND"

After many trials and errors and a bit of research, it turned out to be a Microsoft bug.

"like statement will only work on a string shorter than 64 characters, above that it will fail gracefully leaving you with unexpected results..
"

The work around for my problem was to use CONTAINS as opposed to LIKE.
Please notice that the "company Name" is actually embedded within single quotes as '"Company Name"'.
Otherwise the Search service does not understand that it needs to look for the whole phrase.

SELECT LastName,Company,AccountName,UserProfile_GUID, JobTitle,Size, Rank, Path, Title, Description, Write FROM SCOPE() WHERE ("DAV:contentclass"='urn:content-class:SPSPeople') AND (CONTAINS(Company,'"Company Name"'))

Ref: http://social.microsoft.com/Forums/en-US/Offtopic/thread/12ea7757-7ca7-42b8-9ed2-59acd00d283d