第三方类PHPExcel导出功能的实现

最新推荐文章于 2021-03-28 11:56:49 发布

原创最新推荐文章于 2021-03-28 11:56:49 发布 · 367 阅读

0 ·

CC 4.0 BY-SA版权

其他专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何使用PHPExcel类库将特定用户的运动及饮食数据导出为Excel文件，包括数据获取、格式化和导出过程。

第一步：下载PHPExcel 类库到项目里面

第二步：

$uid = input('get.uid');
//实例化
$objPHPExcel = new \PHPExcel();

//要导出的数据
$arr =  Db::name('diet')->where('uid',$uid)->field('id,uid,day,breakfast_time,breakfast_status,breakfast1_time,breakfast1_status,lunch_time,lunch_status,lunch1_time,lunch1_status,dinner_time,dinner_status,dinner1_time,dinner1_status,additional_con,coachEvaluate,diet_notes,createtime')->select();

$array = array(
    '1' => '正常',
    '2' => '打破餐单',
    '3' => '餐包',
    '4' => '一份蛋白',
);

foreach ($arr as &$v) {
    if (!empty($v['createtime'])) {
        $v['createtime'] = date('Y-m-d',$v['createtime']);
    }
    if (!empty($v['breakfast_status'])) {
        $v['breakfast_status'] = $array[$v['breakfast_status']];
    }
    if (!empty($v['breakfast1_status'])) {
        $v['breakfast1_status'] = $array[$v['breakfast1_status']];
    }
    if (!empty($v['lunch_status'])) {
        $v['lunch_status'] = $array[$v['lunch_status']];
    }
    if (!empty($v['lunch1_status'])) {
        $v['lunch1_status'] = $array[$v['lunch1_status']];
    }
    if (!empty($v['dinner_status'])) {
        $v['dinner_status'] = $array[$v['dinner_status']];
    }
    if (!empty($v['dinner1_status'])) {
        $v['dinner1_status'] = $array[$v['dinner1_status']];
    }
}
unset($v);



/*右键属性所显示的信息*/
$objPHPExcel->getProperties()->setCreator("忆往昔")  //作者
->setLastModifiedBy("忆往昔")  //最后一次保存者
->setTitle('运动数据')  //标题
->setSubject('数据EXCEL导出') //主题
->setDescription('导出数据')  //描述
->setKeywords("excel")   //标记
->setCategory("result file");  //类别

//设置单元格宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);

//设置当前的表格
$objPHPExcel->setActiveSheetIndex(0);
// 设置表格第一行显示内容
$objPHPExcel->getActiveSheet()
    ->setCellValue('A1', '天数')
    ->setCellValue('B1', '时间')
    ->setCellValue('C1', '早餐时间')
    ->setCellValue('D1', '早餐状态')
    ->setCellValue('E1', '早餐加餐时间')
    ->setCellValue('F1', '早餐加餐状态');

$key = 1;
/*以下就是对处理Excel里的数据，横着取数据*/
foreach($arr as $v){
    //设置循环从第二行开始
    $key++;
    $objPHPExcel->getActiveSheet()
        //Excel的第A列，name是你查出数组的键值字段，下面以此类推
        ->setCellValue('A'.$key, $v['day'])
        ->setCellValue('B'.$key, $v['createtime'])
        ->setCellValue('C'.$key, $v['breakfast_time'])
        ->setCellValue('D'.$key, $v['breakfast_status'])
        ->setCellValue('E'.$key, $v['breakfast1_time'])
        ->setCellValue('F'.$key, $v['breakfast1_status']);
}

//设置当前的表格
$objPHPExcel->setActiveSheetIndex(0);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel 2003

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');


ob_end_clean();
$filename = '饮食数据.xls';
header('Pragma:public');

header('Content-Type:application/x-msexecl;name="'.$filename.'"');
header('Content-Disposition:inline;filename="'.$filename.'"');

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