Checking for empty cell in Excel Spreadsheet

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

  1. Pam

    Pam Guest

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

  2. Pam

    Bob Walton Guest

    Pam wrote:
    ....
    Can't find that one. Perhaps you mean Win32::OLE?
    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.
    ....
     
    Bob Walton, Sep 24, 2006
    #2
    1. Advertisements

  3. Pam

    Pam Guest

    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
     
    Pam, Sep 24, 2006
    #3
  4. Pam

    Bob Walton Guest

    Pam wrote:
    ....
    ....

    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
     
    Bob Walton, Sep 24, 2006
    #4
  5. Pam

    Ben Morrow Guest

    In either case, no use on Unix...
    ...so the OP will have to find a way to make SS::parseExcel tell her what
    she needs.

    Ben
     
    Ben Morrow, Sep 25, 2006
    #5
  6. Pam

    Pam Guest

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

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

    Matt Garrish Guest

    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.

    [snip lots of code that won't run under strictures and generally makes
    little sense]
    What do you think the above is doing?

    Matt
     
    Matt Garrish, Sep 25, 2006
    #7
    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.