/**
* Generates the binary excel data
* @return string
* @throws CakeException If the excel writer does not exist
*/
private function __output()
{
$this->PhpExcel->getProperties()->setCreator(Configure::read('App.author'));
$this->PhpExcel->getProperties()->setDescription('generated by ' . Configure::read('App.title'));
$this->PhpExcel->getProperties()->setKeywords("office 2007 openxml php");
ob_start();
$writer = \PHPExcel_IOFactory::createWriter($this->PhpExcel, 'Excel2007');
if (!isset($writer)) {
throw new Exception('Excel writer not found');
}
$writer->setPreCalculateFormulas(false);
$writer->setIncludeCharts(true);
$writer->save('php://output');
$output = ob_get_clean();
return $output;
}
public function cl_to_excel()
{
import("Vendor.PhpExcel.PHPExcel");
$objPHPExcel = new \PhpExcel();
//反序列化数组
$list = unserialize(cookie('menu'));
$list = array_splice($list, 2);
$row = array();
$row[0] = array('用户id', '姓名', '年龄', '身份证号', '手机号', '地址', '注册时间');
$objPHPExcel->setActiveSheetIndex(0);
//头部
foreach ($row[0] as $key => $value) {
$cwr = chr(65 + $key) . '1';
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, $value);
}
foreach ($list['data'] as $key => $value) {
$arr[$key][] = $value['uid'];
$arr[$key][] = $value['real_name'];
$arr[$key][] = $value['age'];
$arr[$key][] = $value['idcard'];
$arr[$key][] = $value['cell_phone'];
$arr[$key][] = $value['address'] ? $value['address'] : "暂无";
$arr[$key][] = date('Y-m-d H:i:s', $value['reg_time']);
}
$total = count($arr);
$cr = count($arr['0']);
//获取每个数组有几个元素用来控制列
for ($i = 0; $i <= $cr; $i++) {
$cwr2 = chr(64 + $i + 1);
for ($j = 2; $j <= $total + 2; $j++) {
$cwr = $cwr2 . $j;
//abcdefg
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, ' ' . $arr[$j - 2][$i]);
}
}
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $cr, $tip);
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save(str_replace('.php', '.xls', __FILE__));
$filename = '区域划分.xls';
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=" . $filename);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
/**
* 导出excel功能
* time:20150713
* by lj
*/
public function cl_to_excel()
{
import("Vendor.PhpExcel.PHPExcel");
$secret_key = $this->secret_key;
$data['pname'] = $this->partner_name;
$data['sname'] = 'receive.plan';
$data['search_month'] = session('month');
$content = json_encode($data);
$string = $content . $secret_key;
$token = md5($string);
$post['content'] = $content;
$post['token'] = $token;
//curl post 请求
$url = $this->url;
$fields = $post;
$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
//控制返回结果不让自动输出
curl_setopt($ch, CURLOPT_COOKIE, "session_id=" . session('session_id'));
//
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_POST, count($fields));
// 启用时会发送一个常规的POST请求，类型为：application/x-www-form-urlencoded，就像表单提交的一样。
curl_setopt($ch, CURLOPT_POSTFIELDS, $fields);
// 在HTTP中的“POST”操作。如果要传送一个文件，需要一个@开头的文件名
$result = curl_exec($ch);
$result = json_decode($result, 1);
curl_close($ch);
$list = $result['data'];
$objPHPExcel = new \PhpExcel();
$row = array();
$row[0] = array('借款标号', '借款人id', '借款人姓名', '应收利息', '应收本金', '利息管理费', '标的状态', '应还日期', '逾期罚金', '预期总收益');
//标的状态
$bid_status = array('未通过', '已还完', '已提前还款', '迟还', '网站代还本金', '逾期贷款', '逾期未还', '还款中');
$objPHPExcel->setActiveSheetIndex(0);
//合并单元格：
//$objPHPExcel->getActiveSheet()->mergeCells('B1:F1');
//头部
foreach ($row[0] as $key => $value) {
$cwr = chr(65 + $key) . '1';
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, $value);
}
foreach ($list as $key => $value) {
$arr[$key][] = $value['borrow_id'];
$arr[$key][] = $value['borrow_uid'];
$arr[$key][] = $value['real_name'];
$arr[$key][] = $value['interest'] ? $value['interest'] : 0;
$arr[$key][] = $value['capital'];
$arr[$key][] = $value['interest_fee'] ? $value['interest_fee'] : 0;
$arr[$key][] = $bid_status[$value['status']];
$arr[$key][] = date('Y-m-d', $value['deadline']);
$arr[$key][] = $value['expired_money'];
$arr[$key][] = $value['interest'] + $value['capital'] - $value['interest_fee'];
}
$total = count($arr);
$cr = count($arr['0']);
//获取每个数组有几个元素用来控制列
for ($i = 0; $i <= $cr; $i++) {
$cwr2 = chr(64 + $i + 1);
for ($j = 2; $j <= $total + 2; $j++) {
$cwr = $cwr2 . $j;
//abcdefg
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, $arr[$j - 2][$i]);
}
}
$status_arr = array('注释1-8代表的状态', '1未通过', '2已还完', '3已提前还款', '4迟还', '5网站代还本金', '6逾期贷款', '7逾期未还', '8还款中');
//注释
$tip = implode($status_arr, ',');
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $cr, $tip);
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save(str_replace('.php', '.xls', __FILE__));
$filename = '回款计划.xls';
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=" . $filename);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
public function deal_record()
{
import("Vendor.PhpExcel.PHPExcel");
$objPHPExcel = new \PhpExcel();
$list = session('deallist');
$list = array_splice($list, 2);
//echo P;var_dump($list);die;
$row = array();
$row[0] = array('交易时间', '交易类型', '影响金额', '代收金额', '说明', '可用金额');
$objPHPExcel->setActiveSheetIndex(0);
//头部
foreach ($row[0] as $key => $value) {
$cwr = chr(65 + $key) . '1';
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, $value);
}
foreach ($list['data'] as $key => $value) {
$arr[$key][] = $value['date'];
$arr[$key][] = $value['type'];
$arr[$key][] = $value['affect_money'];
$arr[$key][] = $value['collect_money'];
$arr[$key][] = $value['desc'];
$arr[$key][] = $value['account_money'];
}
$total = count($arr);
$cr = count($arr['0']);
//获取每个数组有几个元素用来控制列
for ($i = 0; $i <= $cr; $i++) {
$cwr2 = chr(64 + $i + 1);
for ($j = 2; $j <= $total + 2; $j++) {
$cwr = $cwr2 . $j;
//abcdefg
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, $arr[$j - 2][$i]);
}
}
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save(str_replace('.php', '.xls', __FILE__));
$filename = '交易记录.xls';
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=" . $filename);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
public function cl_to_excel()
{
import("Vendor.PhpExcel.PHPExcel");
$objPHPExcel = new \PhpExcel();
// var_dump(cookie('role'));
$list = unserialize(cookie('role'));
$list = array_splice($list, 2);
$row[0] = array('推荐者的id', '推荐者姓名', '推荐者电话', '推荐者贡献额度', '推荐者注册时间');
$objPHPExcel->setActiveSheetIndex(0);
//合并单元格：
//$objPHPExcel->getActiveSheet()->mergeCells('B1:F1');
//头部
foreach ($row[0] as $key => $value) {
$cwr = chr(65 + $key) . '1';
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, $value);
}
foreach ($list['data'] as $key => $value) {
$arr[$key][] = $value['id'];
$arr[$key][] = $value['real_name'];
$arr[$key][] = $value['mobile'];
$arr[$key][] = $value['bonus'];
$arr[$key][] = date('Y-m-d', $value['reg_date']);
}
$total = count($arr);
$cr = count($arr['0']);
//获取每个数组有几个元素用来控制列
for ($i = 0; $i <= $cr; $i++) {
$cwr2 = chr(64 + $i + 1);
for ($j = 2; $j <= $total + 2; $j++) {
$cwr = $cwr2 . $j;
//abcdefg
$objPHPExcel->getActiveSheet()->SetCellValue($cwr, ' ' . $arr[$j - 2][$i]);
}
}
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save(str_replace('.php', '.xls', __FILE__));
$filename = '推荐人.xls';
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=" . $filename);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
