数据库的格式:
后台:
需求:将用户导到excel表中
//将用户导出到excel表中,并保存用户头像
public function export(){
$exportId = json_decode(Request::param('exportId'),true);//需要导出数据的id
$exportS = Db::name('member');//表名
$objPHPExcel = new PHPExcel();
$objActSheet = $objPHPExcel->getActiveSheet();
try {
$objActSheet->setCellValue( 'A1','用户名' );
$objActSheet->setCellValue( 'B1','电话' );
$objActSheet->setCellValue( 'C1','用户类型' );
$objActSheet->setCellValue( 'D1','性别' );
$objActSheet->setCellValue( 'E1','学校' );
$objActSheet->setCellValue( 'F1','班级' );
$objActSheet->setCellValue( 'G1','地址' );
$objActSheet->setCellValue( 'H1','注册时间' );
$objActSheet->setCellValue( 'I1','头像' );
$objActSheet->setCellValue( 'J1','头像链接' );
$cell_counter = 1;
if( $exportId == null){
$store_ids = $exportS->select();
}else{
$store_ids = $exportS->where('id','in',$exportId)->select();
}
foreach ($store_ids as $shopinfo) {
switch ($shopinfo['user_type']) {
case 1:
$shopinfo['user_type'] = '学生';
break;
case 2:
$shopinfo['user_type'] = '老师';
break;
case 3:
$shopinfo['user_type'] = '家长';
break;
}
switch ($shopinfo['gender']) {
case 1:
$shopinfo['gender'] = '男';
break;
case 0:
$shopinfo['gender'] = '女';
break;
}
$cell_counter++;
$objPHPExcel->setActiveSheetIndex( 0 );
if(strstr($shopinfo['avatar'], 'wx.qlogo')){
$avatar = substr($shopinfo['avatar'],strripos($shopinfo['avatar'],"//")+2); //截取微信头像中的https://
$shopinfo['avatar'] = $this->wx_image($avatar);
$shopinfo['customer_return_img'] = $shopinfo['avatar'];//头像地址
}else{
$shopinfo['customer_return_img'] = $shopinfo['avatar'];
}
$objActSheet->setCellValue( 'A' . $cell_counter, $shopinfo['mobile'] );
$objActSheet->setCellValue( 'B' . $cell_counter, $shopinfo['mobile'] );
$objActSheet->setCellValue( 'C' . $cell_counter, $shopinfo['user_type'] );
$objActSheet->setCellValue( 'D' . $cell_counter, $shopinfo['gender'] );
$objActSheet->setCellValue( 'E' . $cell_counter, $shopinfo['school'] );
$objActSheet->setCellValue( 'F' . $cell_counter, $shopinfo['class'] );
$objActSheet->setCellValue( 'G' . $cell_counter, $shopinfo['address'] );
$objActSheet->setCellValue( 'H' . $cell_counter, $shopinfo['create_time'] );
$objActSheet->setCellValue( 'J' . $cell_counter, $shopinfo['avatar'] );
if ($shopinfo['customer_return_img']) {
// 图片生成
$objDrawing[$cell_counter] = new \PHPExcel_Worksheet_MemoryDrawing();
// 截取图片的格式,用不同的方法
$end[$cell_counter] = substr($shopinfo['customer_return_img'], -3);
if ($end[$cell_counter] == 'jpg' || $end[$cell_counter] == 'peg') {
$img[$cell_counter] = @imagecreatefromjpeg($shopinfo['customer_return_img']);
}else if ($end[$cell_counter] == 'png') {
$img[$cell_counter] = @imagecreatefrompng($shopinfo['customer_return_img']);
}else if ($end[$cell_counter] == 'gif') {
$img[$cell_counter] = @imagecreatefromgif($shopinfo['customer_return_img']);
}
$objDrawing[$cell_counter]->setImageResource($img[$cell_counter]);
$objDrawing[$cell_counter]->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT);//渲染方法
$objDrawing[$cell_counter]->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
// // 设置宽度高度
$objDrawing[$cell_counter]->setHeight(40);//照片高度
$objDrawing[$cell_counter]->setWidth(60); //照片宽度
// /*设置图片要插入的单元格*/
$objDrawing[$cell_counter]->setCoordinates('I'.$cell_counter);
// // 图片偏移距离
$objDrawing[$cell_counter]->setOffsetX(8);
$objDrawing[$cell_counter]->setOffsetY(8);
$objDrawing[$cell_counter]->setWorksheet($objPHPExcel->getActiveSheet());
// 表格高度
$objActSheet->getRowDimension($cell_counter)->setRowHeight(80);
}else{
$objActSheet->setCellValue('I'.$cell_counter, '');
// 表格高度
$objActSheet->getRowDimension($cell_counter)->setRowHeight(40);
}
}
// Set column data auto width
for ( $col = 'A'; $col !== 'N'; $col ++ ) {
$objPHPExcel->getActiveSheet()->getColumnDimension( $col )->setAutoSize( true );
}
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle( "用户统计表" );
// Save Excel file
$upload_dir = Env::get('root_path') . 'public/upload/excel/';
if (!file_exists($upload_dir)){
mkdir( $upload_dir, 0755, true );
}
$objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel, 'Excel2007' );
$filesname = time().'.xlsx';
$objWriter->save( $upload_dir . '/'.$filesname );
echo '1|'.config('website').'/upload/excel/'.$filesname;
}catch ( Exception $e ){
echo "导出失败";
}
}
//微信头像转换成正常图片格式(如:jpg、gif、jpeg等)
/**
* @param $url 微信图片地址
* @return string 正常图片地址
*/
public function wx_image($url){
$header = array(
'User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:45.0) Gecko/20100101 Firefox/45.0',
'Accept-Language: zh-CN,zh;q=0.8,en-US;q=0.5,en;q=0.3',
'Accept-Encoding: gzip, deflate',);
$curl = curl_init();
curl_setopt($curl, CURLOPT_URL, $url);curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
curl_setopt($curl, CURLOPT_FOLLOWLOCATION, true);curl_setopt($curl, CURLOPT_ENCODING, 'gzip');
curl_setopt($curl, CURLOPT_HTTPHEADER, $header);
$data = curl_exec($curl);$code = curl_getinfo($curl, CURLINFO_HTTP_CODE);
curl_close($curl);
if ($code == 200) {//把URL格式的图片转成base64_encode格式的!
$imgBase64Code = "data:image/jpeg;base64," . base64_encode($data);
}
$img_content=$imgBase64Code;//图片内容
if (preg_match('/^(data:\s*image\/(\w+);base64,)/', $img_content, $result)) {
$type = $result[2];//得到图片类型png?jpg?gif?
$fileData = base64_decode(str_replace($result[1], '', $img_content));
$image_name = uniqid() .".{$type}";
$upload_dir = ENV::get('root_path') . 'public/upload/img/';
if (!file_exists($upload_dir)){
mkdir( $upload_dir, 0755, true );
}
$filedir = $upload_dir . '/' . $image_name;
$src = config( 'website' ) . 'upload/img/' . $image_name;
if (file_put_contents($filedir,$fileData)) {
return $src;
}
}
}
结果:
还有一个小问题:
php对Excel设置宽度,不起作用,会的小伙伴,留言告知一下!!!