Table generator

Html table generation

The HtmlTable is a helper class to create pure html tables easy way. Let's see pure example where a 3x2 sized table will be generated with header texts.

Let's see the generation of this table:

counttypecolor
oneapplered
twopeachyellow

You can generate this table with the following codes: 

 //One using mode
 $t1 = new HtmlTable();
 $t1->name('customname');
 $t1->heads(['count','type','color']);
 $t1->cellss([['one','apple','red'   ],
             ['two','peach','yellow' ]]);
 $t1->opts(['border'=>1,'class'=>'mytable']);
 print $t1->get();

 //An another using mode
 print h("table") //helper function, which equivalent to new HtmlTable()
        ->name('customname')
        ->opts(['border'=>1,'class'=>'mytable'])
        ->heads(['count','type','color'])
        ->cells(['one','apple','red'   ])
        ->nrow()
        ->cells(['two','peach','yellow'])
        ->get();

The HtmlTable class stores every settings and cell in it's internal sate. As seen above the html generation is done by get() method. The following functions can be used to define the table:

Methods of HtmlTable class:

Excel output generator

The ExcelXmlDocument class works similar way than HtmlTable but ExcelXmlDocument generates Microsoft Office XML formats. You can generate arbitrary formatted tables (even with formulas) and save to XML files which can directly opened in spreadsheets.

Let's see a complete example where the CodKep generates the following small formatted table:

exceltable1.png

You can generate this table with the following codes: 

function hook_mymodule_defineroute()
{
  $r = [];
  $r[] = [
    'path'=> 'sampleexcelxmlfile',
    'callback' => 'rc_sampleexcel_x',
    'type' => 'raw', //This path will generate Microsoft Office XML, not HTML
  ];
  return $r;
}

function rc_sampleexcel_x()
{
    $x = new ExcelXmlDocument();
    $x->setHtmlHeaders('sample.xml');

    $x->name('customname');
    //Header cells, bold fonts and underlined with background color
    $x->cells(['Number','Item','Color'],
              ['strong' => 'yes',
               'border' => 'bottom',
               'background-color' => '#aaaaaa']);
    //Value cells
    $x->cellss([['one','apple','red'   ],
                ['two','peach','yellow' ]]);
    return $x->get();
}

The ExcelXmlDocument class stores every settings and cell in it's internal sate. As seen above the xml output generation is done by get() method. The following functions can be used to build the spreadsheet:

Methods of ExcelXmlDocument class:

The $opts parameter of the opts(),cell(),cells(),cellss() and head(),heads() and nrow() function is an associative array with the following possible options:

Cell modifier options

These options above can be translated to options works with HtmlTable class with table_options_translator()

Complex table generation example

This example shows how to generate a complex table with colors, borders special number formats, and how to use formulas.

exceltable2.png

The generation of the excel table above done with this code: 

 x = new ExcelXmlDocument();
 $x->setHtmlHeaders('sample2.xml');

 $x->name('Groceries');
 //Title
 $x->cell('Shopping at the grocery',
          ['size' => 20,'strong' => 'yes','wrap' => 'off']);
 $x->nrow();
 //Header
 $x->cells(['Item','Color','Weight','Unit price','Sum'],
           ['strong' => 'yes',
            'border' => 'all',
            'background-color' => '#aaaaaa']);
 $x->nrow();

 //Value cells
 $col_1_opts = ['width' => 100,
                'strong' => 'yes',
                'border' => ['left','bottom']];
 $col_2_opts = ['border' => 'bottom',
                 'italic' => 'yes'];
 $col_3_opts = ['t' => 'num',
                 'border' => 'bottom'];
 $col_4_opts = ['t' => 'num',
                 'width' => 80,
                 'border' =>'bottom',
                 'background-color' => '#ffff00',
                 'numberformat' => '#,##0.00\ [$EUR];[Red]\-#,##0.00\ [$EUR]'];
 $col_5_opts = ['t' => 'num',
                 'width' => 80,
                 'formula' => '=RC[-1]*RC[-2]',
                 'border' => ['left','bottom'],
                 'background-color' => '#ff2222',
                 'numberformat' => '#,##0.00\ [$EUR];[Red]\-#,##0.00\ [$EUR]'];
 $x->cell('apple' ,$col_1_opts);
 $x->cell('red'   ,$col_2_opts);
 $x->cell('2.2'   ,$col_3_opts);
 $x->cell('2.55'  ,$col_4_opts);
 $x->cell(''      ,$col_5_opts);
 $x->nrow();

 $x->cell('peach' ,$col_1_opts);
 $x->cell('yellow',$col_2_opts);
 $x->cell('4.9'   ,$col_3_opts);
 $x->cell('3.11'  ,$col_4_opts);
 $x->cell(''      ,$col_5_opts);
 $x->nrow();

 $x->cell('nut'   ,$col_1_opts);
 $x->cell('brown' ,$col_2_opts);
 $x->cell('0.45'  ,$col_3_opts);
 $x->cell('5.02'  ,$col_4_opts);
 $x->cell(''      ,$col_5_opts);
 $x->nrow();

 print $x->get();

Convert Excel XML tables to HTML tables

You can use the table_options_translator() function to translate the cell options of ExcelXmlDocument to options works with cells of HtmlTable. Most option are translated to CSS style to the html table looks similar as spreadsheet.

table_options_translator(array $opts,array $additional = [],$sac_omode = false)
The parameters of the function:


The return array of this function is directly usable as cell option in HtmlTable's methods.

Let's see an example where the previous examples table's code are minimally rewritten to show similar table in html

exceltableashtml.png

