comment on

Hello merrymonk,

I am not really sure how this is happening on you excel sheet and you are not able to read the data by column and row. But there is a really nice simple tutorial How to read an Excel file in Perl that can you help you getting started.

To be honest without being able to replicate your problem I can not imagine any way that I could help you. Can you give it a try and based on the tutorial to write some code that replicates your problem?

The only alternative approach that I could think is to retrieve all data, e.g.

my @rows = Spreadsheet::Read::rows($book->[1]);
foreach my $i (1 .. scalar @rows) {
    foreach my $j (1 .. scalar @{$rows[$i-1]}) {
        say chr(64+$i) . " $j " . ($rows[$i-1][$j-1] // '');
    }
}
[download]

Through the retrieved data and having a reference to each row and column of the data in the sheet you can search for the variable and look up your references. Very briefly this is the only that I could think out of my mine.

Update: Adding sample of code for the tutorial provided above, including proposed solution using key(s) the row and column of the file and value(s) the variables of each cell. Sample of code bellow:

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use feature 'say';
use Excel::Writer::XLSX;
use Spreadsheet::Read qw(ReadData);

my $workbook  = Excel::Writer::XLSX->new( 'simple.xlsx' );
my $worksheet = $workbook->add_worksheet();

my @data_for_row = (1, 2, 3);
my @table = (
    [4, 5],
    [6, 7],
    );
my @data_for_column = (10, 11, 12);


$worksheet->write( "A1", "Hi Excel!" );
$worksheet->write( "A2", "second row" );

$worksheet->write( "A3", \@data_for_row );
$worksheet->write( 4, 0, \@table );
$worksheet->write( 0, 4, [ \@data_for_column ] );

$workbook->close;

my $book = ReadData ('simple.xlsx');

my %hash;
my @rows = Spreadsheet::Read::rows($book->[1]);
foreach my $i (1 .. scalar @rows) {
    foreach my $j (1 .. scalar @{$rows[$i-1]}) {
        # say chr(64+$j) . "$i " . ($rows[$i-1][$j-1] // '');
    $hash{chr(64+$j) . "$i "} = ($rows[$i-1][$j-1] // '');
    }
}

print Dumper \%hash;

my ($key) = grep{ $hash{$_} eq 'second row' } keys %hash;
say "Column/row: " . $key;

__END__

$ perl excel.pl
$VAR1 = {
          'C5 ' => '',
          'D2 ' => '',
          'B6 ' => 7,
          'D6 ' => '',
          'C3 ' => 3,
          'E2 ' => 11,
          'B5 ' => 6,
          'B4 ' => '',
          'B1 ' => '',
          'C4 ' => '',
          'A6 ' => 5,
          'A5 ' => 4,
          'D1 ' => '',
          'A1 ' => 'Hi Excel!',
          'D4 ' => '',
          'B3 ' => 2,
          'A3 ' => 1,
          'A2 ' => 'second row',
          'A4 ' => '',
          'E3 ' => 12,
          'E6 ' => '',
          'C2 ' => '',
          'E5 ' => '',
          'C6 ' => '',
          'C1 ' => '',
          'E1 ' => 10,
          'D5 ' => '',
          'E4 ' => '',
          'B2 ' => '',
          'D3 ' => ''
        };
Column/row: A2
[download]

Update2: Based on Using structured references with Excel tables I created a sample of data and retreive data based on reference.

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use feature 'say';
use Excel::Writer::XLSX;
use Spreadsheet::Read qw(ReadData);

my $workbook  = Excel::Writer::XLSX->new( 'simple.xlsx' );
my $worksheet = $workbook->add_worksheet();

# Add and define a format
my $formatHeader = $workbook->add_format();
$formatHeader->set_bold();
$formatHeader->set_align( 'center' );

# Add and define a format
my $formatTable = $workbook->add_format();
$formatTable->set_align( 'center' );

my @data_for_row = ("Sales",
            "Region",
            "Sales Amount",
            "% Commission",
            "Commission Amount");

my @table = ( ["Joe", "Robert", "Michelle", "Erich", "Dafna", "Rob"],
          ["North", "South", "East", "West", "North", "South"],
          [260, 660, 940, 410, 800, 900],
          ["10%", "15%", "15%", "12%", "15%", "15%"] );

$worksheet->write( "A1", \@data_for_row, $formatHeader );
$worksheet->write( 1, 0, \@table, $formatTable );

$workbook->close;

my $book = ReadData ('simple.xlsx');

my %hash;
my @rows = Spreadsheet::Read::rows($book->[1]);
foreach my $i (1 .. scalar @rows) {
    foreach my $j (1 .. scalar @{$rows[$i-1]}) {
        # say chr(64+$j) . "$i " . ($rows[$i-1][$j-1] // '');
    $hash{chr(64+$j) . "$i "} = ($rows[$i-1][$j-1] // '');
    }
}

print Dumper \%hash;

my ($key) = grep{ $hash{$_} eq 'Sales Amount' } keys %hash;
say "Column/row: " . $key . " Value: " . $hash{$key};

__END__

$ perl excel.pl
$VAR1 = {
          'A7 ' => 'Rob',
          'C5 ' => 410,
          'C4 ' => 940,
          'E5 ' => '',
          'E2 ' => '',
          'B7 ' => 'South',
          'D6 ' => '15%',
          'A2 ' => 'Joe',
          'A5 ' => 'Erich',
          'C7 ' => 900,
          'B6 ' => 'North',
          'C3 ' => 660,
          'A6 ' => 'Dafna',
          'B3 ' => 'South',
          'E3 ' => '',
          'D2 ' => '10%',
          'B5 ' => 'West',
          'A4 ' => 'Michelle',
          'A1 ' => 'Sales',
          'D7 ' => '15%',
          'E1 ' => 'Commission Amount',
          'B1 ' => 'Region',
          'C1 ' => 'Sales Amount',
          'D4 ' => '15%',
          'D5 ' => '12%',
          'E7 ' => '',
          'A3 ' => 'Robert',
          'B4 ' => 'East',
          'B2 ' => 'North',
          'C6 ' => 800,
          'E4 ' => '',
          'E6 ' => '',
          'D3 ' => '15%',
          'C2 ' => 260,
          'D1 ' => '% Commission'
        };
Column/row: C1  Value: Sales Amount
[download]

Hope this helps, BR.

Seeking for Perl wisdom...on the process of learning...not there...yet!

In reply to Re: Reading Variables in Excel Spreadsheet by thanos1983
in thread Reading Variables in Excel Spreadsheet by merrymonk

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.