Can't locate boject methoid Cells via package Sspreadsheet::WriteExcel::Worksheet

[Pam]

Hello:

I am having a problem trying to use print "At ($row, $col) the value is
%s \n",
$worksheet->Cells($row,$col)->{'Value'};

I am trying to get the contents of a cell, this will allow me to check
if the cell is empty
before I wite to it. I only want to write to the Cell if it is empt
but at compilation I get
Can't locate boject method Cells via package
Sspreadsheet::WriteExcel::Worksheet

This is a snippet of my code and modules I have in my perl code

use Spreadsheet:arseExcel;
use Spreadsheet::WriteExcel;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';


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


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



my $oBook =

Spreadsheet:arseExcel::Workbook->Parse("3GSoftwareCCB_MeetingAgenda$datestamp.xls");
#my $oBook =
$oExcel->Parse("3GSoftwareCCB_MeetingAgenda$datestamp.xls");
$row = 1;
$col = 11;
my $myval;


#Tring to check for empty cell

for(my $row =1 ;
$row <= $total ; $row++) {

$myval = $oBook->{Cells} [$row] [$col];
print "This is my value ($row , $col ) <= $total", This
will only give me the row and col number
$myvalue->{'Valaue'},"\n";

print "At ($row, $col) the value is %s \n",
$worksheet->Cells($row,$col)->{'Value'}; Here I want the
contents

print $row, "\n";

}


I want to check for an empty before I write to it.

If ( $worksheet->Cells($row,$col)->{'Value'} = "")
{
{
$worksheet->write($row, $col, "3G_Platform", $format2);



}
$row= $row + 1;

}

If any one can help me it will be greatly appreciated. I tried
everything I know to
get thsi workin


Thank You,
Pamela

 

[Ben Morrow]

I am having a problem trying to use print "At ($row, $col) the value is
%s \n",
$worksheet->Cells($row,$col)->{'Value'};

I am trying to get the contents of a cell, this will allow me to check
if the cell is empty
before I wite to it. I only want to write to the Cell if it is empt
but at compilation I get
Can't locate boject method Cells via package
Sspreadsheet::WriteExcel::Worksheet

Don't retype error messages: copy and paste them.

This is a snippet of my code and modules I have in my perl code

Where are

use strict;
use warnings;

?

use Spreadsheet:arseExcel;
use Spreadsheet::WriteExcel;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

Well, which are you using? Spreadsheet:arseExcel or Win32::OLE? You
are expected to create a *minimal* testcase that exhibits the problem
you are having.

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

What is $datestamp?

IMO it would be clearer to write that as

"3GSoftwareCCB_MeetingAgenda${datestamp}.xls"

, though what you have above isn't wrong.

my $worksheet = $workbook->add_worksheet();

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

my $oBook =

Spreadsheet:arseExcel::Workbook->
Parse("3GSoftwareCCB_MeetingAgenda$datestamp.xls");

[line above wrapped for clarity]

It is clearer to indent when you are continuing a statement on another
line.

When you have a long and/or complex string you are using more than once
it is a very good idea to assign it to a variable.

Do you really mean to be reading from the same workbook you are writing?
I seriously doubt if that works. Create a temporary file and rename it
over the original when you've finished. Or just use Win32::OLE
thoughout, of course, and edit the file using Excel's own methods.

#my $oBook =
$oExcel->Parse("3GSoftwareCCB_MeetingAgenda$datestamp.xls");

I presume this line has wrapped in your newsreader? Please make sure
code is still valid *as posted*.

What is $oExcel? It's undeclared. If you meant $Excel, then I don't
believe the Excel.Application object has a Parse method, although I
don't know.

$row = 1;
$col = 11;

These should both be declared with my. The $row here will never be used,
as you declare a new one below over the scope of the for loop.

my $myval;

#Tring to check for empty cell

