/**
* 保存所有审核数据到excel,包括图片数据
* @param $data 需要保存的数据
*/
public function saveExcel($data)
{
//var_dump($data);
//die;
header("Content-Type:text/html;charset=utf-8");
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
//Excel表格式,
$letter = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
//表头数组
$tableheader = array('任务id', '截图id', '用户id', '提交任务信息', '创建时间', '任务标题', '审核状态', '提交审核图片',);
//创建对象
$excel = new \PHPExcel();
//填充表头信息'
$excel->getActiveSheet()->setTitle('截图任务审核');
//删除原有的文件目录
$this->delFileUnderDir(APP_PATH . '/temp/image/');
for ($i = 0; $i < count($tableheader); $i++) {
$excel->getActiveSheet()->setCellValue("$letter[$i]1", "$tableheader[$i]");
}
$data = json_decode(json_encode($data), true);
//填充表格信息
$objSheet = $excel->getActiveSheet();
$objSheet->getColumnDimension('H')->setWidth(70);//设置默认列宽
$objSheet->getColumnDimension('I')->setWidth(70);//设置默认列宽
$objSheet->getColumnDimension('J')->setWidth(70);//设置默认列宽
$objSheet->getColumnDimension('K')->setWidth(70);//设置默认列宽
for ($i = 2; $i <= count($data) + 1; $i++) {
$j = 0;
foreach ($data[$i - 2] as $key => $value) {
if (gettype($value) == 'array') {
foreach ($value as $k => $v) {
$img = self::curlGet($v);
$dir = APP_PATH . '/temp/image/';
$file_info = pathinfo($v);
if (!empty($file_info['basename'])) { //过滤非文件类型
$basename = $file_info['basename'];
is_dir($dir) OR mkdir($dir, 0777, true); //进行检测文件是否存在
file_put_contents($dir . $basename, $img);
$objDrawing = new \PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($dir . $basename);
$objSheet->getRowDimension($i)->setRowHeight(400);//设置默认行高
//$objDrawing->setWidth(400); //照片宽度
$objDrawing->setHeight(400); //照片高度
// var_dump($letter[$j+$k].$i);die;
$objDrawing->setCoordinates($letter[$j + $k] . $i); //设置图片要插入的单元格
$objDrawing->setOffsetX(12); // 图片偏移距离
$objDrawing->setOffsetY(12);
$objDrawing->setWorksheet($objSheet);
} else {
$excel->getActiveSheet()->setCellValue("$letter[$j]$i", " ");
}
}
} else {
$excel->getActiveSheet()->setCellValue("$letter[$j]$i", "$value");
}
$j++;
}
}
//创建Excel输入对象
ob_end_clean();
$objWriter = new \PHPExcel_Writer_Excel5($excel);
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");
//多浏览器下兼容中文标题
$fileName = '任务截图审核_user_mission_mapping_' . date("Y-m-d-h-i-s", time());
$encoded_filename = urlencode($fileName);
$ua = $_SERVER["HTTP_USER_AGENT"];
if (preg_match("/MSIE/", $ua)) {
header('Content-Disposition: attachment; filename="' . $encoded_filename . '.xls"');
} else if (preg_match("/Firefox/", $ua)) {
header('Content-Disposition: attachment; filename*="utf8\'\'' . $fileName . '.csv"');
} else {
header('Content-Disposition: attachment; filename="' . $fileName . '.csv"');
}
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
}
public static function curlGet($url)
{
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); // 这个是重点 请求https。
$data = curl_exec($ch);
curl_close($ch);
// $data = json_decode($data, true);
return $data;
}
/**
* 删除一个目录下的所有文件
* @param $dirName
*/
public function delFileUnderDir($dirName)
{
if ($handle = opendir("$dirName")) {
while (false !== ($item = readdir($handle))) {
if ($item != "." && $item != "..") {
if (is_dir("$dirName/$item")) {
$this->delFileUnderDir("$dirName/$item");
} else {
unlink("$dirName/$item");
}
}
}
closedir($handle);
}
}
仅仅导出数据
public function downLoadExcel($data)
{
import('PHPExcel.PHPExcel', EXTEND_PATH);
header("Content-Type:text/html;charset=utf-8");
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
$letter = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
//表头数组
$tableheader = array('日期', '注册人数',);
$excel = new \PHPExcel();
//填充表头信息'
$excel->getActiveSheet()->setTitle('用户注册统计');
for ($i = 0; $i < count($tableheader); $i++) {
$excel->getActiveSheet()->setCellValue("$letter[$i]1", "$tableheader[$i]");
}
$data = json_decode(json_encode($data), true);
//填充表格信息
$objSheet = $excel->getActiveSheet();
$objSheet->getColumnDimension('A')->setWidth(50);
$objSheet->getColumnDimension('B')->setWidth(50);
for ($i = 2; $i <= count($data) + 1; $i++) {
$j = 0;
foreach ($data[$i - 2] as $key => $value) {
$excel->getActiveSheet()->setCellValue("$letter[$j]$i", "$value");
$j++;
}
}
//创建Excel输入对象
ob_end_clean();
$objWriter = new \PHPExcel_Writer_Excel5($excel);
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");
//多浏览器下兼容中文标题
$fileName = '用户统计' . date("Y-m-d-h-i-s", time());
$encoded_filename = urlencode($fileName);
$ua = $_SERVER["HTTP_USER_AGENT"];
if (preg_match("/MSIE/", $ua)) {
header('Content-Disposition: attachment; filename="' . $encoded_filename . '.xls"');
} else if (preg_match("/Firefox/", $ua)) {
header('Content-Disposition: attachment; filename*="utf8\'\'' . $fileName . '.csv"');
} else {
header('Content-Disposition: attachment; filename="' . $fileName . '.csv"');
}
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
}