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:
Excel help

 



rekcut01
Novice

May 1, 2014, 2:20 PM

Post #1 of 10 (4122 views)
Excel help Can't Post

Hello Perl experts,

I'm writing this code and wanting to append to an existing Excel spreadsheet but I keep getting an error on this block of code:

# Write data to some cells
my $template->AddCell(0, $row, $col, 1, $format);
my $template->AddCell(0, $row+1, $col, "Hello", $format);

and the error is:

Can't call method "AddCell" on an undefined value at C:/Program Files (x86)/Apache Group/Apache2/cgi-bin/sqlconfig.pl line 65.

can someone more experienced in this give me a hint as to what I'm doing wrong?

Thanks

Rick



#!c:/strawberry/perl/bin/perl.exe
# PERL MODULES WE WILL BE USING
use strict;
use warnings;
use CGI;
use Win32::OLE;
use DBI;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel::SaveParser;

print "Content-type: text/html\n\n";
# Config DB variables
our $q = new CGI;

#Get the parameter from your html form.
my $TxtName=$q->param('TxtName');
my $TxtOrg=$q->param('TxtOrg');
my $TxtCit=$q->param('TxtCit');
my $TxtHPEmp=$q->param('TxtHPEmp');
my $TxtEscort=$q->param('TxtEscort');
my $now_string = localtime;

