[Kc] Excel

[djgoku]

On 9/3/06, djgoku <djgoku at gmail.com> wrote:
> On 9/2/06, Jay Hannah <jay at jays.net> wrote:
> >
> > Perhaps this is an opportunity for you to contribute a patch to
> > Spreadsheet::ParseExcel? I have several things across various CPAN
> > modules I've stumbled into that are in my to-do list to enhance. I'm
> > not very good at getting around to that list, but someday...
>
> I could for now add some VB to the master excel sheet (that is blank
> nothing is selected) that does something like this:
>
> Private Sub CheckBox1_Click()
> IF Worksheets("Sheet1").CheckBox1 = True Then
> Worksheets("Sheet1).Range("Z1") = 1
> ELSE
> Worksheets("Sheet1).Range("Z1") = 0
> ENDIF
> End Sub

Before I get into what I have found I would like to tell you there are
two different check boxes, one is a ActiveX object (In Excel,
View->ToolBars->Control ToolBar), and one is a Forms object? (In
Excel, View->ToolBars->Forms). In my above example I used ActiveX
check boxes, but have found that the worksheets I am creating this
program for are Form check boxes instead of what I thought were
ActiveX. As I was writing this email of my shortcomings I thought I
would do some more google searching before posting (which I found out
how to interact with $Shapes). The part I was stuck with was how to
find the checkboxes object location (Which I will show you below how
to find this.). Another problem I found as writing this email and
testing new things is that if the $shape isn't unique (as in you are
coping/pasting the form check box.) then it might be impossible to
distinguish between one "Check Box 1" and another "Check Box 1" even
though there in two different locations. So here is the code I have
come up with (not sure this works since I just typed it from memory,
and since I don't have windows handy I can't test this.):

use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
my $Win32::OLE::Warn = 3;

my($excelfile, $Excel, $Book, $Sheet, $Shapes);


$excelfile = 'blah.xls';


$Excel = Win32::OLE->new('Excel.Application');
$Book = $Excel->Workbooks->Open($excelfile);
# Open first sheet in $Book and activate it.
$Sheet = $Excel->Worksheets(1);
$Sheet->Activate();

# Get shapes count from worksheet
$Shapes = $Sheet->Shapes->{Count};

# Print location of check boxes
for(my $x = 1; $x < $Shapes + 1; $x++ ) {
        my($shape, $top_column, $top_row, $bottom_column, $bottom_row)

        $shape = $Sheet->Shapes->Item($x)->{Name};

        if($shape =~ /Check Box \d+/i) {
                $top_column = $Sheet->CheckBoxes($shape)->TopLeftCell->{Column};
                $top_row    = $Sheet->CheckBoxes($shape)->TopLeftCell->{Row};

                $bottom_column =
$Sheet->CheckBoxes($shape)->BottomRightCell->{Column};
                $bottom_row    =
$Sheet->CheckBoxes($shape)->BottomRightCell->{Row};

                # Get value of $shape
                if($shape == 1) {
                        $shape_value = "true";
                } else {
                        $shape_value = "false";
                }

                print "Shape: $shape is $shape_value.\n";
                print "TopLeftCell: ($top_column,$top_row)\n";
                print "BottomRightCell: ($bottom_column,$bottom_row)\n";
        }
}