TP5+PHPExcel数据导出

ゝ一纸荒年。 2023-06-28 04:57 242阅读 0赞

一、Excel的数据导出主要分为三步操作：

1.  页面的请求，触发导出方法；
2.  TP5成功引入扩展PHPExcel;
3.  程序制作Excel表格样式，写入数据导出；

二、请求触发

触发条件为a标签的链接跳转直接访问TP5中的导出方法，同时在导出数据时很可能要传递参数作为数据库的查询条件来获取要导出的数据集。

<a href="{:url('Index/exportExcel')}" >Excel导出</a>  //不需要参数
    <a href="{:url('Index/exportExcel',['id'=>1])}" >Excel导出</a> //传参数，多个参数时在数组中继续添加即可

//js访问也是可以的
    window.location.href="{:url('Index/exportExcel',['id'=>1])}";

三、TP5引入扩展

扩展文件放在vendor文件加下，目录结构如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BhbmdqdW53ZWk_size_16_color_FFFFFF_t_70][]

引入扩展的代码如下：

//引入扩展
    import('PHPExcel.Classes.PHPExcel','vendor');
    $objPHPExcel = new \PHPExcel();

四、制作表格，写入数据

下面附上扩展下载的资源链接和PHPExcel在中文操作手册，通过手册可以很好的操作制作表格，最后附上一次导出的实例代码方便理解。

//引入扩展
            import('PHPExcel.Classes.PHPExcel','vendor');
            $objPHPExcel = new \PHPExcel();
            $objPHPExcel->getProperties()->setCreator("ctos")
                ->setLastModifiedBy("wjx")
                ->setTitle("Office 2007 XLSX Test Document")
                ->setSubject("Office 2007 XLSX Test Document")
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("Test result file");
        
            //设置各列宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(16);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(19);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(55);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(43);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(22);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(18);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(25);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(55);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(18);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(18);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(21);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(11);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(11);
        
            $objPHPExcel->getActiveSheet()->getStyle('A:P')->getFont()->setName('宋体');
            $objPHPExcel->getActiveSheet()->getStyle('A:P')->getFont()->setSize(11);
            $objPHPExcel->getActiveSheet()->getStyle('A1:P1')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('A1:P1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A:P')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A1:P1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle('A1:P1')->getFill()->getStartColor()->setARGB("00F79646");
            
            //设置列名
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '订单编号')
                ->setCellValue('B1', '收件人')
                ->setCellValue('C1', '固话')
                ->setCellValue('D1', '手机')
                ->setCellValue('E1', '地址')
                ->setCellValue('F1', '发货信息')
                ->setCellValue('G1', '宝贝数量')
                ->setCellValue('H1', '总重量')
                ->setCellValue('I1', '发件人')
                ->setCellValue('J1', '发件人电话')
                ->setCellValue('K1', '发件人地址')
                ->setCellValue('L1', '备注')
                ->setCellValue('M1', '代收金额')
                ->setCellValue('N1', '报价金额')
                ->setCellValue('O1', '业务类型')
                ->setCellValue('P1', '实付金额');
            
            //给数据
            foreach ($lists as $k => $v) {
                $num = $k + 2;
                $objPHPExcel->setActiveSheetIndex(0)//Excel的第A列，uid是你查出数组的键值，下面以此类推
                ->setCellValue('A' . $num, $v['tid'])
                    ->setCellValue('B' . $num, $v['arrive_name'])
                    ->setCellValueExplicit('D' . $num, $v['arrive_phone'],\PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValue('E' . $num, $v['arrive_address'])
                    ->setCellValue('F' . $num, $v['title_cotent'])
                    ->setCellValue('G' . $num, $v['num'])
                    ->setCellValue('I' . $num, $this->adminInfo['send_name'])
                    ->setCellValueExplicit('J' . $num, $this->adminInfo['send_phone'],\PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValue('K' . $num, $this->adminInfo['send_address']);
            }
            
            
            //导出文件名
            $filename = date('Y-m-d',time()).'发货订单数据.xls';
            header('Content-Type: applicationnd.ms-excel');
            header('Content-Disposition: attachment;filename='.$filename);
            header('Cache-Control: max-age=0');
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel5为xls格式，excel2007为xlsx格式
            $objWriter->save('php://output');

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BhbmdqdW53ZWk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200107163640378.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BhbmdqdW53ZWk=,size_16,color_FFFFFF,t_70