Leading zeros getting truncated while exporting kartik gridview to excel

Question

I am using yii2 kartik's gridview. Have a column in which it has values like R65678, P5675,4568895,006976 etc. Now what happens is while exporting to Excel Some of the values which is having leading zeros getting truncated. So 006976 appears 6976. How can I solve this issue? Is there any way to config the excel format from the grid?

Answer 1

This could be a problem with how excel imports data more than how the gridview is exporting it, you could try exporting the data to csv to check if the gridview is exporting it properly.

When excel imports data it tries to guess what type of value on the cell, 006976 has only digits, so excel will treat it as a number and strip the leading zeroes.

A fix for this would be to prepend a non-numeric character to the value that is being exported, that way excel will treat the value as text and preserve all characters. The Zero width non-joiner works well for that, "\u{200C}".

To prepend the character to the output of the gridview you can do this:

<?= GridView::widget([
        'dataProvider' => $dataProvider,
        'columns' => [
            'column1',
            [
                'attribute' => 'column2',
                'value' => function ($data) {
                    return "\u{200C}" . $data->column2;
                },
            ],
            ...
        ],
    ]); ?>