Checking for empty cell in Excel Spreadsheet

[Pam]

Hello :


Does anyone know how to check for an empty cell in an Excel spreadsheet
using Perl?
I am writing the script in a Windows envirement but the script will ber
run on Unix with a cron job.


I am using the following Perl packages

use strict;
use Spreadsheet::WriteExcel;
use Spreadsheet:arseExcel;
use Win32::Ole;

I have tried all the exmaples regarding parsing but can't find anything
that will
give me the data in a cell or check if the cell is empty before writihg
to it.

HELP

Pam

 

[Bob Walton]

Pam wrote:
....

Does anyone know how to check for an empty cell in an Excel spreadsheet
using Perl?
Yes

I am writing the script in a Windows envirement but the script will ber
run on Unix with a cron job.


I am using the following Perl packages

use strict;
use Spreadsheet::WriteExcel;
use Spreadsheet:arseExcel;
use Win32::Ole;

Can't find that one. Perhaps you mean Win32::OLE?

I have tried all the exmaples regarding parsing but can't find anything
that will
give me the data in a cell or check if the cell is empty before writihg
to it.

How about the first example program in the docs for Win32::OLE? It
looks as if the 'Value' property of the 'Range' class will return undef
for a cell that never had anything put in it (is that what you mean by
"empty"?). I tested this via a slight modification to that example.
Use the defined() function to determine if the value is undef or not.
....

 

[Pam]

Hi:

Yes I want to check each row in a particular column to see if anything
is there and then write to it. Is it possible you can give me a
snippet of that code for the defined function, This is the last piece
I have to fix


Appreciated

Pam

 

[Bob Walton]

Pam wrote:
....

Yes I want to check each row in a particular column to see if anything
is there and then write to it. Is it possible you can give me a
snippet of that code for the defined function, This is the last piece
I have to fix ....
Pam

....

Check the code in the first example in the documentation for the
Win32::OLE module:

perldoc Win32::OLE

It shows you how to read the value of a cell, how to use the defined()
function, and lots more. And, it goes without saying:

perldoc -f defined

 

[Ben Morrow]

Can't find that one. Perhaps you mean Win32::OLE?

In either case, no use on Unix...

How about the first example program in the docs for Win32::OLE? It
looks as if the 'Value' property of the 'Range' class will return undef
for a cell that never had anything put in it (is that what you mean by
"empty"?). I tested this via a slight modification to that example.
Use the defined() function to determine if the value is undef or not.

...so the OP will have to find a way to make SS:arseExcel tell her what
she needs.

Ben

 

[Pam]

Hi


Can you tell me what I am doing wrong here. None of my cells are being
highlighted or wrote to it they are empty.

$filename ="CCB.txt";

open(FILE,">$filename") || die("Cannot Open File $filename : $!" );
print FILE $query_result->content;
print "File open ";

close (FILE);


$datestamp = strftime("%Y%m%d",localtime) ;

# Open the Comma Separated Variable file
open (CSVFILE, $filename) or die "$filename: $!";


# Create a new Excel workbook
my $workbook =
Spreadsheet::WriteExcel->new("3GSoftwareCCB_MeetingAgenda$datestamp.xls");
my $worksheet = $workbook->add_worksheet();


# Create a new CSV parsing object
my $csv = Text::CSV_XS->new;

# Row and column are zero indexed
my $row = 0;

while (<CSVFILE>) {
if ($csv->parse($_)) {
my @Fld = $csv->fields;

my $col = 0;
foreach my $token (@Fld) {
$worksheet->write($row, $col, $token);
$col++;
}
$row++;
if ($row > 1){
$count = $count + 1;

$total = $count;

}

}
else {
my $err = $csv->error_input;
print "Text::CSV_XS parse() failed on argument: ", $err, "\n";
}

}
print "Adding sheet1\n";


print "Now will format .xls file\n";


#Holding Variables for mailing list
$Name1 = '(e-mail address removed)';

