[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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
0
jray9242
Asked:
jray9242
  • 4
  • 3
  • 2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now