Solved

Perl program to add 3 columns to an existing excel file and generate random values

Posted on 2007-11-20
32
474 Views
Last Modified: 2011-04-14
I have just started to learn perl so I am a complete novice.
I have a basic excel table(which will be exported to mySQL eventually).

At the moment the table has columns for title,author, description, category and imageurl.
I would like a perl program which would take the existing .xls file, open it, and add 3 new columns called ID, Price, and Currentstock, and to have each of these columns populated with random numerical (1-9999 for ID, 1-50 for Price, and 0-200 for Currentstock) for each title listed in the title column.

I hope I have explained clearly, and thanks for your help.
0
Comment
Question by:dchid
  • 16
  • 16
32 Comments
 
LVL 39

Expert Comment

by:Adam314
ID: 20323189
What OS will this be running on?  If windows: Is Excel installed on the computer?
0
 

Author Comment

by:dchid
ID: 20323228
At the moment I am working on XP/Vista, Perl is installed on this system as is Excel 2007.
I also have access to UNIX although this is limted and not available at present.
0
 
LVL 39

Accepted Solution

by:
Adam314 earned 250 total points
ID: 20323505
This assumes there are titles in row 1, and the data begins on row 2.  Also, that title is in column A, and that ID, Price, and Currentstock will be going into columns F, G, and H, respectively.

You should make a backup of your data first, in case this doesn't work as you want it to.


#!/usr/bin/perl -w

use strict;

use OLE;
 

my $xl_app = CreateObject OLE 'Excel.Application' || die $!;

$xl_app->{'Visible'} = 0;

my $workbook = $xl_app->Workbooks->Open('/temp_ee/test1.xls');

my $worksheet = $workbook->Worksheets(1);
 

$worksheet->Range("F1")->{Value} = 'ID';

$worksheet->Range("G1")->{Value} = 'Price';

$worksheet->Range("H1")->{Value} = 'Currentstock';
 

my $currow=2;

while($worksheet->Range("A$currow")->{Value}) {

	$worksheet->Range("F$currow")->{Value} = int(rand(9999)+1);

	$worksheet->Range("G$currow")->{Value} = int(rand(50)+1);

	$worksheet->Range("H$currow")->{Value} = int(rand(201));

	$currow++;

}
 

$workbook->Save();
 

$xl_app->ActiveWorkbook->Close(0);

$xl_app->Quit();

Open in new window

0
 

Author Comment

by:dchid
ID: 20323652
Thank you for that, although I have not got it working, Its probably something simple, I am currently getting the error
Can't call method "worksheets" on an undefined value at C:\.(path)\test.pl line 8

Sorry this is probably to with me being clueless at the moment for the most part when it comes to Perl.
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20323964
What do you have on line 7?
Did you update it to have the path to your file?
0
 

Author Comment

by:dchid
ID: 20324006
line 7 has been updated to the path to my workbook
my $workbook = $xl_app->Workbooks->Open('/book1.xls');
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20324060
Is that the correct path?

Replace line 7 with this:
my $workbook = $xl_app->Workbooks->Open('/temp_ee/test1.xls') or die "Could not open workbook: $!\n";
0
 

Author Comment

by:dchid
ID: 20324068
This is the full code used, I changed the path of workbook and the columns in which data will be generated
#!/usr/bin/perl -w

use strict;

use OLE;

 

my $xl_app = CreateObject OLE 'Excel.Application' || die $!;

$xl_app->{'Visible'} = 0;

my $workbook = $xl_app->Workbooks->Open('/book1.xls');

my $worksheet = $workbook->Worksheets(1);

 

$worksheet->Range("A1")->{Value} = 'ID';

$worksheet->Range("I1")->{Value} = 'Price';

$worksheet->Range("H1")->{Value} = 'Currentstock';

 

my $currow=2;

while($worksheet->Range("B$currow")->{Value}) {

	$worksheet->Range("A$currow")->{Value} = int(rand(9999)+1);

	$worksheet->Range("I$currow")->{Value} = int(rand(50)+1);

	$worksheet->Range("H$currow")->{Value} = int(rand(201));

	$currow++;

}

 

$workbook->Save();

 

$xl_app->ActiveWorkbook->Close(0);

$xl_app->Quit();

Open in new window

0
 

Author Comment

by:dchid
ID: 20324089
I replaced the line, and it is telling me it could not open workbook
The workbook is currently in the same directory as the perl program, does it need to be in a seperate directory?
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20328128
If it is in the same directory, don't put a slash before the filename.

Line 7 should be:
my $workbook = $xl_app->Workbooks->Open('book1.xls') or die "Could not open workbook: $!\n";
0
 

Author Comment

by:dchid
ID: 20328448
Hi, thanks I tried that, but still it tells me it could not open workbook, I am not sure why, I have checked file permissions etc and see can think of nothing which should prevent workbook from being opened
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20328586
Did you add the "or die" part to line 7?  What message do you get when you run it?
0
 

