PHPExcel导出(可以根据表字段只导出自己想导出的横列纵列)

首先接值，取数据加处理

public function order_export()
    {
        $post_data = $this->request->get();
        if (!isset($post_data['id']) || '' == $post_data['id']) return json(['code' => 10001, 'msg' => '参数有误']);
        if (!isset($post_data['checkval']) || '' == $post_data['checkval']) return json(['code' => 10001, 'msg' => '参数有误']);
        $order_data = model('***')
            ->alias('a')
            ->field($post_data['checkval'])
            ->join('order_detail b', 'a.order_no=b.order_no', 'RIGHT')
            ->where(['a.id' => explode(',', $post_data['id'])])
            ->select()
            ->toArray();
        $excel_arr = Config::get('orderexcel.excel');
        $xlsCell = [];
        $key_val = [];
        $xlsName = '*****';
        $expTableData = [];
        $expTable = [];
        foreach ($order_data as $k => $v) {
            $expTable[$k] = array_values($v);
            foreach (explode(',', $post_data['checkval']) as $k => $v) {
                $xlsCell[$k][] = $excel_arr[$v];
                $key_val[] = $excel_arr[$v];
            }
        }
        foreach ($expTable as $k => $v) {
            foreach ($v as $kk => $vv) {
                $expTableData[$k][$key_val[$kk]] = $vv;
            }
        }
        //PHP导出Excel
        $this->exportExcel($xlsName, $xlsCell, $expTableData);
    }

然后调取我们的PHPExcel类去做导出操作

public function exportExcel($expTitle, $expCellName, $expTableData)
    {
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $xlsTitle . date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        $objPHPExcel = new PHPExcel();
        $cellName = array('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', 'AV', 'AW', 'AX', 'AY', 'AZ');

        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . '已完成订单导出于:' . date('Y-m-d H:i:s'));
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        //按照指定格式生成Excel文件，‘Excel2007’表示生成2007版本的xlsx，‘Excel5’表示生成2003版本Excel文件
        $PHPWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //告诉浏览器输出07Excel文件
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        //告诉浏览器将要输出Excel03版本文件
        //header('Content-Type:application/vnd.ms-excel');
        //导出的文件名
        header('Content-Disposition:inline;filename="' . $fileName . '"');
        //告诉浏览器输出浏览器名称
        header('Content-Disposition: attachment;filename="01simple.xlsx"');
        //禁止缓存
        header('Cache-Control: max-age=0');
        $PHPWriter->save("php://output");
    }