Excel Export Formatting Demo   Tips

Latest Stable Version Latest Unstable Version Total Downloads Monthly Downloads Daily Downloads
Thankful to Krajee!
to get more out of us.

NOTE: This extension depends on the kartik-v/yii2-krajee-base extension which in turn depends on the yiisoft/yii2-bootstrap extension. Check the composer.json for this extension's requirements and dependencies that may be updated by composer.

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.

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
echo GridView::widget([
    'dataProvider'=>$dataProvider,
    'autoXlFormat'=>true,
    'export'=>[
        'fontAwesome'=>true,
        'showConfirmAlert'=>false,
        'target'=>GridView::TARGET_BLANK
    ],
    'columns'=>[
        [
            'attribute'=>'code', 
            'format'=>'text', 
            'width'=>'100px', 
            'pageSummary'=>'Total'
        ],
        [
            'attribute'=>'name', 
            'format'=>'text', 
            'width'=>'120px'
        ],
        [
            'attribute'=>'buy_date', 
            'format'=>['date', 'php:d-M-Y'], 
            'width'=>'100px'
        ],
        [
            'attribute'=>'launch_time', 
            'format'=>['time', 'php:g:i a'], 
            'hAlign'=>'center', 
            'width'=>'100px'
        ],
        [
            'attribute'=>'quantity', 
            'format'=>'integer', 
            'hAlign'=>'right', 
            'width'=>'100px', 
            'pageSummary'=>true
        ],
        [
            'attribute'=>'price', 
            'format'=>['decimal', 2], 
            'hAlign'=>'right', 
            'width'=>'100px', 
            'pageSummary'=>true
        ],
        [
            'class'=>'kartik\grid\FormulaColumn', 
            'label'=>'Amount', 
            'format' => ['decimal', 2],
            'value'=>function ($model, $key, $index, $widget) { 
                $p = compact('model', 'key', 'index');
                return $widget->col(4, $p) * $widget->col(5, $p) ;
            }, 
            'hAlign'=>'right', 
            'width'=>'120px', 
            'pageSummary'=>true
        ],
        [
            'attribute'=>'created_on', 
            'format'=>['datetime', 'php:d-M-y H:i:s'], 
            'width'=>'140px'
        ]
    ],
    'pjax'=>true,
    'showPageSummary'=>true,
    'panel'=>[
        'type'=>'primary',
        'heading'=>'Products'
    ]
]);

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
echo GridView::widget([
    'dataProvider'=>$dataProvider,
    'autoXlFormat'=>true,
    'export'=>[
        'fontAwesome'=>true,
        'showConfirmAlert'=>false,
        'target'=>GridView::TARGET_BLANK
    ],
    'columns'=>[
        [
            'attribute'=>'code', 
            'format'=>'text', 
            'width'=>'100px', 
            'pageSummary'=>'Total'
        ],
        [
            'attribute'=>'name', 
            'format'=>'text', 
            'width'=>'120px'
        ],
        [
            'attribute'=>'buy_date', 
            'format'=>['date', 'php:d-M-Y'], 
            'width'=>'100px'
        ],
        [
            'attribute'=>'launch_time', 
            'format'=>['time', 'php:g:i a'], 
            'hAlign'=>'center', 
            'width'=>'100px'
        ],
        [
            'attribute'=>'quantity', 
            'format'=>'integer', 
            'hAlign'=>'right', 
            'width'=>'100px', 
            'pageSummary'=>true
        ],
        [
            'attribute'=>'price', 
            'format'=>['decimal', 2], 
            'hAlign'=>'right', 
            'width'=>'100px', 
            'pageSummary'=>true
        ],
        [
            'class'=>'kartik\grid\FormulaColumn', 
            'label'=>'Amount', 
            'format' => ['decimal', 2],
            'value'=>function ($model, $key, $index, $widget) { 
                $p = compact('model', 'key', 'index');
                return $widget->col(4, $p) * $widget->col(5, $p) ;
            }, 
            'hAlign'=>'right', 
            'width'=>'120px', 
            'pageSummary'=>true
        ],
        [
            'attribute'=>'created_on', 
            'format'=>['datetime', 'php:d-M-y H:i:s'], 
            'width'=>'140px'
        ]
    ],
    'pjax'=>true,
    'showPageSummary'=>true,
    'panel'=>[
        'type'=>'primary',
        'heading'=>'Products'
    ]
]);