Author Comment

by:dchid
ID: 20328807
yes the or die has been added, the message i get when i try to run it is could not open workbook.
I tried a different perl program, which opens up, creates a new workbook and adds some values, this worked fine, although this does not help me, for some reason the existing workbook is unable to be opened by the perl program.
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20329184
Try this:


....
my $FileName = 'book1.xls';      #NOTE: This line is new
my $xl_app = CreateObject OLE 'Excel.Application' || die $!;
$xl_app->{'Visible'} = 0;
die "File does not exist, or is not readable\n" unless -r $FileName;     #NOTE: This line is new
my $workbook = $xl_app->Workbooks->Open($FileName)    #NOTE: This line is changed
    or die "Could not open workbook: $!\n";    #NOTE: This line is new
my $worksheet = $workbook->Worksheets(1);
...
0
 

Author Comment

by:dchid
ID: 20331927
Thank you for trying to sort this problem, I entered the new code and same problem, could not open workbook.
I think your code is fine, and this may be an issue on my laptop, possibly with vista, although i have checked permissions.
0
 

Author Comment

by:dchid
ID: 20331996
I transferred the files to XP and still same problem of not being able to open the workbook
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 39

Expert Comment

by:Adam314
ID: 20350753
Can you open the file in excel?
0
 

Author Comment

by:dchid
ID: 20350857
The file opens no problem in excel on XP or Vista
I tried to make a new file, same name, but same problem exists that the perl program can not open workbook. I have tried it on 3 different computers, 1 running Vista, 2 running XP Professional, really has confused me.
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20351016
Did you make the change I suggested in post 20329184?  
With that change, does anything different happen?
What is the exact error message you get?
0
 

Author Comment

by:dchid
ID: 20351602
That change has been made and the line is still could not open workbook
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20351801
Try using the full path to the file:

my $FileName = 'c:\path\to\your\file\book1.xls';
...
my $workbook = $xl_app->Workbooks->Open($FileName)    #NOTE: This line is changed
    or die "Could not open workbook: $!\n";    #NOTE: This line is new
0
 

Author Comment

by:dchid
ID: 20352339
Still the same problem, thank you for your continuing efforts to help with this problem
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20352806
After the "Could not open workbook", is there anything else displayed?
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20352855
Try with this minor change also:

my $workbook = $xl_app->Workbooks->Open($FileName)
    or die "Could not open workbook:\n  $!\n  $@\n";
0
 

Author Comment

by:dchid
ID: 20353052
The only message is Could not open Workbook.
With the new change do I need to add a value to $FileName before trying to run the program?
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20353076
Yes, set filename like so:
my $FileName = 'c:\path\to\your\file\book1.xls';
0
 

Author Comment

by:dchid
ID: 20353482
Same as before "Could not open workbook:"
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20353631
Here is another change...


#!/usr/bin/perl -w
use strict;
use OLE;
 
my $FileName = 'c:\path\to\files\book1.xls';
my $xl_app = CreateObject OLE 'Excel.Application' || die "Could not create app: $!";
$xl_app->{'Visible'} = 0;
die "File does not exist, or is not readable\n" unless -r $FileName;
my $workbook = $xl_app->Workbooks->Open($FileName);
if($workbook) {
    print "Workbook is created\n";
}
else {
    print "Workbook NOT created:\n  $!\n  $@\n";
}
my $worksheet = $workbook->Worksheets(1);
 
$worksheet->Range("F1")->{Value} = 'ID';
$worksheet->Range("G1")->{Value} = 'Price';
$worksheet->Range("H1")->{Value} = 'Currentstock';
 
my $currow=2;
while($worksheet->Range("A$currow")->{Value}) {
      $worksheet->Range("F$currow")->{Value} = int(rand(9999)+1);
      $worksheet->Range("G$currow")->{Value} = int(rand(50)+1);
      $worksheet->Range("H$currow")->{Value} = int(rand(201));
      $currow++;
}
 
$workbook->Save();
 
$xl_app->ActiveWorkbook->Close(0);
$xl_app->Quit();
0
 

Author Comment

by:dchid
ID: 20353730
new message is workbook is created although the workbook still has not been altered after running the program
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20353900
What is the output from this:

...
my $worksheet = $workbook->Worksheets(1);
print "A1 = " . $worksheet->Range("A1") . "\n";
print "B1 = " . $worksheet->Range("B1") . "\n";
print "A2 = " . $worksheet->Range("A2") . "\n";
print "B2 = " . $worksheet->Range("B2") . "\n";

Are those values what is actually in the worksheet?
0
 

Author Comment

by:dchid
ID: 20353989
I ran the code again, and it has worked. I am not sure what you changed in post 20353631 but it seems to of done the trick.
Thank you very much for your patience and help, really appreciated.
0
 
LVL 39

Expert Comment

by:Adam314
ID: 20354229
I'm glad it's working... I was running out of ideas to try.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now