CGI and Excel data 4

[groovygarden]

I have an HTML form into which a user can enter a serial number. The form is processed by CGI code which searches a comma separated text file for the serial number and returns the appropriate information. The data in the text file is taken manually from an Excel spreadsheet. My question is, how easy would it be to link directly to the excel spreadsheet rather than use a text file and how could I do this?

Any suggestion, snippets of useful code or helpful URLs very welcome.

Thanks in Advance.

 

[LightElf]

helpful url )))

http://www.cpan.org

http://search.cpan.org/search?mode=module&query=Excel

 

[goBoating]

If your perl/cgi is running on a machine with Excel installed, then here is an example of opening a spreadsheet and messing around in it. The methods that are available are documented in Excel or any VB reference. I generally just open Excel, figure out what I want to do in the Macro editor in Excel and then move that functionality into the perl code. This approach is kind of heavy in terms of overhead. It essentially opens Excel each time it has to access the file.... not quite as bad as opening it to the screen, because it does not have to render the screen, but, still, it is a lot of overhead to get at a little bit of data. It you wanted to use OLE, I think I would use OLE to automate the creation of the text file on some frequency and let your cgi work off the text. Or, get PostgreSQL or MySQL and ... and.. and.......;-)

It has been a while since I played with any of this, so beware. It should illustrate enough to get you started.

Careful, I nuked a couple of Excel files playing with this. Don't work on anything you can't afford to loose until you get it working well.

#!perl
use Win32::OLE;

# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) 
	{
    $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
            or die "Oops, cannot start Excel";
	}

# get a new workbook
# $book = $ex->Workbooks->Add;
$book = $ex->Workbooks->Open({FileName => "trainees.xls", ReadOnly=>1});
#$ws = $book->Worksheets(train)->Name;
$ws = $book->Worksheets(1);
$wsN = $ws->Name;
print "Name = $wsN\n";
$a1 = $ws->Range("A1")->{Value};
print "A1 = $a1\n";
$ws->Range("A109")->{Value}="Fellows";

# write to a particular cell
#$sheet = $book->Worksheets(1);
#$sheet->Cells(1,1)->{Value} = "foo";

# write a 2 rows by 3 columns range
#@firstRow = ('number', 'Xyzzy', 'Plugh');
#@secondRow = ("42",'Perl',"3.1415" );
# $sheet->Range("A2:C3")->{Value} = [[ 'number', 'Xyzzy', 'Plugh' ],
#                                    [ 42,    'Perl',  3.1415  ]];

#$sheet->Range("A2:C3")->{Value} = [[@firstRow],
#									[@secondRow]];

# print "XyzzyPerl"
#$array = $sheet->Range("A2:C3")->{Value};
#for (@$array) 
#	{
#   for (@$_) 
#    	{
#        print defined($_) ? &quot;$_|&quot; : &quot;<undef>|&quot;;
#    	}
#    print &quot;\n&quot;;
#	}

# save and exit
#$book->SaveAs( 'c:\temp\test.xls' );
undef $book;
undef $ex;

Thanks to George for figuring this stuff out. ;-) 'hope this helps

If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.

 

[tsdragon]

Wish I had a way to bookmark this useful thread instead of emailing it to myself!

I'd give George a star for this too, but he hasn't posted on this thread. Tracy Dryden
tracy@bydisn.com

http://www.bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.