* 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'),
- * '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
),
+ //..... 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..
+
+));
+
+ callbacks: renderer
- // if this is set then it will add a tab foreach one.
- workbooks = array(
- workbook ->
+ function($value, $worksheet, $row, $col, $row_data)
+
+ // callbacks : txtrenderer
+ function($value, $worksheet, $row, $col, $row_data)
+
- 'leave_open' => false
*/
var $formats = array();
var $workbook = false;
var $worksheet= false;
-
- function Pman_Core_SimpleExcel($data,$cfg)
+ var $postRender = array();
+
+ function __construct($data,$cfg)
{
// print_r($cfg);exit;
require_once 'Spreadsheet/Excel/Writer.php';
$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);
}
}
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 "<PRE>WRITE: ". htmlspecialchars(print_r(array($r+1, $c, $cl[$col_cfg['dataIndex']]), true));
+
}
- // echo "<PRE>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) {
$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']];
+ $v = $cl[$col_cfg['dataIndex']] ;
+
}else{
if(isset($col_cfg['fillBlank'])){
$worksheet->write($start_row+$r, $c, '', $formats[$col_cfg['fillBlank']]);
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;
}
}
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;
}
- $v = @iconv('UTF-8', 'UTF-8//IGNORE', $v);
+ $v = @iconv('UTF-8', 'UTF-8//TRANSLIT//IGNORE', $v);
$dataFormat = empty($col_cfg['dataFormat']) ? '' : $col_cfg['dataFormat'];
- ;
-
+
$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 (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' ) {
- if($worksheet_name == 'event' && $start_row == 2 && $c == 1){
- $validator = $this->workbook->addValidator();
-// $validator->setList("a b c");
- $validator->_fixedList = 1;
- $validator->_type = 3;
- $validator->_incell=true;
- $validator->setFormula1('H10');
-
-// $validator->setList('Q2:Q10');
-
- $this->worksheet->setValidation(2,1,0,0,$validator);
- $this->worksheet->_storeDataValidity();
- $this->start_row++;
-
- return $hasRender;
- }
-
$worksheet->writeString($start_row+$r, $c, $v, $format);
-
} else {
$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++;
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
+}
projects / Pman.Core / blobdiff