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?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


Author Comment

ID: 17898198

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…

820 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