php基于phpspreadsheet实现导入Excel数据

phpspreadsheet安装：

composer require phpoffice/phpspreadsheet

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
//可以生成多种格式类
use PhpOffice\PhpSpreadsheet\IOFactory;
public function uploadExcel()
{

    $upload_file = $_FILES['file']['tmp_name'];
    $ext = strtolower(pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION));
    if ($ext == 'xlsx') {
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $spreadsheet = $reader->load($upload_file);
    }else if ($ext == 'xls') {
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
        $spreadsheet = $reader->load($upload_file);
    }
    $sheet = $spreadsheet->getActiveSheet();
    $row_count = $sheet->getHighestRow();//取得总行数
    $create_time = current_time();
    // 启动事务
    Db::startTrans();
    try{
        for ($row = 2; $row <= $row_count+1; $row++) {
            $old_create_time = $sheet->getCell('A'.$row)->getValue();
            $customer_service_number = (string)$sheet->getCell('B'.$row)->getValue();
            $customer_number = (string)$sheet->getCell('C'.$row)->getValue();
            $order_code = (string)$sheet->getCell('D'.$row)->getValue();
            $customer_region = (string)$sheet->getCell('E'.$row)->getValue();
            $customer_service_name = (string)$sheet->getCell('F'.$row)->getValue();
            $inquiry_source = (string)$sheet->getCell('G'.$row)->getValue();
            $customer_education = (string)$sheet->getCell('H'.$row)->getValue();
            $subject = (string)$sheet->getCell('I'.$row)->getValue();
            $order_type = (string)$sheet->getCell('J'.$row)->getValue();
            $remark = (string)$sheet->getCell('K'.$row)->getValue();
            $integral_recharge = (string)$sheet->getCell('M'.$row)->getValue();
            $payment_paypal = (string)$sheet->getCell('N'.$row)->getValue();
            $payment_emt = (string)$sheet->getCell('O'.$row)->getValue();
            $payment_integral = (string)$sheet->getCell('P'.$row)->getValue();
            $payment_alipay = (string)$sheet->getCell('Q'.$row)->getValue();
            $payment_wachat = (string)$sheet->getCell('R'.$row)->getValue();
            $payment_voucher = (string)$sheet->getCell('S'.$row)->getValue();
            $old_order_status = (string)$sheet->getCell('T'.$row)->getValue();
            $old_order_deliver_time = (string)$sheet->getCell('U'.$row)->getValue();
            $actual_deliver_time = (string)$sheet->getCell('V'.$row)->getValue();
            $matching_operator = (string)$sheet->getCell('W'.$row)->getValue();
            $supervised_teacher = (string)$sheet->getCell('X'.$row)->getValue();
            $order_feedback = (string)$sheet->getCell('Z'.$row)->getValue();
            switch ($old_order_status){
                case '正在匹配中':
                    $order_status = 0;break;
                case '正在修改中':
                    $order_status = 5;break;
                case '正在完成中':
                    $order_status = 10;break;
                case '待跟进':
                    $order_status = 25;break;
                case '待报价':
                    $order_status = 20;break;
                case '反馈待处理':
                    $order_status = 25;break;
                case '客户自行取消':
                    $order_status = 30;break;
                case '难度大无法完成':
                    $order_status = 35;break;
                case '时间问题无法成交':
                    $order_status = 40;break;
                case '价格问题无法成交':
                    $order_status = 45;break;
                case '退单':
                    $order_status = 50;break;
                case '已付款未匹配':
                    $order_status = 60;break;
                case '已完成':
                    $order_status = 100;break;
                default:
                    $order_status = 200;break;
            }
            if(!empty($order_code)){
                $sale_customer_id = Db::table('sale_customer')
                    ->where('order_code','=',$order_code)
                    ->value('sale_customer_id');
                if(empty($sale_customer_id)){
                    $sale_customer_id = Db::table('sale_customer')
                        ->where('customer_number','=',$customer_number)
                        ->value('sale_customer_id');
                    if(!empty($sale_customer_id)){
                        Db::table('sale_customer')
                            ->where('sale_customer_id','=',$sale_customer_id)
                            ->inc('order_count',1)
                            ->update();
                    }else{
                        $sale_customer_id = Db::table('sale_customer')
                            ->insertGetId([
                                'customer_number' => $customer_number,
                                'customer_region' => $customer_region,
                                'customer_education' => $customer_education,
                                'order_count' => 1,
                                'create_time' => $create_time
                            ]);
                    }
                    Db::table('sale_customer')
                        ->insert([
                            'sale_customer_id' => $sale_customer_id,
                            'customer_service_name' => $customer_service_name,
                            'customer_service_number' => $customer_service_number,
                            'customer_number' => $customer_number,
                            'order_code' => $order_code,
                            'customer_region' => $customer_region,
                            'inquiry_source' => $inquiry_source,
                            'customer_education' => $customer_education,
                            'subject' => $subject,
                            'order_type' => $order_type,
                            'integral_recharge' => $integral_recharge,
                            'payment_wachat' => $payment_wachat,
                            'payment_alipay' => $payment_alipay,
                            'payment_paypal' => $payment_paypal,
                            'payment_emt' => $payment_emt,
                            'payment_integral' => $payment_integral,
                            'payment_voucher' => $payment_voucher,
                            'order_status' => $order_status,
                            'actual_deliver_time' => $actual_deliver_time,
                            'matching_operator' => $matching_operator,
                            'supervised_teacher' => $supervised_teacher,
                            'order_feedback' => $order_feedback,
                            'remark' => $remark,
                            'create_time' => $create_time,
                            'old_order_status' => $old_order_status,
                            'old_order_deliver_time' => $old_order_deliver_time,
                            'old_create_time' => $old_create_time,
                        ]);
                }
            }

        }
        Db::commit();
        apiJson(200,'导入成功');
    }catch (\Throwable $t){
        Db::rollback();
        Log::write($t->getMessage(),'error');
        apiJson(500,'导入失败');
    }
}

代码仅供参考，根据业务进行修改