模板导出Xls

<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use think\Loader;

Loader::import('phpexcel.Classes.PHPExcel.IOFactory',EXTEND_PATH,'.php');
/**
 * 
 *
 * @icon fa fa-circle-o
 */
class Order extends Backend
{
    
    /**
     * Order模型对象
     * @var \app\admin\model\Order
     */
    protected $model = null;
    protected $dataLimit = 'auth';
    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\Order;

    }
    
    
    function excel_handle($ids){

        $this->success('处理成功，正在生成报表！',null,$ids);
    }
    function excout($ids=null){
        $info = db('order')->find($ids);
        if(empty($info)){
            $this->error('订单不存在');
        }

        $list =  db('order_details')
            ->alias('details')
            ->join('goods','goods.id = details.goods_id')
            ->join('goods_sku','goods_sku.id = details.sku_id')
            ->field('details.*,goods.name as goods_name,goods_sku.name as suk_name')
            ->where('order_id',$info['id'])
            ->select();

        //订单信息

        error_reporting(E_ALL);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);
        ini_set("error_reporting","E_ALL & ~E_NOTICE");

        define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

        date_default_timezone_set('Europe/London');

        include 'extend/phpexcel/Classes/PHPExcel/IOFactory.php';
        //设置模板文件
        $objPHPExcel = \PHPExcel_IOFactory::load("PI-larkkey.xls");

        //将json字符串转换为数组
        //数据
        //$billData = json_decode($_REQUEST["billData"],true);


        //月份表
        $months = array(
            1 => 'Jan',
            2 => 'Feb',
            3 => 'Mar',
            4 => 'Apr',
            5 => 'May',
            6 => 'Jun',
            7 => 'Jul',
            8 => 'Aug',
            9 => 'Sep',
            10 => 'Oct',
            11 => 'Nov',
            12 => 'Dec'
        );


        //基础信息
        $basic_info = array(
            'consignee_company_name' => $info['contactname'],
            'consignee_address' => $info['address'],
            'consignee_tel' => $info['tel'],
            'loading_port' => $info['loading'],
            'destination_port' => $info['destination'],
            'shippment_way' => $info['shippment'],
            'invoice_number' => 'INVOICE NO:'.$info['invoiceno']
        );

        //基础信息  日期
        $basic_info['date'] = $months[(int)Date('m')]." ".Date('D').",".Date("Y");

        //基础信息填充单元格
        $objPHPExcel->getActiveSheet()->setCellValue('B7', $basic_info['consignee_company_name'])
            ->setCellValue('B8', $basic_info['consignee_address'])
            ->setCellValue('B9', $basic_info['consignee_tel'])
            ->setCellValue('B10', 'Ningbo')
            ->setCellValue('B11', $basic_info['destination_port'])
            ->setCellValue('B12', 'by express')
            ->setCellValue('G7', $basic_info['invoice_number'])
            ->setCellValue('G8', $basic_info['date']);


        //设置B10
        $objValidation_ = $objPHPExcel->getActiveSheet()->getCell("B10")->getDataValidation();

        $objValidation_ -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('输入的值有误')
            -> setError('您输入的值不在下拉框列表内.')
            -> setPromptTitle('设备类型')
            -> setFormula1('"Ningbo,Shengzhen,guangzhou"');

        //设置B12
        $objValidation_ = $objPHPExcel->getActiveSheet()->getCell("B12")->getDataValidation();

        $objValidation_ -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('输入的值有误')
            -> setError('您输入的值不在下拉框列表内.')
            -> setPromptTitle('设备类型')
            -> setFormula1('"by express,by air,by express"');


        //列表信息
//        $list_data = array(
//            '0' => array(
//                'pic' => '',//图片
//                'item_no' => '001',//货号
//                'description' => 'wife',//描述
//                'protocol' => 'sex',//属性
//                'color' => 'green',//颜色
//                'qty_pcs' => '100', //数量/个数  -装箱量
//                'usd_pcs' => '50',//美金/个数
//                'amount' => '100' //总计价格
//            )
//        );
        $list_data = $list;