# Add a Format
$format = $workbook->add_format();
$format->set_text_wrap();
$format->set_bold();
$format->set_bg_color('51');
$format->set_border();
$format->set_bottom();
$format->set_top();
$format->set_left();
$format->set_right();


# The general syntax is write($row, $col, $token, $format)

# Write some formatted text

$col = 0;
$row = 0;


$worksheet->write(0, $col, "Identifier", $format,);
$worksheet->write(0, 1, "Team Comments", $format,);
$worksheet->write(0, 2, "Description", $format);
$worksheet->write(0, 3, "Status", $format);
$worksheet->write(0, 4, "Severity", $format);
$worksheet->write(0, 5, "Priority", $format);
$worksheet->write(0, 6, "CCBComments_encl", $format);
$worksheet->write(0, 7, "Primary-feature-team", $format);
$worksheet->write(0, 8, "Sub-feature-team", $format);
$worksheet->write(0, 9, "Project", $format);
$worksheet->write(0, 10,"Product", $format);
$worksheet->write(0, 11,"Products-targeted", $format);
$worksheet->write(0, 12,"Products-targed_del", $format);
$worksheet->write(0, 13,"Products-targetd_add", $format);
$worksheet->write(0, 14,"Program", $format);
$worksheet->write(0, 15,"Programs-targeted", $format);
$worksheet->write(0, 16,"Programs-targeted_del", $format);
$worksheet->write(0, 17,"Programs-targeted_add", $format);
$worksheet->write(0, 18, "CR-type", $format);
$worksheet->write(0, 19, "Software-version", $format);
$worksheet->write(0, 20, "Analysis-de", $format);
$worksheet->write(0, 21, "New-class", $format);
$worksheet->write(0, 22, "Forwarded-to", $format);
$worksheet->write(0, 23, "Duplicate-of", $format);
$worksheet->write(0, 24, "Duplicate-comments", $format);
$worksheet->write(0, 25, "TerminationComments_encl", $format);
$worksheet->write(0, 26, "Reason-for-terminating", $format);
$worksheet->write(0, 27, "Subsystem", $format);
$worksheet->write(0, 28, "Component", $format);
$worksheet->write(0, 29, "MA", $format);


print "Seting borders\n";

# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');


my $oBook = $Excel->Workbooks->Open("D:/Profiles/w8143c/My

Documents/Spreadsheet-WriteExcel-2.17/3GSoftwareCCB_MeetingAgenda$datestamp.xls");


#$row = 1;
my $col = 11;


print "Is this book geeting seen", $oBook, "\n";


#Tring to check for empty cell
#This looks at Sheet1 in the workbook
my $Sheet = $oBook->Worksheets(1);

my $cell = $Sheet->Cells($row,$col);

print "Is the sheet being seen", $Sheet, "\n";
for(my $row =1; $row <= $total ; $row++)
{


print " Will this give me my value $row $col",
$Sheet->Cells($row,$col), "\n";
print "where is the sheet " , $Sheet, "\n";

}

print $total, "\n";

print " Trying to check for empty cell \n";

#Trying to check for a empty cell
$row = 1;
$col = 11;

$format2 = $workbook->add_format();
$format2->set_bg_color('yellow');

while ($row <= $total)
{

$cell = $Sheet->Cells($row,$col);



if (undef $cell){


$worksheet->write($row, $col, "3G_Platform", $format2);

}

$row= $row + 1;



}

Thanks
Pamela

 

[Matt Garrish]

Please stop top posting. You've been told this before. Have you noticed
that you're the only one posting your entire followup over top of the
previous part of the thread?

You've also been told not to post garbage code that can't be run, yet
once again you've done that.

Can you tell me what I am doing wrong here. None of my cells are being
highlighted or wrote to it they are empty.

[snip lots of code that won't run under strictures and generally makes
little sense]

if (undef $cell){

What do you think the above is doing?

Matt