Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re: Create a excel sheet with formula ???

by jmcnamara (Monsignor)
on Nov 08, 2006 at 09:34 UTC ( #582840=note: print w/ replies, xml ) Need Help??

in reply to Create a excel sheet with formula ???

I think that there are 2 things that might help you here. The first is the write_col() worksheet method which will write a column or an array of data in one go.

The second is that since a formula is initially only a string you can generate it programmatically based on the number of cells that you have. Both of these points are shown in this short example:

#/usr/bin/perl -wl use strict; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new("z.xls"); my $worksheet = $workbook->add_worksheet(); my @column = ( 'pass', 'fail', 'pass', 'pass', ); my @array = ( [ '1', '00A', 'pass' ], [ '2', '01A', 'fail' ], [ '3', '01B', 'fail' ], [ '4', '02A', 'pass' ], ); $worksheet->write_col('B2', \@column); $worksheet->write_col('D2', \@array); # Generate the formula. my $first_row = 2; # B2 in this example my $last_row = $first_row + @array -1; $worksheet->write('H2', '=COUNTIF(F2:F' . $last_row . ', "FAIL")' +); __END__


Comment on Re: Create a excel sheet with formula ???
Download Code
Replies are listed 'Best First'.
Re^2: Appened The Value in excel sheet
by samirpatry (Acolyte) on Nov 08, 2006 at 10:57 UTC

    Thnax .By using ur suggestion i shot out my problem.But if i pass the data by argument then it will only write the last data ,Bcz each call it will create the new file.So i need a formula to append the data .Plz give suggestion

      I've recently begun using Spreadsheet::WriteExcel. If you're getting a new worksheet each time a sub is called, you may have

      $ws = $workbook -> add_worksheet();

      In the sub; this will create a new worksheet on each call, numbering them sequentially (1, 2, .... n). You could pass $ws to the sub, and manage worksheet creation in the calling routine (my preference) or surround the add_worksheet call with some logic so it's not invoked after the first invocation.


      At that time [1909] the chief engineer was almost always the chief test pilot as well. That had the fortunate result of eliminating poor engineering early in aviation.

      —Igor Sikorsky, reported in AOPA Pilot magazine February 2003.
        need a formula for this

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://582840]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (8)
As of 2015-11-25 20:25 GMT
Find Nodes?
    Voting Booth?

    What would be the most significant thing to happen if a rope (or wire) tied the Earth and the Moon together?

    Results (690 votes), past polls