CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX 		Search Posts SEARCH
POSTS 		Who's Online WHO'S
ONLINE 		Log in LOG
IN

Home: Perl Programming Help: Advanced:
Update an excel file on sharepoint via Perl script

 


gigaaks
New User

Oct 18, 2012, 3:19 PM

Post #1 of 6 (7726 views)
Update an excel file on sharepoint via Perl script Can't Post
Hi,

Summary:

1. Find if a user exists in Active Directory or not - I have the script.

2. Grant user access in SVN. I have the script add a user under the right group for a project for r or rw access.

3. Now, I have to maintain an excel file available on our companies sharepoint site. Lets say it exists at the following link:

https://teamshare.company.com/sites/SCMTeam/SVN_Access.xls

Excel file just have one single sheet "Sheet1".

Sheet1 has a the following columns (and rows containing valid values) which stores access information for a repository/project/platform etc. See attached image.



Actions that I need is to write a Perl script to do the following in easy understandable way:

1. Checkout this .xls file from sharepoint to local folder.

2. Update this file with for a user.

a. If it's a new user. Add an entry to this file

b. If user exists, then see if user's permission/access requested is different than what we already have.

i. If different, then update that row

ii. If same, dont do anything just exit out successfully.

c. If user needs to be delete, then mark "Status" column as "Inactive" - otherwise in all other cases, it'll hold an "Active"

value.

d. Script will perform necessary checks that whether a user exists in Windows AD(Active Directory) before performing any operation in SVN access and updating the .xls file.

e. .xls file should be sorted via userid. All the columns (except Userid, Module and Date) in the sheet are FILTERD via its

value (depends upon user what you select / to see rows matching that value for FILTERED fields).

3. Check-in this file back to Sharepoint so that information is up to date as soon as someone runs the above actions via a

Perl Script.





Any help.
Attachments: untitled.JPG (68.3 KB)


Laurent_R
Enthusiast / Moderator

Oct 18, 2012, 3:32 PM

Post #2 of 6 (7725 views)
Re: [gigaaks] Update an excel file on sharepoint via Perl script [In reply to] Can't Post
My comment may be outdated, but last time I looked at the Excel Perl modules, it was apparently not possibe to edit an existing Excel file. You could:
- read an existing Excel file
- write a new fresh Excel file
- but not modify an existing Excel file (because,, as far as I remember, of proprietary CRC types of checks added by Microsoft to Excel files).

You still have the option of reading the content of an Excel file and insert its modified content to another file, and then do the necessary house cleaning (deleting old file and renaming the new one).

Please anyone correct me if I am wrong, what I am saying here dates from four or five years ago, it might have changed since and I might have got it wrong or not used the right modules at the time.


gigaaks
New User

Oct 18, 2012, 3:36 PM

Post #3 of 6 (7724 views)
Post deleted by gigaaks [In reply to]
 


FishMonger
Veteran / Moderator

Oct 18, 2012, 5:08 PM

Post #4 of 6 (7723 views)
Re: [gigaaks] Update an excel file on sharepoint via Perl script [In reply to] Can't Post
Quote
Thanks, actually I'm looking more of a script to do this.
Then you'll need to write one and ask for help when you get stuck.

If you want someone to write it for you, then you'll need to hire a programmer.


(This post was edited by FishMonger on Oct 18, 2012, 5:09 PM)


gigaaks
New User

Oct 19, 2012, 8:54 AM

Post #5 of 6 (7718 views)
Post deleted by gigaaks [In reply to]
 


FishMonger
Veteran / Moderator

Oct 19, 2012, 9:20 AM

Post #6 of 6 (7714 views)
Re: [gigaaks] Update an excel file on sharepoint via Perl script [In reply to] Can't Post
Your requirements are to specific/custom to find a prewritten script that comes close to doing what you want, which is why you need to write your own script.

I've never used sharepoint, so I can't provide much help in that area, but if there is a command line interface to it, then it would be very simple to execute those commands in the Perl script.

There are several modules on CPAN that can be used to handle the parsing and writing of the spreadsheets. Such as:
Spreadsheet::ParseExcel - Read information from an Excel file. http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel-0.59/lib/Spreadsheet/ParseExcel.pm
Spreadsheet::WriteExcel - Write to a cross-platform Excel binary file. http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.37/lib/Spreadsheet/WriteExcel.pm

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives