On Fri, 28 Jan 2005 22:55:01 +0900, Ruby Quiz <james / grayproductions.net> wrote:
> Years ago, on a job developing custom reporting software, this was one of the
> side tasks.  Parsing a report may sound boring, but I urge you to at least
> download this report and peek inside.  It's a tragic example of database output
> gone wrong.
> 
>         http://www.grayproductions.net/ruby_quiz/report.zip
> 
> (I've doctored the report heavily to protect my client, but the spirit of the
> data remains the same.  I went out of my way to keep the report's little quirks
> while fudging all the data.)
> 
> My job was literally described as, "I need to take this straight into Excel, no
> clean up or Wizards required."  Some of you may want to stop reading there and
> make your own interpretation of that.  Feel free.

First of all, my apologies to the list for polluting it with PHP code,
I am attempting to learn Ruby, but did not have time to dig into this
quiz.

I did want to share this trick I wrote in PHP to be able to make
completely relative formulas
to dump into the CSV file which will work after importing the CSV.  An
example is a "percent" calculation based on the cell reference, not
just calculating in the script and outputting the value, also using
Excel's formatting.

The key advantage to being completely relative is you can dump the
exact same formula into multiple locations in the file and have it
operate correctly.

Here was a helper function I wrote to construct the relative cell formula:

/**
*      return the formula offset calculations
*
*      used for embedding 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;
}

And here is an example of the helper function in actions, making a
nicely formatted cell with a "safe" divide by zero.  Note this formula
is created once, and then output wherever it is needed in the csv file
(in each row, possibly for more than one column in each row, etc.).
It takes the column four to the left of this cell, and divided it by
the column two to the left of this cell, and formats as a percent
number.

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

HTH someone out there :)

 
Regards,
Jason
http://blog.casey-sweat.us/