Laravel - PhpOffice

 DBExportExcel：

use DB;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

public function DBExportExcel($data, $newFile)
    {
        if(empty($data)){   # 空数据
            return [];
        }
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        # 下标 - title
        $indexs = array_keys(json_decode(json_encode($data[0]),true));
        foreach($indexs as $kindex => $vindex){
            $char = chr($kindex + 65);
            //设置第一栏的标题
            $sheet->getColumnDimension($char)->setAutoSize(true);
            $sheet->setCellValue($char . '1', $vindex);
        }
        # 最大单元列
        $maxColumn = chr(count($indexs) + 64);
        # 编号ID名称
        $id = $indexs[0];
//        $sheet->setCellValue('A1', 'hotel_id');
//        $sheet->setCellValue('B1', 'star');
        # 单元格样式
        $styleArray = [
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
                'rotation' => 90,
                'startColor' => [
                    'argb' => '32CD32',
                ],
                'endColor' => [
                    'argb' => '32CD32',
                ],
            ],
        ];
        foreach($data as $k => $v){
            $k += 2;
            # 编号 ID
            if(in_array($v->$id,$this->arr())){
                $sheet->getStyle('A'.$k.':'.$maxColumn.$k)->applyFromArray($styleArray);
                $sheet->getStyle('A'.$k)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
            }
            foreach($indexs as $kindex => $vindex){
                $sheet->setCellValue(chr($kindex + 65).$k, $v->$vindex);
            }
//            $sheet->setCellValue('A'.$k, $v->hotel_id)
        }

//        $writer = new Xlsx($spreadsheet);
//        $writer->save('test.xlsx');
//        die;
//        $filename = $newFile . $this->ext;

        $filename = $newFile;
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//        $writer->save('php://output');
        $writer->save(public_path().'/'.$filename);
        return;
    }

ExcelImportDB：

public function ExcelImportDB($inputFile, $highestRow){
        $spreadsheet = IOFactory::load($inputFile);
        $excelSheet = $spreadsheet->getSheet(1);// 读取第一个工作表         
//        $highestRow = $excelSheet->getHighestRow();// 取得总行数 
        $highestColumn = $excelSheet->getHighestColumn();// 取得总列数
        # 数据存储arr
        $arr = [];
        $title = [];
        # 读取Excel数据
        for($i=1; $i<=$highestRow; $i++){
            # title
            if($i == 1){
                for($t='A'; $t<=$highestColumn; $t++){
                    $tmp = $excelSheet->getCell( $t.$i)->getValue();
                    $title[] = strtolower(str_replace(' ','_',$tmp));
                }
                continue;
            }

            if(empty($title)){
                return [];
            }
            # getCellValue
            $row = [];
            for($j='A'; $j<=$highestColumn; $j++){
                # nil data , break;
                if(empty($excelSheet->getCell('A'.$i)->getValue())){
                    break;
                }
                $row[] = $excelSheet->getCell($j.$i)->getValue();
            }
            #
            if(empty($row)){
                break;
            }
            # 导入db - excel，获取列并加入标识字段 ----------------------------abcdefg
            $next = ord(strtolower($highestColumn)) + 1;
            $columns = range('a',chr($next));

            $res = DB::insert('insert into excel ('.implode(',',$columns).') values (...)');
        }
        return;
    }