thinkphp5中使用PHPExcel
最简单的生成Excel文件
(1)首先第一步是:实例化phpexcel
[php] view plain copy
- $objPHPExcel = new \PHPExcel();
这里可能会遇到一个问题,报这样的错误。
然后,搜索到这个文件把这一行去掉就ok。
(2)获得当前活动sheet,因为Excel有一个默认的sheet。
[php] view plain copy
- $objSheet = $objPHPExcel->getActiveSheet();
[php] view plain copy
- $objSheet ->setTitle("demo");//可以给sheet设置名称为"demo"
(3)<1>然后就是在表格中写入数据
[php] view plain copy
- $objSheet->setCellValue("A1","姓名")->setCellValue("B1","分数");
- $objSheet->setCellValue("A2","张三")->setCellValue("B2","100");
<2>还有一个方法是用数组
[php] view plain copy
- $array = array(
- array(),
- array('姓名','分数'),
- array('张三','60'),
- array('李四','61'),
- array('王五','62'),
- );
- $objSheet -> fromArray($array);//数据较大时,不建议使用此方法,建议使用setCellValue()
(4)最后就是保存写好的数据
[php] view plain copy
- $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');//生成一个Excel2007文件
- $objWriter->save('E:/test.xlsx');//保存文件
从数据库中到导出文件
[php] view plain copy
- $path = 'E:/';
- $name = 'think_admin';
- $objPHPExcel = new \PHPExcel();
- for ($i=1; $i < 3; $i++) {
- if ($i>1) {
- $objPHPExcel->createSheet();
- }
- $objPHPExcel->setActiveSheetIndex($i-1);//把当前创建的sheet设置为活动sheet
- $objSheet = $objPHPExcel->getActiveSheet();//获得当前活动Sheet
- $objSheet->setTitle("user".$i);
- if($i==1){
- $db_admin = db('admin')->where('sex','男')->select();
- }else{
- $db_admin = db('admin')->where('sex','女')->select();
- }
- $objSheet->setCellValue('A1','id')->setCellValue('B1','user')->setCellValue('C1','name')->setCellValue('D1','password')
- ->setCellValue('E1','imgpath')->setCellValue('F1','tel')->setCellValue('G1','sex')->setCellValue('H1','age');
- $j = 2;
- foreach ($db_admin as $key => $value) {
- $objSheet->setCellValue('A'.$j,$value['id'])->setCellValue('B'.$j,$value['user'])->setCellValue('C'.$j,$value['name'])
- ->setCellValue('D'.$j,$value['password'])
- ->setCellValue('E'.$j,$value['imgpath'])->setCellValue('F'.$j,$value['tel'])->setCellValue('G'.$j,$value['sex'])
- ->setCellValue('H'.$j,$value['age']);
- $j++;
- }
- }
- $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
- $objWriter->save($path.$name.'.xlsx');
在浏览器上输出Excel文件(下载)
[php] view plain copy
- $filename = 'think_admin.xls';
- $objPHPExcel = new \PHPExcel();
- for ($i=1; $i < 3; $i++) {
- if ($i>1) {
- $objPHPExcel->createSheet();
- }
- $objPHPExcel->setActiveSheetIndex($i-1);//把当前创建的sheet设置为活动sheet
- $objSheet = $objPHPExcel->getActiveSheet();//获得当前活动Sheet
- $objSheet->setTitle("user".$i);
- if($i==1){
- $db_admin = db('admin')->where('sex','男')->select();
- }else{
- $db_admin = db('admin')->where('sex','女')->select();
- }
- $objSheet->setCellValue('A1','id')->setCellValue('B1','user')->setCellValue('C1','name')->setCellValue('D1','password')
- ->setCellValue('E1','imgpath')->setCellValue('F1','tel')->setCellValue('G1','sex')->setCellValue('H1','age');
- $j = 2;
- foreach ($db_admin as $key => $value) {
- $objSheet->setCellValue('A'.$j,$value['id'])->setCellValue('B'.$j,$value['user'])->setCellValue('C'.$j,$value['name'])->setCellValue('D'.$j,$value['password'])
- ->setCellValue('E'.$j,$value['imgpath'])->setCellValue('F'.$j,$value['tel'])->setCellValue('G'.$j,$value['sex'])->setCellValue('H'.$j,$value['age']);
- $j++;
- }
- }
- $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
- header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
- header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器将输出文件的名称(文件下载)
- header('Cache-Control: max-age=0');//禁止缓存
- $objWriter->save("php://output");