Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

password protet excel sheet

by mangrove (Acolyte)
on May 11, 2012 at 20:26 UTC ( #970090=perlquestion: print w/replies, xml ) Need Help??
mangrove has asked for the wisdom of the Perl Monks concerning the following question:

Hi I am trying to password protect the excel sheet but somehow it's not working. I know the $worksheet->protect(); is only for worksheets and not for the whole workbook but I tried that for worksheet and that didn't work either. Here is my code.

#!/use/bin/perl use strict; use warnings; use Spreadsheet::WriteExcel; # Create a new Excel workbook my $workbook = Spreadsheet::WriteExcel->new('perl.xls'); # Add a worksheet my $worksheet = $workbook->add_worksheet(); $worksheet->protect('test123'); my $worksheet2 = $workbook->add_worksheet(); # Add and define a format my $format = $workbook->add_format(); # Add a format $format->set_bold(); $format->set_color('red'); $format->set_align('center'); # Write a formatted and unformatted string, row and column notatio +n. my ($col,$row) = (0,0); $worksheet->write($row, $col, 'Hi Excel!', $format); $worksheet->write(1, $col, 'Hi Excel!'); # Write a number and a formula using A1 notation $worksheet->write('A3', 1.2345); $worksheet->write('A4', '=SIN(PI()/4)'); $col =0; $row =0; $worksheet2->write($row, $col, 'Hi Excel Two!', $format); $worksheet2->write(1, $col, 'Hi Excel!'); # Write a number and a formula using A1 notation $worksheet2->write('A3', 1.2345); $worksheet2->write('A4', '=SIN(PI()/4)'); $workbook->close();

Replies are listed 'Best First'.
Re: password protet excel sheet
by runrig (Abbot) on May 11, 2012 at 20:38 UTC
    It worked for me. In what way is it not working for you?

      It never prompts for password to me. Does it for you?.

        No, it doesn't prompt for the password. If you want a password, simply pass it as the only argument to protect. In the code you supplied, you protected the worksheet with a password of 'test123'.

        Update: I may have misunderstood your question but runrig answered nicely.

        When I try to edit any cell, it does not prompt for password, but there is a popup that says something like "this worksheet is protected, you have to unprotect it to edit...". Maybe it depends on version of Excel?
        For my copy of MS Excel 2010 (32-bit) on Windows 7 (64-bit), it works fine. Under this version, to unprotect it, click on "Review" and then click on "Unprotect worksheet". Then it prompts for a password.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://970090]
Approved by marto
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (6)
As of 2018-04-22 13:47 GMT
Find Nodes?
    Voting Booth?