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

New User

Oct 18, 2012, 3:19 PM

Views: 14648
Update an excel file on sharepoint via Perl script



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:


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"


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)

Veteran / Moderator

Oct 18, 2012, 3:32 PM

Views: 14647
Re: [gigaaks] Update an excel file on sharepoint via Perl script

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.

New User

Oct 18, 2012, 3:36 PM

Views: 14646
Post deleted by gigaaks


Veteran / Moderator

Oct 18, 2012, 5:08 PM

Views: 14645
Re: [gigaaks] Update an excel file on sharepoint via Perl script

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)

New User

Oct 19, 2012, 8:54 AM

Views: 14640
Post deleted by gigaaks


Veteran / Moderator

Oct 19, 2012, 9:20 AM

Views: 14636
Re: [gigaaks] Update an excel file on sharepoint via Perl script

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