wpskibum
07-05-2005, 08:43 PM
I have a Excel spread sheet full of terms that I wish to upload to a wiki I have built. In the wiki there are forms that a user can fill out to enter a new term. Initially I would like to populate this with 100 terms in the Excel spread sheet. I have written the code that will go into the sheet and grab each column I want. At the end of the gathering of data I would then like to upload all that data to the website and create this new term. The wiki creates a new page for each term. I was wondering if in a Perl script I could open the appropriate page and auto populate all the fields with the data I have already grabbed and then possibly click the submit button to enter this new term. Then in a loop I would move to the next row grab the new data and open the page and click submit. I would love any help anyone has if you need to see the code I have it is pasted below and feel free to use it if this will help you. Like I said right now it just grabs data out of a .csv file and prints it to the screen. Thanks in advance for any help.
#!/usr/bin/perl -w
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die on errors...
# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
# open Excel file
my $Book = $Excel->Workbooks->Open("c:/perl/ShortSheet.csv");
# You can dynamically obtain the number of worksheets, rows, and columns
# through the Excel OLE interface. Excel's Visual Basic Editor has more
# information on the Excel OLE interface. Here we just use the first
# worksheet, rows 1 through 4 and columns 1 through 3.
# select worksheet number 1 (you can also select a worksheet by name)
my $Sheet = $Book->Worksheets(1);
my $term = '';
my $abb = '';
my $syn = '';
my $def = '';
my $deo = '';
my $row = 1;
my $col = 1;
#While here is a term read in data
while ($Sheet->Cells($row,$col)->{'Value'})
{
#Clear All Variables
$term = '';
$abb = '';
$syn = '';
$def = '';
$deo = '';
#For each column get the cell of data and save the value into the
#correct variable to be entered later.
foreach $col (1..5)
{
# skip empty cells
next unless defined $Sheet->Cells($row,$col)->{'Value'};
# print out the contents of a cell
SWITCH:{
if($col == 1){
#Save Value to poplulate new page creation
$term = $Sheet->Cells($row,$col)->{'Value'};
}
if($col == 2){
#Save Value to poplulate new page creation
$abb = $Sheet->Cells($row,$col)->{'Value'};
}
if($col == 3){
#Save Value to poplulate new page creation
$syn = $Sheet->Cells($row,$col)->{'Value'};
}
if($col == 4){
#Save Value to poplulate new page creation
$def = $Sheet->Cells($row,$col)->{'Value'};
}
if($col == 5){
#Save Value to poplulate new page creation
$deo = $Sheet->Cells($row,$col)->{'Value'};
}
}#End Switch
}#End For Col
#Create new page now that you have all the columns of data here.
printf"TermNumber %i\n", $row;
printf"Term : $term\n";
printf"Abb : $abb\n";
printf"Syn : $syn\n";
printf"Def : $def\n";
printf"Deo : $deo\n";
#Move to Next Term in the spreadsheet
$row=$row+1;
}#End For Row
# clean up after ourselves
$Book->Close;
#!/usr/bin/perl -w
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die on errors...
# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
# open Excel file
my $Book = $Excel->Workbooks->Open("c:/perl/ShortSheet.csv");
# You can dynamically obtain the number of worksheets, rows, and columns
# through the Excel OLE interface. Excel's Visual Basic Editor has more
# information on the Excel OLE interface. Here we just use the first
# worksheet, rows 1 through 4 and columns 1 through 3.
# select worksheet number 1 (you can also select a worksheet by name)
my $Sheet = $Book->Worksheets(1);
my $term = '';
my $abb = '';
my $syn = '';
my $def = '';
my $deo = '';
my $row = 1;
my $col = 1;
#While here is a term read in data
while ($Sheet->Cells($row,$col)->{'Value'})
{
#Clear All Variables
$term = '';
$abb = '';
$syn = '';
$def = '';
$deo = '';
#For each column get the cell of data and save the value into the
#correct variable to be entered later.
foreach $col (1..5)
{
# skip empty cells
next unless defined $Sheet->Cells($row,$col)->{'Value'};
# print out the contents of a cell
SWITCH:{
if($col == 1){
#Save Value to poplulate new page creation
$term = $Sheet->Cells($row,$col)->{'Value'};
}
if($col == 2){
#Save Value to poplulate new page creation
$abb = $Sheet->Cells($row,$col)->{'Value'};
}
if($col == 3){
#Save Value to poplulate new page creation
$syn = $Sheet->Cells($row,$col)->{'Value'};
}
if($col == 4){
#Save Value to poplulate new page creation
$def = $Sheet->Cells($row,$col)->{'Value'};
}
if($col == 5){
#Save Value to poplulate new page creation
$deo = $Sheet->Cells($row,$col)->{'Value'};
}
}#End Switch
}#End For Col
#Create new page now that you have all the columns of data here.
printf"TermNumber %i\n", $row;
printf"Term : $term\n";
printf"Abb : $abb\n";
printf"Syn : $syn\n";
printf"Def : $def\n";
printf"Deo : $deo\n";
#Move to Next Term in the spreadsheet
$row=$row+1;
}#End For Row
# clean up after ourselves
$Book->Close;