当前位置:首页 > PHP编程 > ThinkPHP > 正文内容

ThinkPHP5 PHPExcel 导入和导出

曾经4周前 (01-12)ThinkPHP45

1.必须要先下载一个插件,下载地址:https://github.com/PHPOffice/PHPExcel

    也可以直接使用composer安装安装 

composer require phpoffice/phpexcel

页面引入

use PHPExcel_IOFactory;
use PHPExcel;




2.解压后,里面有个classes文件夹,我们需要把classes文件夹名修改为为PHPExcel

3473533d2f2f461ba3cfa2235e64b994.png


3.然后把他复制到根目录中的extend目录下.

3f38d0480e6541bb812516809ba645e8.png

4.然后,在控制器中这样引入


然后,在控制器中这样引入

引入方法1

import('phpexcel.PHPExcel', EXTEND_PATH);

引入方法2

 require(Env::get('root_path') . 'extend/'."PHPExcel/PHPExcel.php");

也可以放在根目录中的/vendor/目录中
引入方法为

vendor("PHPExcel.PHPExcel");




导入

//示例
//TODO 先上传 保存文件
        /*设置上传路径*/
        $savePath = $this->up_path;
        $tmp_file = $_FILES ['file'] ['tmp_name'];
        $file_types = explode ( ".", $_FILES ['file'] ['name'] );
        $file_type = $file_types [count ( $file_types ) - 1];
        /*以时间来命名上传的文件*/
        $str = '商保商品黑名单'.$pack_id.date ( 'Ymdhis' );
        $file_name = $str . "." . $file_type;
        $file = $savePath . $file_name;
        /*是否上传成功*/
        if (!copy ($tmp_file, $file))
        {
            return ['code' => $params['error'], 'message' => '上传失败', 'data' => ''];
        }

        $extension = $file_type;
        if ($extension =='xlsx') {
            $objReader = new \PHPExcel_Reader_Excel2007();
            $objExcel = $objReader ->load($file);
        } else if ($extension =='xls') {
            $objReader = new \PHPExcel_Reader_Excel5();
            $objExcel = $objReader ->load($file);
        } else if ($extension=='csv') {
            $objReader = new \PHPExcel_Reader_CSV();
            //默认输入字符集
            $objReader->setInputEncoding('GBK');
            //默认的分隔符
            $objReader->setDelimiter(',');
            //载入文件
            $objExcel = $objReader->load($file);
        }

        $sheet = $objExcel->getSheet(); // 获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推
        $allColumn =$sheet->getHighestColumn(); // 获取总列数
        $allRow = $sheet->getHighestRow();// 获取总行数
        $ColumnNum = \PHPExcel_Cell::columnIndexFromString($allColumn);     // 列号 转 列数
        for ($rowIndex = 2; $rowIndex <= $allRow; $rowIndex++) {        //循环读取每个单元格的内容。注意行从1开始,列从A开始
            for ($colIndex = 0; $colIndex < $ColumnNum; $colIndex++) {
                $goods_info[$rowIndex - 2][] = (string)$sheet->getCellByColumnAndRow($colIndex, $rowIndex)->getValue();
            }
        }
//处理完数据删除文件
            unlink($file);

导出

//示例
 //TODO 超时设置
        ignore_user_abort(true);
        set_time_limit(0);
        ini_set("memory_limit", "-1");

        $obpe = new \PHPExcel();


        //TODO 设置外边框
        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'outline' => array(
                    'style' => 'thin', //设置border样式
                    'color' => array('rgb' => 'D9E0EC'), //设置border颜色
                ),
            ),
            'alignment' => array(
                'horizontal' => 'left',
            ),
        );

        //TODO 设置表格宽度
        $obpe->getactivesheet()->getColumnDimension('A')->setWidth(20);
        $obpe->getactivesheet()->getColumnDimension('B')->setWidth(25);
        $obpe->getactivesheet()->getColumnDimension('C')->setWidth(30);
        $obpe->getactivesheet()->getColumnDimension('D')->setWidth(10);
        $obpe->getactivesheet()->getColumnDimension('E')->setWidth(10);
        $obpe->getactivesheet()->getColumnDimension('F')->setWidth(15);
        $obpe->getactivesheet()->getColumnDimension('G')->setWidth(10);
        $obpe->getactivesheet()->getColumnDimension('H')->setWidth(20);
        $obpe->getactivesheet()->getColumnDimension('I')->setWidth(40);

        //TODO 设置表头
        $obpe->getactivesheet()->setcellvalue('A1', '支付时间')->getStyle('A1')->applyFromArray($styleThinBlackBorderOutline);;
        $obpe->getactivesheet()->setcellvalue('B1', '订单号')->getStyle('B1')->applyFromArray($styleThinBlackBorderOutline);;
        $obpe->getactivesheet()->setcellvalue('C1', '第三方单号')->getStyle('C1')->applyFromArray($styleThinBlackBorderOutline);;
        $obpe->getactivesheet()->setcellvalue('D1', '支付类型')->getStyle('D1')->applyFromArray($styleThinBlackBorderOutline);;
        $obpe->getactivesheet()->setcellvalue('E1', '支付金额')->getStyle('E1')->applyFromArray($styleThinBlackBorderOutline);;
        $obpe->getactivesheet()->setcellvalue('F1', '支付状态')->getStyle('F1')->applyFromArray($styleThinBlackBorderOutline);;
        $obpe->getactivesheet()->setcellvalue('G1', '退款金额')->getStyle('G1')->applyFromArray($styleThinBlackBorderOutline);;
        $obpe->getactivesheet()->setcellvalue('H1', '退款时间')->getStyle('H1')->applyFromArray($styleThinBlackBorderOutline);;
        $obpe->getactivesheet()->setcellvalue('I1', '退款原因')->getStyle('I1')->applyFromArray($styleThinBlackBorderOutline);;

        //TODO 设置行高

        $obpe->getActiveSheet()->getRowDimension('1')->setRowHeight(17);

        $num = 0;
        $i = 1;

        $all_totalfee = 0;
        $all_refund = 0;
        $all_count = count($list);
        $all_refund_count = 0;
