asked on

How to lock worksheet in Activeperl?

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
$Sheet->protection("password"),............,
how to find out the right functions from activeperl to protect the worksheet?

Thanks.
Hanqian

Tintin

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

$worksheet->protect();

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

ASKER CERTIFIED SOLUTION

mattaustin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tintin

So what module *are* you using?

Hanqian

ASKER

Tintin,

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

Hanqian

Tintin

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.

mattaustin

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

Hanqian

ASKER

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;

$Sheet1->protect("password");
      $xlBook ->Save();
      $xlBook ->Close();
========================================================================

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

Thanks.
Hanqian

Tintin

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.

Hanqian

ASKER

Tintin,

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.

Thanks.
Hanqian