X-Git-Url: http://git.roojs.org/?p=Pman.Core;a=blobdiff_plain;f=SimpleExcel.php;h=6c36750ea2e301acfaab413f0d3bc9497c7b89a6;hp=f60bc92514c973b28b57cf8fa7eaf4a5a044fa1e;hb=HEAD;hpb=7bb481ca43486df2ead8d26fb316a2a29d9cfde9 diff --git a/SimpleExcel.php b/SimpleExcel.php index f60bc925..0ca12fc6 100644 --- a/SimpleExcel.php +++ b/SimpleExcel.php @@ -4,46 +4,53 @@ * class to generate excel file from rows of data, and a configuration. * * usage : - * $x = new Pman_Core_SimpleExcel(array()) + * $x = new Pman_Core_SimpleExcel($data_array, array()) * $x->send($fn); * * - * cfg: - * formats - * name : [ Align : left, .... ] - * - * workbook : nameof * - * head : [ - [ "a", "b" ] - [], - [ "A", "B" ] - [ "a", ["test", "left"] ] << sub array [text, formatname] - ], - * merged_ranges : array( - * array($first_row, $first_col, $last_row, $last_col), - * * array($first_row, $first_col, $last_row, $last_col), - * ), - * cols : array( + +new Pman_Core_SimpleExcel($data_array, array( + 'formats' => array( + 'format_name' => array( 'Align' => 'left' ), // etc... + ), + 'workbook' => 'name_of_workbook'm + + 'head' => array( + array ( 'this', 'is', 'the' , 'first', 'row'), + array ( 'this', 'is', 'the' , 'seconde', 'row'), // ... etc. + array ( array( 'string', 'format_name') , 'another cell') , // with formating.. + ), + + 'merged_ranges' => array( + array($first_row, $first_col, $last_row, $last_col), + array($first_row, $first_col, $last_row, $last_col), + ), + 'cols' => array( array( 'header'=> "Thumbnail", 'dataIndex'=> 'id', - * 'dataFormat' => 'string' // to force a string.. + 'dataFormat' => 'string' // to force a string.. 'width'=> 75, 'renderer' => array($this, 'getThumb'), - 'txtrenderer' => array($this, 'cleanValue'), // for - * 'color' => 'yellow', // set color for the cell which is a header element - * 'fillBlank' => 'gray', // set the color for the cell which is a blank area + 'txtrenderer' => function($value, $worksheet, $row, $col, $row_data) { + return $value + }, // for text content... + 'color' => 'yellow', // set color for the cell which is a header element + 'fillBlank' => 'gray', // set the color for the cell which is a blank area ), - - // if this is set then it will add a tab foreach one. - workbooks = array( - workbook -> - - 'leave_open' => false - - - // callbacks: renderer + //..... and ther rows... + ), + + // if this is set then it will add a tab foreach one. + 'workbooks' = array( + workbook => '....' // ??? + ), + 'leave_open' => false, // if you call addrows?? later.. + 'nonspacer' => false, // should add line between head and header row. +)); + + callbacks: renderer function($value, $worksheet, $row, $col, $row_data) @@ -67,8 +74,10 @@ class Pman_Core_SimpleExcel extends Pman var $formats = array(); var $workbook = false; var $worksheet= false; - - function Pman_Core_SimpleExcel($data,$cfg) + var $postRender = array(); + var $outfile2; + + function __construct($data,$cfg) { // print_r($cfg);exit; require_once 'Spreadsheet/Excel/Writer.php'; @@ -85,11 +94,14 @@ class Pman_Core_SimpleExcel extends Pman $cfg['formats'] = isset($cfg['formats']) ? $cfg['formats'] : array(); + $this->formats['_default_date_format_'] = $workbook->addFormat();; + $this->formats['_default_date_format_']->setNumFormat('YYYY-MM-DD'); + foreach($cfg['formats'] as $f=>$fcfg) { $this->formats[$f] = & $workbook->addFormat(); foreach((array)$fcfg as $k=>$v) { - $this->formats[$f]->{'set' . $k}($v); + $this->formats[$f]->{'set' . $k}($v); } } @@ -216,16 +228,13 @@ class Pman_Core_SimpleExcel extends Pman } } - print_R($cfg['cols']);exit; + foreach($cfg['cols'] as $c=>$col_cfg) { $format = isset($col_cfg['color']) && isset($this->formats[$col_cfg['color']]) ? $this->formats[$col_cfg['color']] : false; $worksheet->write($start_row, $c, @$col_cfg['header'],$format); - $worksheet->setColumn ( $c, $c, $col_cfg['width'] / 5); -// $worksheet->setRow ( $start_row, $col_cfg['height'] / 5); } - $start_row++; $this->start_row = &$start_row; @@ -235,33 +244,59 @@ class Pman_Core_SimpleExcel extends Pman if (empty($data)) { return; } + + foreach($cfg['cols'] as $c => $col_cfg) { + if (isset($col_cfg['renderer'])) { + $hasRender = true; + break; + } + } - foreach($data as $r=>$clo) { - $hasRenderRow = $this->addLine($cfg['workbook'], $clo); - $hasRender = ($hasRender || $hasRenderRow) ? true : false; - - } - /// call user render on any that are defined.. - if ($hasRender) { - foreach($data as $r=>$cl) { + + if (is_object($data)) { + $data_ar = array(); + $count = $data->count(); + $data->find(); - foreach($cfg['cols'] as $c=>$col_cfg) { - $v = isset($cl[$col_cfg['dataIndex']]) ? $cl[$col_cfg['dataIndex']] : ''; - if (empty($cl[$col_cfg['dataIndex']])) { - continue; - } - if (isset($col_cfg['renderer'])) { - // not sure if row is correct here...!!!? - call_user_func($col_cfg['renderer'], $cl[$col_cfg['dataIndex']], $worksheet, $r+1, $c, $cl); - + while($data->fetch()) { + $hasRenderRow = $this->addLine($cfg['workbook'], $data); + $hasRender = ($hasRender || $hasRenderRow) ? true : false; + } + $start_row += $count; + } else { + + foreach($data as $r=>$clo) { + $hasRenderRow = $this->addLine($cfg['workbook'], $clo); + $hasRender = ($hasRender || $hasRenderRow) ? true : false; + + } + /// call user render on any that are defined.. + if ($hasRender) { + foreach($data as $r=>$cl) { + + foreach($cfg['cols'] as $c=>$col_cfg) { + $v = isset($cl[$col_cfg['dataIndex']]) ? $cl[$col_cfg['dataIndex']] : ''; + if (empty($cl[$col_cfg['dataIndex']])) { + continue; + } + if (!empty($col_cfg['renderer'])) { + if (is_a($col_cfg['renderer'], 'Closure')) { + $col_cfg['renderer']($cl[$col_cfg['dataIndex']], $worksheet, $r+1, $c, $cl); + } else { + // not sure if row is correct here...!!!? + call_user_func($col_cfg['renderer'], $cl[$col_cfg['dataIndex']], $worksheet, $r+1, $c, $cl); + } + + } + // echo "
WRITE: ". htmlspecialchars(print_r(array($r+1, $c, $cl[$col_cfg['dataIndex']]), true));
+                 
                     }