for(my $row =1 ;
$row <= $total ; $row++) {

This would be better written

for my $row (1 .. $total-1) {

(though, again, what you have isn't wrong).

Please sort out your indentation before posting code: it makes it much
easier to read.

$myval = $oBook->{Cells} [$row] [$col];
print "This is my value ($row , $col ) <= $total", This
will only give me the row and col number

Comments start with #. This is not valid Perl code.

$myvalue->{'Valaue'},"\n";

I'm fairly sure you've misspelled 'Value'

Hash keys that match /^\w+$/ don't need quoting.

print is much easier to use if you learn about $\: read about it in
perldoc perlvar.

It's generally easier to use warn rather than print for debugging
output such as this, not least because Perl will tell you where in your
program it is talking about.

print "At ($row, $col) the value is %s \n",
$worksheet->Cells($row,$col)->{'Value'}; Here I want the
contents

print ne printf. It's probably easier to just use print, especially if
you use the magic vars that control it:

local ($,, $\) = (' ', "\n");
print "At ($row, $col) the value is",
$worksheet->Cells($row, $col)->{Value};

$worksheet is the worksheet you are writing to, not the one you are
reading from (as the error message told you). If you give your variables
better names it will be easier to avoid making that sort of mistake.

Ben

 

[Matt Garrish]

Hello:

I am having a problem trying to use print "At ($row, $col) the value is
%s \n",
$worksheet->Cells($row,$col)->{'Value'};

I am trying to get the contents of a cell, this will allow me to check
if the cell is empty
before I wite to it. I only want to write to the Cell if it is empt
but at compilation I get
Can't locate boject method Cells via package
Sspreadsheet::WriteExcel::Worksheet

The error is telling you that there's no such method in
Spreadsheet::WriteExcel. As you'll see later, it's because you're
calling the method on the wrong object.

This is a snippet of my code and modules I have in my perl code

use strict;
use warnings;

Unless you don't need them, you should always use them (and turn them
off only where you don't). The code you've pasted is unrunnable, which
is bad usenet form.

use Spreadsheet:arseExcel;
use Spreadsheet::WriteExcel;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';


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

Here is your WriteExcel object.

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

But what do you think the above is doing? You don't use a running
application anywhere that I see in your code, or is this not your
real/full code?

my $oBook =

Please try and format your code so that other people can read it. Of
note, howeve, is that $oBook now contains a ParseExcel object, which is
what you should be using to read your excel sheet.

Spreadsheet:arseExcel::Workbook->Parse("3GSoftwareCCB_MeetingAgenda$datestamp.xls");
#my $oBook =
$oExcel->Parse("3GSoftwareCCB_MeetingAgenda$datestamp.xls");

Again, we don't need to see code that you aren't using. The cleaner you
can make you example the more likely you'll be to get help.

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


#Tring to check for empty cell

for(my $row =1 ;
$row <= $total ; $row++) {

for my $row (1..$total) {

But where did $total come from?

$myval = $oBook->{Cells} [$row] [$col];
print "This is my value ($row , $col ) <= $total", This
will only give me the row and col number
$myvalue->{'Valaue'},"\n";

Where did $myvalue come from and what is 'Valaue'? I have to think you
tried to type this code in. If you aren't going to post your real
problem why do you expect people to guess what you might really be
doing wrong in your code?

print "At ($row, $col) the value is %s \n",
$worksheet->Cells($row,$col)->{'Value'}; Here I want the
contents

Syntax error. I assume this is where you're getting the error, which is
understandable for the reason I stated above. Why aren't you using the
$oBook object?

print $row, "\n";

}


I want to check for an empty before I write to it.

The point of a short and runnable example is that people don't have to
go back and correct all your errors. Please try running your examples
before posting them for others to look at.

Matt

 

[Pam]

Hello:

I am having a problem trying to use print "At ($row, $col) the value is
%s \n",
$worksheet->Cells($row,$col)->{'Value'};

I am trying to get the contents of a cell, this will allow me to check
if the cell is empty
before I wite to it. I only want to write to the Cell if it is empt
but at compilation I get
Can't locate boject method Cells via package
Sspreadsheet::WriteExcel::Worksheet

The error is telling you that there's no such method in
Spreadsheet::WriteExcel. As you'll see later, it's because you're
calling the method on the wrong object.

This is a snippet of my code and modules I have in my perl code

use strict;
use warnings;

Unless you don't need them, you should always use them (and turn them
off only where you don't). The code you've pasted is unrunnable, which
is bad usenet form.

use Spreadsheet:arseExcel;
use Spreadsheet::WriteExcel;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';


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

Here is your WriteExcel object.

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

But what do you think the above is doing? You don't use a running
application anywhere that I see in your code, or is this not your
real/full code?

my $oBook =

Please try and format your code so that other people can read it. Of
note, howeve, is that $oBook now contains a ParseExcel object, which is
what you should be using to read your excel sheet.

Spreadsheet:arseExcel::Workbook->Parse("3GSoftwareCCB_MeetingAgenda$datestamp.xls");
#my $oBook =
$oExcel->Parse("3GSoftwareCCB_MeetingAgenda$datestamp.xls");

Again, we don't need to see code that you aren't using. The cleaner you
can make you example the more likely you'll be to get help.

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


#Tring to check for empty cell

for(my $row =1 ;
$row <= $total ; $row++) {

for my $row (1..$total) {

But where did $total come from?

$myval = $oBook->{Cells} [$row] [$col];
print "This is my value ($row , $col ) <= $total", This
will only give me the row and col number
$myvalue->{'Valaue'},"\n";

Where did $myvalue come from and what is 'Valaue'? I have to think you
tried to type this code in. If you aren't going to post your real
problem why do you expect people to guess what you might really be
doing wrong in your code?

print "At ($row, $col) the value is %s \n",
$worksheet->Cells($row,$col)->{'Value'}; Here I want the
contents

Syntax error. I assume this is where you're getting the error, which is
understandable for the reason I stated above. Why aren't you using the
$oBook object?

print $row, "\n";

}


I want to check for an empty before I write to it.

The point of a short and runnable example is that people don't have to
go back and correct all your errors. Please try running your examples
before posting them for others to look at.

Matt

Hello Matt:

Sorry my code does run, Let me be a bit more concise. What I am doing
is dialing into a
database that we use at my company(DDTS) I use a saved query. My
script actually dials in using my log in and pass word. I get the
contents of the query, I then put that date inot comma seperated file.
Then I take that comma seperated file and put it into an Excel format
which works fine. The $total variable is the count for how many lines
are in the spreadsheet which tells us how many defects there will be
for that day. I take the $total variable and put it into an email
which is also working. $myvalue is just a variable I am using.

The reason I did not use obook is because when I did a print on oBook
it did not give me my file name

$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();

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


# 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";
}

}

# 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);

my $oBook =
Spreadsheet:arseExcel::Workbook->Parse("3GSoftwareCCB_MeetingAgenda$datestamp.xls");

$row = 1;
$col = 11;
my $myval;
print "Is this book geeting seen", $oBook, "\n";
I get a HASH(some numbers) didn't think it was working


#Triyng to check for empty cell

print "Trying to seek if worksheet exist", $currentwksheet,
"\n";
for(my $row =1 ;
$row <= $total ; $row++) {

$myval = $oBook->{Cells} [$row] [$col];
print "This is my value ($row , $col ) <= $total",
$myvalue->{'Valaue'},"\n";

# print "This is the value( $row , $col ) <= $total\n" ;
#if($myval);
print "At ($row, $col) the value is %s \n",
$worksheet->Cells($row,$col)->{'Value'};

print $row, "\n";


I want to add something like this once I get the Cells working


while ($row <= $total)
{


if($worksheet->Cells($row,$col)->{'Value'} = " ")

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



}
$row= $row + 1;

}


Right now iI'm writing to every cell and not paying attention to if
there is somethiung already in the cell
I don't have this added yet.
if($worksheet->Cells($row,$col)->{'Value'} = " ")


Hope this is better, I left out a lot becuase I thought it was a bit
much, sorry won't make that mistake again.


Thank You,
Pamela

 

[jmcnamara]

I get
Can't locate boject method Cells via package
Spreadsheet::WriteExcel::Worksheet

That is because Cell() isn't a Spreadsheet::WriteExcel method it is a
Spreadsheet:arseExcel method.

You are calling the wrong method on the wrong object.

John.
--

 

[Pam]

That is because Cell() isn't a Spreadsheet::WriteExcel method it is a
Spreadsheet:arseExcel method.

You are calling the wrong method on the wrong object.

John.
--

Yes, I was calling the wrong methond on the wrong object. I have
corrected that, I think the confussion because I am trying to write to
a file as well as read it. I have corrected that as well, but I am
still having problems getting the contents of the cell.
I no longer get the warning about not being able to locate object
method. I fixed that part.

$filename ="CCB.txt";

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

close (FILE);

#This is the holding variable for date because the requirements want
the date in filename

$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();

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



# 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();
#must set wrap for CCB comments and Description
$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);

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

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


#$row = 1;
$col = 11;


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

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


for(my $row =1 ;
$row <= $total ; $row++) {



printf "At ($row, $col) the value is %s and the formula is %s\n",
$Sheet->Cells($row,$col)->{'Value'},
$Sheet->Cells($row,$col)->{'Formula'};

print $row, "\n";

}

After this I get warning talking about use of uninitialized value and
it does not print value(which I was hoping to get contents of the cell
but print statement is empty


print $total, "\n";

while ($row <= $total)
{
$worksheet->write($row, $col, "3G_Platform", $format2);

$row= $row + 1;

}


#Here I am writing to the file but I need to check if the row is empty

If I use something like if ($Sheet->Cells($row,$col)->{'Value'} " ")
it blows up on me. If I put a string in it complains about it is not
numeric



Thanks,
Pamela

 

[Pam]

Yes, I was calling the wrong methond on the wrong object. I have
corrected that, I think the confussion because I am trying to write to
a file as well as read it. I have corrected that as well, but I am
still having problems getting the contents of the cell.
I no longer get the warning about not being able to locate object
method. I fixed that part.

To increase the chances of getting help from the people who read this
newsgroup, you should shorten your program to the smallest example that
still shows your problem. You are not having any problems reading and
parsing a CSV file, so leave that part out. Also, please do not start a
new thread with a similar subject, but continue to respond in the old
thread.

[many irrelevant lines snipped]

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


for(my $row =1 ;
$row <= $total ; $row++) {



printf "At ($row, $col) the value is %s and the formula is %s\n",
$Sheet->Cells($row,$col)->{'Value'},
$Sheet->Cells($row,$col)->{'Formula'};

There are two methods for Worksheet:

Cells[$row][$col]
Cell($row,$col)

You seem to be confusing them.

 

[Pam]

Hello


Regarding the last response I received from the group. I am aware that
there
are two ways to get the value of a cell, {Cells} [$row], [$col] This
one is used for Spreadsheet Module and Cell($row, $col) is used for
Win32:Ole Module

I am not having luck with either one in which I can get the data from a
cell.
I have looked at countless examples which are basically the same but
my code does not work '

I am able to create the sheet as you can see from the code below, I can
write to it and format it. When I open the spreadsheet the columns and
formating is there. I can't parse it to get the value I know
Spreadsheet:arseExcel should work

I have seen examples were Cell($row, $col) where used on Spreadsheet
module but it does not work for me. Right now I am trying to use
{Cells} [$row] [$col]

I thought what I was trying to do would be simple but I just can't seem
to get it to work.
I only want to read from an existing worksheet to see if there is any
contents in a cell before I write to it.

use strict;
Spreadsheet::WriteExcel;
Spreadsheet:arseExcel;


#This is the parser
my $oBook = new
Spreadsheet:arseExcel::Workbook->Parse("3GSoftwareCCB_MeetingAgenda$datestamp.xls");



$col = 11;
my $oWorksheet;
$oBook->{SheetCount} = 1;
my $sName= 'Sheet1';
my $ocell;

print "Is it getting the book", $oBook, "\n";


#Tring to check for empty cell
print "--------- SHEET:", $oBook->{Name}, "\n";
#print "Trying to get sheet name", $oWks, "\n";

for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
{

print "what is sheet", $iSheet, "\n"; Print out gives me 0

#I seem to be having a bit of a problem with this one. I am not sure
#if I am getting the worksheet. I tried using $Book->Worksheet(1) but
I gat
#complaint aqbout unblessed reference, so used the below code

$oWorksheet = $oBook->{Worksheet} [$iSheet];
for( $row = 1; $row <= $total; $row++) {

$ocell = $oWorksheet->{Cells}[$row][$col];
print "Row:$row Col:$col Value:", $ocell->{Val},"\n";


}
}

I get the correct number of rows so it has to reading my sheet.
Why can't I get the data ?

CAN ANYONE HELP ME PLEASE

Pamela

Yes, I was calling the wrong methond on the wrong object. I have
corrected that, I think the confussion because I am trying to write to
a file as well as read it. I have corrected that as well, but I am
still having problems getting the contents of the cell.
I no longer get the warning about not being able to locate object
method. I fixed that part.

To increase the chances of getting help from the people who read this
newsgroup, you should shorten your program to the smallest example that
still shows your problem. You are not having any problems reading and
parsing a CSV file, so leave that part out. Also, please do not start a
new thread with a similar subject, but continue to respond in the old
thread.

[many irrelevant lines snipped]

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


for(my $row =1 ;
$row <= $total ; $row++) {



printf "At ($row, $col) the value is %s and the formula is %s\n",
$Sheet->Cells($row,$col)->{'Value'},
$Sheet->Cells($row,$col)->{'Formula'};

There are two methods for Worksheet:

Cells[$row][$col]
Cell($row,$col)

You seem to be confusing them.