//        $PHPSheet->getColumnDimension('C')->setWidth(70);
//        $PHPSheet->getRowDimension(21)->setRowHeight(75);//高度

        //循环填充相同标题单元格
        $row=14;
        foreach($list_data as $r => $dataRow) {
            $this->excelPic($dataRow['image'],'A'.$row,$objPHPExcel->getActiveSheet());
            $objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(100);
            $objPHPExcel->getActiveSheet()
//                ->setCellValue('A'.$row, $dataRow['image'])
                ->setCellValue('B'.$row, $dataRow['model_no'])
                ->setCellValue('c'.$row, $dataRow['goods_name'])
                ->setCellValue('D'.$row, $dataRow['suk_name'])
                ->setCellValue('E'.$row, $dataRow['mode']==1?'Read To Sheep':'Customization')
                ->setCellValue('F'.$row, $dataRow['num'])
                ->setCellValue('G'.$row, $dataRow['price'])
                ->setCellValue('H'.$row, $dataRow['num']*$dataRow['price']);
            $row++;
        }

        //显示总价
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, 'Total：'."")
            ->setCellValue('H'.$row, $info['amount'])
            ->setCellValue('A'.($row+2), "Note:") //下面是固定文本信息
            ->setCellValue('A'.($row+3), "1,Price Term:Fob Ningbo")
            ->setCellValue('A'.($row+4), "2,Price Valid:30 days")
            ->setCellValue('A'.($row+5), "3,Payment Term:30% T/T,70% balance before shippment   or L/C  or Paypal")
            ->setCellValue('A'.($row+6), "4,Packing:white netural packing or our logo packing,")
            ->setCellValue('A'.($row+7), "         If need customized packing,need MOQ.And packing cost need to be adjusted.")
            ->setCellValue('A'.($row+10), "Bank Detail:");


        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        ob_end_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=larkkey_'.time().'.xlsx');
        header('Cache-Control: max-age=0');

        $objWriter->save("php://output"); exit;
        //让访问浏览器直接下载文件流
//        Header("Location: /uploads/larkkey_".time().".xls");
    }

    function excelPic($url,$seat,$obj){
        if(empty($url)){
            return $obj;
        }
        $temp_pic = $this->download($url,'../public/uploads/temp/');
        $temp[] =$local_pic_path = '../public/uploads/temp/'.$temp_pic;
        if (file_exists($local_pic_path)) {
            $img = new \PHPExcel_Worksheet_Drawing();
            $img->setPath($local_pic_path);//写入图片路径
            // $img->setHeight(100);//写入图片高度
            $img->setWidth(100);//写入图片宽度
//        $img->setOffsetX(105);//写入图片在指定格中的X坐标值
//        $img->setOffsetY(5);//写入图片在指定格中的Y坐标值
//        $img->setRotation(1);//设置旋转角度
//        $img->getShadow()->setVisible(true);
//        $img->getShadow()->setDirection(50);
            $img->setCoordinates($seat);//设置图片所在表格位置
            $img->setWorksheet($obj);//把图片写到当前的表格中
        }
        return $obj;
    }
    function download($url, $path = 'images/')
    {

        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); // 信任任何证书
        $file = curl_exec($ch);
        curl_close($ch);

        $filename = pathinfo($url, PATHINFO_BASENAME);
        $resource = fopen($path . $filename, 'a');
        fwrite($resource, $file);
        fclose($resource);

        return $filename;
    }
}

文件

php介绍

PHP即“超文本预处理器”，是一种通用开源脚本语言。PHP是在服务器端执行的脚本语言，与C语言类似，是常用的网站编程语言。PHP独特的语法混合了C、Java、Perl以及 PHP 自创的语法。利于学习，使用广泛，主要适用于Web开发领域。

更多关于php的阅读

Tags 标签

扩展阅读

加个好友，技术交流