office文件的导出导入

一、导入excel

• tp6导入excel文件使用phpoffice
• 使用composer安装office

composer require phpoffice/phpspreadsheet

• 安装成功后项目的composer.json可以看到

• 具体代码

    /**
     * 导入excel
     */
    public function importData(){
        // 接受文件上传
        $file = Request()->file('file');
        $savename = Filesystem::disk('public')->putFile('office',$file);
        // 获取文件后缀名
        $fileExtendName = substr(strrchr($savename, '.'), 1);
        // 有Xls和Xlsx格式两种
        if ($fileExtendName == 'xlsx') {
            // $objReader = IOFactory::createReader('Xlsx');
            $objReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        } else {
            // $objReader = IOFactory::createReader('Xls');
            $objReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');
        }
        $objReader->setReadDataOnly(TRUE);
        // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
        $objPHPExcel = $objReader->load(root_path() . '/public/storage/' . $savename);
        // 获取excel表中的对一张sheet
        $sheet = $objPHPExcel->getSheet(0);
        // 获取总行数
        $highestRow = $sheet->getHighestRow();
        // 获取总列数
        $highestColumn = $sheet->getHighestColumn();
        // 将列名转换成数字
        \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
        $lines = $highestRow - 1;
        if($lines <= 0){
            return Result::Error(0,'导入失败，文件内容不能为空!');
        }
        $data = array();
        for($j=2;$j<=$highestRow;$j++){
            $data[$j-2] = [
                'name'=> trim($objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue()),
                'price'=> trim($objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue()),
            ];
        }
        $result = Db::connect('mysql2')->table('goods')->insertAll($data);
        if($result){
            return Result::Success('','导入成功!');
        }else{
            return Result::Error(0,'导入失败!');
        }
    }

二、导出excel

• 引入要用到的包

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory;

• 具体代码

    /**
     * 导出excel
     */
    public function exportData(){
        // excel 模板下载
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $objSheet->setTitle('导出模板');  //设置当前sheet的标题
        // 设置宽度为true,不然太窄了
        $newExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
        $newExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
        // 设置第一栏的标题
        $objSheet->setCellValue('A1','用户id')
        ->setCellValue('B1','用户名称')
        ->setCellValue('C1','登录密码')
        ->setCellValue('D1','头像')
        ->setCellValue('E1','真实名称')
        ->setCellValue('F1','状态');
        //默认数据
        $explame_data_list = array(
            array(
                'id' => '1',
                'username' => '小明',
                'password' => '15012345678',
                'avatar' => '/upload/img/20220321/asdkjfkasdjf.png',
                'real_name' => '陈亚明',
                'status' => '0',
            ),
        );
        $explame_data_list = Db::connect('mysql2')->table('user')->select();
        $baseRow = 2; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
        foreach ($explame_data_list as $k => $val) {
            $i = $k + $baseRow;
            $objSheet->setCellValue('A' . $i, $val['id'])
                ->setCellValue('B' . $i, $val['username'])
                ->setCellValue('C' . $i, $val['password'])
                ->setCellValue('D' . $i, $val['avatar'])
                ->setCellValue('E' . $i, $val['real_name'])
                ->setCellValue('F' . $i, $val['status']);
        }
        $result = $this->downloadExcel($newExcel, '会员批量导出', 'Xls');
        return $result;
    }
    // 公共文件，用来传入xls并下载
    public function downloadExcel($newExcel, $filename, $format){
        // $format只能为 Xlsx 或 Xls
        // if ($format == 'Xlsx') {
        //     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        // } elseif ($format == 'Xls') {
        //     header('Content-Type: application/vnd.ms-excel');
        // }
        // header("Content-Disposition: attachment;filename=". $filename . date('Y-m-d') . '.' . strtolower($format));
        // header('Cache-Control: max-age=0');
        // $objWriter = IOFactory::createWriter($newExcel, $format);
        // $objWriter->save('php://output');
        // 通过php保存到本地的时候需要用到
        $filenames = root_path() . '/public/storage/office/' . $filename . date('Y-m-d') . '.' . $format;
        $writer = IOFactory::createWriter($newExcel, $format);
        $writer->save($filenames);
        $data = ['file_url' => 'office/' . $filename . date('Y-m-d') . '.' . $format];
        return Result::Success($data,'导出成功!');
        // exit;
    }

• 如果直接导出可以用 $objWriter->save(‘php://output’)，一般前后端分离就要先保存到本地，然后提供本地的导出文件链接给前端，供前端下载。

三、导出word

• 安装PhpWord扩展包

composer require phpoffice/phpword

• 用到的地方引入扩展包

use \PhpOffice\PhpWord\TemplateProcessor;

• 提前制作好 Word 模板，进行内容替换，以达到生成新Word文档的目的

• 具体代码

    /**
     * 导出word
     */
    public function exportWord(){
        $tp = new TemplateProcessor(root_path() . '/public/storage/office/word导出模板.docx');
        $tp->setValue('company_name','食安科技股份公司');
        $tp->setValue('legal_person','张三');
        $tp->setValue('appId','sdfkasdf4845485a4');
        $tp->setValue('address','深圳市龙华区观澜街道');
        $tp->setValue('code_num','121223324asdfas');
        $tp->setValue('contact_name','李四');
        $tp->setValue('contact_phone','1578178547');
        // $tp->setImageValue('license_img',root_path() . '/public/storage/office/license.png');
        $tp->setImageValue('license_img',['path'=>root_path() . '/public/storage/office/license.png','width'=>250,'height'=>200]);
        $filename = root_path() . '/public/storage/office/小程序主体信息导出.docx';
        $tp->saveAs($filename);
        // $name = pathinfo($filename,PATHINFO_FILENAME);
        // return download($filename,$name)->expire(0);
        $data = ['file_url' => 'office/小程序主体信息导出.docx'];
        return Result::Success($data,'导出成功!');
    }