How to open excel so I can write information to it in PERL

This question is worth  50 Points
      

I am using PERL 5.5.8 and I am able to create and write to Excel using the following code.

use Spreadsheet::WriteExcel;
# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("CaseInformation.xls");

# Add some worksheets
my $drive = $workbook->add_worksheet("Drive Information");

# Set the active worksheet
$drive->activate();

# Set the active cell
$drive->set_selection(0, 1);

# Write the headers
$drive->write(0, 0, "Drive", $format);

-----------------------------------

System: XP PRO SP2
PERL 5.5.8

So far so good. Now the problem is when I call the next PERL program I want to write more information to it and I can't find any information that is helpful. Can some one please  help me with this. The code will be complied at it's finished state.

Thank you

Jim
jray9242Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

clockwatcherCommented:
Don't think Spreadsheet::WriteExcel will deal with existing files.  Assuming you don't need a cross platform version and Excel is installed on the machine you're planning on running this on, you can use Win32::OLE.

use Win32::OLE;

my $excel = Win32::OLE->new("Excel.Application");
$excel->DisplayAlerts(0);
my $wkbk = $excel->Workbooks->Open("c:/excelwriter/CaseInformation.xls");
my $drive = $wkbk->Worksheets("Drive Information");
$drive->Cells(5,5)->{Value} = localtime(time());
$wkbk->Save();
$wkbk->Close();
$excel->Quit();


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jray9242Author Commented:
So I understand, using the Win32::OLE allows me to open and a speadsheet without having to Excel on the machine?

Thanks and I will give this a try. I was hoping to make this crossplaform, but I don't know if it will be using Win32::OLE but that is ok.

Jim
0
clockwatcherCommented:
You'll need Excel installed on the machine you're planning on running it on, if you rely on Win32::OLE.  

The only cross platform away I'm aware of doing it is to manipulate the xml version of Excel files available with Excel 2003.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

sugarfreelessCommented:
You don't need to have excel installed on the host running this script.  I use it on Solaris 8.  Then spam the finished product to gobs of people.

from the looks of it you are continually overwriting you excel cell.  You need to make sure you continually tell the data where to go.  You do that by adding count to your rows.

If you want to write lots of data to a spread sheet you need to create a while loop and add row count while you go.

hope this helps
for example....

I predeclare my vars by the column title and row.

my $firstRow = 1;
my $secRow = 1;
my $thirdRow = 1;
my $fourthRow = 1;

# Create new workbook.  You may have to use absolute path, or you may not.
my $workbook = Spreadsheet::WriteExcel->New("/absolute/path/to/file.xls");

# I then do some worksheet names and titles with formatting.
my $sheet1 = $workbok->addworksheet("NAME");

# format
my $formatTITLE = $workbook->addformat();
$formatTITLE->set_bold();
$formatTITLE->set_size(12);
$formatTITLE->set_color('blue');
$formatTITLE->set_align('center');

my $formatDATA = $workbook->addformat();
$formatDATA->set_align('left');
$formatDATA->set_size(10);

# set active
$sheet1->activate();

# then typically you would need to set the width of colums
# the general syntax is write($row, $col, $token, $format)

# I open my flat file and write it to spreadsheet
open(FILE,'/absolute/path/to/file.log') || die "\nCan't open file.log\n";

# Add column titles, I used more but left this example to one
$sheet1->write(0,0 "TITLE", $formatTITLE);

# start loop
while (<FILE>){
 #get data
 (my $first, my $sec, my $third, my $fourth) = split(/\|/,$_);
#chop data with new line char
 chop $fourth;
 # write data
 $sheet1->write($firstRow, $firstCol, $first, $formatDATA);
 $sheet1->write($secRow, $secCol, $sec, $formatDATA);
 sheet1->write($thirdRow, $thirdCol, $third, $formatDATA);
 $sheet1->write($fourthRow, $fourthCol, $fourth, $formatDATA);
 $firstRow++;
 $secRow++;
 $thirdRow++;
 $fourthRow++;

}
0
jray9242Author Commented:
I want to explain this a little further and maybe this will shed more light. I have 4 Perl apps and the first one does this

The first one creates the XLS and all 4 workbooks. After the first one close the next one is suppose to open the XLS and write it's information to the right workbook and the same goes for 3 thu 4. Does this change what you all have mentioned here?

Thanks again.

Jim
0
sugarfreelessCommented:
jray9242,

Yes.

I'm assuming some things here.
1) The worksheets will need to be written to more than once, adding data to existing data at different times.
2) You are unable to write all the data at the same time (preferred method).
3) You already know how to write to the correct worksheets, and understand perl pretty well.

You will need to save the row number to a flat file for later use.

#declare at the top.
my $sheetone = sheetone.log
my $sheettwo = sheettwo.log
my $sheetthree = sheetthree.log
my $sheetfour = sheetfour.log

open before starting code.
open F1 ">$sheetone" || die $!;
open F2 ">$sheettwo" || die $!;
open F3 ">$sheetthree" || die $!;
open F4 ">$sheetfour" || die $!;

then in the while loop above adding count print to file.
print F1 $firstRow|$secRow|$thirdRow|$fourthRow;

then after the close bracket ( } ) in the while loop.
close(F1);
close(F2); etc.

Now you will need to write into the begging of the script the ability to read from the saved data so the script knows where to start from since the last write.  I would recommend using some trigger to tell the script to use the saved row data.
open R "<$sheetone" || die $!;
($firstRow,$secRow,$thirdRow,$fourthRow) =split(/|/,$_);

if any of this confuses you let me know and I'll clarify.
0
jray9242Author Commented:
Hey, I think the light just came on, or I think it did. What you're saying is first write the information to a text file and then created the Excel, reading in all the files into the worksheet.


I wish I could send you a sample file of what I am doing. It would become more clear. This is good information though. This is another view of what I am doing. I wish I could do it in one PL, but this isn't possible because of what they do and what I am doing with the information.

Flow Chart

1. Run first PL
2. Create Text File A
3. Close PL
4. Run Next PL
5. Create Text File B
6. Close PL
7. Run Next PL
8. Create Spreedsheet->NEW
9. Create Workbooks "A" and "B"
10. Read in A->Text
11. Write A->TEXT to Workbook A
12. Write B->TEXT to Workbook B
0
sugarfreelessCommented:
You can still do that in one shot.

If steps 8 - 12 are done after all the text files are written just open both in the same script.

Once that is done then just do two seperate while(<FILE>) loops.

while(<FILE1>){
  do code
}
close(FILE1);

while(<FILE2)>){
  do code
}
close(FILE2);
0
sugarfreelessCommented:
Jray9242,

Glad you were able to get your script working.  Good job clockwatcher!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.