How to lock worksheet in Activeperl?

Posted on 2006-11-07
Last Modified: 2010-05-18
Dear expert,

I have an application to write data to excel file in Activeperl. After I write data to the excel file, I need to lock or protect (or set password) to the file so that other people can't change the data in the file. I have searched on the web so far I have not find the answer yet.

I found one module that can do this is Spreadsheet::WriteExcel package, it is too complicate to install and use, I have trouble to install it.
I try to avoid using this method.

My question is : Is there simple way to do it?  

I use this line:
$Sheet->{Locked}=1; it always complains: Exception Occurred in PROPERTYPUT  at "Locked"
I always think that it should have a simple way to do it, like
how to find out the right functions from activeperl to protect the worksheet?

Question by:Hanqian
  • 4
  • 3
  • 2
LVL 48

Expert Comment

ID: 17892404
What module are you running?

Spreadsheet::WriteExcel is the module you want.  It is *very* easy to install, ie:

In ppm

install Spreadsheet::WriteExcel

then you want


to protect/lock a spreadsheet.

If you find Spreadsheet::WriteExcel to complex (personally, I think it's pretty straight forward), you can use Spreadsheet::WriteExcel::Simple

Accepted Solution

mattaustin earned 125 total points
ID: 17892524
you can put a password in $worksheet->protect();
LVL 48

Expert Comment

ID: 17892900
So what module *are* you using?

Author Comment

ID: 17898198

I didn't use any module. I just use activeperl and call "$worksheet->protect("password");" in my code.

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

LVL 48

Expert Comment

ID: 17902192
You *must* be using a module as Perl (Activeperl) or not, does *not* have inbuilt excel commands.

I don't really care about points, but I'm curious as to why you awarded mattaustin the points when I gave you the answer and told you how to install Spreadsheet::WriteExcel, which you appear to have installed and are using anyway.

Expert Comment

ID: 17902246
i agree at some point in your code you have:
use Spreadsheet::WriteExcel;

i think what he really wanted to know what how to set the password... (without really reading any docs) :P

Author Comment

ID: 17906728
I am 100% honest to to tell you that  I do only use Activeperl module, I thought  "module"  that Tintin aske me is others except Activeperl. I have told you that in my origrinal question that I use Activeperl, otherwise how do I do my application on window in perl. My question is whether Activeperl has the right function that I can use to protect my file, I always think that Activeperl should have a simple function can do it, please look my question again!!!!

 I don't have  Spreadsheet::WriteExcel module install on my machine at all!! It fails me in the very first step, I didn't continue it since I thought that it should have a simple way to do it, that is why I posted my question here, otherwise I will have to try more time with  Spreadsheet::WriteExcel module.

 My question is not how to install  Spreadsheet::WriteExcel, it is whether Activeperl module can have a simple function to do protection.

When I looked at comments for my answer that time, both Titin and mattausin's comments are  here, I just think that mattausin's function is what I want, so I put it in my code, it works immediately, I didn't try Tintin's method at all. If I have a simple way to work my problem out, why I choose a complicated wya:Spreadsheet::WriteExcel??  

ALSO my code has been run in my co-work's machine and my laptop both yesterday, both machine never even download Spreadsheet::WriteExcel module(at least I downloaded it on my machine), it works greate.

If you still do't believe me, you can try a simple code to prove to yourself  that mattausin's code works without Spreadsheet::WriteExcel module installed. Install Activeperl and then add following lines in the code (bascially what I have done in my code)
#! C:/Perl/bin/perl -w

use Win32::OLE;
use File::Copy;
use Cwd;
use strict;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;        # die on errors...

# Start Excel and make it invisible
      my $xlApp = Win32::OLE->new('Excel.Application');
      $xlApp->{Visible} = 0;
      # Open excel file.
      my $xlBook = $xlApp->Workbooks->Open($excel_file);

       my $Sheet1 = $xlBook ->Worksheets(1);

       $Sheet1->Cells($row,$col)->{'Value'} = 5;

      $xlBook ->Save();
      $xlBook ->Close();

I do really appreciate Tintin's help, I am sorry that I didn't take your help.

LVL 48

Expert Comment

ID: 17909003
Ahh, so you use the Win32::OLE module.  I hadn't considered that.

While that method may suit you needs now, the advantage of using Spreadsheet::WriteExcel are:

1.  Much lower overheads, as a Excel does not need to be loaded up.
2.  Portable across multiple platforms.
3.  Can produce Excel spreadsheets on platforms that don't/can't have Excel installed.

Author Comment

ID: 17909140

Thanks for your good advice, I may have to use Spreadsheet::WriteExcel soon since I will move my application to Linux.
I don't know if I could write to an existing excel file in Linux. If I have the problem using Spreadsheet::WriteExcel, I may
need you help later.


Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
HTTP Error 502.2 - Bad Gateway 3 197
perl script help 12 101
Perl Awk Need Help 3 94
Regex rule to match two different url 5 36
On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (,  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now