perlquestion
finhagen
I have a script which queries a mysql 5.5 database which is working well. However, the output is more rigid than I would like - my output is informing a Spreadsheet::WriteExcel based report. What I would like is to assign each member of each row a variable so I can place the data in the spreadsheet cell of my choosing.
Here's the code snippet I am using:
<code>###################################################################################
#
# Account Contacts Tab
#
use strict;
use warnings;
use DBI;
use DBD::mysql;
use Spreadsheet::WriteExcel;
our $acnse;
our $emc;
my $worksheet1 = $workbook->add_worksheet('Contacts');
my $acnse = $dbh->prepare("SELECT a.`function`, b.`fname`,b.`lname`, b.`email`, b.`ophone`, b.`mobile`, b.`loffice` FROM `acct` a, `acnse` b WHERE a.`cid` ='BBYPRD' AND a.`eid` = b.`eid` order by function;");
$acnse->execute or die $acnse->errstr;
my $row = 7;
my $col = 1;
$worksheet1->write_row($row++,$col,['Role','First Name','Last Name','Email', 'Office Phone', 'Cell', 'Office'], $format_body1);
while(my @data = $acnse ->fetchrow_array)
{
$worksheet1->write_row($row++,$col,\@data);
}
$worksheet1->set_column(6, 2, 15);
$worksheet1->set_column(6, 3, 15);
$worksheet1->set_column(6, 4, 30);
$worksheet1->set_column(6, 5, 20);
$worksheet1->set_column(6, 6, 20);
$worksheet1->set_column(6, 7, 25);
my $emc = $dbh->prepare("SELECT a.`function`, b.`fname`, b.`lname`, b.`email`, b.`ophone`, b.`cphone`, b.`geo` FROM `acct` a, `emc` b WHERE a.`cid` ='BBYPRD' AND b.`eid` = a.`eid` order by function;");
$emc->execute or die $emc->errstr;
my $row = 21;
my $col = 1;
$worksheet1->write_row($row++,$col,['Role','First Name','Last Name','Email', 'Office Phone', 'Cell', 'Office'], $format_body1);
while(my @data = $emc ->fetchrow_array)
{
$worksheet1->write_row($row++,$col,\@data);
}
$worksheet1->write (0, 0, $acct, $format_title);
$worksheet1->write (0, 7, $ymd, $format_body);
$worksheet1->write (1, 0, 'Contact Report', $format_title3);
$worksheet1->write (5, 0, 'Accenture Contacts', $format_title3);
$worksheet1->write (20, 0, 'EMC Contacts', $format_title3);</code>
That works but the output includes the column header information and iterates one row at a time:
<code>
Contacts
Role First Name Last Name Email Office Phone Cell Office
*** xxxxx xxxxx xxxxxx 999-999-9999 Somwhere
*** xxxxx xxxxx xxxxxx 999-999-9999 Somwhere
*** xxxxx xxxxx xxxxxx 999-999-9999 Somwhere
*** xxxxx xxxxx xxxxxx 999-999-9999 Somwhere
*** xxxxx xxxxx xxxxxx 999-999-9999 Somwhere
</code>
I would prefer to capture the output into an array and use the array_name[0] to identify the output members and write them to the spreadsheet with Spreadsheet::WriteExcel format strings.
Thanks in advance for your wisdom.
<!-- Node text goes above. Div tags should contain sig only -->
<div class="pmsig"><div class="pmsig-712628">
Hagen Finley
Boulder, CO
</div></div>