trickyq has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to access specific columns in an excel spreadsheet. I have used Data::dumper to access the data structure, and print it to a file, so I know I'm seeing it. However, I have no idea how to access specific columns and I need 2 specific columns to compare to each other through arrays, however I cant get the specific data I need because I dont know the syntax and cant find the shiz anywhere. help.

heres what i have

use Spreadsheet::XLSX;

$excel = Spreadsheet::XLSX -> new ('build.xlsx');

use Data::Dumper;
open FILE, ">feckyou.txt" or die $!;

print FILE (Dumper($excel). "\n");
close FILE;

[download]

thanks

Replies are listed 'Best First'.
Re: excel columns and my ignorance
by roboticus (Chancellor) on May 31, 2012 at 17:58 UTC

    trickyq:

    If you read the documentation for Spreadsheet::XLSX, the very first thing you'll see is a bit of example code to open a spreadsheet and access the contents, once cell at a time. The tricky bits of the example code are:

    • The $col variable holds the column number
    • The $row variable holds the row number
    • The $excel variable holds the data for the excel workbook
    • The $sheet variable holds the data for the worksheet in the workbook

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]

      I see that. However, if the variable $col holds the column # by default how do i tell it to give me just column 0 and column 3? I'm missing some essential piece of this. can you type a line of code that would access Column 0 only (or A in excel)? I would get it all if i could see that

      thanks

[reply]
        Does this help 
        use strict;
use Spreadsheet::XLSX;

my $excel = Spreadsheet::XLSX -> new ('build.xlsx');
my $sheet = $excel->Worksheet('Sheet1');
my ($row_min,$row_max) = $sheet->row_range();
my @col=();
for my $row ($row_min..$row_max){
  $col[0] = $sheet->{Cells}[$row][0]->{Val};
  $col[3] = $sheet->{Cells}[$row][3]->{Val};
  print "$row $col[0] $col[3] \n"
}

        [download]
        poj
[reply]
[d/l]
Re: excel columns and my ignorance
by kcott (Archbishop) on May 31, 2012 at 18:08 UTC

    The Spreadsheet::XLSX SYNOPSIS shows how to access columns (as well as rows and individual cells).

    -- Ken

[reply]

      I just don't get it. I don't even see how it shows me that. nothing there has helped me to figure out how to access only one specific column. I know its there, but I don't know what i'm looking for. can you type a line of code that would access/output column 0 say. If I could see it directly, I would get it. sorry

      Ken

[reply]

        I don't know exactly what sort of comparison you want to do. The following (based on the SYNOPSIS code) may give you a starting point: 

        ...

# Comparing columns 3 and 5

my $cmp_col_1 = 3;
my $cmp_col_2 = 5;

foreach my $sheet (@{$excel -> {Worksheet}}) {
    ...

    foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {
        # Here's where you'd compare
        #   $sheet -> {Cells} [$row] [$cmp_col_1]
        # with
        #   $sheet -> {Cells} [$row] [$cmp_col_2]
    }
}

        [download]

        -- Ken

[reply]
[d/l]

Back to Seekers of Perl Wisdom