Spreadsheet::WriteExcel & worksheet->write

Discussion in 'Perl Misc' started by courtney.machi@gmail.com, Sep 25, 2006.

  1. Guest

    Hi everyone,

    I am grabbing database information using fetchrow() and storing the
    results in variables. I need to write these results to an excel
    spreadsheet. Does worksheet->write work when you're using variables? It
    doesn't seem to like what I'm doing:

    $worksheet1->write(0,0,$lastname);
    $worksheet1->write(0,1,$platform);
    $worksheet1->write(0,2,$balance);

    when $lastname, $platform and $balance are the results from the
    fetchrow()...
    I can print these results OUT to a textfile, so I know they contain
    data, but it just doesn't work with the spreadsheet.

    Any suggestions?

    Thanks,
    Courtney
     
    , Sep 25, 2006
    #1
    1. Advertising

  2. David Squire Guest

    wrote:
    > Hi everyone,
    >
    > I am grabbing database information using fetchrow() and storing the
    > results in variables. I need to write these results to an excel
    > spreadsheet. Does worksheet->write work when you're using variables? It
    > doesn't seem to like what I'm doing:
    >
    > $worksheet1->write(0,0,$lastname);
    > $worksheet1->write(0,1,$platform);
    > $worksheet1->write(0,2,$balance);
    >
    > when $lastname, $platform and $balance are the results from the
    > fetchrow()...
    > I can print these results OUT to a textfile, so I know they contain
    > data, but it just doesn't work with the spreadsheet.
    >


    Could you please show us the code you use to populate these variables,
    and to print them out successfully? That would help a lot...


    DS
     
    David Squire, Sep 25, 2006
    #2
    1. Advertising

  3. Paul Lalli Guest

    wrote:

    > I am grabbing database information using fetchrow() and storing the
    > results in variables. I need to write these results to an excel
    > spreadsheet. Does worksheet->write work when you're using variables?


    ARRG!! I just responded to this in perl.beginners, not realizing you'd
    posted an identical copy of the same message to another group. PLEASE
    DON'T DO THAT! If you *NEED* to post to more than one group,
    crosspost, do not multi-post!!

    http://groups.google.com/group/perl...5f7816a8127/5a89cd83b68a7bc8#5a89cd83b68a7bc8

    Paul Lalli
     
    Paul Lalli, Sep 25, 2006
    #3
  4. Guest

    AHHH! My apologies!!! I wasn't aware it'd be a problem.

    OK, well the script now writes to the spreadsheet, but it will only
    write one line. I am reading in data from a text file and need the
    script to write one line to the spreadsheet per line in the text file
    based on information in a database. Here is the code:

    #open file
    $filename = shift;
    open(GR,"$filename") or die("Unable to open file");
    @sub =<GR>;
    close(GR);


    #FOR EACH RECORD IN TEXT FILE...
    foreach $record (@sub)
    {
    chop($record);
    $sub = uc($record);


    $sql = "query";

    $sth= $alloc_dbh->prepare($sql_psc_rachel);
    $sth->execute();
    while (($masterNum, $subNum, $platform, $machine,
    $lastAlloc, $lastAllocDate, $chargeID,
    $lastname, $balance) = $sth->fetchrow())
    {
    print OUT ("PSC Data: $masterNum, $subNum,
    $platform, $machine, $lastAlloc, $lastAllocDate,
    $chargeID, $lastname, $balance\n");


    #TGCDB info
    %tgdata=getTGData();

    $start = $tgdata{"$chargeID $platform
    AllocData"};
    $alloc = $tgdata{"$chargeID $platform
    Alloc"};
    $remaining = $tgdata{"$chargeID
    $platform Remaining"};
    print OUT ("data: $chargeID, Start
    $start, Alloc $alloc, Remaining
    $remaining\n");

    my $row = 1;

    $worksheet1->write($row, 0,
    $masterNum);
    $worksheet1->write($row, 1,
    $subNum);
    $worksheet1->write($row, 2,
    $platform);
    $worksheet1->write($row, 3,
    $machine);
    $worksheet1->write($row, 4,
    $lastAlloc);
    $worksheet1->write($row, 5,
    $alloc);
    $worksheet1->write($row, 6,
    $lastAllocDate);
    $worksheet1->write($row, 7,
    $start);
    $worksheet1->write($row, 8,
    $chargeID);
    $worksheet1->write($row, 9,
    $balance);
    $worksheet1->write($row, 10,
    $remaining);
    $worksheet1->write($row, 11,
    $lastname);
    $row++;

    }

    $sth->finish();
    }

    Can anyone see a problem?

    Thanks,
    Courtney


    Paul Lalli wrote:
    > wrote:
    >
    > > I am grabbing database information using fetchrow() and storing the
    > > results in variables. I need to write these results to an excel
    > > spreadsheet. Does worksheet->write work when you're using variables?

    >
    > ARRG!! I just responded to this in perl.beginners, not realizing you'd
    > posted an identical copy of the same message to another group. PLEASE
    > DON'T DO THAT! If you *NEED* to post to more than one group,
    > crosspost, do not multi-post!!
    >
    > http://groups.google.com/group/perl...5f7816a8127/5a89cd83b68a7bc8#5a89cd83b68a7bc8
    >
    > Paul Lalli
     
    , Sep 25, 2006
    #4
  5. David Squire Guest

    wrote:

    [Top-posting corrected. Please don't do that. Please *do* read the
    posting guidelines for this group, that are posted here twice weekly.]

    > Paul Lalli wrote:
    >> wrote:
    >>
    >>> I am grabbing database information using fetchrow() and storing the
    >>> results in variables. I need to write these results to an excel
    >>> spreadsheet. Does worksheet->write work when you're using variables?

    >> ARRG!! I just responded to this in perl.beginners, not realizing you'd
    >> posted an identical copy of the same message to another group. PLEASE
    >> DON'T DO THAT! If you *NEED* to post to more than one group,
    >> crosspost, do not multi-post!!
    >>
    >> http://groups.google.com/group/perl...5f7816a8127/5a89cd83b68a7bc8#5a89cd83b68a7bc8
    >>

    > AHHH! My apologies!!! I wasn't aware it'd be a problem.
    >
    > OK, well the script now writes to the spreadsheet, but it will only
    > write one line. I am reading in data from a text file and need the
    > script to write one line to the spreadsheet per line in the text file
    > based on information in a database. Here is the code:
    >


    Missing:

    use strict;
    use warnings;

    Including those at the top of every script will catch many problems
    before they lead you here.

    > #open file
    > $filename = shift;
    > open(GR,"$filename") or die("Unable to open file");


    Would be better as:

    open my $GR, '<', $filename or die "Unable to open file $filename: $!";

    - you don't need to quote variables
    - lexically scoped filehandles are nicer
    - the three-argument form of open is safer (see perldoc -f open)
    - it's nice to have an informative error message

    > @sub =<GR>;
    > close(GR);
    >
    >
    > #FOR EACH RECORD IN TEXT FILE...
    > foreach $record (@sub) {


    Why do you slurp in the whole contents of the file when you only need
    one line at a time? This wastes memory. It would be better as:

    while (my $record = <GR>) {

    > chop($record);


    You almost certainly want 'chomp' here, not 'chop'.

    > $sub = uc($record);


    This variable never gets used in the script you show. What is it for?
    Please post minimal, *complete*, scripts.

    >
    >
    > $sql = "query";
    >
    > $sth= $alloc_dbh->prepare($sql_psc_rachel);


    Where did these mystery variables $alloc_dbh and $sql_psc_rachel come
    from? There are too many unknowables for us in the script fragment you
    have posted.

    [snip]

    Please work on reducing your script to a minimal version that produces
    no errors or warnings when 'use strict;' and 'use warnings;' are in
    effect, yet still exhibits the problem. This exercise might even allow
    you to find the problem.


    DS
     
    David Squire, Sep 25, 2006
    #5
  6. Paul Lalli Guest

    wrote:
    > AHHH! My apologies!!! I wasn't aware it'd be a problem.


    Another problem is that you're top-posting. Please stop that. Trim
    your quoted material down to the smallest relevant bits, and
    intersperce your comments as appropriate.

    > OK, well the script now writes to the spreadsheet, but it will only
    > write one line. I am reading in data from a text file and need the
    > script to write one line to the spreadsheet per line in the text file
    > based on information in a database. Here is the code:
    >
    > #open file
    > $filename = shift;


    Are you using strict and warnings? If not, please start. They catch
    99% of the errors programmers make.

    > open(GR,"$filename") or die("Unable to open file");


    1) Do not double-quote variables without reason. See also: perldoc -q
    quoting
    2) Use lexical filehandles, not global barewords (they are subject to
    strict, they're not global, and they auto-close when they go out of
    scope)
    3) Use the three-argument form of open
    4) State the *reason* the open failed if it does:

    open my $GR, '<', $filename or die "Cannot open file: $!";

    > @sub =<GR>;
    > close(GR);
    >
    > #FOR EACH RECORD IN TEXT FILE...
    > foreach $record (@sub)


    There is absolutely no reason to read in the entire file into memory
    and keep it there for the duration of this loop. Instead, read one
    line at a time. At each iteration, discard the previously read line
    and read the next:

    while (my $record = <$GR>) {

    > {
    > chop($record);


    chop() is almost entirely a holdover from Perl 4. The new standard
    idiom is chomp(). (What would happen if your text file happened to not
    end with a newline?)

    chomp $record;

    > $sub = uc($record);
    > $sql = "query";
    > $sth= $alloc_dbh->prepare($sql_psc_rachel);


    Where did any of these variables come from?

    > $sth->execute();
    > while (($masterNum, $subNum, $platform, $machine,
    > $lastAlloc, $lastAllocDate, $chargeID,
    > $lastname, $balance) = $sth->fetchrow())
    > {
    > print OUT ("PSC Data: $masterNum, $subNum,
    > $platform, $machine, $lastAlloc, $lastAllocDate,
    > $chargeID, $lastname, $balance\n");


    When did the OUT filehandle get declared?

    > #TGCDB info
    > %tgdata=getTGData();
    >
    > $start = $tgdata{"$chargeID $platform
    > AllocData"};
    > $alloc = $tgdata{"$chargeID $platform
    > Alloc"};
    > $remaining = $tgdata{"$chargeID
    > $platform Remaining"};
    > print OUT ("data: $chargeID, Start
    > $start, Alloc $alloc, Remaining
    > $remaining\n");
    >
    > my $row = 1;


    Here you declare a brand new variable, within this loop. It does not
    exist before this line, nor after this iteration of the loop ends.
    >
    > $worksheet1->write($row, 0,
    > $masterNum);


    Here (and for 10 more nearly identical lines), you use the $row
    variable that you just declared.

    > $row++;


    Here you increment this variable...

    > }


    .... but here, that variable goes out of scope. The next time through
    the loop, a new $row is declared and initialized to 1. No piece of
    code ever uses $row when it is any value other than 1.

    Move your declaration of $row outside the loop.

    Paul Lalli
     
    Paul Lalli, Sep 25, 2006
    #6
  7. J. Gleixner Guest

    wrote:
    > AHHH! My apologies!!! I wasn't aware it'd be a problem.


    What would be a problem??

    >
    > OK, well the script now writes to the spreadsheet, but it will only
    > write one line. I am reading in data from a text file and need the
    > script to write one line to the spreadsheet per line in the text file
    > based on information in a database. Here is the code:


    use strict;
    use warnings;

    >
    > #open file
    > $filename = shift;
    > open(GR,"$filename") or die("Unable to open file");
    > @sub =<GR>;
    > close(GR);
    >
    >
    > #FOR EACH RECORD IN TEXT FILE...
    > foreach $record (@sub)
    > {
    > chop($record);
    > $sub = uc($record);
    >
    >
    > $sql = "query";
    >
    > $sth= $alloc_dbh->prepare($sql_psc_rachel);


    No idea what "$sql_psc_rachel" contains, however this could probably be
    outside of the for loop.

    > $sth->execute();
    > while (($masterNum, $subNum, $platform, $machine,
    > $lastAlloc, $lastAllocDate, $chargeID,
    > $lastname, $balance) = $sth->fetchrow())
    > {


    > my $row = 1;


    $row will always be 1.

    > }
    >
    > $sth->finish();
    > }
    >
    > Can anyone see a problem?
     
    J. Gleixner, Sep 25, 2006
    #7
  8. In article <>,
    <> wrote:
    >OK, well the script now writes to the spreadsheet, but it will only
    >write one line. I am reading in data from a text file and need the
    >script to write one line to the spreadsheet per line in the text file
    >based on information in a database. Here is the code:


    [ much snippage below ]

    > while (($masterNum, $subNum, $platform, $machine,
    >$lastAlloc, $lastAllocDate, $chargeID,
    > $lastname, $balance) = $sth->fetchrow())
    > {
    > my $row = 1;
    >
    > $worksheet1->write($row, 0,
    >$masterNum);
    > $row++;
    > }
    >
    >Can anyone see a problem?


    You're resetting $row to 1 on every trip through the while() loop.

    The "my $row = 1" needs to be moved outside of all the loops
    that write to the same sheet.

    Gary Ansok
    --
    The recipe says "toss lightly," but I suppose that depends
    on how much you eat and how bad the cramps get. - J. Lileks
     
    Gary E. Ansok, Sep 25, 2006
    #8
  9. Guest

    As you can probably gather...this is a temporary gig for me.

    Thanks for your responses.

    Paul Lalli wrote:
    > wrote:
    > > AHHH! My apologies!!! I wasn't aware it'd be a problem.

    >
    > Another problem is that you're top-posting. Please stop that. Trim
    > your quoted material down to the smallest relevant bits, and
    > intersperce your comments as appropriate.
    >
    > > OK, well the script now writes to the spreadsheet, but it will only
    > > write one line. I am reading in data from a text file and need the
    > > script to write one line to the spreadsheet per line in the text file
    > > based on information in a database. Here is the code:
    > >
    > > #open file
    > > $filename = shift;

    >
    > Are you using strict and warnings? If not, please start. They catch
    > 99% of the errors programmers make.
    >
    > > open(GR,"$filename") or die("Unable to open file");

    >
    > 1) Do not double-quote variables without reason. See also: perldoc -q
    > quoting
    > 2) Use lexical filehandles, not global barewords (they are subject to
    > strict, they're not global, and they auto-close when they go out of
    > scope)
    > 3) Use the three-argument form of open
    > 4) State the *reason* the open failed if it does:
    >
    > open my $GR, '<', $filename or die "Cannot open file: $!";
    >
    > > @sub =<GR>;
    > > close(GR);
    > >
    > > #FOR EACH RECORD IN TEXT FILE...
    > > foreach $record (@sub)

    >
    > There is absolutely no reason to read in the entire file into memory
    > and keep it there for the duration of this loop. Instead, read one
    > line at a time. At each iteration, discard the previously read line
    > and read the next:
    >
    > while (my $record = <$GR>) {
    >
    > > {
    > > chop($record);

    >
    > chop() is almost entirely a holdover from Perl 4. The new standard
    > idiom is chomp(). (What would happen if your text file happened to not
    > end with a newline?)
    >
    > chomp $record;
    >
    > > $sub = uc($record);
    > > $sql = "query";
    > > $sth= $alloc_dbh->prepare($sql_psc_rachel);

    >
    > Where did any of these variables come from?
    >
    > > $sth->execute();
    > > while (($masterNum, $subNum, $platform, $machine,
    > > $lastAlloc, $lastAllocDate, $chargeID,
    > > $lastname, $balance) = $sth->fetchrow())
    > > {
    > > print OUT ("PSC Data: $masterNum, $subNum,
    > > $platform, $machine, $lastAlloc, $lastAllocDate,
    > > $chargeID, $lastname, $balance\n");

    >
    > When did the OUT filehandle get declared?
    >
    > > #TGCDB info
    > > %tgdata=getTGData();
    > >
    > > $start = $tgdata{"$chargeID $platform
    > > AllocData"};
    > > $alloc = $tgdata{"$chargeID $platform
    > > Alloc"};
    > > $remaining = $tgdata{"$chargeID
    > > $platform Remaining"};
    > > print OUT ("data: $chargeID, Start
    > > $start, Alloc $alloc, Remaining
    > > $remaining\n");
    > >
    > > my $row = 1;

    >
    > Here you declare a brand new variable, within this loop. It does not
    > exist before this line, nor after this iteration of the loop ends.
    > >
    > > $worksheet1->write($row, 0,
    > > $masterNum);

    >
    > Here (and for 10 more nearly identical lines), you use the $row
    > variable that you just declared.
    >
    > > $row++;

    >
    > Here you increment this variable...
    >
    > > }

    >
    > ... but here, that variable goes out of scope. The next time through
    > the loop, a new $row is declared and initialized to 1. No piece of
    > code ever uses $row when it is any value other than 1.
    >
    > Move your declaration of $row outside the loop.
    >
    > Paul Lalli
     
    , Sep 25, 2006
    #9
  10. Ben Morrow Guest

    Quoth "Paul Lalli" <>:
    >
    > chop() is almost entirely a holdover from Perl 4. The new standard
    > idiom is chomp(). (What would happen if your text file happened to not
    > end with a newline?)


    More importantly, what would happen if you were on win32, or doing
    socket programming, and your eol sequence was "\r\n"?

    Ben

    --
    Outside of a dog, a book is a man's best friend.
    Inside of a dog, it's too dark to read.
    Groucho Marx
     
    Ben Morrow, Sep 25, 2006
    #10
    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. Fabio

    Spreadsheet::WriteExcel

    Fabio, Mar 2, 2004, in forum: Perl
    Replies:
    2
    Views:
    881
    Tom Niesytto
    Mar 4, 2004
  2. Erica
    Replies:
    2
    Views:
    104
    John M. Gamble
    Sep 5, 2003
  3. Pam
    Replies:
    7
    Views:
    331
  4. Katja
    Replies:
    1
    Views:
    217
  5. Justin C
    Replies:
    4
    Views:
    183
    Peter J. Holzer
    Apr 22, 2011
Loading...

Share This Page