xinyi/application/common/service/ServiceExcel.php

<?php
/**
 * Created by PhpStorm.
 * User: vowkin
 * Date: 2017/5/22
 * Time: 21:49
 */

namespace app\common\service;


class ServiceExcel
{
    public static function excel_exchange($list,$excel_name,$code){
        $excel=new ServiceExcel();
        $Ym=date('Y-m-d',time());
        vendor("phpExcel.PHPExcel");
        $objPHPExcel =new \PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        $obj = $objPHPExcel->getActiveSheet();
        $obj->setTitle($Ym.$excel_name);
        $AZ = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
            'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU',
        ];
        switch ($code)
        {
            case 'goods_info':
                $excel->excel_goods($obj,$list,$AZ);
                break;
            case 'good_order':
                $excel->excel_goods_order($obj,$list,$AZ);
                break;
            case 'user_update':
                $excel->excel_user_integral_detail($obj,$list,$AZ);
                break;
            case 'user_package':
                $excel->excel_user_package($obj,$list,$AZ);
                break;
            case 'user_arrange':
                $excel->excel_arrange($obj,$list,$AZ);
                break;
            case 'payCode':
                $excel->excel_PayCode($obj,$list,$AZ);
                break;
        }
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$Ym.'--'.$excel_name.'.xlsx"');
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // 在过去的日期
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // 总是修改
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        \PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007')->save('php://output');
        exit;
    }

    //导出商品信息列表
    private function excel_goods($obj,$list,$AZ){
        $i=1;
        $j=1;
        $col_index=['goods_type_name'=>1,'goods_name'=>2,'money'=>3,'points'=>4,'cost'=>5,'count'=>6,'add_ts'=>7,'status'=>8];//需要导出内容
        $titles=['序号','商品类型','商品名称','价格（RMB)','价格（卡币）','商品成本','库存','添加时间','状态'];
        foreach($titles as $index=>$v){//标题填充
            $obj->setCellValue($AZ[$index].$i, $v);
        }
        $i = 2;
        foreach($list as $k=>$info){
            foreach($info as $col=>$v){//填充内容
                $obj->setCellValue($AZ[$col_index[$col]].$i, $v);
                $obj->setCellValue('A'.$i, $j);
            }
            $i++;$j++;
        }
    }

    //excel表格导入信息
    public static function excel_import($file_name){
        $file = request()->file('excel');
        // 移动到框架应用根目录/public/uploads/ 目录下
        $info = $file->validate(['size'=>3145728,'ext'=>'xls,csv,xlsx'])->move(ROOT_PATH . 'public' . DS . 'uploads/'.$file_name);
        if($info) {
            $filename = $info->getSaveName();
            $filenames = ROOT_PATH . 'public' . DS . 'uploads/'.$file_name.'/' . $filename;
            vendor("phpExcel.PHPExcel");
            $filetype = \PHPExcel_IOFactory::identify($filenames);//自动获取文件类型
            $objReader = \PHPExcel_IOFactory::createReader($filetype);//获取文件读取对象
            $objPHPExcel = $objReader->load($filenames);//加载excel文件
            $name = $objPHPExcel->getsheetNames();
            $sheetn = end($name);
            $objReader->setLoadSheetsOnly($sheetn);
            $objPHPExcel = $objReader->load($filenames);
            $sheetname = $objPHPExcel->getSheet();//获取当前sheet
            $data = $sheetname->toArray();
            if(!empty($data)){
                unset($data[0]);
                return $data;
            }else{
                return false;
            }
        }else{
            return false;
        }
    }
}