/**
     * 解析 Excel 数据并写入到数据库
     * @param string $file Excel 路径名文件名
     * @param array $header 表头对应字段信息 [\'A\'=>\'field1\', \'B\'=>\'field2\', ...]
     * @param int $perLimit 每次一次性写入数据库中的行数
     * @param mixed $insertFunc 写入数据库的回调函数，可以用匿名函数
     * @param string $type Excel2007|Excel5
     * @return int
     */
    public static function parse($file, $header, $perLimit, $insertFunc, $type = \'\')
    {
        $type = self::getType($file, $type);
        $objReader = \PHPExcel_IOFactory::createReader($type);
        $objPHPExcel = $objReader->load($file);
        // 数据数组
        $data = [];
        // 已导入数据计数
        $count = 0;
        // 跳过第一行
        foreach ($objPHPExcel->getSheet()->getRowIterator(2) as $row) {
            // 逐个单元格读取，减少内存消耗
            $cellIterator = $row->getCellIterator();
            // 不跳过空值
            $cellIterator->setIterateOnlyExistingCells();
            // 只读取显示的行、列，跳过隐藏行、列
            if ($objPHPExcel->getActiveSheet()->getRowDimension($row->getRowIndex())->getVisible()) {
                $rowData = [];
                foreach ($cellIterator as $cell) {
                    if ($objPHPExcel->getActiveSheet()->getColumnDimension($cell->getColumn())->getVisible()) {
                        if (isset($header[$cell->getColumn()])) {
                            $rowData[$header[$cell->getColumn()]] = $cell->getValue();
                        }
                    }
                }
                $data[] = $rowData;
                $count++;
                // 数据分批写入数据库，防止一条SQL太长数据库不支持
                if ($count && $count % $perLimit == 0) {
                    $insertFunc($data);
                    // 清空已有数据
                    $data = [];
                }
            }
        }
        // 写入剩余数据
        if ($data) {
            $insertFunc($data);
        }

        return $count;
    }

    /**
     * 自动获取 Excel 类型
     * @param string $file Excel 路径名文件名
     * @param string $type Excel2007|Excel5
     * @return string
     * @throws Exception
     */
    private static function getType($file, $type = \'\')
    {
        if (!$type) {
            $ext = pathinfo($file, PATHINFO_EXTENSION);
            switch ($ext) {
                case \'xls\' :
                    $type = \'Excel5\';
                    break;
                case \'xlsx\' :
                    $type = \'Excel2007\';
                    break;
                default:
                    throw new Exception(\'请指定Excel的类型\');
            }
        }
        return $type;
    }

    /**
     * 将 Excel 时间转为标准的时间格式
     * @param $date
     * @param bool $time
     * @return array|int|string
     */
    public static function excelTime($date, $time = false)
    {
        if (function_exists(\'GregorianToJD\')) {
            if (is_numeric($date)) {
                $jd = GregorianToJD(1, 1, 1970);
                $gregorian = JDToGregorian($jd + intval($date) - 25569);
                $date = explode(\'/\', $gregorian);
                $date_str = str_pad($date [2], 4, \'0\', STR_PAD_LEFT)
                    . "-" . str_pad($date [0], 2, \'0\', STR_PAD_LEFT)
                    . "-" . str_pad($date [1], 2, \'0\', STR_PAD_LEFT)
                    . ($time ? " 00:00:00" : \'\');

                return $date_str;
            }
        } else {
            $date = $date > 25568 ? $date + 1 : 25569;
            $ofs = (70 * 365 + 17 + 3) * 86400;
            $date = date("Y-m-d", ($date * 86400) - $ofs) . ($time ? " 00:00:00" : \'\');
        }

        return $date;
    }

/**
     * 创建上传表单.
     *
     * @return \think\Response
     */
    public function import()
    {
        $field = [
            Form::uploadFileOne(\'file\', \'上传Excel文件\', Url::buildUrl(\'admin/setting.system_config/file_upload?file=file\')),
        ];
        $form = Form::make_post_form(\'导入\', $field, Url::buildUrl(\'import_save\'), 2);
        $this->assign(compact(\'form\'));
        return $this->fetch(\'public/form-builder\');
    }

    /**
     * 导入文件内容至数据库
     *
     * @param Request $request
     */
    public function import_save(Request $request)
    {
        $data = Util::postMore([
            [\'file\', []],
        ], $request);
        if (count($data[\'file\']) < 1) return Json::fail(\'请上传Excel文件\');
        $file = app()->getRootPath() . \'public\' . $data[\'file\'][0];//获取文件的绝对路径
        $header = [\'A\' => \'username\', \'B\' => \'student_no\', \'C\' => \'classname\', \'D\' => \'phone\'];
        $result = PHPExcelService::parse($file, $header, 100, function ($data) {
            $StudentsModel = new StudentsModel();
            $insertData = [];
            foreach ($data as $k => $v) {
                if (!$StudentsModel->be([\'phone\' => $v[\'phone\'], \'student_no\' => $v[\'student_no\']])) {
                    $insertData[] = [
                        \'uid\' => 0,
                        \'username\' => $v[\'username\'],
                        \'phone\' => $v[\'phone\'],
                        \'student_no\' => $v[\'student_no\'],
                        \'classname\' => $v[\'classname\'],
                    ];
                }
            }
            if (!empty($insertData)) $StudentsModel->saveAll($insertData);
        });
        if(!$request) return Json::fail(\'导入失败！\');
        return Json::successful(\'导入成功!\');
    }