-                  //  echo "
WRITE: ". htmlspecialchars(print_r(array($r+1, $c, $cl[$col_cfg['dataIndex']]), true));
-             
                 }
             }
+            $start_row += count($data);
+
         }
-        $start_row += count($data);
         
         if (!empty($cfg['foot'])) {
             foreach($cfg['foot'] as $row) { 
@@ -302,7 +337,10 @@ class Pman_Core_SimpleExcel extends Pman
             $worksheet->setRow($start_row +$r, $cfg['row_height']);
         }
         
-        foreach($cfg['cols']  as $c=>$col_cfg) {
+        $line_height = (isset($cfg['line_height'])) ? $cfg['line_height'] : 12;
+        $height = 0;
+        
+        foreach($cfg['cols']  as $c => $col_cfg) {
             
             if(isset($col_cfg['dataIndex']) && isset($cl[$col_cfg['dataIndex']])){
                 $v =    $cl[$col_cfg['dataIndex']]  ;
@@ -318,8 +356,14 @@ class Pman_Core_SimpleExcel extends Pman
                 continue;
             }
             if (isset($col_cfg['txtrenderer'])) {
-                $v = call_user_func($col_cfg['txtrenderer'], 
-                        $cl[$col_cfg['dataIndex']], $worksheet, $r+1, $c, $clo);
+                
+                 if (is_a($col_cfg['txtrenderer'], 'Closure')) {
+                     
+                    $v =     $col_cfg['txtrenderer']($cl[$col_cfg['dataIndex']], $worksheet, $r+1, $c, $clo);
+                } else {
+                    $v = call_user_func($col_cfg['txtrenderer'], 
+                            $cl[$col_cfg['dataIndex']], $worksheet, $r+1, $c, $clo);
+                }
                 if ($v === false) {
                     continue;
                 }
@@ -327,6 +371,18 @@ class Pman_Core_SimpleExcel extends Pman
             }
             if (isset($col_cfg['renderer'])) {
                 $hasRender = true;
+                
+                $v = isset($cl[$col_cfg['dataIndex']]) ? $cl[$col_cfg['dataIndex']] : '';
+                if (empty($cl[$col_cfg['dataIndex']])) {
+                    continue;
+                }
+                $this->postRender[] = array(
+                    $col_cfg['renderer'], $cl[$col_cfg['dataIndex']], $worksheet, $start_row+$r+1, $c, $cl
+                );
+                  
+                
+                
+                
                 continue;
             }
             
@@ -338,14 +394,34 @@ class Pman_Core_SimpleExcel extends Pman
             $format = isset($col_cfg['format'])  && isset($formats[$col_cfg['format']] )   ? $formats[$col_cfg['format']] : false;
           //  print_R(array($start_row+$r, $c, $v, $format));exit;
           // handle 0 prefixes..
-            if ( (is_numeric($v) &&  strlen($v) > 1 && substr($v,0,1) == '0' && substr($v,1,1) != '.') 
+          
+            if ($dataFormat == 'date' || preg_match('/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/', $v)) {
+                $dataFormat = 'date';
+                $format = empty($format) ? $this->formats['_default_date_format_']: $format;
+                $ut_to_ed_diff = 86400 * 25569;
+                $gmt = strtotime('1970-01-01');
+
+                $v = (strtotime($v) + $ut_to_ed_diff - $gmt) / 86400;
+                // need to +8hrs to get real time..
+                
+                
+            }
+          
+            if ( (is_numeric($v) &&  strlen($v) > 1 && substr($v,0,1) == '0' && substr($v,1,1) != '.' )
                     || 
                     $dataFormat == 'string' ) {
+                //var_dump("Write ( {$r}, {$c} ) = " . $v);
                 $worksheet->writeString($start_row+$r, $c, $v, $format);
             } else {
-          
+                //var_dump("Write String ( {$r}, {$c} ) = " . $v);
                 $worksheet->write($start_row+$r, $c, $v, $format);
             }
+            
+            if(isset($col_cfg['autoHeight'])){
+                $vv = explode("\n", $v);
+                $height = MAX(count($vv) * $line_height, $height);;
+                $worksheet->setRow($start_row+$r, $height);
+            }
         }
         $this->start_row++;
         
@@ -355,16 +431,32 @@ class Pman_Core_SimpleExcel extends Pman
     
     function send($fname)
     {
+        
+        if (!empty($this->postRender)) {
+            foreach($this->postRender as $ar) {
+                 if (is_a($ar[0], 'Closure')) {
+                    $ar[0]($ar[1], $ar[2], $ar[3], $ar[4], $ar[5]);
+                } else {
+                // not sure if row is correct here...!!!?
+                    call_user_func($ar[0],$ar[1], $ar[2], $ar[3], $ar[4], $ar[5]);
+                }
+            }
+            
+        }
+        
+        
         if (!empty($this->workbook)) {
             $this->workbook->close();
             $this->workbook = false;
         }
         
         require_once 'File/Convert.php';
+        //var_Dump($this->outfile2);
         $fc=  new File_Convert($this->outfile2, "application/vnd.ms-excel");
-        $fn = $fc->convert("application/vnd.ms-excel"); 
+        $fn = $fc->convert("application/vnd.ms-excel");
+        //print_r($fc);
         $fc->serve('attachment',$fname); // can fix IE Mess
     }
      
     
-}
\ No newline at end of file
+}