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

Posted on 2006-04-01
Last Modified: 2010-08-05
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

# 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

Question by:jray9242
    LVL 25

    Accepted Solution

    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");
    my $wkbk = $excel->Workbooks->Open("c:/excelwriter/CaseInformation.xls");
    my $drive = $wkbk->Worksheets("Drive Information");
    $drive->Cells(5,5)->{Value} = localtime(time());


    Author Comment

    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.

    LVL 25

    Expert Comment

    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.
    LVL 3

    Expert Comment

    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();

    my $formatDATA = $workbook->addformat();

    # set active

    # 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);


    Author Comment

    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.

    LVL 3

    Expert Comment



    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(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.

    Author Comment

    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
    LVL 3

    Expert Comment

    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.

      do code

      do code
    LVL 3

    Expert Comment


    Glad you were able to get your script working.  Good job clockwatcher!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    work on ods spreadsheet with perl in ubuntu 4 70
    compress files in RAR using perl 13 64
    iSeries PERL Scripts 7 117
    Awk Question 2 106
    I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
    Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
    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…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    760 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

    7 Experts available now in Live!

    Get 1:1 Help Now