Excel - Perl? 1

[safra]

Hi,

Probably this is not possible but maybe there is some module that can handle this.

I have a small (Perl) database system on the server and at home I work with Excel.

Is it possible to send an excel file or defined cells in a spreadsheet to a perl script that can do something with it and save it in a simple flat text file?

regards,

Ron

 

[Tve]

In your standard Perl Documentation look at "Using OLE with Perl"

I copied this from the docs:

How do I extract a series of cells from Microsoft Excel?

If you have a sheet object you can extract the values of a series of cells through $Sheet->Range->{'Value'}, for example:

my $array = $Sheet->Range("A8:B9&quot->{'Value'};

Now $array[0][0] contains the value of cell A8, $array[0][1] the value of cell B8, $array[1][0] the value of cell A9 and $array[1][1] the value of cell B9.

What is returned is an two-dimensional array (OK, an array with references to arrays) that contains the values of the requested cells.

A complete example is here:



use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die on errors...
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit'); # get already active Excel
# application or open new
my $Book = $Excel->Workbooks->Open("C:\\DOCUMENTS\\test.xls" # open Excel file
my $Sheet = $Book->Worksheets(1); # select worksheet number 1
my $array = $Sheet->Range("A8:B9&quot->{'Value'}; # get the contents
$Book->Close;
foreach my $ref_array (@$array) { # loop through the array
# referenced by $array
foreach my $scalar (@$ref_array) {
print "$scalar\t";
}
print "\n";
}
To retrieve the formatted value of a cell you should use the {'Text'} property instead of the {'Value'} property. This returns exactly what is being displayed on the screen though! If the column is not wide enough, you get a value of '######':

my $array = $Sheet->Range("A8:B9&quot->{'Text'};



Thierry

 

[safra]

Tve, I did not expect to receive a positive answer on this question but this sounds very promising.

One more question before I start playing with this:

The Perl stuff is run on a remote server (Internet). Will Perl be able to access the .xls file on my machine? If so how should I set up this path correctly?

But so far, thanks a lot!

Ron

 

[safra]

I guess this is not possible?

Ron

 

[sleuth]

Not really, you'd have to have a program that you could interact with on your pc to talk with the internet program. That's what I'm tinkering with right now.

The best thing you could do is make an upload script, upload scripts are really easy and you could upload you file to the bin with your sript on the net and it could then open it from there.

If you don't know how to write an upload script....

#########################################################
#!/usr/bin/perl
use CGI qw/:standard :html3/;
print "Content-type: text/html\n\n";
$upload_dir = "path/to/dir";
$upload = param('upload');
$do = param('do');
$name = "$upload";
$name =~ s!^.*(\\|/)!!;
$file = param('upload');
$file =~ m!([^/:\\]*)$!;
$short_name = $1;
open (SAVE,">$upload_dir/$short_name"
while ($size = read($file,$data,1024)){
print SAVE $data;
$total_size += $size;
}
close SAVE;
print "Done Uploading Your File";

##########################################################
# HTML FORM

ENCTYPE="multipart/form-data"

Is the killer line most people forget, whenever you are making an upload program
and it creates the file, but is emtpy, you left out this line in the form.

Never Forget this line! ENCTYPE="multipart/form-data"

##########################################################
<form method=&quot;POST&quot; ENCTYPE=&quot;multipart/form-data&quot; action=&quot;upload.cgi&quot;>
<p align=&quot;center&quot;>
<font color=&quot;#FFFFFF&quot;>
<input type=&quot;file&quot; name=&quot;upload&quot; size=&quot;33&quot;>
<input type=&quot;submit&quot; value=&quot; Upload &quot; name=&quot;B1&quot;></font></p>
</form>
##########################################################


That's really really simple but it'll get anyone started at least, I literally keep that in my My Documents as upload.txt since so many people ask about upload scripts.

Hope that'll get you on your way Mate,

Tony

PS. Is Excel any good?

 

[safra]

Sleuth, I didn't think of that, thanks for the tip!

I use an upload script to upload images. I will try that.

The script that Tve provided will probably not work as it will be run on a Unix server.

Am I right that this part will give errors:

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die on errors...
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');

How will this part look like for a Unix system?

regards,

Ron

Ps Sleuth. No idea, I don't use this sort of programs so much and as Excel came with the package I started to use it.

 

[powahusr]

Have you considered installing Perl on your system so that all the work can be done on your system? This way here all you have to do is just FTP the file to your Database on the server, once the file is formated to your liking by Perl.

 

[safra]

Sorry, a bit late. (Out of town for a couple of days)

I have tried installing Perl twice now and I can't get it to work. After it is installed there are tons of files. It doesn't do what the manuals imply and reading through them to troubleshoot makes me only more confusing. I just don't know where to start. Probably some time I will try again.

Ron

 

[sleuth]

Hey mate,

Say your computer name is &quot;Ron&quot;

To install perl you'll also need PWS from Microsoft, Apache For Windows & Xitami would work too, but I've only worked with PWS and it's very easy.

Go to

http://www.microsoft.com/msdownload/ntoptionpack/askwiz.asp

And try getting the Personal Web Server, they make it really confusing unfortunatly, but if you do it right, you'll have an icon on your desktop the would take you to the 5 section control panel.

Also, if you have the windows 95 disk, you can just browse the disk, and copy the folder called PWS in the extras section if I remember correctly. It's on the disk, just can't remember exactly where. Then just transfer it off the disk, then open the PWS folder and double click setup.

You can run it from the disk but I usually back it up onto my hard disk just in case something happends to the disk.

Ok, now I'll give you the install directions.

1. Install Pws, Latest version.

2. Now Install The DCOM Module. Download From ActiveState.com, also get perl 5.6 from them. Dcom mod is for windows 98/95 machines. Anything greater won't require it. More details in the requirements section at activestat.com

3. Install The MSI Installer If You Don't Have The Latest Version Already.

4. Then Restart.

5. Install Perl Then Restart

6. Go to Start Menu, Run, then type &quot;regedit&quot; Then Go to the Script Map folder located in

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\w3svc\parameters\Script Map

Add the string value .pl then .cgi. Right Click on them then click modify.

Type

C:\Perl\bin\Perl.exe %s %s

For both of them.

8. Go to your explorer menu add cgi-bin in C:\Inetpub\

http://wwwroot

Then double click on the PWS Icon, then go to the advanced options, then add a directory. Browse till you find the C:\Inetpub\

http://wwwroot\cgi-bin

Make sure you allow it to execute.

9. Now Restart and add a cgi file in the cgi-bin and run it from you browser,

http://ron/cgi-bin/test.cgi

You don't need the Shebang line #!/path/to/perl in windows, it's all done with file association with pws.

print &quot;Content-type: text/html\n\n&quot;
print &quot;Perl Is running&quot;;

Is a good test.

Good luck Mate,

Tony

 

[safra]

Thanks for that,Sleuth!

Not the way I tried to install it. I will try your method!

Thanks a lot!

Ron

 

[safra]

Sleuth, I tried to install Perl the way you described.

When I run a script the action is cancelled and a Dos box appears but only for a fraction of a second and I can't read the error message.

Just to be sure I did everything right:

&quot;6. Go to Start Menu, Run, then type &quot;regedit&quot; Then Go to the Script Map folder located in

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\w3svc\parameters\Script Map

Add the string value .pl then .cgi. Right Click on them then click modify.

Type

C:\Perl\bin\Perl.exe %s %s

For both of them.&quot;

About the last part (adding the string values). I right clicked in the right box > new > 4 options (Key, horizontal line 3 options) > I choose the first below the horizontal line. An icon &quot;ab&quot; is created and I modified it the way you described.

Is this correct?


&quot;8. Go to your explorer menu add cgi-bin in C:\Inetpub\

http://wwwroot

Then double click on the PWS Icon, then go to the advanced options, then add a directory. Browse till you find the C:\Inetpub\

http://wwwroot\cgi-bin

Make sure you allow it to execute.&quot;

I did create the folder cgi-bin in the indicated folder. But then you write &quot;then go to the advanced options, then add a directory.&quot;

I don't understand what you mean by this?

I did make sure the cgi-bin is set to execute.


&quot;9. Now Restart and add a cgi file in the cgi-bin and run it from you browser,

http://ron/cgi-bin/test.cgi

&quot;

I don't understand the path. Shouldn't it be:
C:\Inetpub\

http://wwwroot\cgi-bin\test.cgi

But this doesn't work!

http://mycomputer/cgi-bin/test.cgi

doesn't work neither!


Could the fact that I installed Perl on the D drive (I adjusted C:\Perl\bin\Perl.exe %s %s into D:\Perl\bin\Perl.exe %s %s) cause the problem?

Anymore things I could try?

Ron