php 实现excel导入导出

excel 导出函数
function exportExcel($params)
{

$arr = $params; // 获取需要导出的信息（我这边是：二维数组）
$fileName = "零二七用户表";
$xlsTitle = iconv('utf-8', 'gbk', "零二七用户表"); //注意编码转换
require "./PHPExcel/PHPExcel.php";

$objPHPExcel = new PHPExcel();
$objSheet = $objPHPExcel->getActiveSheet(); // 获取当前活动sheet
$objSheet->setTitle('零二七用户表'); // 给当前的活动sheet起个名称

$objSheet->setCellValue('A1', '用户名');
$objSheet->setCellValue('B1', '号码归属地');

// $objSheet->setCellValue('C1', '会员昵称');
$objSheet->setCellValue('C1', '来源终端');

$objSheet->setCellValue('D1', '用户类型');
$objSheet->setCellValue('E1', '注册时间');

$i = 2;
foreach ($arr as $key => $val) {
switch ($val['sid']) {
case 0:
$val['sid'] = '电脑';
break;
case 1:
$val['sid'] = '手机';
break;

}
switch ($val['usertype']) {
case 1:
$val['usertype'] = '学生';
break;
case 2:
$val['usertype'] = '家长';
break;
case 3:
$val['usertype'] = '老师';
break;
case 4:
$val['usertype'] = '机构';
break;
default:
$val['usertype'] = '打酱油';
break;
}

$objSheet->setCellValue('A' . $i, $val['username'])
->setCellValue('B' . $i, $val['dingwei'])
// ->setCellValue('C' . $i, $val['nickname'])
->setCellValue('C' . $i, $val['sid'])
->setCellValue('D' . $i, $val['usertype'])
->setCellValue('E' . $i, date('Y-m-d H:i:s',$val[registertime]));
$i++;
}

header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xlsx"');
header("Content-Disposition:attachment;filename=$fileName.xlsx");
header('Cache-Control: max-age=0');
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
//$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}

excel 导入

//获取上传文件的后缀
function getExt2($filename)
{
$ext = strrchr($filename,'.');
return $ext;
}
//添加上传Excel
function addExcel()
{
$ex = $_FILES['filename'];

if(empty($ex)){
exit('未上传表格');
}
//重设置文件名
$filename = time() . substr($ex['name'], stripos($ex['name'], '.'));
$str = getExt2($filename);
if($str=='.xls'){
echo "上传失败！请上传.xlsx格式的文件";
exit();
}
$path = 'excel/' . $filename;//设置移动路径
move_uploaded_file($ex['tmp_name'], $path);
//表用函数方法 返回数组
$exfn = _readExcel($path); // 读取内容
return $exfn;
}
//读取Excel
function _readExcel($path)
{
//这个是上传文件到需要保存的位置，
//引用PHPexcel 类
include_once('./PHPExcel.php');
include_once('./PHPExcel/IOFactory.php');//静态类
$type = 'excel2007';//设置为Excel5代表支持2003或以下版本，Excel2007代表2007版
$xlsReader = PHPExcel_IOFactory::createReader($type);
$xlsReader->setReadDataOnly(true);
$xlsReader->setLoadSheetsOnly(true);
$Sheets = $xlsReader->load($path);
//开始读取上传到服务器中的Excel文件，返回一个二维数组
$dataArray = $Sheets->getSheet(0)->toArray();
return $dataArray;
}