none
Export user list to Excel sharepoint 2007

    Question

  • Hello,

    I wonder if you can, if you can export to excel the sharepoint user list, ordered by sites.

    Thanks

    Thursday, December 09, 2010 10:25 AM

Answers

  • I don't know what you mean by ordered by sites.

    Also, remember that you will not be able to "expand" security groups.

    I suppose that you look for a OOTB feature that would allow you ti do this. The only way I think it could be possible would be inside a site collection, playing around with "User Information List" but you will not have the site information nor the permission level.

    Here a sample code (just put it in a console applciation) that will export all the autorisation (at web level only so no list/list item level) from the whole web application and to a .csv file (you can easily open then the CSV file from Excel).

    class Program
        {
            static void Main(string[] args)
            {

                using (StreamWriter sw = new StreamWriter(@"C:\export_autorisations.csv", false, Encoding.Unicode))
                {
                    // le header des colonnes
                    sw.WriteLine("Site collection relative url,Web realtive url,Web title,SharePoint group,Security group,User,Permission");

                    //SPWebApplication webApp = SPFarm.Local.

                    using (SPSite siteRoot = new SPSite("http://my_sharepoint_site.com"))
                    {
                        SPWebApplication webApp = siteRoot.WebApplication;

                        for (int i = 0; i < webApp.Sites.Count; i++)
                        {
                            using (SPSite site = webApp.Sites[i])
                            {
                                for (int j = 0; j < site.AllWebs.Count; j++)
                                {
                                    using (SPWeb web = site.AllWebs[j])
                                    {
                                        foreach (SPRoleAssignment assignment in web.RoleAssignments)
                                        {
                                            SPPrincipal member = assignment.Member;

                                            if (member is SPUser)
                                            {
                                                SPUser usr = member as SPUser;

                                                if (usr.IsDomainGroup)
                                                {
                                                    // AD Security Group
                                                    sw.WriteLine(
                                                        string.Format("{0},{1},{2},{3},{4},{5},{6}",
                                                          site.ServerRelativeUrl
                                                        , web.ServerRelativeUrl
                                                        , web.Title
                                                        , "" //SPGroup
                                                        , usr.LoginName //Sec group
                                                        , "" //User
                                                        , getRoles(assignment.RoleDefinitionBindings)     // Permissions
                                                    ));
                                                }
                                                else
                                                {
                                                    // AD User
                                                    sw.WriteLine(
                                                        string.Format("{0},{1},{2},{3},{4},{5},{6}",
                                                          site.ServerRelativeUrl
                                                        , web.ServerRelativeUrl
                                                        , web.Title
                                                        , "" //SPGroup
                                                        , "" //Sec group
                                                        , usr.LoginName //User
                                                        , getRoles(assignment.RoleDefinitionBindings)     // Permissions
                                                    ));
                                                }
                                            }
                                            else
                                            {
                                                if (member is SPGroup)
                                                {
                                                    SPGroup group = member as SPGroup;

                                                    // AD User
                                                    sw.WriteLine(
                                                        string.Format("{0},{1},{2},{3},{4},{5},{6}",
                                                          site.ServerRelativeUrl
                                                        , web.ServerRelativeUrl
                                                        , web.Title
                                                        , group.Name //SPGroup
                                                        , ""
                                                        , ""
                                                        , getRoles(assignment.RoleDefinitionBindings)     // Permissions
                                                    ));
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }

                    sw.Flush();
                    sw.Close();
                }
            }

            private static string getRoles(SPRoleDefinitionBindingCollection roleDefs)
            {
                string roles = "";

                foreach(SPRoleDefinition roleDef in roleDefs)
                {
                    if(!string.IsNullOrEmpty(roles))
                    {
                        roles += ";" + roleDef.Name;
                    }
                    else
                    {
                        roles += roleDef.Name;
                    }
                }
                return roles;
            }
        }


    Florin DUCA
    MCSE 2003 +Sec,MCTS conf/dev WSS3/MOSS, MCITP/MCPD SP 2010, MCPD ASP.Net 3.5, MCTS ISA 2006
    Logica Business Consulting, France
    • Marked as answer by Mike Walsh FIN Thursday, December 09, 2010 4:37 PM
    Thursday, December 09, 2010 10:51 AM

All replies

  • I don't know what you mean by ordered by sites.

    Also, remember that you will not be able to "expand" security groups.

    I suppose that you look for a OOTB feature that would allow you ti do this. The only way I think it could be possible would be inside a site collection, playing around with "User Information List" but you will not have the site information nor the permission level.

    Here a sample code (just put it in a console applciation) that will export all the autorisation (at web level only so no list/list item level) from the whole web application and to a .csv file (you can easily open then the CSV file from Excel).

    class Program
        {
            static void Main(string[] args)
            {

                using (StreamWriter sw = new StreamWriter(@"C:\export_autorisations.csv", false, Encoding.Unicode))
                {
                    // le header des colonnes
                    sw.WriteLine("Site collection relative url,Web realtive url,Web title,SharePoint group,Security group,User,Permission");

                    //SPWebApplication webApp = SPFarm.Local.

                    using (SPSite siteRoot = new SPSite("http://my_sharepoint_site.com"))
                    {
                        SPWebApplication webApp = siteRoot.WebApplication;

                        for (int i = 0; i < webApp.Sites.Count; i++)
                        {
                            using (SPSite site = webApp.Sites[i])
                            {
                                for (int j = 0; j < site.AllWebs.Count; j++)
                                {
                                    using (SPWeb web = site.AllWebs[j])
                                    {
                                        foreach (SPRoleAssignment assignment in web.RoleAssignments)
                                        {
                                            SPPrincipal member = assignment.Member;

                                            if (member is SPUser)
                                            {
                                                SPUser usr = member as SPUser;

                                                if (usr.IsDomainGroup)
                                                {
                                                    // AD Security Group
                                                    sw.WriteLine(
                                                        string.Format("{0},{1},{2},{3},{4},{5},{6}",
                                                          site.ServerRelativeUrl
                                                        , web.ServerRelativeUrl
                                                        , web.Title
                                                        , "" //SPGroup
                                                        , usr.LoginName //Sec group
                                                        , "" //User
                                                        , getRoles(assignment.RoleDefinitionBindings)     // Permissions
                                                    ));
                                                }
                                                else
                                                {
                                                    // AD User
                                                    sw.WriteLine(
                                                        string.Format("{0},{1},{2},{3},{4},{5},{6}",
                                                          site.ServerRelativeUrl
                                                        , web.ServerRelativeUrl
                                                        , web.Title
                                                        , "" //SPGroup
                                                        , "" //Sec group
                                                        , usr.LoginName //User
                                                        , getRoles(assignment.RoleDefinitionBindings)     // Permissions
                                                    ));
                                                }
                                            }
                                            else
                                            {
                                                if (member is SPGroup)
                                                {
                                                    SPGroup group = member as SPGroup;

                                                    // AD User
                                                    sw.WriteLine(
                                                        string.Format("{0},{1},{2},{3},{4},{5},{6}",
                                                          site.ServerRelativeUrl
                                                        , web.ServerRelativeUrl
                                                        , web.Title
                                                        , group.Name //SPGroup
                                                        , ""
                                                        , ""
                                                        , getRoles(assignment.RoleDefinitionBindings)     // Permissions
                                                    ));
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }

                    sw.Flush();
                    sw.Close();
                }
            }

            private static string getRoles(SPRoleDefinitionBindingCollection roleDefs)
            {
                string roles = "";

                foreach(SPRoleDefinition roleDef in roleDefs)
                {
                    if(!string.IsNullOrEmpty(roles))
                    {
                        roles += ";" + roleDef.Name;
                    }
                    else
                    {
                        roles += roleDef.Name;
                    }
                }
                return roles;
            }
        }


    Florin DUCA
    MCSE 2003 +Sec,MCTS conf/dev WSS3/MOSS, MCITP/MCPD SP 2010, MCPD ASP.Net 3.5, MCTS ISA 2006
    Logica Business Consulting, France
    • Marked as answer by Mike Walsh FIN Thursday, December 09, 2010 4:37 PM
    Thursday, December 09, 2010 10:51 AM
  • ok, thanks Florin. Proves it, I guess that this will be worth to me what I do.

    Thursday, December 09, 2010 4:27 PM
  • Hi,

    There is a simple way to export the user list into excel by passing the LIST ID and VIEW ID of the user groups as the query string.

    Read the below article for more details:

    Export SharePoint Groups to Excel

    Hope this helps you!


    Cheers!

    Maruthu.

    http://sharepoint-works.blogspot.com


    • Edited by Maruthu Thursday, March 15, 2012 6:01 AM
    • Proposed as answer by Maruthu Thursday, March 15, 2012 6:02 AM
    Thursday, March 15, 2012 6:01 AM