if ($TxtName eq "")
{
print "Error: Must enter Name\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtOrg eq "")
{
print "Error: Must enter Organization\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtCit eq "")
{
print "Error: Must enter Citizenship\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtHPEmp eq "")
{
print "Error: Must Confirm HP Employment\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtEscort eq "")
{
print "Error: Must enter Escort Name\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
else
{
# Open the template with SaveParser
my $parser = new Spreadsheet::ParseExcel::SaveParser;
my $template = $parser->Parse('/users/rtucker/desktop/Report.xls');

my $sheet = 0;
my $row = 0;
my $col = 0;

# Get the format from the cell
my $format = $template->{Worksheet}[$sheet]
->{Cells}[$row][$col]
->{FormatNo};


# Write data to some cells
my $template->AddCell(0, $row, $col, 1, $format);
my $template->AddCell(0, $row+1, $col, "Hello", $format);

# Add a new worksheet
$template->AddWorksheet('Visitors');

my $workbook;
{
# SaveAs generates a lot of harmless warnings about unset
# Worksheet properties. You can ignore them if you wish.
local $^W = 0;

# Rewrite the file or save as a new file
$workbook = $template->SaveAs('/users/rtucker/desktop/Report.xls');
}

# Use Spreadsheet::WriteExcel methods


my $worksheet = $workbook->sheets(0);

$worksheet->write($row+2, $col, "World2");

$workbook->close();
}


FishMonger
Veteran / Moderator

May 1, 2014, 2:24 PM

Post #2 of 10 (4120 views)
Re: [rekcut01] Excel help [In reply to] Can't Post


Quote
my $template->AddCell(0, $row, $col, 1, $format);
my $template->AddCell(0, $row+1, $col, "Hello", $format);

Take the 'my' keyword off of those to lines and see if you still get the same error.


rekcut01
Novice

May 2, 2014, 8:16 AM

Post #3 of 10 (3808 views)
Re: [FishMonger] Excel help [In reply to] Can't Post

Thank you, I tried that and it didn't work so I tried a different example, here is a copy of my code as it stands now.

print "Content-type: text/html\n\n";
# Config DB variables
our $q = new CGI;

#Get the parameter from your html form.
my $TxtName=$q->param('TxtName');
my $TxtOrg=$q->param('TxtOrg');
my $TxtCit=$q->param('TxtCit');
my $TxtHPEmp=$q->param('TxtHPEmp');
my $TxtEscort=$q->param('TxtEscort');
my $now_string = localtime;

if ($TxtName eq "")
{
print "Error: Must enter Name\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtOrg eq "")
{
print "Error: Must enter Organization\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtCit eq "")
{
print "Error: Must enter Citizenship\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtHPEmp eq "")
{
print "Error: Must Confirm HP Employment\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtEscort eq "")
{
print "Error: Must enter Escort Name\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
else
{

# Open an existing file with SaveParser
my $parser = Spreadsheet::ParseExcel::SaveParser->new();
my $workbook = $parser->Parse('/users/rtucker/desktop/Report.xls');



# Get the first worksheet.
my $worksheet = $workbook->worksheet(0); <--------- it fails right here and say its an undefined value, I don't see how this is undefined? do you see what I'm doing wrong?
my $row = 0;
my $col = 0;


# Overwrite the string in cell A1
$worksheet->AddCell( $row, $col, 'New string' );


# Add a new string in cell B1
$worksheet->AddCell( $row, $col + 1, 'Newer' );


# Add a new string in cell C1 with the format from cell A3.
my $cell = $worksheet->get_cell( $row + 2, $col );
my $format_number = $cell->{FormatNo};

$worksheet->AddCell( $row, $col + 2, 'Newest', $format_number );


# Write over the existing file or write a new file.
$workbook->SaveAs('/users/rtucker/desktop/Report.xls');


FishMonger
Veteran / Moderator

May 2, 2014, 8:42 AM

Post #4 of 10 (3796 views)
Re: [rekcut01] Excel help [In reply to] Can't Post


Quote
my $worksheet = $workbook->worksheet(0); <--------- it fails right here and say its an undefined value, I don't see how this is undefined? do you see what I'm doing wrong?


That means $workbook is undefined and that would happen if this prior statement failed to parse the file.

Code
my $workbook = $parser->Parse('/users/rtucker/desktop/Report.xls');


Add this between those 2 statements.

Code
 if ( !defined $workbook ) { 
die $parser->error(), ".\n";
}



(This post was edited by FishMonger on May 2, 2014, 8:43 AM)


rekcut01
Novice

May 2, 2014, 9:04 AM

Post #5 of 10 (3788 views)
Re: [FishMonger] Excel help [In reply to] Can't Post

BINGO!! That was the problem!!! Thank you so much! But how do I tell the parser where exactly the file is that I want to parse?



Thanks so much!

Rick Tucker


FishMonger
Veteran / Moderator

May 2, 2014, 9:22 AM

Post #6 of 10 (3782 views)
Re: [rekcut01] Excel help [In reply to] Can't Post

You did specify the full path to the file, however you had a typo.

*nix systems are case sensitive so your correct path is probably: '/users/rtucker/Desktop/Report.xls'

Note the capital D where you had used a lowercase d. All *nix systems I've used where I used the desktop spelled the Desktop with a capitol D.


(This post was edited by FishMonger on May 2, 2014, 9:24 AM)


rekcut01
Novice

May 7, 2014, 8:08 AM

Post #7 of 10 (3698 views)
Re: [FishMonger] Excel help [In reply to] Can't Post

That worked thank you!!! if you don't mind I'm having another error maybe you can point me in the right direction please?

Here is my code now:


Code
#!c:/strawberry/perl/bin/perl.exe 
# PERL MODULES WE WILL BE USING
use strict;
use warnings;
use CGI;
use Win32::OLE;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::ParseExcel::SaveParser::Workbook;
use Spreadsheet::ParseExcel::Worksheet;
use Spreadsheet::ParseExcel::Workbook;
use Spreadsheet::WriteExcel::Format;
use Spreadsheet::WriteExcel::OLEwriter;
use Spreadsheet::WriteExcel::Properties ':property_sets';

print "Content-type: text/html\n\n";
# Config DB variables
our $q = new CGI;

#Get the parameter from your html form.
my $TxtName=$q->param('TxtName');
my $TxtOrg=$q->param('TxtOrg');
my $TxtCit=$q->param('TxtCit');
my $TxtHPEmp=$q->param('TxtHPEmp');
my $TxtEscort=$q->param('TxtEscort');
my $now_string = localtime;

if ($TxtName eq "")
{
print "Error: Must enter Name\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtOrg eq "")
{
print "Error: Must enter Organization\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtCit eq "")
{
print "Error: Must enter Citizenship\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtHPEmp eq "")
{
print "Error: Must Confirm HP Employment\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
elsif ($TxtEscort eq "")
{
print "Error: Must enter Escort Name\n";
print "<input type='button' value='Back' onclick='javascript:history.back(1);' />";
}
else
{


# Open an existing file with SaveParser
# my $filename = '/Users/Rtucker/Desktop/vBadge.xls';
#my $parser2 = Spreadsheet::ParseExcel::SaveParser->new('/Users/Rtucker/Desktop/template.xls');
my $parser2 = Spreadsheet::ParseExcel::SaveParser->new();
my $workbook2 = $parser2->Parse('/Users/Rtucker/Desktop/vBadge.xls');

if ( !defined $workbook2 ) {
die $parser2->error(), ".\n";
}

## Get the first worksheet.
my $worksheet2 = $workbook2->worksheet(0);
#my $format2 = $workbook2->add_format(font => 'New Times Roman', align => 'center', size => '20', bold => 1, num_format => 'mm/dd/yy');
my $format2 = $workbook2->add_format(); <---- FAILS RIGHT HERE
$format2->set_align('center');
$format2->set_align('vcenter');

my ( $row_min1, $row_max1 ) = $worksheet2->row_range();
my $bRow = 0;
my $bCol = 0;
my $cell1 = $worksheet2->get_cell( $bRow, $bCol );

#my $format_number1 = $cell1->{FormatNo};
$worksheet2->AddCell( $bRow, $bCol, $TxtName,$format2 );
$worksheet2->AddCell( $bRow + 1, $bCol, $TxtOrg );
#$worksheet2->AddCell( $bRow + 2, $bCol, $now_string, $date_format );
$worksheet2->AddCell( $bRow + 2, $bCol, $now_string );

# Write over the existing file or write a new file.
$workbook2->SaveAs('/Users/Rtucker/Desktop/vBadge.xls');


the problem I'm having is its having an error on add_format, can't find the method. I have tried changing libraries and even to the 'addformat()' and it still fails. Is there an obvious error that I'm over looking?

As always, thank you for your help

Rick


(This post was edited by FishMonger on May 7, 2014, 8:17 AM)


FishMonger
Veteran / Moderator

May 7, 2014, 8:51 AM

Post #8 of 10 (3671 views)
Re: [rekcut01] Excel help [In reply to] Can't Post

Please use the code tags!

You should always post the full error message.

As I mentioned in my prior post, Perl is case sensitive. You need to remember that fact.

The answer to your problem is very clear once you read the documentation for the module that you're using.


Code
my $format2 = $workbook2->add_format();

Should be:

Code
my $format2 = $workbook2->AddFormat();


The difference being that add_format() is a method in the Spreadsheet::WriteExcel module whereas AddFormat() is a method in the Spreadsheet::ParseExcel::SaveParser module. Since your $workbook2 object is derived from the Spreadsheet::ParseExcel::SaveParser module, you should use its format method, not the format method from Spreadsheet::WriteExcel.

On a side note, you don't need to explicitly load these modules:

Code
use Spreadsheet::WriteExcel::Format; 
use Spreadsheet::WriteExcel::OLEwriter;
use Spreadsheet::WriteExcel::Properties ':property_sets';

They will be loaded via this statement:

Code
use Spreadsheet::WriteExcel;


I have not tested it but I believe these modules:

Code
use Spreadsheet::ParseExcel::SaveParser;  
use Spreadsheet::ParseExcel::SaveParser::Workbook;
use Spreadsheet::ParseExcel::Worksheet;
use Spreadsheet::ParseExcel::Workbook;

will be loaded via

Code
use Spreadsheet::ParseExcel;



rekcut01
Novice

May 8, 2014, 10:20 AM

Post #9 of 10 (3424 views)
Re: [FishMonger] Excel help [In reply to] Can't Post

Thanks a BUNCH FishMonger that worked!

Now I'm trying to generate a barcode to my security badges but my strawberry perl does not have the libraries, do you know where I can find the libraries at?

As always thank you!!!

Rick


FishMonger
Veteran / Moderator

May 8, 2014, 10:30 AM

Post #10 of 10 (3418 views)
Re: [rekcut01] Excel help [In reply to] Can't Post

I have not used any of the barcode modules, but you can search through this list.
http://search.cpan.org/search?query=barcode&mode=all

It would be best to limit your choice to the newer modules.


(This post was edited by FishMonger on May 8, 2014, 10:32 AM)

 
 


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

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