ThinkPHP5.1导出excel表
注意,5.1版本禁用了Loader的内置规则
第一步:下载phpexcel拓展
*composer require phpoffice/phpexcel
黄色警告不需要理会,下载完成后可以在框架的/vendor/phpoffice/phpexcel下查看
第二步:部署代码
use PHPExcel;
use PHPExcel_IOFactory;//引入两个类
function excel()
{
$data = session('data');//我的数据是存储在了Session里面,所以这里的获取数据
$path = dirname(__FILE__);//找到当前脚本所在路径
$PHPExcel = new \PHPExcel();//实例化phpexcel
$PHPSheet = $PHPExcel->getActiveSheet();
$PHPSheet->setTitle("demo");//设置表内部名称
$PHPSheet->setCellValue("A1", "ID")->setCellValue("B1", "订单编号")
->setCellValue("C1", "企业名称")
->setCellValue("D1", "企业邮箱")
->setCellValue("E1", "下单时间")
->setCellValue("F1", "购买套餐")
->setCellValue("G1", "金额");//表格数据
$num=2;
//数据
foreach ($data as $k => $v) {
$PHPSheet->setCellValue("A" . $num, $v['order_id']);
$PHPSheet->setCellValue("B" . $num, $v['order_num']);
$PHPSheet->setCellValue("C" . $num, $v['trade_name']);
$PHPSheet->setCellValue("D" . $num, $v['trade_email']);
$v['now_time']=date("Y-m-d H:i",$v['now_time']) ;
$PHPSheet->setCellValue("E" . $num, $v['now_time']);
$PHPSheet->setCellValue("F" . $num, $v['static_name']);
$PHPSheet->setCellValue("G" . $num, $v['money']);
$num++;
}
$PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, "Excel2007");//创建生成的格式
header('Content-Disposition: attachment;filename="表单数据.xlsx"');//下载下来的表格名
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$PHPWriter->save("php://output");//表示在$path路径下面生成demo.xlsx文件
}
第三步:在html上面请求这个方法就可以实现导出并下载了
---------------------第二种方法-------------------
<?php
namespace app\index\controller;
use think\Db;
use PHPExcel;
use PHPExcel_IOFactory;//引入两个类
class Index
{
/**
* Undocumented function
* 导出
* @param [type] $expTitle
* @param [type] $expCellName
* @param [type] $expTableData
* @param [type] $fileName
* @return void
*/
public function exportExcel($expTitle,$expCellName,$expTableData,$fileName){
$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
$fileName = $fileName;//or $xlsTitle 文件名称可根据自己情况设定
$cellNum = count($expCellName);
$dataNum = count($expTableData);
// vendor("PHPExcel.PHPExcel");
$objPHPExcel = new PHPExcel();
$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
// $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.' Export time:'.date('Y-m-d H:i:s'));
for($i=0;$i<$cellNum;$i++){
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
}
// Miscellaneous glyphs, UTF-8
for($i=0;$i<$dataNum;$i++){
for($j=0;$j<$cellNum;$j++){
$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
}
}
ob_end_clean(); // 清除缓冲区,避免乱码
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xlsx"');
header("Content-Disposition:inline;filename=$fileName.xlsx");//attachment新窗口打印inline本窗口打印
$objWriter->save('php://output');
}
function expUser(){//导出Excel
$xlsName = "User";
$xlsCell = array(
array('id','账号序列'),
array('name','名字'),
array('sex','性别'),
);
$xlsData = DB::table('app_association')->Field('id,name,sex')->select();
foreach ($xlsData as $k => $v)
{
$xlsData[$k]['sex']=$v['sex']==1?'男':'女';
}
$this->exportExcel($xlsName,$xlsCell,$xlsData,'123');
}
}