phpspreadsheet 导入导出功能

/* 订单导出
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */

    public function export()
    {
//        $starttime = Request::get('starttime') ? Request::get('starttime') : '';
//        $endtime   = Request::get('endtime') ? Request::get('endtime') : '';
        $data = Db::name('order')
            ->select()
            ->toArray();
        if(empty($data))
        {
            $this->error('你要导出的数据是空的哇！',url('index'));
        }
        $title = [
            'id','date'..... //具体字段根据自己需要来设置
        ];

        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        foreach ($title as $key => $value) {
            $worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
        }

        $row = 2; //从第二行开始
        foreach ($data as $item) {
            $column = 1;
            foreach ($item as $value) {
                $worksheet->setCellValueByColumnAndRow($column, $row, $value);
                $column++;
            }
            $row++;
        }

        $fileName = date('YmdHis');
        $fileType = 'Xlsx';

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); //按照指定格式生成Excel文件
        $this->excelBrowserExport($fileName, $fileType);
        $writer->save('php://output');
    }

 

public function import()
    {
        $info = Session::get('admin');
        $info = json_decode($info,TRUE);
        $file_size = $_FILES['file']['size'];
        if ($file_size > 5 * 1024 * 1024) {
            $this->error('文件大小不能超过5M');
            exit();
        }

        //限制上传表格类型
        $fileExtendName = substr(strrchr($_FILES['file']["name"], '.'), 1);
        //application/vnd.ms-excel  为xls文件类型
        if ($fileExtendName != 'xlsx') {
            $this->error('必须为excel表格，且必须为xlsx格式！');
            exit();
        }

        if (is_uploaded_file($_FILES['file']['tmp_name'])) {
            // 有Xls和Xlsx格式两种
            $objReader = IOFactory::createReader('Xlsx');

            $filename = $_FILES['file']['tmp_name'];
            $objPHPExcel = $objReader->load($filename);  //$filename可以是上传的表格，或者是指定的表格
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
            $pinyin = new Pinyin();
            Db::startTrans();
            try {
                for ($j = 2; $j <= $highestRow; $j++)
                {
                    $data[$j - 2] = [
                        'id' => $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue(),//这一个为参考，多字段可以按照这个继续添加即可
                    ];
                    if (empty($data[$j - 2]['id']))
                    {
                        throw new \Exception('id不能为空！');
                    }
                    $result = Db::name('order')->save($data[$j - 2]);
                }
                Db::commit();
            } catch (\Exception $e) {
                Db::rollBack();
                $this->error($e->getMessage(), url('index'));
            }
            if ($result == true) {
                $this->success('导入成功！', url('index'));
            }
        }
    }

function excelBrowserExport($fileName, $fileType)
{

    //文件名称校验
    if (!$fileName) {
        trigger_error('文件名不能为空', E_USER_ERROR);
    }

    //Excel文件类型校验
    $type = ['Excel2007', 'Xlsx', 'Excel5', 'xls'];
    if (!in_array($fileType, $type)) {
        trigger_error('未知文件类型', E_USER_ERROR);
    }

    if ($fileType == 'Excel2007' || $fileType == 'Xlsx') {
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
        header('Cache-Control: max-age=0');
    } else {
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
        header('Cache-Control: max-age=0');
    }
}