Sharepoint Server

Sharepoint 2010 has a feature of connecting an Excel workbook
Whenever an item is added/updated/deleted in SharPoint list the record is also added/updated/deleted in the connected Excel workbook
In this example we will see how to connect an Excel workbook to SharePoint list

Initially I have created a simple SharePoint custom list with few records in that, the list contains four columns Title (default column), Employee Name (single line text), Designation (choice filed), Department (choice field)
(See in Figure 1)

Figure 1 - Simple SharePoint Custom List

Now, we are ready to connect, click on List then click Export to Excel option (see Figure 2)

Figure 2 - Click on Export to Excel

A file download window pops, click on Save (see Figure 3)

Figure 3 - File Download Window, Click Save

Save the file in some location (in this example I have saved in E:\Employee location) (see Figure 4)

Figure 4 - Saving Excel Web Query File

Then open the saved Excel Web Query file from the location where you saved or click on the Open button in download dialog window (see Figure 5)

Figure 5 - Open the downloaded file

While the Excel Web Query File is opening you can view the Microsoft Excel Security Notice (the dialog window says warning about the data connection) click on Enable (see Figure 6)

Figure 6 - Enable Data Connections

As soon as you click on Enable button you can view the SharePoint list data in the Excel Web Query File (see Figure 7)

Figure 7 - Excel Web Query File, Shows SharePoint List Data

Now we have to set the data connection to this workbook, to do that select Data menu and click on Connections (see Figure 8 )

Figure 8 - Data Connections for the workbook

A Workbook Connections window opens, select the workbook name and click on Properties (see Figure 9)

Figure 9 - Workbook Connections

Then in Connection Properties dialog box select Enable background refresh and Refresh data when opening the file, then click OK (see Figure 10)

Figure 10 - Settings the Connection Properties

Now we have to save the Excel file in a trusted location, so before saving the Excel file first we have to specify a trusted location, to do that Select File menu and click on Options (see Figure 11)

Figure 11 - Specifying Trusted Location

In Excel Options window select Trust Center and click on Trust Center Settings (see Figure 12)

Figure 12 - Specifying Trusted Location

In Trust Center dialog window select Trusted Locations and click on Add new locations, specify a path for trusted source, then click  OK (see Figure 13)

Figure 13 - Specifying Trusted Location

Click OK on Trust Center window, click OK on Excel Options window, then save the Excel workbook in some location, in this example I’m saving the Excel file in the same location (E:\Employees) where Excel Web Query File was saved

Now I will try to add the new Item in the SharePoint list, so we will see whether item is added in the Excel spread sheet (see Figure 14)

Figure 14 - Adding Item in Sharepoint List

Now I can see the newly item added SharePoint list is also updated in the Excel workbook (see Figure 14)

Figure 15 - Item Updated in the Connected Excel Workbook

We have to discuss about the most thrilling and useful feature for the document library today.

Yes I’m talking about Metadata Navigation Settings in SharePoint 2010 document library.

SharePoint 2010 Metadata Navigation is the new piece of feature that supports to navigate and filter the documents in a library by means of metadata.

It is one of the most significant feature to navigate through the documents.

If we wants to navigate through the documents by metadata, first we have to configure the Metadata Navigation Settings

In this article I would like to show how to navigate and filter the documents by content types and columns

First of all I have enabled the document library to support the content types, please follow the article Document templates by using site content types in MOSS

However enabling site content types in SharePoint 2010 is similar to MOSS

So now I’m ready with the document library which contains few documents and content types like Word/Excel/Power Point documents etc.

I can create/upload documents using the above content types, so I have uploaded few documents (Word, Excel, PowerPoint etc) to my library can view in Figure 1

Figure 1 -SharePoint 2010 Document Library With Different Types of Document

Then, go to SharePoint 2010 document library > library settings

• Under General Settings section click on Metadata navigation settings
•  Select fields from avaliable hirerarchy fields and add for configuring navigation
• Then, select available key filter fields and add for configuring filters
• Then, click on OK button (please follow Figure 2)

Figure 2 - Configuring Metadata Navigation and Filter Settings

Now, you are also most done, navigate the document library you can view the newly added section which shows the navigation hirerarchy and filtering options (see Figure 3)

Figure 3 - Navigation and Filtering Through Metadata

It allows filtering the document library documents by means of content types, columns.

After configure Metadata navigation, I can navigate through the document very easily by selecting the appropriate content type.
I can also filter the document library to show only one group of document types like word/excel/powerpoint/publisher/infopath document type.
It is possible to filter through column like….If I want to show only documents which belongs to HR/IT/Admin/Finance/Security department.

In my previous post can view the most common custom webparts part 1

From last post I want to continue the series of most commonly used custom webparts, so once again I come up with a simple and small custom menu webpart shows all the sites and sub-sites of a SharePoint site in fly-out style

SharePoint Menu WebPart in fly-Out Style

