搜索
简帛阁>技术文章>php基于phpspreadsheet实现导入Excel数据

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,'导入失败');
    }
}

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

phpspreadsheet安装:composerrequirephpoffice/phpspreadsheetusePhpOffice\PhpSpreadsheet\Spreadsheet;use
phpspreadsheet批量导入使用的进阶版关联文章:thinkphpthinkadmin使用phpspreadsheet批量导入excel数据https://blogcsdnnet/qq_363
简便方法:使用PHP原生导出Excel和CSV文件|LaravelChina社区usePhpOffice\PhpSpreadsheet\Spreadsheet;usePhpOffice\PhpSpre
单个sheet页Excel2003版最大行数是65536行。Excel2007开始的版本最大行数是1048576行。Excel2003的最大列数是256列,2007以上版本是16384列。xlswr
一、安装PhpSpreadsheetcomposerrequirephpoffice/phpspreadsheet二、控制器代码publicfunctionimportExcel(){<!>
本文以导入学生成绩表为例,给大家讲解使用PhpSpreadsheetExcel导入的MySQL数据库。准备首先我们需要准备一张MySQL表,表名t_student,表结构如下:CREATETABL
:演示的页面前端我用的layui框架,前端这些样式细节我并没有详细说明,这篇文章主要说的是后端一些细节关联文章phpspreadsheet的安装和批量导出excel数据https://blogcsd
先生成Excel模板,然后导入Excel数据到mysql,每条数据对应图片上传到阿里云1<?php2/**3*CreatedbyPhpStorm4*User:Administrator5*Da
Excel是一个出色的数据管理工具,如果能把Excel的全部潜能发挥出来,其功能不亚于一个完成的程序语言。可是大多数人对Excel的使用仅限于做表格,填写数据,会使用SUM函数进行自动求和的已经是高
usePhpOffice\PhpSpreadsheet\IOFactory;usePhpOffice\PhpSpreadsheet\Spreadsheet;classXlsx{publicstatic