Excel, Drop Down Menu, and Perl

[qball]

My first question is geared more towards Excel than Perl. First, is there a way to create drop down menus for any column and its associated cells? Second, can this be done with any of the CPAN Excel modules such as Spreadsheet::WriteExcel or is there an easier way to do it?

Basically, I want to pull data from the database into the CSV file, but create a drop down menu on one of the columns. I've already written the code to create the CSV file, but not the drop down menu.

qball~"I have node idea?!"

[the_slycer]

With Excel, yes there is a way to do this, something to do with column format I believe. I would think that Spreadsheet::WriteExcel would be capable of performing the same function, but you should probably read the docs to find out.

If it doesn't pan out for you, you may be able to use OLE to convince Excel to do what you want.

[Hero Zzyzzx]

Dump the column that you want to create the drop-down menu on into an array, then pass a reference to that array to a CGI object, thusly:



#!/usr/bin/perl -wT
use strict;
use CGI;
my $q = new CGI;

#Other stuff that works with with an excel file and creates the array 
+. . .
#which is probably the stuff you were looking for here. . .


print 
  $q->header(),
  $q->start_form(-method =>'get', -action =>'/cgi-bin/script.pl'),
  $q->popup_menu(-name=>"column",-values=>\@array_you_made), 
  $q->submit("Go!"),
  $q->end_form();

Actually, looking at this, the hard part might be making the array. I'm not sure, I've never worked with Excel via perl.

[mpolo]

If I understand you correctly, you are talking about Excel's AutoFilters. Using Win32::OLE, you should be able to make this work... According to the documentation at Microsoft, the AutoFilter method applies to a Range. So, you would have something on this line...

use Win32::OLE;
my $ex=Win32::OLE->new('Excel.Application');
$ex->{Visible}=1;
my $book=$ex->Workbooks->Add;
my $sheet=$book->Worksheets(1);
## Here you would populate the spreadsheet
## I assume that the titles of the columns are contained in
## cells A1-Z1 here...
$sheet->Range("a1:z1")->AutoFilter;
## Now you have dropdowns for columns A1-Z1 that let you
## filter on the given column.

The M$ page cited will give you more options on the use of AutoFilter (you can pre-program the filter, for instance).

This assumes that you are running on a Windows platform so you can use OLE, of course -- WriteSpreadsheet is cross-platform, but I haven't used it.