Krajee

Excel Export Formatting Demo

  1. Home
  2. Excel Export Formatting
  3. Demo
Star Fork Watch Follow
Thankful to Krajee! BUY A COFFEEor to get more out of us.
Demonstration examples and scenarios for Excel Export of data. You would see the changes only on exporting data as Excel format via the export menu button dropdown for each grid. Note that the GridView pjax property has been enabled (set to true) for all the demo examples.

Tip

Not seeing the updated content on this page! Hard refresh your browser to clean cache for this page (e.g. SHIFT-F5 on Windows Chrome)

Example 1

Auto formatting example. This example showing auto formatting of cells for excel export by setting the autoXlFormat property at the GridView widget level to true. It will use the format setting for each column to auto-derive the excel cell format. Click on export dropdown menu on top right above and select excel export.

Click for demo
  1. echo GridView::widget([
  2.     'dataProvider'=>$dataProvider,
  3.     'autoXlFormat'=>true,
  4.     'toggleDataContainer' => ['class' => 'btn-group mr-2 me-2'],
  5.     'export'=>[
  6.         'showConfirmAlert'=>false,
  7.         'target'=>GridView::TARGET_BLANK
  8.     ],
  9.     'columns'=>[
  10.         [
  11.             'attribute'=>'code', 
  12.             'format'=>'text', 
  13.             'width'=>'100px', 
  14.             'pageSummary'=>'Total'
  15.         ],
  16.         [
  17.             'attribute'=>'name', 
  18.             'format'=>'text', 
  19.             'width'=>'120px'
  20.         ],
  21.         [
  22.             'attribute'=>'buy_date', 
  23.             'format'=>['date', 'php:d-M-Y'], 
  24.             'width'=>'100px'
  25.         ],
  26.         [
  27.             'attribute'=>'launch_time', 
  28.             'format'=>['time', 'php:g:i a'], 
  29.             'hAlign'=>'center', 
  30.             'width'=>'100px'
  31.         ],
  32.         [
  33.             'attribute'=>'quantity', 
  34.             'format'=>'integer', 
  35.             'hAlign'=>'right', 
  36.             'width'=>'100px', 
  37.             'pageSummary'=>true
  38.         ],
  39.         [
  40.             'attribute'=>'price', 
  41.             'format'=>['decimal', 2], 
  42.             'hAlign'=>'right', 
  43.             'width'=>'100px', 
  44.             'pageSummary'=>true
  45.         ],
  46.         [
  47.             'class'=>'kartik\grid\FormulaColumn', 
  48.             'label'=>'Amount', 
  49.             'format' => ['decimal', 2],
  50.             'value'=>function ($model, $key, $index, $widget) { 
  51.                 $p = compact('model', 'key', 'index');
  52.                 return $widget->col(4, $p) * $widget->col(5, $p) ;
  53.             }, 
  54.             'hAlign'=>'right', 
  55.             'width'=>'120px', 
  56.             'pageSummary'=>true
  57.         ],
  58.         [
  59.             'attribute'=>'created_on', 
  60.             'format'=>['datetime', 'php:d-M-y H:i:s'], 
  61.             'width'=>'140px'
  62.         ]
  63.     ],
  64.     'pjax'=>true,
  65.     'showPageSummary'=>true,
  66.     'panel'=>[
  67.         'type'=>'primary',
  68.         'heading'=>'Products'
  69.     ]
  70. ]);

Example 2

Formatting each column (overrides the autoXlFormat setting). This example showing different formats for columns - i.e. scientific number format and different date or time formats.

Click for demo
  1. echo GridView::widget([
  2.     'dataProvider'=>$dataProvider,
  3.     'autoXlFormat'=>true,
  4.     'toggleDataContainer' => ['class' => 'btn-group mr-2 me-2'],
  5.     'export'=>[
  6.         'showConfirmAlert'=>false,
  7.         'target'=>GridView::TARGET_BLANK
  8.     ],
  9.     'columns'=>[
  10.         [
  11.             'attribute'=>'code', 
  12.             'format'=>'text', 
  13.             'width'=>'100px', 
  14.             'pageSummary'=>'Total'
  15.         ],
  16.         [
  17.             'attribute'=>'name', 
  18.             'format'=>'text', 
  19.             'width'=>'120px'
  20.         ],
  21.         [
  22.             'attribute'=>'buy_date', 
  23.             'format'=>['date', 'php:d-M-Y'], 
  24.             'xlFormat'=>'mmm\-dd\, yyyy',  // different date format
  25.             'width'=>'100px'
  26.         ],
  27.         [
  28.             'attribute'=>'launch_time', 
  29.             'format'=>['time', 'php:g:i a'], 
  30.             'hAlign'=>'center', 
  31.             'xlFormat'=>'Long Time', // long time
  32.             'width'=>'100px'
  33.         ],
  34.         [
  35.             'attribute'=>'quantity', 
  36.             'format'=>'integer', 
  37.             'hAlign'=>'right', 
  38.             'width'=>'100px', 
  39.             'xlFormat'=>'0\.00E+00', // scientific
  40.             'pageSummary'=>true
  41.         ],
  42.         [
  43.             'attribute'=>'price', 
  44.             'format'=>['decimal', 2], 
  45.             'hAlign'=>'right', 
  46.             'width'=>'100px', 
  47.             'pageSummary'=>true
  48.         ],
  49.         [
  50.             'class'=>'kartik\grid\FormulaColumn', 
  51.             'label'=>'Amount', 
  52.             'format' => ['decimal', 2],
  53.             'value'=>function ($model, $key, $index, $widget) { 
  54.                 $p = compact('model', 'key', 'index');
  55.                 return $widget->col(4, $p) * $widget->col(5, $p) ;
  56.             }, 
  57.             'hAlign'=>'right', 
  58.             'width'=>'120px', 
  59.             'pageSummary'=>true
  60.         ],
  61.         [
  62.             'attribute'=>'created_on', 
  63.             'format'=>['datetime', 'php:d-M-y H:i:s'], 
  64.             'width'=>'140px'
  65.         ]
  66.     ],
  67.     'pjax'=>true,
  68.     'showPageSummary'=>true,
  69.     'panel'=>[
  70.         'type'=>'primary',
  71.         'heading'=>'Products'
  72.     ]
  73. ]);

Comments & Discussion

Note

You can now visit the Krajee Webtips Q & A forum for searching OR asking questions OR helping programmers with answers on these extensions and plugins. For asking a question click here. Select the appropriate question category (i.e. Krajee Plugins) and choose this current page plugin in the question related to field.

The comments and discussion section below are intended for generic discussions or feedback for this plugin. Developers may not be able to search or lookup here specific questions or tips on usage for this plugin.

 
4,690,272 visitors to Krajee Yii2 Demos since 22-May-2017