php 导出excel

最新推荐文章于 2022-04-18 14:23:46 发布

每天都进步一点点

最新推荐文章于 2022-04-18 14:23:46 发布

阅读量227

点赞数

分类专栏： php

本文链接：https://blog.csdn.net/gongqinglin/article/details/83210931

版权

php 专栏收录该内容

98 篇文章 3 订阅

订阅专栏

以下代码只是为了以后方便我自己复制使用，哈哈

/*------------------------------------------------------ */
//-- 导出分类
/*------------------------------------------------------ */
if ($_REQUEST['act'] == 'export')
{
    ini_set('memory_limit','500M');
    set_time_limit(0);
    $cat_name = '分类表';
    //PHPExcel类
    require_once ROOT_PATH . 'includes/modules/PHPExcel/Classes/PHPExcel.php';
    require_once ROOT_PATH . 'includes/modules/PHPExcel/Classes/PHPExcel/IOFactory.php';
    $PHPExcel = new PHPExcel();

    //设置excel属性基本信息
    $PHPExcel->getProperties()->setCreator("Neo")
    ->setLastModifiedBy("Neo")
    ->setTitle($cat_name)
    ->setSubject("分类表")
    ->setDescription("")
    ->setKeywords("分类表")
    ->setCategory("");

    $PHPExcel->setActiveSheetIndex(0);
    $PHPExcel->getActiveSheet()->setTitle("分类表");
    //填入表头主标题
    $PHPExcel->getActiveSheet()->setCellValue('A1', $cat_name);
    //填入表头副标题
    $PHPExcel->getActiveSheet()->setCellValue('A2', '操作者：'.$_SESSION['admin_name'].' 导出日期：'.local_date('Y-m-d',time()));
    //合并表头单元格
    $PHPExcel->getActiveSheet()->mergeCells('A1:Q1');
    $PHPExcel->getActiveSheet()->mergeCells('A2:Q2');
    
    //设置表头行高
    $PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(40);
    $PHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(20);
    $PHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(30);
    
    //设置表头字体
    $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('黑体');
    $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
    $PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
    $PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体');
    $PHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
    $PHPExcel->getActiveSheet()->getStyle('A3:Q3')->getFont()->setBold(true);
         
    // 表格宽度
    $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);//分类ID
    $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);//分类名称
    $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);//上级分类ID
    $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);//排序
    $PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);//是否显示

    // $objActSheet->getColumnDimension('B')->setAutoSize(true);   //内容自适应

    //表格标题
    $PHPExcel->getActiveSheet()->setCellValue('A3', '分类ID');
    $PHPExcel->getActiveSheet()->setCellValue('B3', '分类名称');
    $PHPExcel->getActiveSheet()->setCellValue('C3', '上级分类ID');
    $PHPExcel->getActiveSheet()->setCellValue('D3', '排序');
    $PHPExcel->getActiveSheet()->setCellValue('E3', '是否显示');

    

    /* 获取分类数据 */
    $sql = "SELECT * FROM " .$GLOBALS['ecs']->table('category') ."  ORDER BY cat_id asc";

    $hang = 3;
    $max_row=0;
    $res=$db->query($sql);

    while($row=$db->fetchRow($res)){  
        $hang++;
        $PHPExcel->getActiveSheet()->setCellValue('A' . ($hang), $row['cat_id']);//分类ID
        $PHPExcel->getActiveSheet()->setCellValue('B' . ($hang), $row['cat_name']);//分类名称
        $PHPExcel->getActiveSheet()->setCellValue('C' . ($hang), $row['parent_id']);//上级分类ID
        $PHPExcel->getActiveSheet()->setCellValue('D' . ($hang), $row['sort_order']);//排序
        $PHPExcel->getActiveSheet()->setCellValue('E' . ($hang), $row['is_show']);//是否显示
    
    }      

    ob_end_clean();//清除缓冲区,避免乱码
    header('Content-Type: application/vnd.ms-excel'); 
    header('Content-Disposition: attachment;filename=" '.$cat_name.'.xls"'); //日期为文件名后缀 
    header('Cache-Control: max-age=0'); 
    $objWriter = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5'); //excel5为xls格式，excel2007为xlsx格式 
    $objWriter->save('php://output');
    exit;
}