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. Advertising

  2. Pam

    Bob Walton Guest

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

    --
    Bob Walton
    Email: http://bwalton.com/cgi-bin/emailbob.pl
     
    Bob Walton, Sep 24, 2006
    #2
    1. Advertising

  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

    Bob Walton wrote:
    > 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::parseExcel;
    > > 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

    > --
    > Bob Walton
    > Email: http://bwalton.com/cgi-bin/emailbob.pl
     
    Pam, Sep 24, 2006
    #3
  4. Pam

    Bob Walton Guest

    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

    --
    Bob Walton
    Email: http://bwalton.com/cgi-bin/emailbob.pl
     
    Bob Walton, Sep 24, 2006
    #4
  5. Pam

    Ben Morrow Guest

    Quoth Bob Walton <>:
    >
    > > 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;

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


    In either case, no use on Unix...

    > > 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.


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

    Ben

    --
    If I were a butterfly I'd live for a day, / I would be free, just blowing away.
    This cruel country has driven me down / Teased me and lied, teased me and lied.
    I've only sad stories to tell to this town: / My dreams have withered and died.
    (Kate Rusby)
     
    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

    Bob Walton wrote:
    > 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
    >
    > --
    > Bob Walton
    > Email: http://bwalton.com/cgi-bin/emailbob.pl
     
    Pam, Sep 25, 2006
    #6
  7. Pam

    Matt Garrish Guest

    Pam wrote:
    > Bob Walton wrote:
    > > 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



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

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?ZGF2ZQ==?=

    Reading Excel sheet from asp.net returns empty cell

    =?Utf-8?B?ZGF2ZQ==?=, Feb 12, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    5,484
    venkatu2005
    Sep 5, 2009
  2. Savvoulidis Iordanis

    Empty gridview cells and checking for empty string

    Savvoulidis Iordanis, Sep 5, 2008, in forum: ASP .Net
    Replies:
    1
    Views:
    557
    Savvoulidis Iordanis
    Sep 5, 2008
  3. venkatu2005
    Replies:
    0
    Views:
    1,038
    venkatu2005
    Sep 5, 2009
  4. venkatu2005
    Replies:
    0
    Views:
    1,485
    venkatu2005
    Sep 5, 2009
  5. Justin C
    Replies:
    4
    Views:
    175
    Justin C
    Jul 10, 2006
Loading...

Share This Page