office文件的导出导入

编辑: admin 分类: 电脑知识 发布时间: 2023-06-14 来源:互联网
一、导入excel
  • tp6导入excel文件使用phpoffice
  • 使用composer安装office
  1. composer require phpoffice/phpspreadsheet
  • 安装成功后项目的composer.json可以看到

  • 具体代码
  1.     /**
  2.      * 导入excel
  3.      */
  4.     public function importData(){
  5.         // 接受文件上传
  6.         $file = Request()->file('file');
  7.         $savename = Filesystem::disk('public')->putFile('office',$file);
  8.         // 获取文件后缀名
  9.         $fileExtendName = substr(strrchr($savename, '.'), 1);
  10.         // 有Xls和Xlsx格式两种
  11.         if ($fileExtendName == 'xlsx') {
  12.             // $objReader = IOFactory::createReader('Xlsx');
  13.             $objReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
  14.         } else {
  15.             // $objReader = IOFactory::createReader('Xls');
  16.             $objReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');
  17.         }
  18.         $objReader->setReadDataOnly(TRUE);
  19.         // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
  20.         $objPHPExcel = $objReader->load(root_path() . '/public/storage/' . $savename);
  21.         // 获取excel表中的对一张sheet
  22.         $sheet = $objPHPExcel->getSheet(0);
  23.         // 获取总行数
  24.         $highestRow = $sheet->getHighestRow();
  25.         // 获取总列数
  26.         $highestColumn = $sheet->getHighestColumn();
  27.         // 将列名转换成数字
  28.         \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
  29.         $lines = $highestRow - 1;
  30.         if($lines <= 0){
  31.             return Result::Error(0,'导入失败，文件内容不能为空!');
  32.         }
  33.         $data = array();
  34.         for($j=2;$j<=$highestRow;$j++){
  35.             $data[$j-2] = [
  36.                 'name'=> trim($objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue()),
  37.                 'price'=> trim($objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue()),
  38.             ];
  39.         }
  40.         $result = Db::connect('mysql2')->table('goods')->insertAll($data);
  41.         if($result){
  42.             return Result::Success('','导入成功!');
  43.         }else{
  44.             return Result::Error(0,'导入失败!');
  45.         }
  46.     }
二、导出excel
  • 引入要用到的包
  1. use \PhpOffice\PhpSpreadsheet\Spreadsheet;
  2. use \PhpOffice\PhpSpreadsheet\IOFactory;
  • 具体代码
  1.     /**
  2.      * 导出excel
  3.      */
  4.     public function exportData(){
  5.         // excel 模板下载
  6.         $newExcel = new Spreadsheet();  //创建一个新的excel文档
  7.         $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
  8.         $objSheet->setTitle('导出模板');  //设置当前sheet的标题
  10.         // 设置宽度为true,不然太窄了
  11.         $newExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
  12.         $newExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
  13.         $newExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
  14.         $newExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
  15.         $newExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
  16.         $newExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
  18.         // 设置第一栏的标题
  19.         $objSheet->setCellValue('A1','用户id')
  20.         ->setCellValue('B1','用户名称')
  21.         ->setCellValue('C1','登录密码')
  22.         ->setCellValue('D1','头像')
  23.         ->setCellValue('E1','真实名称')
  24.         ->setCellValue('F1','状态');
  26.         //默认数据
  27.         $explame_data_list = array(
  28.             array(
  29.                 'id' => '1',
  30.                 'username' => '小明',
  31.                 'password' => '15012345678',
  32.                 'avatar' => '/upload/img/20220321/asdkjfkasdjf.png',
  33.                 'real_name' => '陈亚明',
  34.                 'status' => '0',
  35.             ),
  36.         );
  38.         $explame_data_list = Db::connect('mysql2')->table('user')->select();
  40.         $baseRow = 2; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
  42.         foreach ($explame_data_list as $k => $val) {
  43.             $i = $k + $baseRow;
  44.             $objSheet->setCellValue('A' . $i, $val['id'])
  45.                 ->setCellValue('B' . $i, $val['username'])
  46.                 ->setCellValue('C' . $i, $val['password'])
  47.                 ->setCellValue('D' . $i, $val['avatar'])
  48.                 ->setCellValue('E' . $i, $val['real_name'])
  49.                 ->setCellValue('F' . $i, $val['status']);
  50.         }
  52.         $result = $this->downloadExcel($newExcel, '会员批量导出', 'Xls');
  53.         return $result;
  54.     }
  56.     // 公共文件，用来传入xls并下载
  57.     public function downloadExcel($newExcel, $filename, $format){
  58.         // $format只能为 Xlsx 或 Xls
  59.         // if ($format == 'Xlsx') {
  60.         //     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  61.         // } elseif ($format == 'Xls') {
  62.         //     header('Content-Type: application/vnd.ms-excel');
  63.         // }
  64.         // header("Content-Disposition: attachment;filename=". $filename . date('Y-m-d') . '.' . strtolower($format));
  65.         // header('Cache-Control: max-age=0');
  66.         // $objWriter = IOFactory::createWriter($newExcel, $format);
  67.         // $objWriter->save('php://output');
  69.         // 通过php保存到本地的时候需要用到
  70.         $filenames = root_path() . '/public/storage/office/' . $filename . date('Y-m-d') . '.' . $format;
  71.         $writer = IOFactory::createWriter($newExcel, $format);
  72.         $writer->save($filenames);
  73.         $data = ['file_url' => 'office/' . $filename . date('Y-m-d') . '.' . $format];
  74.         return Result::Success($data,'导出成功!');
  76.         // exit;
  77.     }
  • 如果直接导出可以用 $objWriter->save(‘php://output’)，一般前后端分离就要先保存到本地，然后提供本地的导出文件链接给前端，供前端下载。
三、导出word
  • 安装PhpWord扩展包
  1. composer require phpoffice/phpword
  • 用到的地方引入扩展包
  1. use \PhpOffice\PhpWord\TemplateProcessor;

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

  • 具体代码

  1.     /**
  2.      * 导出word
  3.      */
  4.     public function exportWord(){
  5.         $tp = new TemplateProcessor(root_path() . '/public/storage/office/word导出模板.docx');
  6.         $tp->setValue('company_name','食安科技股份公司');
  7.         $tp->setValue('legal_person','张三');
  8.         $tp->setValue('appId','sdfkasdf4845485a4');
  9.         $tp->setValue('address','深圳市龙华区观澜街道');
  10.         $tp->setValue('code_num','121223324asdfas');
  11.         $tp->setValue('contact_name','李四');
  12.         $tp->setValue('contact_phone','1578178547');
  13.         // $tp->setImageValue('license_img',root_path() . '/public/storage/office/license.png');
  14.         $tp->setImageValue('license_img',['path'=>root_path() . '/public/storage/office/license.png','width'=>250,'height'=>200]);
  15.         $filename = root_path() . '/public/storage/office/小程序主体信息导出.docx';
  16.         $tp->saveAs($filename);
  17.         // $name = pathinfo($filename,PATHINFO_FILENAME);
  18.         // return download($filename,$name)->expire(0);
  19.         $data = ['file_url' => 'office/小程序主体信息导出.docx'];
  20.         return Result::Success($data,'导出成功!');
  21.     }
【文章原创作者：美国服务器 https://www.68idc.cn 欢迎留下您的宝贵建议】
更多阅读