php数据导出excel

最新推荐文章于 2021-03-11 08:57:59 发布

原创最新推荐文章于 2021-03-11 08:57:59 发布 · 308 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#php #phpexcel

工作笔记专栏收录该内容

2 篇文章

订阅专栏

博客介绍了PHP数据导出到Excel表格的两种方式。一是导出csv格式，简单便捷，但不能设计样式、无法导出图片；二是使用phpexcel导出。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

PHP数据导出到Eexel表格可以有多种方式，这里简单介绍两种。

导出csv格式，简单便捷，缺点是不能设计表格样式，无法导出图片等。

        $str = "手机号,状态,报名时间,消费时间\n";
        $str = iconv('utf-8', 'gb2312', $str);
        $phone = iconv('utf-8', 'gb2312', '13333333333');

        $status = iconv('utf-8', 'gb2312', '已消费');

        $createtime = date('Y-m-d H:i:s','1560758450');
            
        $createtime = date('Y-m-d H:i:s','1560758450');
            
        $str .= $phone.",".$status.",".$createtime.",".$usertime. "\n";
        $filename = 'demo.csv';
        
        ob_end_clean();//清空输出缓存区
        header("Content-type:text/csv");
        header("Content-Disposition:attachment;filename=" . $filename);
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Expires:0');
        header('Pragma:public');
        echo $str;

使用phpexcel导出

    ini_set('display_errors','On');
    error_reporting(E_ALL);

    date_default_timezone_set('Asia/ShangHai');
    require_once DISCUZ_ROOT.'./source/include/PHPExcel/Classes/PHPExcel.php';

    //初始化PHPExcel()
    $objPHPExcel = new PHPExcel();

    //水平居中
    $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    //垂直居中
    $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

    $objPHPExcel->getActiveSheet()
        ->setCellValue("A1", '会员ID')
        ->setCellValue("B1", '人民币(元)')
        ->setCellValue("C1", '状态')
        ->setCellValue("D1", '收款码');

    //单元格宽度
    $objPHPExcel->getActiveSheet()->getColumnDimension("B")->setWidth(11);
    $objPHPExcel->getActiveSheet()->getColumnDimension("C")->setWidth(25);
    $objPHPExcel->getActiveSheet()->getColumnDimension("D")->setWidth(25);

    $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
    $filename = 'demo.xlsx';


    //接下来就是写数据到表格里面去
    $objActSheet = $objPHPExcel->getActiveSheet();
    $startRow = 2;
    //这里是设置单元格的内容
    $objActSheet->setCellValue('A'.$startRow, '张三');
    $objActSheet->setCellValue('B'.$startRow, 10000);
    $objActSheet->setCellValue('C'.$startRow, '等待打款');

    // 图片生成
    $objDrawing[$startRow] = new \PHPExcel_Worksheet_Drawing();
    $objDrawing[$startRow]->setPath('./Uploads/201906/17/demo.jpg');//图片地址
    // 设置宽度高度
    $objDrawing[$startRow]->setHeight(150);//照片高度
    $objDrawing[$startRow]->setWidth(150); //照片宽度
    /*设置图片要插入的单元格*/
    $objDrawing[$startRow]->setCoordinates('D'.$startRow);

    $objDrawing[$startRow]->setWorksheet($objPHPExcel->getActiveSheet());
    // 表格高度
    $objActSheet->getRowDimension($startRow)->setRowHeight(100);
    ob_end_clean();//清空输出缓存区
    // 下载这个表格，在浏览器输出
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");;
    header('Content-Disposition:attachment;filename='.$filename.'');
    header("Content-Transfer-Encoding:binary");
    $objWriter->save('php://output');