Home > Archive > PERL Miscellaneous > September 2006 > Spreadsheet::WriteExcel & worksheet->write
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Spreadsheet::WriteExcel & worksheet->write
|
|
| courtney.machi@gmail.com 2006-09-25, 6:59 pm |
| 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
| |
| David Squire 2006-09-25, 6:59 pm |
| courtney.machi@gmail.com 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
| |
| Paul Lalli 2006-09-25, 6:59 pm |
| courtney.machi@gmail.com 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...a89cd83b68a7bc8
Paul Lalli
| |
| courtney.machi@gmail.com 2006-09-25, 6:59 pm |
| 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:
> courtney.machi@gmail.com wrote:
>
>
> 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...a89cd83b68a7bc8
>
> Paul Lalli
| |
| David Squire 2006-09-25, 6:59 pm |
| courtney.machi@gmail.com wrote:
[Top-posting corrected. Please don't do that. Please *do* read the
posting guidelines for this group, that are posted here twice w ly.]
> Paul Lalli wrote:
> 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
| |
|
| courtney.machi@gmail.com wrote:
> 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:
>
[snip]
>
>
> #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;
You are reseting $row back to 1 for each fetchrow().
>
> $worksheet1->write($row, 0,
> $masterNum);
> $worksheet1->write($row, 1,
> $subNum);
[snip]
> $remaining);
> $worksheet1->write($row, 11,
> $lastname);
> $row++;
>
> }
>
> $sth->finish();
> }
>
> Can anyone see a problem?
>
> Thanks,
> Courtney
--
Len
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com
| |
| Paul Lalli 2006-09-25, 6:59 pm |
| courtney.machi@gmail.com 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
| |
| J. Gleixner 2006-09-25, 6:59 pm |
| courtney.machi@gmail.com 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?
| |
| Gary E. Ansok 2006-09-25, 6:59 pm |
| In article <1159207605.070706.172770@d34g2000cwd.googlegroups.com>,
<courtney.machi@gmail.com> 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
| |
| courtney.machi@gmail.com 2006-09-25, 6:59 pm |
| As you can probably gather...this is a temporary gig for me.
Thanks for your responses.
Paul Lalli wrote:
> courtney.machi@gmail.com wrote:
>
> 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.
>
>
> Are you using strict and warnings? If not, please start. They catch
> 99% of the errors programmers make.
>
>
> 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: $!";
>
>
> 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() 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;
>
>
> Where did any of these variables come from?
>
>
> When did the OUT filehandle get declared?
>
>
> 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.
>
> Here (and for 10 more nearly identical lines), you use the $row
> variable that you just declared.
>
>
> 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
| |
| Mumia W. (reading news) 2006-09-25, 6:59 pm |
| On 09/25/2006 01:06 PM, courtney.machi@gmail.com wrote:
> [...]
> #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;
>
I know nothing about Spreadsheet::WriteExcel, but it seems that you set
the row to "1" on each iteration of the loop. How can it write to any
row other than "1"?
> $worksheet1->write($row, 0,
> $masterNum);
> $worksheet1->write($row, 1,
> $subNum);
> $worksheet1->write($row, 2,
> $platform); [...]
HTH
--
paduille.4058.mumia.w@earthlink.net
| |
| Ben Morrow 2006-09-26, 6:59 pm |
|
Quoth "Paul Lalli" <mritty@gmail.com>:
>
> 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.
benmorrow@tiscali.co.uk Groucho Marx
|
|
|
|
|