【Laravel】使用phpoffice/phpspreadsheet导入数据

【Laravel】使用phpoffice/phpspreadsheet导入数据

一、安装PHPExcel

1.下载：PHPExcel  https://github.com/PHPOffice/PHPExcel 

2.解压后：Classes文件夹改名为PHPExcel 

3.把文件夹PHPExcel 放到根目录vendor文件夹下

或者

1、composer安装：

composer require phpoffice/phpspreadsheet

二、控制器引用

use \PhpOffice\PhpSpreadsheet\IOFactory;

use \PhpOffice\PhpSpreadsheet\Style\NumberFormat;

use \PhpOffice\PhpSpreadsheet\Spreadsheet;

<?php
public function import(){
    header("content-type:text/html;charset=utf-8");

//上传excel文件
    $files = request()->file();

//将文件保存到public/uploads目录下面
    try {
        validate(['image'=>'fileSize:1048576|fileExt:xls'])
            ->check($files);

        $savename = [];
        foreach($files as $file){
            $savename[] = \think\facade\Filesystem::disk('public')->putFile( 'billfile', $file,'md5');
        }

    } catch (think\exception\ValidateException $e) {
        return json(['status' => '1', 'message' => $e->getMessage()]);
    }
//获取文件路径
    $filePath = ROOT_PATH().'/public/uploads/'.$savename[0];
    $spreadsheet = IOFactory::load($filePath);
    $sheetData = $spreadsheet->getActiveSheet()->toArray(true, true, true, true,true);
    $row_num = count($sheetData);

    $now_time = time();
    $import_data = []; //数组形式获取表格数据
    for ($i = 2; $i <= $row_num; $i++) {

        $bank_deal_no = $sheetData[$i]['A'];
        $account_name = $sheetData[$i]['B'];
        $bank_card = $sheetData[$i]['C'];
        $deal_money = $sheetData[$i]['D'];
        $deal_time = $sheetData[$i]['E'];

        if(!empty($bank_deal_no) && !empty($account_name) && !empty($bank_card) && !empty($deal_money) && !empty($deal_time) ){
            $import_data[$i]['bank_deal_no'] = $bank_deal_no;
            $import_data[$i]['account_name'] = $account_name;
            $import_data[$i]['bank_card'] = $bank_card;
            $import_data[$i]['deal_money'] = $deal_money;
            $import_data[$i]['deal_time'] = $deal_time;
            $import_data[$i]['create_time'] = $now_time;
            $import_data[$i]['update_time'] = $now_time;
        }
    }

    sort($import_data);

    if (empty($import_data)) {
        return json(['status' => '1', 'message' => '数据解析失败']);
    }

    $total_num = count($import_data);
    if ($total_num > 100) {
        return json(['status' => '1', 'message' => '数据超出限制，最多100条']);
    }

//校验是否重复:交易流水号
    $data_array = array_column($import_data, 'bank_deal_no');
    $data_ids = implode(',', $data_array);
    $result_data = Db::name('user_bank_bill')
        ->field('bank_deal_no')
        ->where('bank_deal_no', 'in', $data_ids)
        ->select()
        ->toArray();

    $error_message = '';
    if (!empty($result_data)) {
        $result_data_array = array_column($result_data, 'bank_deal_no');
        $result_data_ids = implode(',', $result_data_array);
        $error_message = '以下流水号有重复，已筛选出: '.$result_data_ids;
        foreach ($import_data as $key => $value) {
            if(in_array($value['bank_deal_no'],$result_data_array)){
                unset($import_data[$key]);
            }
        }
    }


    if(!empty($import_data)){
//将数据保存到数据库
        $res = Db::name('user_bank_bill')->insertAll($import_data);
        if ($res) {
            return json(['status' => '2', 'message' => '操作成功','result'=>$error_message]);
        } else {
            return json(['status' => '1', 'message' => '提交失败，请刷新重试']);
        }
    }

    return json(['status' => '2', 'message' => '数据错误','result' => $error_message]);



}