Re: Reading Variables in Excel Spreadsheet

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!

Comment on Re: Reading Variables in Excel Spreadsheet Select or Download Code

Replies are listed 'Best First'.
Re^2: Reading Variables in Excel Spreadsheet by merrymonk (Hermit) on Jan 15, 2018 at 11:31 UTC
Thanks for that I will try what you 'attached'. I am trying to find out how the 'reference' has become a variable name. I have been reading spreadsheets for some time it was the use of 'variables' which was new to me.	[reply]
Re^3: Reading Variables in Excel Spreadsheet by thanos1983 (Parson) on Jan 15, 2018 at 15:27 UTC
Hello again merrymonk, I have updated my reply to your question with a second sample of data. Is this something that you are looking for? If not update the question with sample of data so we can experiment. BR / Thanos Seeking for Perl wisdom...on the process of learning...not there...yet!	[reply] [d/l] [select]
Re^2: Reading Variables in Excel Spreadsheet by Anonymous Monk on Jan 15, 2018 at 15:30 UTC
I tried to use both the exmaples you sent. Sadly I do not have Excel::Writer with the version of Perl I use. Therefore I could not make much headway.	[reply]
Re^3: Reading Variables in Excel Spreadsheet by thanos1983 (Parson) on Jan 15, 2018 at 15:39 UTC
Hello Anonymous Monk, I assume you are the same user with merrymonk. You could install the module Excel::Writer::XLSX. In case you do not want to install the module (for any reason) you can use your own Excel sheet with the module Spreadsheet::Read to read the file and search for the value (reference) and it will return the column data. Give it a try and let me know if it works for you. BR / Thanos Seeking for Perl wisdom...on the process of learning...not there...yet!	[reply] [d/l] [select]