laravel PhpSpreadsheet 导入类库

最新推荐文章于 2024-05-16 10:30:18 发布

Sunny_LG

最新推荐文章于 2024-05-16 10:30:18 发布

阅读量894

点赞数

CC 4.0 BY-SA版权

文章标签： PhpSpreadsheet

本文链接：https://blog.csdn.net/Sunny_LG/article/details/97009467

本文介绍了如何在laravel项目中利用PhpSpreadsheet库读取Excel文件，通过`$excel->readExcel($path, 2)`来读取指定工作表的数据。" 106148346,9506434,BFS算法深度解析与最短路径证明,"['算法', '图论', '数据结构']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

<?php
namespace App\Libs;

use \PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
require '../vendor/autoload.php';

ini_set('max_execution_time', 0);
ini_set ('memory_limit', "50000M");

/*  安装步骤  1.composer require phpoffice/phpspreadsheet  */
/*  安装，导入 导出 参照手册  https://www.helloweba.net/php/561.html */
class Excel extends Controller{

    /**  Excel 导入 数据
     * @param $path    string  导入路径
     * @param $readnum  string  从第几行开始读取
     * @return array  返回数据
     *  导入5万以上数据 尽量一条条添加， 导入小量数据 拼接添加
     */
    function readExcel($path,$readnum){
        $reader = IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = IOFactory::load($path);// 载入excel表格
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数
        $highestColumn = $worksheet->getHighestColumn(); // 总列数
        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);
        $data = [];
        for ($row = $readnum; $row <= $highestRow; ++$row) { // 从第几行开始读取
            $row_data = [];
            for ($column = 1; $column <= $highestColumnIndex; $column++) {
                $row_data[] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
            }
            $data[] = $row_data; //获取
        }
        return $data;
    }

}

引用：

$excel= new Excel()

$data = $excel->readExcel($path,2)

DB::beginTransaction();
foreach ($data as $key=>$val){
    $result = ['a'=>$val[0],'b'=>$val[1]]
    \DB::table($table)->insert($result);
    if($key%10000 == 0){
         DB::commit();
        DB::beginTransaction();
    }

}
DB::commit(); // 每隔10000条数据 提交一次