How to get real number while importing on Laravel Excel maatwebsite?

Question

I'd like to get numbers as the real value while importing a file, e.g:

When I open the csv, cell value: 198610012009011005

But when I import that using Laravel Excel, it'll be formatted to 1.98610012009011E+17

How can I get the real value of the number (198610012009011005) ? I tried bellow code but it didn't work

$data['excel'] = Excel::load($path, function ($reader) {
          $reader->sheet(0, function ($sheet) {
              $sheet->setColumnFormat(["A" => "@"]);
          });
      })->toArray();

Dharma Saputra · Accepted Answer · 2018-01-09T12:25:20.097

2

Actually the value you get is true. 1.98610012009011E+17 is the form of exponential value. But if you want get it as string form try this approach.

You should create a ValueBinder class.

// MyValueBinderClass
use PHPExcel_Cell;
use PHPExcel_Cell_DataType;
use PHPExcel_Cell_IValueBinder;
use PHPExcel_Cell_DefaultValueBinder;

class MyValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
{
    public function bindValue(PHPExcel_Cell $cell, $value = null)
    {

        if (is_numeric($value))
        {
            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);

            return true;
        }

        // else return default behavior
        return parent::bindValue($cell, $value);
    }
}

And then bind it to while you load the csv file:

$myValueBinder = new MyValueBinder;
$data = Excel::setValueBinder($myValueBinder)
    ->load($path)->toArray();

reference: http://www.maatwebsite.nl/laravel-excel/docs/import#formatting

edited Jan 09 '18 at 12:25

answered Jan 09 '18 at 11:33

Dharma Saputra

1,524
12
17

Actually, I've been tried this way as well, but it didn't work I printed data of array returned after set the binder, but the number format didn't change (still 1.98610012009011E+17). Have any idea? I've been search for this issue but didn't get done till now. Anw thanks – Arie Pratama Jan 10 '18 at 00:01
Can you screenshoot `dd` of the data? – Dharma Saputra Jan 10 '18 at 00:28
Here the screenshot of dd : https://drive.google.com/open?id=1pbgOyErCjXjf9cGG8OjiRzK5PR9Q_eLP I created the file (MyValueBinder.php, with namespace App\Helpers) as the reference, and then call the file using this : use App\Helpers\MyValueBinder; $myValueBinder = new MyValueBinder; $data['excel'] = Excel::setValueBinder($myValueBinder)->load($path); dd($data['excel']->toArray()); – Arie Pratama Jan 10 '18 at 06:50
Oh the screenshot, the number is on array name. – Arie Pratama Jan 10 '18 at 06:55
Its strange. I tried it on my local. Here is the output: https://i.imgur.com/6e4dXyB.png. Anyway this is the csv file that I load: https://i.imgur.com/z5anOGj.png – Dharma Saputra Jan 10 '18 at 13:48
Could you upload your code, please? I'll try it on my local. – Arie Pratama Jan 11 '18 at 01:42
Actually it's same with my answer above. https://pastebin.com/WjLNS2jN. Just in case you are missing something. – Dharma Saputra Jan 11 '18 at 06:25
My code was as same as it, but I didn't know why it didn't work. I also found something weird, when I uploaded the file, in my storage it was stored with different extension (e.g I upload csv, store in txt ext.). Then I solved this problem by adding space in the number so the number detected as string. Big thanks.. – Arie Pratama Jan 15 '18 at 05:34

score 1 · Answer 2 · answered Jan 09 '18 at 10:42

1

It's out of the range for integer type .

use string for column type in DB

or cast it

 $number=  (float) $value;;

answered Jan 09 '18 at 10:42

Mahdi Younesi

6,889
2
20
51

How to get real number while importing on Laravel Excel maatwebsite?

2 Answers2