Solved

How to lock worksheet in Activeperl?

Posted on 2006-11-07
9
405 Views
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
$Sheet->protection("password"),............,
how to find out the right functions from activeperl to protect the worksheet?

Thanks.
Hanqian
0
Comment
Question by:Hanqian
  • 4
  • 3
  • 2
9 Comments
 
LVL 48

Expert Comment

by:Tintin
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

$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
0
 
LVL 2

Accepted Solution

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

Expert Comment

by:Tintin
ID: 17892900
So what module *are* you using?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Hanqian
ID: 17898198
Tintin,

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

Hanqian
0
 
LVL 48

Expert Comment

by:Tintin
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.
0
 
LVL 2

Expert Comment

by:mattaustin
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
0
 

Author Comment

by:Hanqian
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;

        $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
0
 
LVL 48

Expert Comment

by:Tintin
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.
0
 

Author Comment

by:Hanqian
ID: 17909140
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
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
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…

821 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