今天正好需要需要对数据库中用户数据进行整理，自己简单写了个导出xls的小程序，具体控制器代码如下，使用的thinkphp3.2的框架写的，需要将附件的解压放入\ThinkPHP\Library\Org\Util下应该是一个文件夹一个文件，并将PHPExcel.php改名为PHPExcel.class.php
然后我们就开始撸代码了，我们是翻到哪页就将哪页的数据导出为EXCEL，控制器代码如下
<?php
namespace Home\Controller;
use Think\Controller;
class IndexController extends Controller {
public function index(){
$data = M('miibeian');
$count = $data->count();
$Page = new \Think\Page($count,10000);
$show = $Page->show();
$list = $data->order('id')->limit($Page->firstRow.','.$Page->listRows)->select();
$this->assign('page',$show);
$this->assign("list",$list);
$this->display();
}
public function phpexcel(){
$seg = isset($_GET['seg'])?I('get.seg'):1;
$num = 10000;
if($seg==1){
$start = 0;
}else{
$start = ($seg-1) * $num;
}
$data = M('miibeian')->order('id')->limit($start,$num)->select();
//echo M("miibeian")->getLastSql();
import("Org.Util.PHPExcel");
import("Org.Util.PHPExcel.Writer.Excel5.php");
import("Org.Util.PHPExcel.IOFactory.php");
$filename = "beian_p_".$seg.".xls";
$headArr=array("ID","账户","姓名","电话","邮箱","ID2","状态");
$this->getExcel($filename,$headArr,$data);
}
private function getExcel($fileName,$headArr,$data){
//对数据进行检验
if(empty($data) || !is_array($data)){
die("data must be a array");
}
if(empty($fileName)){
exit;
}
$objPHPExcel = new \PHPExcel();
$objProps = $objPHPExcel->getProperties();
//设置表头
$key = ord("A");
foreach($headArr as $v){
$colum = chr($key);
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
$key += 1;
}
$column = 2;
$objActSheet = $objPHPExcel->getActiveSheet();
foreach($data as $key => $rows){ //行写入
$span = ord("A");
foreach($rows as $keyName=>$value){// 列写入
$j = chr($span);
$objActSheet->setCellValue($j.$column, $value);
$span++;
}
$column++;
}
$fileName = iconv("utf-8", "gb2312", $fileName);
//重命名表
// $objPHPExcel->getActiveSheet()->setTitle('test');
//设置活动单指数到第一个表,所以Excel打开这是第一个表
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean();
ob_start();
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"$fileName\"");
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); //文件通过浏览器下载
exit;
}
}前端页面代码如下
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>用户数据分页导出小程序</title>
<link rel="stylesheet" type="text/css" href="__PUBLIC__/css/index.css">
<script type="text/javascript" src="__PUBLIC__/js/jquery.min.js"></script>
</head>
<body>
<a id="btn" href="{:U('Index/phpexcel',array('seg'=>$_GET['p']))}" target="_blank">导出到EXECL</a>
<div class="tips"><p class="red">只导出本页数据，如果需要导出其它数据请翻页再导出！</p></div>
<div class="page">{$page}</div>
<table>
<tr>
<th>ID</th>
<th>账户</th>
<th>姓名</th>
<th>电话</th>
<th>邮箱</th>
<th>ID2</th>
<th>状态</th>
</tr>
<volist name="list" id="vo">
<tr>
<td>{$vo.id}</td>
<td>{$vo.name}</td>
<td>{$vo.xm}</td>
<td>{$vo.tel}</td>
<td>{$vo.mail}</td>
<td>{$vo.id2}</td>
<td>{$vo.status}</td>
</tr>
</volist>
</table>
</body>
</html>
下面数据就不展现了，反正就是翻一页导出一页数据，最好上传PHPExcel类的附件提供下载
我要评论