<blockquote class="layui-elem-quote">
<div class="layui-inline">
<label class="layui-form-label">开始时间</label>
<div class="layui-input-block">
<input id="kaishi" type="text" name="time" lay-verify="date" placeholder="请选择...." autocomplete="off" class="layui-input" onclick="layui.laydate({elem: this})">
</div>
</div>
<div class="layui-inline">
<label class="layui-form-label">结束时间</label>
<div class="layui-input-block">
<input id="jieshu" type="text" name="time" lay-verify="date" placeholder="请选择...." autocomplete="off" class="layui-input" onclick="layui.laydate({elem: this})">
</div>
</div>
<a style="margin-left: 40px;" id="daochu" href="" class="layui-btn layui-btn-small">
<i class="layui-icon"></i> 导出成Excle
</a>
</blockquote>
//导出excel表
public function excle(){
header("Content:text/html;charset=utf-8");
$ks_time = strtotime(I('ks_time'));
$js_time = strtotime(I('js_time'));
if($ks_time && $js_time)
{
$row = M("content")->where("addtime > {$ks_time} and addtime < {$js_time}")->select();
}
else if($ks_time)
{
$row = M("content")->where("addtime > {$ks_time}")->select();
}
else
{
$row = M("content")->limit(0,100)->select();
}
if(!$row)
{
$this->error("没有查到任何数据",U("main"));
}
//输出Excel文件头，可把user.csv换成你要的文件名
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="order.csv"');
header('Cache-Control: max-age=0');
// 从数据库中获取数据，为了节省内存，不要把数据一次性读到内存，从句柄中一行一行读即可
// $stmt = $order->query($sql);
//$is = mysql_query($sql);
//打开PHP文件句柄，php://output 表示直接输出到浏览器
$fp = fopen('php://output', 'a');
//输出Excel列名信息
$head = array('ID','姓名','手机','学校','薪资','捐款金额','主管','时间');
foreach ($head as $i => $v) {
// CSV的Excel支持GBK编码，一定要转换，否则乱码
$head[$i] = iconv('utf-8', 'gbk', $v);
}
//将数据通过fputcsv写到文件句柄
fputcsv($fp, $head);
//计数器
$cnt = 0;
//每隔$limit行，刷新一下输出buffer，不要太大，也不要太小
$limit = 100000;
//逐行取出数据，不浪费内存
foreach ($row as $key => $value) {
$cnt ++;
if ($limit == $cnt) { //刷新一下输出buffer，防止由于数据过多造成问题
ob_flush();
flush();
$cnt = 0;
}
$value['addtime'] = date("Y-m-d H:i:s",$value['addtime']);
foreach ($value as $i => $v) {
$value[$i] = iconv('utf-8', 'gbk', $v);
}
fputcsv($fp, $value);
}
}导出晋级版带统计
/**
* 导出excle表
*
* @param 如果没有时间条件默认一个月的数据统计
*
* @return csv文件、
*
*/
public function excle(){
$ks_time = strtotime(I('ks_time'));
$js_time = strtotime(I('js_time').'23:59:59');
//echo $js_time;
// echo $ks_time;
// exit;
if($ks_time && $js_time)
{
$row = M("user")->where(array('type' => 1,array('addtime'=>array('between',array($ks_time,$js_time)))))->field("id,name,shouji,gongsi_name,hangye_name,beizhu,tuiguang_name,xiaoshou_name,jiedantime,tuiguang_jiagou_id,xiaoshou_jiagou_id")->select();
}
else if($ks_time)
{
$row = M("user")->where("addtime > {$ks_time}")->field("id,name,shouji,gongsi_name,hangye_name,beizhu,tuiguang_name,xiaoshou_name,jiedantime,tuiguang_jiagou_id,xiaoshou_jiagou_id")->select();
}
else
{
$row = M("user")->where(array("type" => 1,array('addtime'=>array('GT',mktime(0,0,0,date('m'),1,date('Y'))))))->field("id,name,shouji,gongsi_name,hangye_name,beizhu,tuiguang_name,xiaoshou_name,jiedantime,tuiguang_jiagou_id,xiaoshou_jiagou_id")->select();
}
if(!$row)
{
$this->error("没有查到任何数据",U("user"));
}
//输出Excel文件头，可把user.csv换成你要的文件名
header("Content:text/html;charset=utf-8");
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="盛安内部人员订单统计表.csv"');
header('Cache-Control: max-age=0');
// 从数据库中获取数据，为了节省内存，不要把数据一次性读到内存，从句柄中一行一行读即可
// $stmt = $order->query($sql);
//$is = mysql_query($sql);
//打开PHP文件句柄，php://output 表示直接输出到浏览器
$fp = fopen('php://output', 'a');
//输出Excel列名信息
$head = array('ID','姓名','手机','公司名称','所属行业','备注','负责推广人','负责销售人','接单时间');
// foreach ($head as $i => $v) {
// CSV的Excel支持GBK编码，一定要转换，否则乱码
//$head[$i] = iconv('utf-8', 'gbk', $v);
// }
// print_r($head);
// exit;
//将数据通过fputcsv写到文件句柄
fputcsv($fp, $head);
//计数器
$cnt = 0;
//每隔$limit行，刷新一下输出buffer，不要太大，也不要太小
$limit = 100000;
//逐行取出数据，不浪费内存
foreach ($row as $key => $value) {
$cnt ++;
if ($limit == $cnt) { //刷新一下输出buffer，防止由于数据过多造成问题
ob_flush();
flush();
$cnt = 0;
}
$value['jiedantime'] = "\t".date("Y-m-d H:i:s",$value['jiedantime']);
$value['shouji'] = "\t".$value['shouji'];
$tuiguang_zhandui_name = M("jiagou")->where("id = ".$value['tuiguang_jiagou_id'])->find();
$xiaoshou_zhandui_name = M("jiagou")->where("id = ".$value['xiaoshou_jiagou_id'])->find();
$value['tuiguang_name'] = $tuiguang_zhandui_name['name'].':'.$value['tuiguang_name'];
$value['xiaoshou_name'] = $xiaoshou_zhandui_name['name'].':'.$value['xiaoshou_name'];
unset($value['tuiguang_jiagou_id']);
unset($value['xiaoshou_jiagou_id']);
// foreach ($value as $i => $v) {
//$value[$i] = iconv('utf-8', 'gbk', $v);
// }
fputcsv($fp, $value);
}
if($ks_time && $js_time)
{
$where['addtime'] = array('between',array($ks_time,$js_time));
fputcsv($fp, array('销售部从'.I('ks_time').'到'.I('js_time').'订单数据统计：'));
}
else
{
$where['addtime'] = array('GT',mktime(0,0,0,date('m'),1,date('Y')));
fputcsv($fp, array('销售部当月订单数据统计：'));
}
/*销售部统计*/
$xiaoshou_qu_arr = M("jiagou")->where("parent_id = 2")->field('id,name')->select();
foreach($xiaoshou_qu_arr as $key => $value)
{
$qu_id = $value['id'];
unset($value['id']);
fputcsv($fp, $value);
$xiaoshou_zd_arr = M("jiagou")->where("parent_id = {$qu_id}")->field('id,name')->select();
foreach($xiaoshou_zd_arr as $key2 => $value2)
{
$zd_id = $value2['id'];
$zhandui_count = M("xiaoshou_list")->where(array('jiagou_id' => $zd_id,'type' => 1,$where))->count();
unset($value2['id']);
$value2['name'] = $value2['name'].'('.$zhandui_count.')';
fputcsv($fp, $value2);
$admin_user_arr = M("admin_user")->where("jiagou_id = {$zd_id}")->field('id,name')->select();
foreach($admin_user_arr as $key3 => $value3)
{
$admin_id = $value3['id'];
unset($value3['id']);
$user_count = M("xiaoshou_list")->where(array('xiaoshou_id' => $admin_id,'type' => 1,$where))->count();
$admin_user_arr[$key3] = $value3['name'].'('.$user_count.')';
}
if($admin_user_arr)
{
$admin_user_arr = array_merge(array(0 => '成员：'),$admin_user_arr);
}
else
{
$admin_user_arr['chengyuan'] = '成员：';
$admin_user_arr['zanwu'] = '暂无';
}
// echo "<pre>";
// print_r($admin_user_arr);
fputcsv($fp, $admin_user_arr);
}
}
if($ks_time && $js_time)
{
$where['addtime'] = array('between',array($ks_time,$js_time));
fputcsv($fp, array('推广部从'.I('ks_time').'到'.I('js_time').'订单数据统计：'));
}
else
{
$where['addtime'] = array('GT',mktime(0,0,0,date('m'),1,date('Y')));
fputcsv($fp, array('推广部当月订单数据统计：'));
}
/*推广部统计*/
$tuiguang_qu_arr = M("jiagou")->where("parent_id = 3")->field('id,name')->select();
foreach($tuiguang_qu_arr as $key => $value)
{
$qu_id = $value['id'];
unset($value['id']);
fputcsv($fp, $value);
$xiaoshou_zd_arr = M("jiagou")->where("parent_id = {$qu_id}")->field('id,name')->select();
foreach($xiaoshou_zd_arr as $key2 => $value2)
{
$zd_id = $value2['id'];
$zhandui_count = M("user")->where(array('tuiguang_jiagou_id' => $zd_id,'type' => 1,$where))->count();
unset($value2['id']);
$value2['name'] = $value2['name'].'('.$zhandui_count.')';
fputcsv($fp, $value2);
$admin_user_arr = M("admin_user")->where("jiagou_id = {$zd_id}")->field('id,name')->select();
foreach($admin_user_arr as $key3 => $value3)
{
$admin_id = $value3['id'];
unset($value3['id']);
$user_count = M("user")->where(array('tuiguang_id' => $admin_id,'type' => 1,$where))->count();
$admin_user_arr[$key3] = $value3['name'].'('.$user_count.')';
}
if($admin_user_arr)
{
$admin_user_arr = array_merge(array(0 => '成员：'),$admin_user_arr);
}
else
{
$admin_user_arr['chengyuan'] = '成员：';
$admin_user_arr['zanwu'] = '暂无';
}
// echo "<pre>";
// print_r($admin_user_arr);
fputcsv($fp, $admin_user_arr);
}
}
}
<div style="width: 400px;height: 25px; display: inline-block; float: right;">
<form action="__CONTROLLER__/upload" enctype="multipart/form-data" method="post">
<input type="file" name="photo" />
<input type="submit" value="导入数据">
</form>
</div>
//导入
public function upload() {
ini_set('memory_limit','1024M');
if (!empty($_FILES)) {
$config = array(
'exts' => array('xlsx','xls'),
'maxSize' => 3145728000,
'rootPath' =>"./Public/",
'savePath' => 'Uploads/',
'subName' => array('date','Ymd'),
);
$upload = new \Think\Upload($config);
if (!$info = $upload->upload()) {
$this->error($upload->getError());
}
vendor("PHPExcel.PHPExcel");
$file_name=$upload->rootPath.$info['photo']['savepath'].$info['photo']['savename'];
$extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表格后缀格式
if ($extension == 'xlsx') {
$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
} else if ($extension == 'xls'){
$objReader =\PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
}
$sheet =$objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();//取得总行数
$highestColumn =$sheet->getHighestColumn(); //取得总列数
$j = 0;
//D('content')->execute('truncate table pro_info');
for ($i = 2; $i <= $highestRow; $i++) {
//看这里看这里,前面小写的a是表中的字段名，后面的大写A是excel中位置
$data['name'] =$objPHPExcel->getActiveSheet()->getCell("A" .$i)->getValue();
$data['shouji'] =$objPHPExcel->getActiveSheet()->getCell("B" .$i)->getValue();
$data['xuexiao'] = $objPHPExcel->getActiveSheet()->getCell("C". $i)->getValue();
$data['xinzi'] = $objPHPExcel->getActiveSheet()->getCell("D". $i)->getValue();
$data['juekuan'] = $objPHPExcel->getActiveSheet()->getCell("E". $i)->getValue();
$data['zhuguan'] = $objPHPExcel->getActiveSheet()->getCell("F". $i)->getValue();
$data['addtime'] = time();
if($data['name'] == '' || $data['name'] == null)
{
$data['name'] = '无';
}
if($data['shouji'] == '' || $data['shouji'] == null)
{
$data['shouji'] = 0;
}
if($data['xuexiao'] == '' || $data['xuexiao'] == null)
{
$data['name'] = '无';
}
if($data['xinzi'] == '' || $data['xinzi'] == null)
{
$data['xinzi'] = 0;
}
if($data['juekuan'] == '' || $data['juekuan'] == null)
{
$data['juekuan'] = 0;
}
if($data['zhuguan'] == '' || $data['zhuguan'] == null)
{
$data['zhuguan'] = '无';
}
//看这里看这里,这个位置写数据库中的表名
if(M('content')->add($data))
{
$j++;
}
}
// echo "<pre>";
// print_r($data);
// exit;
$this->success('成导入成功'.$j.'条记录!');
} else {
$this->error("请选择上传的文件");
}
}
250
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
