Page 1 of 1

can you do this in PHP?

Posted: Wed Jul 26, 2006 8:49 am
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.

Posted: Wed Jul 26, 2006 9:28 am
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.

thanks for that

Posted: Wed Jul 26, 2006 9:43 am
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

Posted: Wed Jul 26, 2006 9:52 am
by feyd
I can't say, as I have yet to need to write any formulas into the generated spreadsheets I've done.

Posted: Wed Jul 26, 2006 10:04 am
by pickle

thanks

Posted: Wed Jul 26, 2006 11:15 am
by bennythemink
thanks for all the help! il have a look into that.

Posted: Wed Jul 26, 2006 12:31 pm
by kbrown3074
What if you do the calculations in PHP and just dump the resulting calculation out onto the spreadsheet?

Posted: Wed Jul 26, 2006 12:52 pm
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.

wont work :(

Posted: Thu Jul 27, 2006 3:53 am
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.

Posted: Thu Jul 27, 2006 10:51 am
by RobertGonzalez
Search at PHPClasses for 'Excel'. Thats what I did.

Posted: Thu Jul 27, 2006 12:17 pm
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:)

Posted: Thu Jul 27, 2006 3:37 pm
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

Posted: Fri Jul 28, 2006 3:52 am
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