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

Discussion in 'Perl Misc' started by Pam, Sep 17, 2006.

  1. Pam

    Pam Guest

    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::parseExcel;
    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::parseExcel::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
     
    Pam, Sep 17, 2006
    #1
    1. Advertisements

  2. Pam

    Ben Morrow Guest

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

    use strict;
    use warnings;

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

    IMO it would be clearer to write that as

    "3GSoftwareCCB_MeetingAgenda${datestamp}.xls"

    , though what you have above isn't wrong.
    [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.
    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.
    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.
    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.
    Comments start with #. This is not valid Perl code.
    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 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
     
    Ben Morrow, Sep 18, 2006
    #2
    1. Advertisements

  3. Pam

    Matt Garrish Guest

    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.
    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.
    Here is your WriteExcel object.
    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?
    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.
    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.
    for my $row (1..$total) {

    But where did $total come from?
    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?
    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?
    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
     
    Matt Garrish, Sep 18, 2006
    #3
  4. Pam

    Pam Guest


    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::parseExcel::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
     
    Pam, Sep 18, 2006
    #4
  5. Pam

    jmcnamara Guest

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

    You are calling the wrong method on the wrong object.

    John.
    --
     
    jmcnamara, Sep 18, 2006
    #5
  6. Pam

    Pam Guest


    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 = '';




    # 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, Sep 18, 2006
    #6
  7. Pam

    Pam Guest

     
    Pam, Sep 22, 2006
    #7
  8. Pam

    Pam Guest

    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::parseExcel 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::parseExcel;


    #This is the parser
    my $oBook = new
    Spreadsheet::parseExcel::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

     
    Pam, Sep 23, 2006
    #8
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.