[PHP知识库]Thinkphp5 导出Excel直接下载 设置各个参数属性

		//利用excel导出插件PHPExcel
        // 引入phpexcel核心类文件
        require_once ROOT_PATH . '/vendor/phpexcel/Classes/PHPExcel.php';
        // 实例化excel类
        $objPHPExcel = new \PHPExcel();
        // 操作第一个工作表
        $objPHPExcel->setActiveSheetIndex(0);
        // 设置sheet名
        $objPHPExcel->getActiveSheet()->setTitle('列表');

        // 设置表格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(25);


        // 列名表头文字加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1:Q1')->getFont()->setBold(true);
        // 列表头文字居中
        $objPHPExcel->getActiveSheet()->getStyle('A1:Q1')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // 列名赋值
        $objPHPExcel->getActiveSheet()->setCellValue('A1', 'ID');
        $objPHPExcel->getActiveSheet()->setCellValue('B1', '房屋编号');
        $objPHPExcel->getActiveSheet()->setCellValue('C1', '房屋类型');
        $objPHPExcel->getActiveSheet()->setCellValue('D1', '姓名');
        $objPHPExcel->getActiveSheet()->setCellValue('E1', '地址');
        $objPHPExcel->getActiveSheet()->setCellValue('F1', '身份证号');
        $objPHPExcel->getActiveSheet()->setCellValue('G1', '所属关系');
        $objPHPExcel->getActiveSheet()->setCellValue('H1', '常驻人口');
        $objPHPExcel->getActiveSheet()->setCellValue('I1', '层数');
        $objPHPExcel->getActiveSheet()->setCellValue('J1', '高度');
        $objPHPExcel->getActiveSheet()->setCellValue('K1', '是否安全鉴定');
        $objPHPExcel->getActiveSheet()->setCellValue('L1', '房屋材质');
        $objPHPExcel->getActiveSheet()->setCellValue('M1', '年');
        $objPHPExcel->getActiveSheet()->setCellValue('N1', '备注');
        $objPHPExcel->getActiveSheet()->setCellValue('O1', '房屋信息');
        $objPHPExcel->getActiveSheet()->setCellValue('P1', '其他材料');
        $objPHPExcel->getActiveSheet()->setCellValue('Q1', '创建时间');
        $objPHPExcel->getActiveSheet()->setCellValue('R1', '采集人员');
        $objPHPExcel->getActiveSheet()->setCellValue('S1', '是否变形裂缝');
        $objPHPExcel->getActiveSheet()->setCellValue('T1', '是否经营活动');
        $objPHPExcel->getActiveSheet()->setCellValue('U1', '是否设计专业施工');
        $objPHPExcel->getActiveSheet()->setCellValue('V1', '是否加固改扩建');

        // 数据起始行
        $row_num = 2;
        // 向每行单元格插入数据
        foreach ($res as $value) {
            // 设置所有垂直居中
            $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'Q' . $row_num)->getAlignment()
                ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            // 设置价格为数字格式
            $objPHPExcel->getActiveSheet()->getStyle('D' . $row_num)->getNumberFormat()
                ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
            // 居中
            $objPHPExcel->getActiveSheet()->getStyle('E' . $row_num . ':' . 'H' . $row_num)->getAlignment()
                ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


            // 设置单元格数值
           /* $strarr = explode(',', $value['room_info_images']);
            $room_str = '';
            foreach ($strarr as $s) {
                if ($s) {
                    $room_str .= '<a href="' . $s . '">' . $s . '</a>';
                }
            }
            $strarr2 = explode(',', $value['other_images']);
            $other_str = '';
            foreach ($strarr2 as $s) {
                if ($s) {
                    $other_str .= '<a href="' . $s . '">' . $s . '</a>';
                }
            }*/


            //Add a drawing to the worksheet 添加图片
          /*  $objDrawing = new \PHPExcel_Worksheet_Drawing();
            $objDrawing->setName('Logo');
            $objDrawing->setDescription('Logo');
            $objDrawing->setPath($value['room_info_images']);
            $objDrawing->setHeight(36);
            $objDrawing->setCoordinates('O' . $row_num);
            $objDrawing->setOffsetX(110);
            $objDrawing->setRotation(25);
            $objDrawing->getShadow()->setVisible(true);
            $objDrawing->getShadow()->setDirection(45);
            $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

            $objDrawing = new \PHPExcel_Worksheet_Drawing();
            $objDrawing->setName('Logo');
            $objDrawing->setDescription('Logo');
            $objDrawing->setPath($value['other_images']);
            $objDrawing->setHeight(36);
            $objDrawing->setCoordinates('P' . $row_num);
            $objDrawing->setOffsetX(110);
            $objDrawing->setRotation(25);
            $objDrawing->getShadow()->setVisible(true);
            $objDrawing->getShadow()->setDirection(45);
            $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());*/

            $objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $value['room_num']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['room_type_text']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $value['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['address']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['id_card'] . ' ');
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, $value['relationship_text']);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . $row_num, $value['permanent']?'使用人':'产权人');
            $objPHPExcel->getActiveSheet()->setCellValue('I' . $row_num, $value['layers']);
            $objPHPExcel->getActiveSheet()->setCellValue('J' . $row_num, $value['heigh']);
            $objPHPExcel->getActiveSheet()->setCellValue('K' . $row_num, $value['security'] ? '是' : '否');
            $objPHPExcel->getActiveSheet()->setCellValue('L' . $row_num, $value['material_text']);
            $objPHPExcel->getActiveSheet()->setCellValue('M' . $row_num, $value['yaer']);
            $objPHPExcel->getActiveSheet()->setCellValue('N' . $row_num, $value['remarks']);
            $objPHPExcel->getActiveSheet()->setCellValue('O' . $row_num, $value['room_info_images']);
            $objPHPExcel->getActiveSheet()->setCellValue('P' . $row_num, $value['other_images']);
            $objPHPExcel->getActiveSheet()->setCellValue('Q' . $row_num, date('Y-m-d h:i:s', $value['createtime']));
            $objPHPExcel->getActiveSheet()->setCellValue('R' . $row_num, $value['cname']);
            $objPHPExcel->getActiveSheet()->setCellValue('S' . $row_num, $value['is_deformation'] ? '是' : '否');
            $objPHPExcel->getActiveSheet()->setCellValue('T' . $row_num, $value['is_management'] ? '是' : '否');
            $objPHPExcel->getActiveSheet()->setCellValue('U' . $row_num, $value['is_construction'] ? '是' : '否');
            $objPHPExcel->getActiveSheet()->setCellValue('V' . $row_num, $value['is_extension'] ? '是' : '否');

            $row_num++;
        }

        $outputFileName = '采集信息_' . time() . '.xls';
        $xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);

        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outputFileName . '"');

        header("Content-Transfer-Encoding: binary");
        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: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $xlsWriter->save("php://output");
        die();