thinkphp导出Excel文件的示例

最新推荐文章于 2024-05-14 11:16:03 发布

转载最新推荐文章于 2024-05-14 11:16:03 发布 · 1.5k 阅读

thinkphp 专栏收录该内容

11 篇文章

订阅专栏

本文介绍两种在ThinkPHP框架中导出Excel的方法：一种是直接输出xls文件，另一种是利用PHPExcel库。提供了详细的代码示例及注意事项。

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

下面我来给各位整理两款在thinkphp中导出Excel文件的两种方法一种是原生态的输出xls文件，外一种是使用PHPExcel来实现下面我们来看看例子。

其中要注意几点：

1、必须要有 spl_autoload_register(array(‘Think’,'autoload’));
2、在本Action和父级Action均不能出现BOM头（切记）或者会导出乱码

例子1

代码如下

复制代码

/*导出xls用户个人信息*/
function export(){
header("Content-type:application/octet-stream");
header("Accept-Ranges:bytes");
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=用户信息表".date("Y-m-d").".xls");
header("Pragma: no-cache");
header("Expires: 0");
//导出xls 开始
$tag0 = iconv("UTF-8", "GB2312",'用户ID');
$tag1 = iconv("UTF-8", "GB2312",'用户名');
$tag2 = iconv("UTF-8", "GB2312",'待领取');
$tag3 = iconv("UTF-8", "GB2312",'奖金总数');
$tag4 = iconv("UTF-8", "GB2312",'开户行');
$tag5 = iconv("UTF-8", "GB2312",'开户城市');
$tag6 = iconv("UTF-8", "GB2312",'开户行地址');
$tag7 = iconv("UTF-8", "GB2312",'银行卡号');
$tag8 = iconv("UTF-8", "GB2312",'持卡人姓名');
echo "$tag0＼t$tag1＼t$tag2＼t$tag3＼t$tag4＼t$tag5＼t$tag6＼t$tag7＼n";
////查询的一张表
//$arr=M ('textpage')->field('username,count(id) as allcount,sum(price) as allprice ')->group('username')->select();
$field="crowd_textpage.tid,crowd_textpage.username,users.bankName,users.city,users.bankAddress,
users.bankCard,users.bankUsr,count(crowd_textpage.id) as allcount,sum(crowd_textpage.price) as allprice";
  $arr = M ('textpage')->field($field)
->join('crowd_user as users ON crowd_textpage.username=users.username')
->group('crowd_textpage.username')
->select();
//dump(M ('textpage')->getLastSql());die;
foreach($arr as $key=>$val){
//$date        =    date('Y-m-d',$val['pay_time']);
$tid    =    iconv("UTF-8", "GB2312", $val['tid']);
$tid=$tid?$tid:'-';
$username    =    iconv("UTF-8", "GB2312", $val['username']);
$username=$username?$username:'-';
$allcount    =    iconv("UTF-8", "GB2312", $val['allcount']);
$allcount=$allcount?$allcount:'-';
$allprice    =    iconv("UTF-8", "GB2312", $val['allprice']);
$allprice=$allprice?$allprice:'-';
$bankName    =    iconv("UTF-8", "GB2312", $val['bankName']);
$bankName=$bankName?$bankName:'-';
$city    =    iconv("UTF-8", "GB2312", $val['city']);
$city=$city?$city:'-';
$bankAddress    =    iconv("UTF-8", "GB2312", $val['bankAddress']);
$bankAddress=$bankAddress?$bankAddress:'-';
$bankCard    =    iconv("UTF-8", "GB2312", $val['bankCard']);
$bankCard=$bankCard?$bankCard:'-';
$bankUsr    =    iconv("UTF-8", "GB2312", $val['bankUsr']);
$bankUsr=$bankUsr?$bankUsr:'-';
echo "$tid＼t$username＼t$allcount＼t$allprice＼t$bankName＼t$city
＼t$bankAddress＼t'$bankCard＼t$bankUsr＼n";
  }
}

例子2

1：下载PHPexcel
2：将下载的文件夹放到thinkphp中的＼Extend＼Vendor下面命名为Classes
我的是在这个文件夹下还有个PHPExcel和一个PHPExcel.php文件
3:

代码如下

复制代码

function pushExcel(){
$user=D("User");
$list=$user->relation(true)->select();
Vendor("Classes.PHPExcel");
Vendor("Classes.PHPExcel.IOFactory");
Vendor("Classes.PHPExcel.Reader.Excel5");
//创建处理对象实例
$objPhpExcel=new PHPExcel();
$objPhpExcel->getActiveSheet()->getDefaultColumnDimension()->setAutoSize(true);//设置单元格宽度
//设置表格的宽度手动
$objPhpExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$objPhpExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$objPhpExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$objPhpExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
//设置标题
$rowVal = array(0=>'编号',1=>'姓名', 2=>'性别', 3=>'年龄', 4=>'所属部门',
5=>'职位', 6=>'邮箱', 7=>'办公电话', 8=>'移动电话',
9=>'住址');
foreach ($rowVal as $k=>$r){
   $objPhpExcel->getActiveSheet()->getStyleByColumnAndRow($k,1)
   ->getFont()->setBold(true);//字体加粗
   $objPhpExcel->getActiveSheet()->getStyleByColumnAndRow($k,1)->
   getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//文字居中
   $objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($k,1,$r);
}
//设置当前的sheet索引用于后续内容操作
$objPhpExcel->setActiveSheetIndex(0);
$objActSheet=$objPhpExcel->getActiveSheet();
//设置当前活动的sheet的名称
$title="公司通讯录";
$objActSheet->setTitle($title);
//设置单元格内容
foreach($list as $k => $v)
{
   $num=$k+2;
   $objPhpExcel->setActiveSheetIndex(0)
   //Excel的第A列，uid是你查出数组的键值，下面以此类推
   ->setCellValue('A'.$num, $v['userID'])
   ->setCellValue('B'.$num, $v['username'])
   ->setCellValue('C'.$num, $v['sex'])
   ->setCellValue('D'.$num, $v['age'])
   ->setCellValue('E'.$num, $v['department']['departmentName'])
   ->setCellValue('F'.$num, $v['position'])
   ->setCellValue('G'.$num, $v['Email'])
   ->setCellValue('H'.$num, $v['phone'])
   ->setCellValue('I'.$num, $v['mobilephone'])
   ->setCellValue('J'.$num, $v['Address']);
}
$name=date('Y-m-d');//设置文件名
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Transfer-Encoding:utf-8");
header("Pragma: no-cache");
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$title.'_'.urlencode($name).'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPhpExcel, 'Excel5');
$objWriter->save('php://output');
}
捣鼓了一上午终于好了现在把写好的贴出来
程序中有与ThinkPHP不兼容的地方在config.php中添加'OUTPUT_ENCODE' => false ,
另外html中给按钮添加事件监听
<input    id="toExcel" name="button_toExcel" type="button" value="导出Excel">
<script type="text/javascript">
$(function (){
   $("#toExcel").click(function(){
    document.location.href="__URL__/pushExcel";
    });
});
</script>