Prevent from scientific notation numbers in Yii2 kartik export to excel cell format

Question

I'm using kartik export widget for exporting my grid view in excel. I am able to export all the data but there is an issue, I have a column name imsi which is in text, but after exporting it this column is having all the same numbers i.e. all the IMSI numbers are same. See below

Grid Columns

 $gridColumns =[
                [
                    'class'=>'kartik\grid\SerialColumn',

                ],
                 'id',
                ['attribute'=>'imsi','format'=>'text'],
                'sim_number'
  ]

How can I set the correct format? Any help would be highly appreciated.

ttrasn · Accepted Answer · 2019-12-24T07:50:43.983

3

Cause your cell data (imsi) is number. excel or some other programs. when size of cell is fewer than lenght of number. they change number to Scientific number or some thing like this.

to prevent from doing this. you must increase width of cell.

$gridColumns =[
    ['class'=>'kartik\grid\SerialColumn'],
    'id',
    [
        'attribute'=>'imsi',
        'vAlign'=>'middle',
        'width'=>'200px',
        'format'=>'raw',// or text
        'value'=>function($model){
            return $model->imsi.' ';
        }
    ],
    'sim_number'
]

update: after discussion. we found when we add spaces to value it will be force to text.

PS. If you want to use this numbers from excel file. you must trim to remove spaces.

edited Dec 24 '19 at 07:50

answered Dec 23 '19 at 06:29

ttrasn

4,322
4
26
43

I have tried your solution but still the issue remains same – Moeez Dec 23 '19 at 06:39
@Faisal mybe `200px` is not enough. did you increase it to bigger ? – ttrasn Dec 23 '19 at 06:56
yes I increased it to `2000px` but still same issue – Moeez Dec 23 '19 at 06:57
If you didnt handle numbers after exporting. you can add some symbol in your column. see this [link](https://superuser.com/questions/452832/turn-off-scientific-notation-in-excel/1301584) unfortunately this is problem of excel viewer. it force your data to scientific notation – ttrasn Dec 23 '19 at 07:14
can we change the notation? – Moeez Dec 24 '19 at 06:49
@Faisal Unfortunately excel does not allow you to turn this functionality off by default. However if you select your data, right click, and click "Format cells..." and choose Number you can stop excel from changing your data to scientific notation. – ttrasn Dec 24 '19 at 07:04
Yes it can be done but the main problem is that the data is already changed before formating it – Moeez Dec 24 '19 at 07:10
I have already attached a snapshot of this issue in my question – Moeez Dec 24 '19 at 07:10
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/204770/discussion-between-ttrasn-and-faisal). – ttrasn Dec 24 '19 at 07:28

score 2 · Answer 2 · answered Apr 07 '21 at 14:49

You can use:

$gridColumns =[
    ['class'=>'kartik\grid\SerialColumn'],
    'id',
    [
        'attribute'=>'imsi',
        'contentOptions' => ['cellFormat' => DataType::TYPE_STRING], 
    ],
    'sim_number'
]

remember to include

use PhpOffice\PhpSpreadsheet\Cell\DataType;

score 0 · Answer 3 · edited Jul 02 '21 at 03:20

0

[
  'attribute'=>'imsi',
  'value' => function ($model) {
    return ' '.$model->imsi;
  }
],

edited Jul 02 '21 at 03:20

Shaido

27,497
23
70
73

answered Jul 01 '21 at 07:26

Abdujalilov Dilshod

1

Welcome to Stack Overflow. Code without any explanation are rarely helpful. Stack Overflow is about learning, not providing snippets to blindly copy and paste. Please edit your question and explain how it answers the specific question being asked. See [How to Answer](https://stackoverflow.com/help/how-to-answer). – Sfili_81 Jul 01 '21 at 14:02
@C.O. - this is not a link-only answer, in fact it does not contain any links, - [From Review](https://stackoverflow.com/review/low-quality-posts/29308122). – dbc Jul 01 '21 at 17:22
I was being an idiot, the review que had a link to the SO post on top and I mistook that as the "answer" in question, sorry. – C.O. Jul 01 '21 at 17:55

Prevent from scientific notation numbers in Yii2 kartik export to excel cell format

3 Answers3