本文主要讲解phpexcle的导入导出操作， phpexcle下载地址: https://github.com/PHPOffice/PHPExcel ,这里以CI 框架为例进行说明，其他框架类似。
php excel 导出操作
首先从数据库查询出要导出的数据，指定导出字段和标题的对应关系：
$title = '购车订单列表-'.date('Y-m-d-His',time());//导出文件名称
$firstRow = array('订单编号','下单时间','下单车型','网点','用户姓名','用户电话','状态');//标题
$map = array('A'=>'order_num','B'=>'ctime','C'=>'version_title','D'=>'point_name','E'=>'name','F'=>'phone','G'=>'status_name');//数据显示位置
$this->phpexcel_xls($title,$firstRow,$data,$map); //调用导出函数
在CI 框架中使用方法如下：
/**
* 用户购车订单导出
* @author dichuanjun
* @return mixed
*/
public function buy_order_export(){
$phone=$this->input->get('phone',true);//手机号
$name=$this->input->get('name',true);//姓名
$cv_id=$this->input->get('cv_id',true);//车型版本id
$start_time=$this->input->get('start_time',true);//下单日期开始时间
$end_time=$this->input->get('end_time',true);//下单日期结束时间
$status=$this->input->get('status',true);//订单状态 1：进行中 2：已完成 0：已取消 -1：全部
$this->load->model('M_buy_order');
$data=$this->M_buy_order->buy_order_export($cv_id,$start_time,$end_time,$name,$phone,$status);
$title = '购车订单列表-'.date('Y-m-d-His',time());
$firstRow = array('订单编号','下单时间','下单车型','网点','用户姓名','用户电话','状态');
$map = array('A'=>'order_num','B'=>'ctime','C'=>'version_title','D'=>'point_name','E'=>'name','F'=>'phone','G'=>'status_name');
$this->phpexcel_xls($title,$firstRow,$data,$map);
}
导出核心方法phpexcel_xls()如下：
/**
* 2018-03-14
* [phpexcel_xls 公用数据导出]
* @param [type] $title [导出excel标题]
* @param [type] $firstRow [第一行标题名称]
* @param [type] $data [数据]
* @param [type] $map [数据对应的字段]
* @return [type] [description]
*/
public function phpexcel_xls($title,$firstRow,$data,$map){
set_time_limit(0);
ini_set("memory_limit","300M");
$this->load->library('PHPExcel/Classes/PHPExcel'); //引入phpexcel 核心类
$objPHPExcel = new PHPExcel();
$one = 1;
for($i=0;$i< count($firstRow);$i++){
$column = $this->stringFromColumnIndex($i);
$objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(22);
$objPHPExcel->getActiveSheet()->getStyle($column)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中
$objPHPExcel->getActiveSheet()->getStyle($column)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
$objPHPExcel->getActiveSheet()->getStyle($column)->getAlignment()->setWrapText(true);//特定位置添加换行符
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($column.$one,$firstRow[$i]);
}
$num = 2;
foreach ($data as $k=>$v) {
foreach ($map as $col=>$name) {
$v[$name] = isset($v[$name])?$v[$name]:' ';
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($col.$num, ' '.$v[$name]);
}
$num++;
}
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$title.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //设置以Excel5格式(Excel97-2003工作簿)
$objWriter->save('php://output');
exit;
}
导出多个工作区表格文件
// 导出
$map = array(
'A' => 'trade_time',
'B' => 'use_date',
);
$firstRow = array(
'A1' => '交易日期',
'B1' => '乘车日期',
);
$title = "测试";//标题
$export_data =array(
0=>array(
'map'=>$map,
'firstRow'=>$firstRow,
'sheet_title'=>'微信',
'data'=>$ret['wxpay_list']
),
1=>array(
'map'=>$map,
'firstRow'=>$firstRow,
'sheet_title'=>'支付宝',
'data'=>$ret['alipay_list']
)
);
$this->more_export_xls($export_data,$title);exit;
/**
* 导出多个工作区表格数据
* @param $map excel中的列与数据库列名的映射
* @param $firstRow excel中的列名称
* @param $data 数据
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*/
public function more_export_xls($export_data,$title) {
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
$objPHPExcel = new PHPExcel();
$filename1 = urlencode('导出_'.$title) . '_' . date('Y-m-dHis');
if(!empty($export_data)){
foreach($export_data as $key=>$value){
$map = $value['map'];
$firstRow = $value['firstRow'];
$sheet_title = $value['sheet_title'];
$data = $value['data'];
//设置基本信息
//创建一个新的工作空间(sheet)
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($key);
$objPHPExcel->getActiveSheet()->setTitle($sheet_title); //设置标题
//设置列的宽度，第一行加粗居中
foreach ($map as $k=>$v){
$objPHPExcel->getActiveSheet()->getColumnDimension($k)->setWidth(22);
$objPHPExcel->getActiveSheet()->getStyle($k.'1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle($k.'1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
//设置列名
foreach ($firstRow as $k=>$v){
$objPHPExcel->setActiveSheetIndex($key)->setCellValue($k,$v);
}
$i = 2;
foreach ($data as $k=>$v) {
foreach ($map as $col=>$name) {
$objPHPExcel->setActiveSheetIndex($key)->setCellValue($col.$i, ' '.$v[$name]);
// $objPHPExcel->getActiveSheet()->setCellValueExplicit($col.$i, $v[$name], PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->getStyle($col.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
$i++;
}
}
}
//如果是IE将文件名转为GB2312
if(strpos($_SERVER['HTTP_USER_AGENT'],'MSIE') !== false
|| strpos($_SERVER['HTTP_USER_AGENT'],'rv:') !== false
|| strpos($_SERVER['HTTP_USER_AGENT'],'Firefox') !== false){
$filename1 = iconv("utf-8","GB2312",$filename1);
}
setcookie('revenue_total_flag', 0); //防止用户重复点击
ob_end_clean();//清除缓冲区,避免乱码
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename1.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
php excel 导入操作
使用phpexcle 进行导入操作，具体步骤如下：
- 文件上传
- 读取文件数据
- 对读取的数据进行业务处理
具体示列代码如下：
/**
* 共享工单导入
*/
public function work_order_import(){
//$file = "./logs/test.csv";
$path="./logs/";
$ext_arr=['.xlsx','.xls','.csv'];
if(!is_dir($path)){
mkdir($path);
}
if(isset($_FILES['file']) && $_FILES['file']){
$file = $_FILES['file'];
$extension = strrchr($file['name'],'.');
if(!in_array($extension,$ext_arr)){
$this->api_response_result(5008);
}
if($file['error']>0){
switch ($file['error']){
case 1:
$this->api_response_result(5003);break;
case 2:
$this->api_response_result(5003);break;
case 3:
$this->api_response_result(5004);break;
case 6:
$this->api_response_result(5005);break;
case 7:
$this->api_response_result(5006);break;
case 8:
$this->api_response_result(5007);break;
}
}
$move_to_file=$path."/".date('Y-m-d')."_".(microtime(true) * 10000) . mt_rand(100000,999999).$extension;
if(!move_uploaded_file($file['tmp_name'],iconv("utf-8","gb2312",$move_to_file))) {
$this->api_response_result(5007);
}
}else{
$this->api_response_result(5009);
}
$data= $this->import_excel($move_to_file);
if(empty($data)){
$this->api_response_result(5009);
}
$this->load->model('M_share_activity');
$this->load->model('M_progress_status');
$msg='';
$success=0;
$fail=0;
foreach ($data as $k=>$v){
$flag=0;
$updata_data=[];
$share_info = $this->M_share_activity->share_by_order_num($v[1]);
//淘车_下次邀约结果
$invite_results=1;
$result=$this->config->item('share_invite_results');
if(!empty($v[6])){
foreach ($result as $i=>$item){
if($v[6]==$item){
$invite_results=$i;
}
}
}
if($invite_results==5){
$updata_data['is_stop']=1;
$updata_data['reason']=$v[5];
}
$updata_data['invite_results']=$invite_results;
$updata_data['invite_count']=$share_info['invite_count']+1;
$updata_data['utime'] = date('Y-m-d H:i:s',time());
$updata_data['is_urgent'] =0;
if(empty($share_info)){
$k=$k-1;
$msg .=";第".$k."行【淘车_共享编号】不存在";
$fail++;
continue;
}
if($invite_results==2 || $invite_results==4){
//车型
if(!empty($v[2]) && is_numeric($v[2])){
$updata_data['cv_id']=$v[2];
}
//活动
if(!empty($v[3]) && is_numeric($v[3])){
$this->load->model('M_activity');
$activity=$this->M_activity->activity_detail($v[3]);
$updata_data['lp_id']=$activity['lp_id'];
$updata_data['act_id']=$v[3];
$updata_data['progress']=3;
$updata_data['audit_status']=2; //已有活动
$process_data=[
'status'=>1,
'utime'=>date('Y-m-d H:i:s',time()),
];
$flag=$this->M_progress_status->progress_pass_update($share_info['id'],1,$process_data);
}
}
//淘车_下次邀约时间
if(!empty($v[4])){
$updata_data['invite_time']=date('Y-m-d H:i:s',strtotime($v[4]));
}
//淘车_下次邀约备注
$str="第".$updata_data['invite_count']."次邀约 ".$v[6].'\r\n'.$v[5];
if(!empty($share_info['invite_remark'])){
$updata_data['invite_remark'] =$share_info['invite_remark'].'\r\n\r\n'.$str;
}else{
$updata_data['invite_remark']=$str;
}
$this->M_share_activity->activity_update($share_info['id'],$updata_data);
$success++;
if($flag){
$share = $this->M_share_activity->share_by_order_num($v[1]);
//发送短信
$this->load->library('message');
$content['car_type']=$share['car_type'];
$content['phone']=$share['mobile_phone'];
$content['activity_time']=$activity['activity_time'];
$content['act_name']=$activity['name'];
$content['point_address']=$activity['point_address'];
$content['contact_phone']=$activity['contact_phone'];
$this->message->send_user_sms($content,18);
//参与活动发送模板消息
$this->load->library('wxtemplate');
$this->wxtemplate->template_message($share_info['openid'],2,25,$content,'');
}
}
$msg= trim($msg,';');
$message="工单导入情况: 成功".$success."个,失败".$fail."个";
if(!empty($msg)){
$message.=";失败原因：".$msg;
}
unlink($move_to_file);
$this->api_response_result(0,$message);
}
- 其中读取excel表格数据方法
$this->import_excel($move_to_file),其中$move_to_file为文件路径，具体方法如下：
/**
* 导入 excel文件
* @param string $file excel文件路径
* @return array excel文件内容数组
*/
function import_excel($file){
ini_set('max_execution_time', '0');
$this->load->library('PHPExcel/Classes/PHPExcel');//引入phpexcel 核心类
// 判断使用哪种格式
$extension = strtolower(pathinfo($file, PATHINFO_EXTENSION) );
if ($extension =='xlsx') {
$objReader = new PHPExcel_Reader_Excel2007();
} else if ($extension =='xls') {
$objReader = new PHPExcel_Reader_Excel5();
}else if ($extension=='csv') {
$objReader = new PHPExcel_Reader_CSV();
//默认输入字符集
$objReader->setInputEncoding('GBK');
//默认的分隔符
$objReader->setDelimiter(',');
}
//载入文件
$objPHPExcel = $objReader->load($file);
$sheet = $objPHPExcel->getSheet(0);
// 取得总行数
$highestRow = $sheet->getHighestRow();
// 取得总列数
$highestColumn = $sheet->getHighestColumn();
//循环读取excel文件,读取一条,插入一条
//从第一行开始读取数据
$data=array();
if($highestRow>1){
for($j=2;$j<=$highestRow;$j++){
//从A列读取数据
for($k='A';$k<=$highestColumn;$k++){
// 读取单元格
$data[$j][]=trim($sheet->getCell($k.$j)->getValue());
}
}
}
return $data;
}
- 附：php生成excel列名超过26列大于Z时的解决方法
/**
* php生成excel列名超过26列大于Z时的解决方法
*/
public static function stringFromColumnIndex($pColumnIndex = 0) {
static $_indexCache = array();
if (!isset($_indexCache[$pColumnIndex])) {
// Determine column string
if ($pColumnIndex < 26) {
$_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
} elseif ($pColumnIndex < 702) {
$_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) .
chr(65 + $pColumnIndex % 26);
} else {
$_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) .
chr(65 + ((($pColumnIndex - 26) % 676) / 26)) .
chr(65 + $pColumnIndex % 26);
}
}
return $_indexCache[$pColumnIndex];
}