The code shows this table above (All options passed to cells are same as above): 

  $x = new HtmlTable();
  $x->name('Groceryes');
  //Title
  $x->cell('Shopping at the grocery',
           table_options_translator(
            ['size' => 20,'strong' => 'yes','wrap' => 'off']));
  $x->nrow();
  //Header
  $x->cells(['Item','Color','Weight','Unit price','Sum'],
            table_options_translator(
             ['strong' => 'yes',
              'border' => 'all',
              'background-color' => '#aaaaaa']));
  $x->nrow();
  //Value cells
  $col_1_opts = table_options_translator(
                ['width' => 100,
                 'strong' => 'yes',
                 'border' => ['left','bottom']]);
  $col_2_opts = table_options_translator(
                 ['border' => 'bottom',
                  'italic' => 'yes']);
  $col_3_opts = table_options_translator(
                 ['t' => 'num',
                  'border' => 'bottom']);
  $col_4_opts = table_options_translator(
                 ['t' => 'num',
                  'width' => 80,
                  'border' =>'bottom',
                  'background-color' => '#ffff00',
                  'numberformat' => '#,##0.00\ [$EUR];[Red]\-#,##0.00\ [$EUR]']);
  $col_5_opts = table_options_translator(
                 ['t' => 'num',
                  'width' => 80,
                  'formula' => '=RC[-1]*RC[-2]',
                  'border' => ['left','bottom'],
                  'background-color' => '#ff2222',
                  'numberformat' => '#,##0.00\ [$EUR];[Red]\-#,##0.00\ [$EUR]']);
  $x->cell('apple' ,$col_1_opts);
  $x->cell('red'   ,$col_2_opts);
  $x->cell(2.2     ,$col_3_opts);
  $x->cell(2.55    ,$col_4_opts);
  $x->cell(2.2*2.55  ,$col_5_opts);
  $x->nrow();

  $x->cell('peach' ,$col_1_opts);
  $x->cell('yellow',$col_2_opts);
  $x->cell(4.9     ,$col_3_opts);
  $x->cell(3.11    ,$col_4_opts);
  $x->cell(4.9*3.11  ,$col_5_opts);
  $x->nrow();

  $x->cell('nut'   ,$col_1_opts);
  $x->cell('brown' ,$col_2_opts);
  $x->cell(0.45    ,$col_3_opts);
  $x->cell(5.02    ,$col_4_opts);
  $x->cell(0.45*5.02   ,$col_5_opts);
  $x->nrow();
  print $x->get();

Automatic conversion of options

The HtmlTable can do this option translation automatically. If the options array contains the "type" => "uni" element the HtmlTable automatically call the table_options_translator() on whole option array.

Note: Most options are converted to css style.

The result of this that we can generate Excel XML and HTML tables with same codes with minimal difference. Look the next example which generate exactly same tables than previous, but uses same codes to generate both output: 

 //If this variable is
 // TRUE  - This code generates an Excel XML table
 // FALSE - This code generates a HTML table
 $excel_or_html = true;

 $x = $excel_or_html ? (new ExcelXmlDocument()):(new HtmlTable());
 if($excel_or_html)
     $x->setHtmlHeaders("sameashtml.xml");
 $x->name('Groceries');
 //Title
 $x->cell('Shopping at the grocery',
          ['type'=>'uni','size' => 20,'strong' => 'yes','wrap' => 'off']);
 $x->nrow();
 //Header
 $x->cells(['Item','Color','Weight','Unit price','Sum'],
            ['type'=>'uni',
             'strong' => 'yes',
             'border' => 'all',
             'background-color' => '#aaaaaa']);
 $x->nrow();
 //Value cells
 $col_1_opts = ['type' => 'uni',
                'width' => 100,
                'strong' => 'yes',
                'border' => ['left','bottom']];
 $col_2_opts = ['type' => 'uni',
                'border' => 'bottom',
                'italic' => 'yes'];
 $col_3_opts = ['type' => 'uni',
                't' => 'num',
                'border' => 'bottom'];
 $col_4_opts = ['type' => 'uni',
                't' => 'num',
                'width' => 80,
                'border' =>'bottom',
                'background-color' => '#ffff00',
                'numberformat' => '#,##0.00\ [$EUR];[Red]\-#,##0.00\ [$EUR]'];
 $col_5_opts = ['type' => 'uni',
                't' => 'num',
                'width' => 80,
                'formula' => '=RC[-1]*RC[-2]',
                'border' => ['left','bottom'],
                'background-color' => '#ff2222',
                'numberformat' => '#,##0.00\ [$EUR];[Red]\-#,##0.00\ [$EUR]'];
 $x->cell('apple' ,$col_1_opts);
 $x->cell('red'   ,$col_2_opts);
 $x->cell(2.2     ,$col_3_opts);
 $x->cell(2.55    ,$col_4_opts);
 $x->cell(2.2*2.55  ,$col_5_opts);
 $x->nrow();

 $x->cell('peach' ,$col_1_opts);
 $x->cell('yellow',$col_2_opts);
 $x->cell(4.9     ,$col_3_opts);
 $x->cell(3.11    ,$col_4_opts);
 $x->cell(4.9*3.11  ,$col_5_opts);
 $x->nrow();

 $x->cell('nut'   ,$col_1_opts);
 $x->cell('brown' ,$col_2_opts);
 $x->cell(0.45    ,$col_3_opts);
 $x->cell(5.02    ,$col_4_opts);
 $x->cell(0.45*5.02   ,$col_5_opts);
 $x->nrow();
 print $x->get();

The result of the code above with $excel_or_html = true :

exceltable2.png

The result of the code above with $excel_or_html = false :

exceltableashtml.png

You can use this functionality for SQL queries with to_table() function. The to_table() uses the ExcelXmlDocument and HtmlTable classes to format query results. Read Query formatter documentation to learn it.