using System;
using System.ComponentModel;
using System.Web;
using System.Drawing;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;

namespace CustomWebParts.VisualWebPart1
{
    [ToolboxItemAttribute(false)]
    public class VisualWebPart1 : WebPart
    {
        // Visual Studio might automatically update this path when you change the Visual Web Part project item.
       // private const string _ascxPath = @"~/_CONTROLTEMPLATES/CustomWebParts/TreeView/TreeViewUserControl.ascx";

        System.Web.UI.WebControls.Menu menu = null;

        protected override void CreateChildControls()
        {
            //Control control = Page.LoadControl(_ascxPath);
            //Controls.Add(control);

            menu = new System.Web.UI.WebControls.Menu();
            SPSite site = SPContext.Current.Site;
            SPWeb web = SPContext.Current.Web;

            menu.StaticDisplayLevels = 1;
            menu.MaximumDynamicDisplayLevels = 200;
            menu.Orientation = System.Web.UI.WebControls.Orientation.Horizontal;
            menu.StaticEnableDefaultPopOutImage = false;
            menu.StaticPopOutImageUrl = "/_layouts/images/menudark.gif";
            menu.SkipLinkText = "";
            menu.DynamicHoverStyle.BackColor = System.Drawing.Color.FromName("#CBE3F0");
            menu.CssClass = "ms-topNavContainer";

            menu.StaticMenuItemStyle.ItemSpacing = Unit.Pixel(0);
            menu.StaticSelectedStyle.CssClass = "ms-topnavselected";
            menu.StaticHoverStyle.CssClass = "ms-topNavHover";

            menu.DynamicMenuStyle.BackColor = System.Drawing.Color.FromName("#F2F3F4");
            menu.DynamicMenuStyle.BorderColor = System.Drawing.Color.FromName("#A7B4CE");
            menu.DynamicMenuStyle.BorderWidth = Unit.Pixel(1);

            menu.DynamicMenuItemStyle.CssClass = "ms-topNavFlyOuts";
            menu.DynamicHoverStyle.CssClass = "ms-topNavFlyOutsHover";
            menu.DynamicSelectedStyle.CssClass = "ms-topNavFlyOutsSelected";

            MenuItemStyle stMenuStyle = menu.StaticMenuItemStyle;
            stMenuStyle.CssClass = "ms-topnav";

            stMenuStyle.HorizontalPadding = 0;
            stMenuStyle.VerticalPadding = 0;
            stMenuStyle.ItemSpacing = Unit.Pixel(0);

            MenuItemStyle dyMenuStyle = menu.DynamicMenuItemStyle;
            dyMenuStyle.CssClass = "ms-topNavFlyOuts";
            dyMenuStyle.HorizontalPadding = 0;
            dyMenuStyle.VerticalPadding = 0;

            System.Web.UI.WebControls.MenuItem mItem = new System.Web.UI.WebControls.MenuItem(web.Title);
            mItem.NavigateUrl = web.Site.Url;
            menu.Items.Add(mItem);

            GenerateMenu(menu, Context);
            menu.DataBind();
            this.Controls.Add(menu);
        }

        public static void GenerateMenu(System.Web.UI.WebControls.Menu menu, HttpContext context)
        {
            SPSite site = SPContext.Current.Site;
            SPWeb web = SPContext.Current.Web;

            foreach (SPWeb subsite in web.Webs)
            {
                System.Web.UI.WebControls.MenuItem mItem = new System.Web.UI.WebControls.MenuItem();
                mItem.NavigateUrl = subsite.Url;
                mItem.Text = subsite.Title;
                mItem.ToolTip = subsite.Title;
                menu.Items.Add(mItem);
                BuildMenu(subsite, mItem, context);
            }
        }

        private static void BuildMenu(SPWeb subweb, System.Web.UI.WebControls.MenuItem mItem, HttpContext context)
        {
            SPSite site = SPContext.Current.Site;
            SPWeb web = SPContext.Current.Web;

            foreach (SPWeb subsite in subweb.Webs)
            {
                System.Web.UI.WebControls.MenuItem mSubItem = new System.Web.UI.WebControls.MenuItem();
                mSubItem.NavigateUrl = subsite.Url;
                mSubItem.Text = subsite.Title;
                mSubItem.ToolTip = subsite.Title;
                mItem.ChildItems.Add(mSubItem);
            }
        }
    }
}

I desired to support most common custom webparts used by the SharePoint professional, so that these can be helpful for them.
A custom tree view webpart shows all the sites and sub-sites of a SharePoint site

SharePoint Custom Tree View WebPart

Please use the below code and to get the exact look as show in the aboave image, the tree view webpart can expand/collapse

using System;
using System.ComponentModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;

namespace CustomWebParts.CustomTreeView
{
    [ToolboxItemAttribute(false)]
    public class CustomTreeView : WebPart
    {
        // Visual Studio might automatically update this path when you change the Visual Web Part project item.
        //private const string _ascxPath = @"~/_CONTROLTEMPLATES/CustomWebParts/CustomTreeView/CustomTreeViewUserControl.ascx";

