How to import Excel data with PHPExcel-PHP Tutorial-php.cn

How to import Excel data with PHPExcel

不言

Release： 2023-04-02 11:06:01

Original

4861 people have browsed it

This article mainly introduces the method of importing Excel data into PHPExcel. It has certain reference value. Now I share it with you. Friends in need can refer to it.

1: Use composer to download phpoffice/phpexcel or Directly download the installation package

composer require phpoffice/phpexcel

Copy after login

二

1: Import data

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

Note: If your Excel file is encrypted, it cannot be read. I really wasted a lot of time on this. If anyone knows how to do it, Solved, please let me know. Thank you.

Let’s take a look at how to implement it

Implementation method

  public static function importExcel($file = &#39;&#39;, $sheet = 0)
    {        
    $file = iconv("utf-8", "gb2312", $file);   //转码
        if (empty($file) OR !file_exists($file)) {            
        die(&#39;file not exists!&#39;);
        }        
        $objRead = new PHPExcel_Reader_Excel2007();   //建立reader对象
        if (!$objRead->canRead($file)) {            
        $objRead = new PHPExcel_Reader_Excel5();            
        if (!$objRead->canRead($file)) {                
        die(&#39;No Excel!&#39;);
            }
        }        
        $cellName = [&#39;A&#39;, &#39;B&#39;, &#39;C&#39;, &#39;D&#39;, &#39;E&#39;, &#39;F&#39;, &#39;G&#39;, &#39;H&#39;, &#39;I&#39;, &#39;J&#39;, &#39;K&#39;, &#39;L&#39;, &#39;M&#39;, &#39;N&#39;, &#39;O&#39;, &#39;P&#39;, &#39;Q&#39;,
            &#39;R&#39;, &#39;S&#39;, &#39;T&#39;, &#39;U&#39;, &#39;V&#39;, &#39;W&#39;, &#39;X&#39;, &#39;Y&#39;, &#39;Z&#39;, &#39;AA&#39;, &#39;AB&#39;, &#39;AC&#39;, &#39;AD&#39;, &#39;AE&#39;, &#39;AF&#39;, &#39;AG&#39;, &#39;AH&#39;, &#39;AI&#39;,
            &#39;AJ&#39;, &#39;AK&#39;, &#39;AL&#39;, &#39;AM&#39;, &#39;AN&#39;, &#39;AO&#39;, &#39;AP&#39;, &#39;AQ&#39;, &#39;AR&#39;, &#39;AS&#39;, &#39;AT&#39;, &#39;AU&#39;, &#39;AV&#39;, &#39;AW&#39;, &#39;AX&#39;, &#39;AY&#39;, &#39;AZ&#39;];        $obj = $objRead->load($file);  //建立excel对象
        $currSheet = $obj->getSheet($sheet);   //获取指定的sheet表
        $columnH = $currSheet->getHighestColumn();   //取得最大的列号
        $columnCnt = array_search($columnH, $cellName);        
        $rowCnt = $currSheet->getHighestRow();   //获取总行数

        $data = [];        
        for ($_row = 1; $_row <= $rowCnt; $_row++) {  //读取内容
            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;
    }

Copy after login

//Lite version

 public static function importExcel($file = &#39;&#39;, $sheet = 0)
    {        $file = iconv("utf-8", "gb2312", $file);   //转码
        if (empty($file) OR !file_exists($file)) {            
        die(&#39;file not exists!&#39;);
        }        $objRead = new PHPExcel_Reader_Excel2007();   //建立reader对象
        if (!$objRead->canRead($file)) {            
        $objRead = new PHPExcel_Reader_Excel5();            
        if (!$objRead->canRead($file)) {                
        die(&#39;No Excel!&#39;);
            }
        } 
        $obj = $objRead->load($file);  //建立excel对象
        $data =$obj->getSheet($sheet)->toArray();//获取为数组

        $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;
    }

Copy after login

Note that if the file is too large, PHP will take too long to read, we can set it.

 set_time_limit(0);//0代表永久

Copy after login

The above is the entire content of this article. I hope it will be helpful to everyone's study. For more related content, please pay attention to the PHP Chinese website!

Related recommendations:

How to import data from PHP Excel to MySQL database

php method to remove BOM headers from project files in batches

The above is the detailed content of How to import Excel data with PHPExcel. For more information, please follow other related articles on the PHP Chinese website!