//$list 需要导出的数据
        foreach ($list as $key => $val) {
            $num++;
            if ($num == 1000) {//清空内存防止溢出
                ob_flush();
                flush();
                $num = 0;
            }
            $i = $i + 1;

            $status = '';
            if ($val['status'] == 1) {
                $status = '支付成功';
            } else if ($val['status'] == 2) {
                $status = '退款中';
            } else if ($val['status'] == 3) {
                $status = '退款成功';
            }
            $obpe->getActiveSheet()->getRowDimension($i)->setRowHeight(17);
            $obpe->getactivesheet()->setcellvalue('A' . $i, '`' . $val['paytime'])->getStyle('A' . $i)->applyFromArray($styleThinBlackBorderOutline);
            $obpe->getactivesheet()->setcellvalue('B' . $i, '`' . $val['ordernum'])->getStyle('B' . $i)->applyFromArray($styleThinBlackBorderOutline);
            $obpe->getactivesheet()->setcellvalue('C' . $i, '`' . $val['transaction_id'])->getStyle('C' . $i)->applyFromArray($styleThinBlackBorderOutline);
            $obpe->getactivesheet()->setcellvalue('D' . $i, $val['channel'] == 1 ? "支付宝" : "微信")->getStyle('D' . $i)->applyFromArray($styleThinBlackBorderOutline);
            $obpe->getactivesheet()->setcellvalue('E' . $i, '`' . $val['totalfee'])->getStyle('E' . $i)->applyFromArray($styleThinBlackBorderOutline);
            $obpe->getactivesheet()->setcellvalue('F' . $i, $status)->getStyle('F' . $i)->applyFromArray($styleThinBlackBorderOutline);
            $obpe->getactivesheet()->setcellvalue('G' . $i, $val['status'] == 3 ? '`' . $val['totalfee'] : '')->getStyle('G' . $i)->applyFromArray($styleThinBlackBorderOutline);
            $obpe->getactivesheet()->setcellvalue('H' . $i, $val['refund_time'] ? '`' . date('Y-m-d H:i:s', $val['refund_time']) : '')->getStyle('H' . $i)->applyFromArray($styleThinBlackBorderOutline);
            $obpe->getactivesheet()->setcellvalue('I' . $i, $val['refund_reason'])->getStyle('I' . $i)->applyFromArray($styleThinBlackBorderOutline);
            $all_totalfee = $val['totalfee']+$all_totalfee;
            if($val['status'] == 3){
                $all_refund = $val['totalfee']+$all_refund;
                $all_refund_count = $all_refund_count + 1;
            }

        }

        $i = $i+1;
        $obpe->getactivesheet()->setcellvalue('A' . $i, '')->getStyle('A' . $i)->applyFromArray($styleThinBlackBorderOutline);


        $i = $i+1;
        $obpe->getactivesheet()->setcellvalue('D' . $i, '总计')->getStyle('A' . $i)->applyFromArray($styleThinBlackBorderOutline);
        $obpe->getactivesheet()->setcellvalue('E' . $i, (string)$all_totalfee)->getStyle('C' . $i)->applyFromArray($styleThinBlackBorderOutline);
        $obpe->getactivesheet()->setcellvalue('G' . $i, (string)$all_refund)->getStyle('E' . $i)->applyFromArray($styleThinBlackBorderOutline);



        $obpe->setactivesheetindex(0); //工作副本1
        $obpe->getActiveSheet(0)->setTitle('商保普惠明细');


        $fileName = $other['drugstoresInfo']['name'] . '商保普惠明细';
        if ($other['postdata']['start_time'] == $other['postdata']['end_time']) {
            $fileName .= "_{$other['postdata']['start_time']}.xls";
        } else {
            $fileName .= "_{$other['postdata']['start_time']}到{$other['postdata']['start_time']}.xls";
        }
        $obpe->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');
        //写入类容
        $obwrite = \PHPExcel_IOFactory::createWriter($obpe, 'Excel5');
        //保存文件
        ob_start();
        $obwrite->save('php://output'); //文件通过浏览器下载
        $xlsData = ob_get_contents();
        ob_end_clean();
        $data = [
            'file' => "data:application/vnd.ms-excel;base64," . base64_encode($xlsData),
            'file_name' => $fileName
        ];
        return ['code' => $this->successCode, 'message' => '操作成功', 'data' => $data];

            require(Env::get('root_path') . 'extend/'."PHPExcel/PHPExcel.php");



Thinkphp5使用PHPExcel包操作excel示例


扫描二维码推送至手机访问。

版权声明:本文由珍惜发布,如需转载请注明出处。

本文链接:https://www.zp68.com/article/45.html

分享给朋友:
返回列表

上一篇:ThinkPHP数据库中文问题

没有最新的文章了...

“ThinkPHP5 PHPExcel 导入和导出” 的相关文章

发表评论

访客

看不清,换一张

◎欢迎参与讨论,请在这里发表您的看法和观点。