Yii2框架控制器中利用Phpexcel将数据导出excel文档
安装：
composer require "phpoffice/phpexcel": "*"使用：
在控制器中use PHPExcel;
控制器导出方法:
public function actionExport()
{
//要使用的数据
$g_model = new Gifts;
$gifts = $g_model::find()->asArray()->all();
//实例化
$objPHPExcel = new \PHPExcel();
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
//phpexcel在使用时必须先设置值再进行其他设置
//设置标题
$objPHPExcel->getActiveSheet()->setCellValue('A1', '2017年学术礼品会议情况');
//设置居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
//设置宽度
//$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
//也可以设置为固定
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
//设置表头的值还有颜色
$objPHPExcel->getActiveSheet()->setCellValue('A2', '序号');//这里是设置A2单元格的内容
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '名称');
$objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
$objPHPExcel->getActiveSheet()->setCellValue('C2', '单价（元）');
$objPHPExcel->getActiveSheet()->getStyle('C2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
$objPHPExcel->getActiveSheet()->setCellValue('D2', '库存');
$objPHPExcel->getActiveSheet()->getStyle('D2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
//动态生成列，从f开始，f对应的ascii吗为69
$r_model = new Records;
$records = $r_model::find()->asArray()->all();
$ascii=69;
foreach ($records as $key => $value) {
$cell=chr($ascii).'2';
$objPHPExcel->getActiveSheet()->setCellValue($cell, $value['name']);
$objPHPExcel->getActiveSheet()->getStyle($cell)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
$data[chr($ascii)]=json_decode($value['used'],true);
$last_count[chr($ascii)]['price']=$value['price'];
$last_count[chr($ascii)]['stock']=$value['num'];
$ascii+=1;
}
//在最后一列加上库存
$objPHPExcel->getActiveSheet()->setCellValue(chr($ascii).'2', '');
$objPHPExcel->getActiveSheet()->getStyle(chr($ascii).'2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
$objPHPExcel->getActiveSheet()->setCellValue(chr($ascii+1).'2', '库存');
$objPHPExcel->getActiveSheet()->getStyle(chr($ascii+1).'2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('#ADDE79');
$last['price']='';
$last['stock']='';
//遍历数据
foreach ($gifts as $key => $value) {
$i=$key+3;
$temp_num='0';
$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $key+1);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $value['name']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $value['price']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $value['stock']);
//处理used字段中的记录
foreach ($data as $k => $v) {
$objPHPExcel->getActiveSheet()->setCellValue($k.$i, isset($v[$value['id']])?'-'.$v[$value['id']]:0);
if(isset($v[$value['id']])){
$temp_num+=$v[$value['id']];
}
}
//计算单行的库存
$objPHPExcel->getActiveSheet()->setCellValue(chr($ascii+1).$i, $value['stock']-$temp_num);
$last['price']+=$value['stock']*$value['price'];
$last['stock']+=$value['stock'];
}
//合并单元格,最后两行的统计
$temp_l=$i+3;
$temp_l2=$i+4;
$objPHPExcel->getActiveSheet()->setCellValue('A'.$temp_l, '合计');
$objPHPExcel->getActiveSheet()->setCellValue('B'.$temp_l, '金额');
$objPHPExcel->getActiveSheet()->setCellValue('C'.$temp_l, $last['price']);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$temp_l2, '数量');
$objPHPExcel->getActiveSheet()->setCellValue('D'.$temp_l2, $last['stock']);
$str='A'.($temp_l).':A'.($temp_l2);
$objPHPExcel->getActiveSheet()->mergeCells($str);
//计算单列会议的消耗
$allprice='';
$allstock='';
foreach ($last_count as $key => $value) {
$objPHPExcel->getActiveSheet()->setCellValue($key.$temp_l, '-'.$value['price']);
$objPHPExcel->getActiveSheet()->setCellValue($key.$temp_l2, '-'.$value['stock']);
$allprice += $value['price'];
$allstock += $value['stock'];
}
//计算最后一列的统计
$objPHPExcel->getActiveSheet()->setCellValue(chr($ascii+1).$temp_l, $allprice);
$objPHPExcel->getActiveSheet()->setCellValue(chr($ascii+1).$temp_l2, $allstock);
//下载这个表格，在浏览器输出
$file_name = '院外预算会议评估';
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='.$file_name.'.xls');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
}效果图:
yii2_db_to_excel
/**
* @数据导出excel文档
* @return string
* @throws \PHPExcel_Exception
* @throws \PHPExcel_Reader_Exception
* @throws \PHPExcel_Writer_Exception
*/
public function actionUpexcel()
{
$data = Models::find()->asArray()->all();//取得要导出的数据
$objPHPExcel = new \PHPExcel();
//设置文件的一些属性，在xls文件——>属性——>详细信息里可以看到这些值，xml表格里是没有这些值的
$objPHPExcel
->getProperties() //获得文件属性对象，给下文提供设置资源
->setCreator( "MaartenBalliauw") //设置文件的创建者
->setLastModifiedBy( "MaartenBalliauw") //设置最后修改者
->setTitle( "Office2007 XLSX Test Document" ) //设置标题
->setSubject( "Office2007 XLSX Test Document" ) //设置主题
->setDescription( "Test document for Office2007 XLSX, generated using PHP classes.") //设置备注
->setKeywords( "office 2007 openxmlphp") //设置标记
->setCategory( "Test resultfile"); //设置类别
// 位置aaa *为下文代码位置提供锚
//给表格添加数据
$objPHPExcel->setActiveSheetIndex(0)//表头的信息
->setCellValue('A1', "商家ID")
->setCellValue('B1', "用户ID")
->setCellValue('C1', "姓名/公司")
->setCellValue('D1', "电话")
->setCellValue('E1', "店铺名称")
->setCellValue('F1', "入驻时间")
->setCellValue('G1', "审核状态")
->setCellValue('H1', "商品数量")
->setCellValue('I1', "商品图片数量")
->setCellValue('J1', "订单数量")
->setCellValue('K1', "商家一级分类")
->setCellValue('L1', "商家二级分类")
->setCellValue('M1', "分站");
$i=2;
foreach ($data as $key => $value) {
$objPHPExcel->getActiveSheet() // 设置第一个内置表（一个xls文件里可以有多个表）为活动的
->setCellValue( 'A'.$i, $value['id'] ) //给表的单元格设置数据
->setCellValue( 'B'.$i, $value['uid'] ) //数据格式可以为字符串
->setCellValue( 'C'.$i, $value['name']) //数字型
->setCellValue( 'D'.$i, " ".$value['mobile'] ) //
->setCellValue( 'E'.$i, $value['shop_name'] ) //布尔型
->setCellValue( 'F'.$i, $value['created_at'] )
->setCellValue( 'G'.$i, $value['status'] ? '已审核':'未审核' )
->setCellValue( 'H'.$i, $value['goods_total'] )
->setCellValue( 'I'.$i, $value['goods_img_total'])
->setCellValue( 'J'.$i, $value['order_total'] )
->setCellValue( 'K'.$i, ShopCategory::findOne($value['shop_cate_parent'])->name )
->setCellValue( 'L'.$i, ShopCategory::findOne($value['shop_cate'])->name)
->setCellValue( 'M'.$i, \common\models\Fenzhan::find()->where(['site_id'=>$value['site_id']])->one()->site_name );
$i++;
}
//合计
$objPHPExcel->getActiveSheet() // 设置第一个内置表（一个xls文件里可以有多个表）为活动的
->setCellValue( 'A'.$i, '' ) //给表的单元格设置数据
->setCellValue( 'B'.$i, '' ) //数据格式可以为字符串
->setCellValue( 'C'.$i, '') //数字型
->setCellValue( 'D'.$i, '' ) //
->setCellValue( 'E'.$i, '共'.count($data).'条记录') //布尔型
->setCellValue( 'F'.$i, '' )
->setCellValue( 'G'.$i, '' )
->setCellValue( 'H'.$i, '' )
->setCellValue( 'I'.$i, '' )
->setCellValue( 'J'.$i, '' )
->setCellValue( 'K'.$i, '' );
//公式
//得到当前活动的表,注意下文教程中会经常用到$objActSheet
$objActSheet =$objPHPExcel->getActiveSheet();
// 位置bbb *为下文代码位置提供锚
//给当前活动的表设置名称
$objActSheet->setTitle('统计表');
//代码还没有结束，可以复制下面的代码来决定我们将要做什么
//我们将要做的是
//1,直接生成一个文件
$objWriter =\PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('myexchel.xlsx');
ob_end_clean();//清空缓冲区
header('Content-Type:application/vnd.ms-excel');
header("Content-Disposition:attachment;filename=记录表".date('YmdHis').'.xls');
header('Cache-Control:max-age=0');
$objWriter =\PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
return $this->render($this->actionID);
}
