Building a C# app to keep a excel spreadsheet up to date with users in specific active directory group

Question

I don't generally write in C# so this is a bit of a struggle for me.

I am building a console app that will query a active directory security group then I need to ignore if the user exists, add them if they are new or remove them from the excel sheet if they are not in the security group.

I have managed to get the app to query AD I just need it to check the existing excel sheet and update, add or remove as necessary.

Any help would be really appreciated

The excel sheet has the following headings;

UserID | Fname | Lname | Accesslvl | Department

 //--------------------------------START-------------------------------------------------------------

       //Sync Users From AD Security Group - (STAFF - Paxton All Access)



        PrincipalContext ctx = new PrincipalContext(ContextType.Domain); // set up domain context
        GroupPrincipal group = GroupPrincipal.FindByIdentity(ctx, "STAFF - Paxton All Access");// find the group in question
        if (group != null)// if found....
        {
            // iterate over members
            foreach (Principal p in group.GetMembers())
            {
                //do whatever you need to do to those members
                UserPrincipal theUser = p as UserPrincipal;

                if (theUser != null)
                {
                    if (theUser.Enabled != true) //If user account is disabled then
                    {
                    }
                    else //IF user account is enabled then
                    {
                        //Console.WriteLine("{0}: {1}", p.StructuralObjectClass, p.DisplayName);

                        //VARIABLES
                        //fname = {ADFirstname}
                        //lname= {ADLastname}
                        //UserID = {ADusername}
                        //Accesslevel = "STAFF ALL"
                        //Department = "STAFF"

                        //------------ADD ROW TO EXCEL HERE-----------------------
                        //Search if user exists via UserID
                        //IF user exist then update fname, lname, AccessLevel or Department columns if needed or continue
                        //Else add user using variables stated above

                    }
                }
            }
        }

score 0 · Answer 1 · answered Jan 04 '20 at 02:30

There are a few ways to interact with an Excel document from your c# code.

Use the oledb data adapter and access the Excel sheet as if it were a database table. This approach is pretty simple but it does have one big limitation, you can't delete rows from the spreadsheet, you can only read, add and update. Here is an example.
Use the Office Interop Library for Excel. This library allows you to access the COM API exposed by Excel from .Net code. It will give you the most flexibility and control but it's also the most difficult and complex and can only be reliably used on a workstation. It's not really meant for a server environment.
Convert the Excel document to a .csv, then you can use a .csv library for c# or just write a bit of code to parse the .csv file by line and delimiter, which is pretty simple.
A hybrid approach I have also used is to write a small amount of code using the Office Interop, just to open the spreadsheet and save it as a .csv file, then process the .csv file, then use Interop code to save it back to the .xlsx format. If the file needs to remain as a .xlsx, this approach can work and is generally much easier than writing all of your processing code to use the Interop library.

I would go with the oledb option if you can figure out a way to work around the delete limitation. Maybe update a status column which can be used to filter out the rows in Excel. If not then I would try one of the .csv options. Using the Interop Library would be my last choice.

Building a C# app to keep a excel spreadsheet up to date with users in specific active directory group

1 Answers1