Cant seem to detect empty cells in Excel 2007

[MatKhine]

Please tell me what I'm doing wrong here:
#!/usr/bin/perl -w

use strict;
use File::Copy;
use Spreadsheet::ParseExcel;

my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('item.xls');

if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
my $out_file = 'item_ti.sql';
open(OUT_FILE,"> $out_file") or die "No output file";

for my $worksheet ( $workbook->worksheets() ) {

my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();

for my $row ( $row_min +1 .. $row_max ) {

print OUT_FILE "Insert into ktf_items_360 values (";
for my $col ( $col_min .. 11 ) {
my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;
if ( $col < 10 ) # need to stop comma print on last col
{
if ( $cell )
{
print OUT_FILE "",'\'', $cell->value(),'\',',"";
}
else {
print OUT_FILE ",\'BLANK\'";
}
}
else
{
if ( $cell )
{
print OUT_FILE "",'\'', $cell->value(),'\',',"";
}
else {
print OUT_FILE ",\'BLANK\'";
}
print OUT_FILE "",'\'', $cell->value(),'\''," ";
}
}
print OUT_FILE ",\'\')\n";
}
}
close(OUT_FILE);

[jmcnamara]

Hi,

Excel differentiates between an "Empty" cell and a "Blank" cell. An "Empty" cell is a cell which doesn't contain data whilst a "Blank" cell is a cell which doesn't contain data but does contain formatting.

For an empty Excel cell the cell object returned by get_cell() will be undef. For a blank Excel cell the cell object returned by get_cell() will be defined but $cell->value() will be "" (i.e. an empty string). In the example below I am assuming that you wish to treat "Empty" and "Blank" cells the same.

Anyway, I think the main problem was that you were skipping "Empty" cells with this line:

    next unless $cell; 

I have rewritten your example to take this into account and to handle "Empty" and "Blank" cells. This may not be exactly what you need but it should get you most of the way:

#!/usr/bin/perl -w

use strict;

use File::Copy;

use Spreadsheet::ParseExcel;

my $parser   = Spreadsheet::ParseExcel->new();

my $workbook = $parser->parse( 'item.xls' );

if ( !defined $workbook ) {

    die $parser->error(), ".\n";

}

my $out_file = 'item_ti.sql';

open( OUT_FILE, "> $out_file" ) or die "No output file";

for my $worksheet ( $workbook->worksheets() ) {

    my ( $row_min, $row_max ) = $worksheet->row_range();

    my ( $col_min, $col_max ) = $worksheet->col_range();

    for my $row ( $row_min + 1 .. $row_max ) {

        my @values;

        print OUT_FILE "Insert into ktf_items_360 values (";

        for my $col ( 0 .. 11 ) {

            my $cell = $worksheet->get_cell( $row, $col );

            if ( defined $cell and $cell->value() ne '' ) {

                push @values, q{'} . $cell->value() . q{'};

            }

            else {

                push @values, q{'BLANK'};

            }

        }

        print OUT_FILE join ",", @values;

        print OUT_FILE ")\n";

    }

}

close( OUT_FILE );

John.

-- 

[Nishant]

Thank you very much for the explanation John. Hope you have a food holiday season.

Nishant Pandit

714 785 6233

--
--
You received this message because you are subscribed to the Spreadsheet::ParseExcel Google Group.
 
For posting and other options visit this group at:
http://groups.google.com/group/spreadsheet-parseexcel?hl=en
 
You can also post by sending an email to:
http://groups.google.com/group/spreadsheet-parseexcel?hl=en

[Tanscia]

Hi
I am having a similar issue with my script. I've tried this solution and a lot of others but as yet am unable to get my script to skip empty rows.
I do get some output (the dates) and these errors with blank lines in between ( I'm assuming the blank lines are the empty cells in my spreadsheet ).
2012-11-01
Use of uninitialized value in scalar chomp at test.pl line 50.
Use of uninitialized value in hash element at test.pl line 51.
Use of uninitialized value in concatenation (.) or string at test.pl line 51.

2012-01-01
Use of uninitialized value in scalar chomp at test.pl line 50.
Use of uninitialized value in hash element at test.pl line 51.
Use of uninitialized value in concatenation (.) or string at test.pl line 51.

My script looks like this
#!/usr/bin/perl -w

use Spreadsheet::ParseExcel;   


my $cell;
my $row_min;
my $row_max;
my $col_min;
my $col_max;
my %account_chg_date;
my @account_names;
my $key;

my $parser = Spreadsheet::ParseExcel->new();

my $workbook = $parser->parse('test2.xls');

   


    if ( !defined $workbook ) {
        die $parser->error(), ".\n";
    }

    for my $worksheet ( $workbook->worksheet(0) ) {

        my ( $row_min, $row_max ) = $worksheet->row_range();
        my ( $col_min, $col_max ) = $worksheet->col_range();

ROW:
    for my $row ( $row_min .. $row_max ) {
        next if ($row == 0);



        for my $col ( $col_min .. $col_max ) {
                if ($col == 0 || $col == 2) {

                    my $cell = $worksheet->get_cell( $row, $col );

                    if (($col == 0) and (defined $cell) and ($cell->value() ne '')) {
                        $account_names[$row]= $cell->{_Value};
                        }elsif (defined $cell and $cell->value() ne ''){
                            $account_chg_date{$account_names[$row]} = $cell->{_Value};
                            }
                }       
        }
    }
    }
   

while (@account_names){
    chomp ($key = pop @account_names);
    print "$account_chg_date{$key}\n"
    }

Any help would really be appreciated.
Tanscia

[jmcnamara]

On Nov 13, 2:41 pm, Tanscia <rbmwebmast...@googlemail.com> wrote:

>                     if (($col == 0) and (defined $cell) and ($cell->value() ne '')) {
>                         $account_names[$row]= $cell->{_Value};

In this line you are setting $account_names[$row] to the cell value
which is correct. However, if the first row that matches is 4, for
example, then the @account_names elements 0 ..3 will be created (auto-
vivified) with undef values.

You can fix this is several ways. Either push() to the array or else
filter out the undef values after reading the worksheet.

John.

[Tanscia]

Oh fab.

Thank you John.