Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

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
0
Hanqian
Asked:
Hanqian
  • 4
  • 3
  • 2
1 Solution
 
TintinCommented:
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
 
mattaustinCommented:
you can put a password in $worksheet->protect();
ex:
$worksheet->protect("password");
0
 
TintinCommented:
So what module *are* you using?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
HanqianAuthor Commented:
Tintin,

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

Hanqian
0
 
TintinCommented:
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
 
mattaustinCommented:
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
 
HanqianAuthor Commented:
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
 
TintinCommented:
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
 
HanqianAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now