        TreeView myTree;
        LinkButton lnkBtnExpand;
        LinkButton lnkBtnCollapse;
        Label lblPipeDivider;
        Label lbl_ErrorMsg;
        private string errorMessage = string.Empty;
        private int level = 0;

        protected override void CreateChildControls()
        {
            //Control control = Page.LoadControl(_ascxPath);
            //Controls.Add(control);

            this.Controls.Clear();
            myTree = new TreeView();
            myTree.ExpandImageUrl = "/_layouts/images/tvplus.gif";
            myTree.RootNodeStyle.ImageUrl = "/_layouts/images/stsicon.gif";
            myTree.ParentNodeStyle.ImageUrl = "/_layouts/images/stsicon.gif";
            myTree.LeafNodeStyle.ImageUrl = "/_layouts/images/stsicon.gif";
            myTree.CollapseImageUrl = "/_layouts/images/tvminus.gif";
            myTree.NoExpandImageUrl = "/_layouts/images/stsicon.gif";            

            myTree.NodeWrap = true;
            myTree.ShowLines = true;
            myTree.ShowExpandCollapse = true;
            myTree.EnableClientScript = true;

            GenerateTreeView();
            myTree.CollapseAll();
            myTree.CssClass = "ms-navitem a";
            this.Controls.Add(myTree);

            lnkBtnExpand = new LinkButton();
            lnkBtnExpand.Click += new EventHandler(expandAll_Click);
            lnkBtnExpand.Text = "Expand all";
            lnkBtnExpand.CssClass = "ms-navitem a";
            this.Controls.Add(lnkBtnExpand);

            lblPipeDivider = new Label();
            lblPipeDivider.Text = "  |  ";
            this.Controls.Add(lblPipeDivider);

            lnkBtnCollapse = new LinkButton();
            lnkBtnCollapse.Click += new EventHandler(collapseAll_Click);
            lnkBtnCollapse.Text = "Collapse all";
            lnkBtnCollapse.CssClass = "ms-navitem a";
            this.Controls.Add(lnkBtnCollapse);

            lbl_ErrorMsg = new Label();
            this.Controls.Add(lbl_ErrorMsg);

            base.CreateChildControls();
        }

        private void GenerateTreeView()
        {
            SPSite mySite = null;
            SPWeb myWeb = SPContext.Current.Web;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                try
                {
                    mySite = new SPSite(myWeb.Site.ID);
                    using (mySite)
                    {
                        level = 1;
                        foreach (SPWeb subWeb in myWeb.Webs)
                        {
                            TreeNode myTreeNode = new TreeNode(subWeb.Title);
                            myTreeNode.NavigateUrl = subWeb.Url;
                            ReadSubSites(subWeb, ref myTreeNode);
                            myTree.Nodes.Add(myTreeNode);
                        }

                    }
                }
                catch (Exception ex)
                {
                    errorMessage = ex.ToString();
                }
            });
        }

        private void ReadSubSites(SPWeb subWeb, ref TreeNode myTreeNode)
        {
            try
            {
                foreach (SPWeb myChildSubWeb in subWeb.Webs)
                {
                    TreeNode subnode = new TreeNode(myChildSubWeb.Title);
                    subnode.NavigateUrl = myChildSubWeb.Url;

                    myTreeNode.ChildNodes.Add(subnode);

                    if (myChildSubWeb.Webs.Count > 0)
                    {
                        if (myTreeNode.ChildNodes.Count > 0)
                        {
                            level = level + 1;
                            TreeNode myChildNode = myTreeNode.ChildNodes[myTreeNode.ChildNodes.Count - 1];
                            ReadSubSites(myChildSubWeb, ref myChildNode);
                            level = level - 1;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                errorMessage = ex.ToString();
            }
        }

        void expandAll_Click(object sender, EventArgs e)
        {
            myTree.ExpandAll();
        }

        void collapseAll_Click(object sender, EventArgs e)
        {
            myTree.CollapseAll();
        }      

        protected override void RenderContents(HtmlTextWriter writer)
        {
            EnsureChildControls();
            lnkBtnExpand.RenderControl(writer);
            lblPipeDivider.RenderControl(writer);
            lnkBtnCollapse.RenderControl(writer);
            RenderChildren(writer);
        }
    }
}

We will be happy to announce that we are starting introductory session on SharePoint in Hyderabad, India on Sunday September 19th 2010 at 11:00 a.m. If you or your friends interested to join please let us know through the contact form
The seminar will be on topics of why SharePoint, where SharePoint and how SharePoint
Please forward this in the community, thank you.

Venu:

# 17, IInd Floor,
Above Cafe Coffee Day,
Beside ICICI Bank,
Vanasthali Puram,
Hyderabad.500070

Mobile: +91-9987491026 (or) 91-9030043050