PHP导出Excel表格

一、

1.引入PHPExcel.php类

2.下面的代码可以直接拿来用，此例中$list为前端传过来的json数据

public function excel()
    {
        $pre_list = input('list');
        $list = json_decode($pre_list);     

        //创建对象
        $excel = new \PHPExcel();
        $excel->getActiveSheet()->getDefaultColumnDimension()->setAutoSize(true);//设置单元格宽度

        //设置表格的宽度  手动
        $excel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $excel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $excel->getActiveSheet()->getColumnDimension('C')->setWidth(45);
        $excel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $excel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
        $excel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $excel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
        $excel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
        $excel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
        $excel->getActiveSheet()->getColumnDimension('J')->setWidth(70);
        $excel->getActiveSheet()->getColumnDimension('K')->setWidth(15);

        //表头数组
        $rowVal = array(0=>'订单编号', 1=>'店铺名称',2=>'商品名称', 3=>'买家', 4=>'下单时间', 5=>'订单总额',6=>'订单状态',7=>'收货人',8=>'收货人电话',9=>'收货人地址',10=>'支付方式');

        foreach ($rowVal as $k=>$r){
            $excel->getActiveSheet()->getStyleByColumnAndRow($k,1)->getFont()->setBold(true);//字体加粗
            $excel->getActiveSheet()->getStyleByColumnAndRow($k,1)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//文字居中
            $excel->getActiveSheet()->setCellValueByColumnAndRow($k,1,$r);
        }

        //表格数组
        foreach($list as $k => $v)
        {
            $num=$k+2;
            $goods_name = '';
            foreach($v->order_item_list as $key => $val){
                $goods_name = $goods_name.' '.$val->goods_name;
            }
            $excel->setActiveSheetIndex(0)
                ->setCellValue('A'.$num, $v->order_no)
                ->setCellValue('B'.$num, $v->shop_name)
                ->setCellValue('c'.$num, $goods_name)
                ->setCellValue('D'.$num, $v->user_name)
                ->setCellValue('E'.$num, $v->shipping_time)
                ->setCellValue('F'.$num, $v->pay_money)
                ->setCellValue('G'.$num, $v->status_name)
                ->setCellValue('H'.$num, $v->receiver_name)
                ->setCellValue('I'.$num, $v->receiver_mobile)
                ->setCellValue('J'.$num, $v->receiver_province_name.' '.$v->receiver_city_name.' '.$v->receiver_district_name.' '.$v->receiver_address)
                ->setCellValue('K'.$num, $v->pay_type_name);
            $excel->getActiveSheet()->getStyleByColumnAndRow($k,1)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//文字居中
        }

        $write = new \PHPExcel_Writer_Excel5($excel);

        //创建Excel输入对象
        ob_clean();      //清除缓冲区
        Header('content-Type:application/vnd.ms-excel;charset=utf-8');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="订单汇总表(' . date('Ymd-His') . ').xls"');
        header('Cache-Control: max-age=0');

        $write->save('php://output');
    }

 二、通用导出模板

1.
/*
  * @name 通用导出
  * @params title 表格名称
  * @param tit 标题数组
  * @param con 数据
  * @param key 数据 对应标题的键值数组
  * @param sizes 需要加宽的列
  * */
    function excel($title='', $tit=[], $con=[], $key=[], $sizes=[])
    {
        include_once(Env::get('root_path').'vendor/PHPExcel/PHPExcel.php');
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties()->setCreator("ctos")
            ->setLastModifiedBy("ctos")
            ->setTitle($title)
            ->setSubject($title)
            ->setDescription($title)
            ->setKeywords($title)
            ->setCategory($title);

        $letter = array(
            'A',
            'B',
            'C',
            'D',
            'E',
            'F',
            'G',
            'H',
            'I',
            'J',
            'K',
            'L',
            'M',
            'N',
            'O',
            'P',
            'Q',
            'R',
            'S',
            'T'
        );
        foreach ($sizes as $size){
            $objPHPExcel->getActiveSheet(0)->getColumnDimension($size)->setAutoSize(true);
        }

        foreach ($tit as $k => $v)
        {

            $objPHPExcel->getActiveSheet()->setCellValue($letter[$k] . "1", $v);
        }
        foreach ($con as $k => $v)
        {
            foreach ($key as $k2 => $v2)
            {

                $objPHPExcel->getActiveSheet()->setCellValue($letter[$k2] . ($k + 2), $v[$v2]);
            }
        }

        // excel头参数
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment; filename=\"$title.xls\"");
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel5为xls格式，excel2007为xlsx格式

        $objWriter->save('php://output');

        die();
   }
2.调用($list为导出的列表)

public function export($list){
    $table_name = '投诉信息表('.date('Ymd-His').')';
    $tit = array(
        "编号ID",
        "用户",
　　　　　"性别"
    );

    $key = array(
        "id",
        "name",
　　　　 "sex"
    );

    $size = ['C'];
    $this->excel($table_name, $tit, $lists, $key, $size);
}