Phpexcel How to import Excel data

This article mainly introduces the method of Phpexcel import Excel data, has certain reference value, now share to everybody, need friend can refer to

One: Download phpoffice/phpexcel using composer or download the installation package directly

Composer require Phpoffice/phpexcel

Two

1: Import data

Principle: Read the file, get the maximum row of the file, and the maximum column. Then assemble the data and import the database.

Note : If your Excel file is encrypted it is not readable, I really wasted a lot of time on this, if anyone knows how to solve, trouble to tell one or two thank you.

Let's take a look at how to implement

Implementation method

  public static function Importexcel ($file = "', $sheet = 0) {$file = Iconv (" Utf-8 "," gb2312 ", $file);        transcoding if (Empty ($file) OR!file_exists ($file)) {die (' File not exists! ');   } $objRead = new phpexcel_reader_excel2007 ();                    Create the Reader object if (! $objRead->canread ($file)) {$objRead = new Phpexcel_reader_excel5 ();            if (! $objRead->canread ($file)) {die (' No excel! '); }} $cellName = [' A ', ' B ', ' C ', ' D ', ' E ', ' F ', ' G ', ' H ', ' I ', ' J ', ' K ', ' L ', ' M ', ' N ', ' O ', ' P ', ' Q '            , ' R ', ' S ', ' T ', ' U ', ' V ', ' W ', ' X ', ' Y ', ' Z ', ' AA ', ' AB ', ' AC ', ' AD ', ' AE ', ' AF ', ' AG ', ' AH ', ' AI ',        ' AJ ', ' AK ', ' AL ', ' AM ', ' an ', ' AO ', ' APs ', ' AQ ', ' AR ', ' as ', ' at ', ' AU ', ' AV ', ' AW ', ' AX ', ' AY ', ' AZ ';  $obj = $objRead->load ($file);   Create an Excel object $currSheet = $obj->getsheet ($sheet); Gets the specified sheet table $columnH = $currSHeet->gethighestcolumn ();                Get the maximum column number $columnCnt = Array_search ($columnH, $cellName);   $rowCnt = $currSheet->gethighestrow ();                Gets the total number of rows $data = []; for ($_row = 1; $_row <= $rowCnt; $_row++) {//Read contents for ($_column = 0; $_column <= $columnCnt; $_column+                            +) {$cellId = $cellName [$_column]. $_row;                            $cellValue = $currSheet->getcell ($cellId)->getvalue ();            $data [$_row][$_column] = $cellValue;                }} $return _data = []; foreach ($data as $k = + $v) {if ($k > 1) {$test = Array_combine ($data [1]                        , $data [$k]);            Array_push ($return _data, $test);    }} return $return _data; }

Lite version

public static function Importexcel ($file = "', $sheet = 0)    {        $file = iconv (" Utf-8 "," gb2312 ", $file);   transcoding        if (empty ($file) OR!file_exists ($file)) {die                    (' File not exists! ');        }        $objRead = new phpexcel_reader_excel2007 ();   Create the Reader object        if (! $objRead->canread ($file)) {                    $objRead = new Phpexcel_reader_excel5 ();                    if (! $objRead->canread ($file)) {die                        (' No excel! ');            }        }         $obj = $objRead->load ($file);  Create an Excel object        $data = $obj->getsheet ($sheet)->toarray ();//Gets an array        $return _data = [];                foreach ($data as $k = + $v) {                    if ($k > 0) {                        $test = array_combine ($data [0], $data [$k]);                        Array_push ($return _data, $test);            }        }         return $return _data;    }

Note If the file is too large, the PHP read will be too long and we can set it up.

Set_time_limit (0);//0 Representative Permanent

The above is the whole content of this article, I hope that everyone's learning has helped, more relevant content please pay attention to topic.alibabacloud.com!