can you do this in PHP?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bennythemink
Forum Newbie
Posts: 16
Joined: Thu Jun 15, 2006 4:32 am

can you do this in PHP?

Post by bennythemink »

Hi guys,

is there any way I can format an excel sheet generated through php without using PEAR? by formatting I mean the appearance and if possible applying formulas, really stuck on this one so any help is appricated.

thanks
b.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

technically yes, but you'd need to basically reimplement the entire PEAR class or implement an entire class from what they referenced when they built it.
bennythemink
Forum Newbie
Posts: 16
Joined: Thu Jun 15, 2006 4:32 am

thanks for that

Post by bennythemink »

was thinking id have to do that, was just hoping i wont have to:(

thanks for the heads up, will this allow for cell formulas to be included by PHP or would I still have to apply those by opening the generated Excel file and writing them manually? if i know the answer to this question then i can argue that there is no point in going to all the hassel to rewirte that PEAR class as an ordinary PHP page.

thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I can't say, as I have yet to need to write any formulas into the generated spreadsheets I've done.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
bennythemink
Forum Newbie
Posts: 16
Joined: Thu Jun 15, 2006 4:32 am

thanks

Post by bennythemink »

thanks for all the help! il have a look into that.
User avatar
kbrown3074
Forum Contributor
Posts: 119
Joined: Thu Jul 20, 2006 1:36 pm

Post by kbrown3074 »

What if you do the calculations in PHP and just dump the resulting calculation out onto the spreadsheet?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

First suggestion: try it and see. Either the formulas will show up as text or they will work as expected/hoped for.

Second suggestion: Browse the repositories at PHPClasses.org. I grabbed a killer little lightweight Excel exporter class there a few years back that I haven't stoppedusing since.
bennythemink
Forum Newbie
Posts: 16
Joined: Thu Jun 15, 2006 4:32 am

wont work :(

Post by bennythemink »

hey thanks for all the help.

Everah: do you remember the name of that excel script at PHPClasses.org? does it allow for formatting?

Pickle: did you manage to get that PHP_writeexcel to work? I downloaded it and put it on my server put it just gives me errors anytime I try to run the examples that come with it?!

many thanks for your advice on this.

benny.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Search at PHPClasses for 'Excel'. Thats what I did.
bennythemink
Forum Newbie
Posts: 16
Joined: Thu Jun 15, 2006 4:32 am

Post by bennythemink »

thanks for all the help. in the end i used CSS to format the resultant table generated by PHP. some of the formulas suffered as a result but it might just pass the test yet:)
User avatar
sweatje
Forum Contributor
Posts: 277
Joined: Wed Jun 29, 2005 10:04 pm
Location: Iowa, USA

Post by sweatje »

I remembered some old code of mine which embeds formulas in csv file output, which excel picks up and works with fine:

Code: Select all

// Formula for % weight.
    $pct_lbs_formula = '"=TEXT(IF('.c(-2).'=0,0,'.c(-4).'/'.c(-2).'),""0.0%"")"';
    // Formula for % Revenue.
    $pct_rev_formula = '"=TEXT(IF('.c(-3).'*'.c(-2).'=0,0,'.c(-5).'*'.c(-4).'/'.c(-3).'*'.c(-2).'),""0.0%"")"';
//...

    header("Content-type: application/ms-excel; name={$sub_dist}_fcst.csv");
    header("Content-Disposition: inline; filename={$sub_dist}_fcst.csv");
    $t->display('distfcst_csv.tpl');

//...

/**
*    return the formula offset calculations
*
*    used for embeding a formula into the csv file to be output to excel
*
*    @author        Jason E. Sweat
*    @since        2002-05-01
*    @param        int        $coff    optional - column offset
*    @param        int        $roff    optional - row offset
*    @return        string            the excel formula for a relative cell reference
*/
function c($coff = 0, $roff = 0)
{
    $ret = 'OFFSET($A$1,ROW()';
    (int)$roff--;
    (int)$coff--;
    if ($roff < 0) {
        $ret .= $roff;
    } elseif ($roff > 0) {
        $ret .= '+'.$roff;
    }
    $ret .= ',COLUMN()';
    if ($coff < 0) {
        $ret .= $coff;
    } elseif ($coff > 0) {
        $ret .= '+'.$coff;
    }
    $ret .= ')';
    return $ret;
}
The advantage of the c() function in my code was to make generic formula with relative references. This generic forumula could then be output over and over in each line without modification.

HTH
bennythemink
Forum Newbie
Posts: 16
Joined: Thu Jun 15, 2006 4:32 am

Post by bennythemink »

hey sweatje,

thanks for the code, im actually outputting straight to excel. its working fine so far except that i cannot reference cells in formulas which have not yet had content added to them, il prob have to create some kind of buffer.

what i mean is that if i have a formula in the first cell that references cell A100 then il get an error in the excel sheet as cell A100 is not yet populated.

benny
Post Reply