CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Spreadsheet::ParseExcel::SaveParser issue

 



etspaceman
New User

Mar 11, 2011, 12:57 PM

Post #1 of 3 (2330 views)
Spreadsheet::ParseExcel::SaveParser issue Can't Post

First time poster here; if this post doesn't follow forum rules, I apologize. Let me know and I'll correct that in the future.

I'm trying to take some information that is parsed from a CSV file, and use that to append an Excel File. To do this, I'm using Spreadsheet::ParseExcel::SaveParser, but after countless hours of debugging, I can't seem to get this to work. The CSV Parser should work; I've used this same loop in some of my other code. My code is as follows (forgive me for not implementing "strict", I know it's a bad practice, I'm going to put that in later):


Code
 
use Text::CSV;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Utility;



($date) = $ARGV[0];

$csv = Text::CSV->new();
$parser = Spreadsheet::ParseExcel::SaveParser->new();
$CallBackStats = $parser->Parse("Z:\\Solution Center\\carrants\\Statistics\\Callback Statistics.xls");


open(CSV, "<", "CallBackUpdate.csv");

@Callbackers = ();
@ClosedBy = ();
@Contacted = ();

$i = 0;

while(<CSV>){

if($csv->parse($_)){

@CallBackUpdate = $csv->fields();
$TotalAssigned = $CallBackUpdate[1];
$TotalClosed = $CallBackUpdate[4];
$Callbackers[$i] = $CallBackUpdate[0];
$ClosedBy[$i] = $CallBackUpdate[2];
$Contacted[$i] = $CallBackUpdate[3];

$Callbackers[$i]=~s/\s+//g;
$ClosedBy[$i]=~s/\s+//g;
$Contacted[$i]=~s/\s+//g;
$TotalAssigned=~s/\s+//g;
$TotalClosed=~s/\s+//g;
}
else{
$err = $csv->error_input;
print "Failed to parse line: $err";
}

$i++;

}

close CSV;

$StatsByDay = $CallBackStats->worksheet('Stats By Day');

$isTrue = 0;
$row = 0;

while($isTrue == 0){

my $cell = $StatsByDay->get_cell($row, 0);

if($cell == undef){
$isTrue = 1;
}

$row++;
}


$cellOne = $StatsByDay->{Cells}[1][0];
$cellTwo = $StatsByDay->{Cells}[1][1];
$cellThree = $StatsByDay->{Cells}[1][2];
$cellFour = $StatsByDay->{Cells}[1][3];
$cellFive = $StatsByDay->{Cells}[1][5];
$cellSix = $StatsByDay->{Cells}[1][7];
$excelDate = xl_parse_date($date);


$StatsByDay->AddCell(1, $row, 0, $excelDate, $cellOne);
$StatsByDay->AddCell(1, $row, 1, $Callbackers[0], $cellTwo);
$StatsByDay->AddCell(1, $row, 2, $TotalAssigned, $cellThree);
$StatsByDay->AddCell(1, $row, 3, $ClosedBy[0], $cellFour);
$StatsByDay->AddCell(1, $row, 5, $Contacted[0], $cellFive);
$StatsByDay->AddCell(1, $row, 7, $TotalClosed, $cellSix);

$row++;

$StatsByDay->AddCell(1, $row, 0, $excelDate, $cellOne);
$StatsByDay->AddCell(1, $row, 1, $Callbackers[1], $cellTwo);
$StatsByDay->AddCell(1, $row, 2, $TotalAssigned, $cellThree);
$StatsByDay->AddCell(1, $row, 3, $ClosedBy[1], $cellFour);
$StatsByDay->AddCell(1, $row, 5, $Contacted[1], $cellFive);
$StatsByDay->AddCell(1, $row, 7, $TotalClosed, $cellSix);

$CallBackStats->SaveAs('Callback Statistics.xls');


I'm attaching a sample of the CSV/Excel files that would be used for this as well.

The errors I get are as follows:


Quote
Use of uninitialized value in hash element at C:/strawberry/perl/site/lib/Spread
sheet/ParseExcel.pm line 2087, <DATA> line 496.
Use of uninitialized value $iFmtIdx in hash element at C:/strawberry/perl/site/l
ib/Spreadsheet/ParseExcel/FmtDefault.pm line 89, <DATA> line 496.
Use of uninitialized value $iFmtIdx in hash element at C:/strawberry/perl/site/l
ib/Spreadsheet/ParseExcel/FmtDefault.pm line 94, <DATA> line 496.
Argument "CallBackAssignee" isn't numeric in addition (+) at C:/strawberry/perl/
site/lib/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 382, <DATA> line 496
.
Argument "ClosedbyCallbackperson" isn't numeric in addition (+) at C:/strawberry
/perl/site/lib/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 382, <DATA> li
ne 496.
Argument "ContactedbyCallbackperson" isn't numeric in addition (+) at C:/strawbe
rry/perl/site/lib/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 382, <DATA>
line 496.
Can't call method "AddCell" on an undefined value at toCallBackStats.pl line 77,
<DATA> line 496.
Press any key to continue . . .



(This post was edited by etspaceman on Mar 17, 2011, 10:53 AM)
Attachments: CallBackUpdate.csv (0.13 KB)
  Callback Statistics.xls (45.0 KB)


etspaceman
New User

Mar 17, 2011, 10:39 AM

Post #2 of 3 (2304 views)
Re: [etspaceman] Spreadsheet::ParseExcel::SaveParser issue [In reply to] Can't Post

Bump - would love some insight here.


Karazam
User

Mar 17, 2011, 2:36 PM

Post #3 of 3 (2300 views)
Re: [etspaceman] Spreadsheet::ParseExcel::SaveParser issue [In reply to] Can't Post

Ok, I've never used these module, but had a look. I found one line that needs changing:


Code
# if ( $cell == undef ) {  # == is numerical comparison 
if ( not $cell ) {


After that I get the following errors:


Quote
$ ./excel.pl today
Argument "CallBackAssignee" isn't numeric in addition (+) at /Library/Perl/5.10.0/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 382.
Argument "ClosedbyCallbackperson" isn't numeric in addition (+) at /Library/Perl/5.10.0/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 382.
Argument "ContactedbyCallbackperson" isn't numeric in addition (+) at /Library/Perl/5.10.0/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 382.
Argument "netid1" isn't numeric in addition (+) at /Library/Perl/5.10.0/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 382.


Line 382 in that file belongs to the AddCell method. Looking at the docs at CPAN, it seems to me the syntax for calling it is a bit different than what you use.


Code
AddCell($row, $col, $value, $format [$encoding]); # CPAN version 
AddCell( 1, $row, 7, $TotalClosed, $cellSix ); # your version


Hope this helps. Smile


(This post was edited by Karazam on Mar 17, 2011, 11:15 PM)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives