Solved

How to lock worksheet in Activeperl?

Posted on 2006-11-07
9
407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hard perl script 16 166
Problem with one substitution modifying another 3 83
Using Perl DBI to query oracle 3 53
batch script for automated email 12 142
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 …
Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
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…

737 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