- PHPExcel是功能完善的Excel导入导出插件。Github地址:https://github.com/PHPOffice/PHPExcel
1.横向导出
效果图如下:
//横向导出
public function cross()
{
require_once '/www/data/assets/PHPExcel/Classes/PHPExcel.php';
$objExcel = new PHPExcel();
$objProps = $objExcel->getActiveSheet();
$names = ['A'=>'姓名','B'=>'电话','C'=>'邮箱'];
//循环设置
foreach ($names as $key=>$value) {
// 设置水平居中
$objProps->getStyle($key)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 设置垂直居中
$objProps->getStyle($key)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置宽
$objProps->getColumnDimension($key)->setWidth(20);
//设置列名
$name = $key.'1';
$objProps->setCellValue($name, $value);
}
//也可以不循环,单独设置
// 设置宽度
// $objProps->getColumnDimension('A')->setWidth(30);
// $objProps->getColumnDimension('B')->setWidth(30);
// $objProps->getColumnDimension('C')->setWidth(30);
//设置列名
// $objProps->setCellValue("A1", '姓名');
// $objProps->setCellValue("B1", '区服');
// $objProps->setCellValue("C1", '电话');
$line = 2;
$data = [
['name'=>'mike','phone'=>'123','email'=>'123@.com'],
['name'=>'lucy','phone'=>'456','email'=>'456@.com'],
['name'=>'jack','phone'=>'789','email'=>'789@.com'],
];
foreach ($data as $key => $value) {
$objProps->setCellValue("A" . $line, $value['name']);
$objProps->setCellValue("B" . $line, $value['phone']);
$objProps->setCellValue("C" . $line, $value['email']);
$line++;
}
//设输出格式
$write = new PHPExcel_Writer_Excel5($objExcel);
// $write = new PHPExcel_Writer_Excel2007($objExcel);
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="横向(' . date('d/m/Y', time()) . '-' . date('d/m/Y', time()) . ').xls"');
header("Content-Transfer-Encoding:binary");
// 保存
$write->setPreCalculateFormulas(false);
$write->save('php://output');
}
2.垂直向
效果图
//垂直向
public function vertical11()
{
require_once '/www/data/assets/PHPExcel/Classes/PHPExcel.php';
$objExcel = new PHPExcel();
$objProps = $objExcel->getActiveSheet();
$names = ['A'=>'姓名','B'=>'电话','C'=>'邮箱'];
//循环设置
foreach ($names as $key=>$value) {
// 设置水平居中
$objProps->getStyle($key)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 设置垂直居中
$objProps->getStyle($key)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置宽
$objProps->getColumnDimension($key)->setWidth(20);
//设置列名
$name = $key.'1';
$objProps->setCellValue($name, $value);
}
$num = 1;
//模拟数据
$data = [
['name'=>'mike','phone'=>'123','email'=>'123@.com'],
['name'=>'lucy','phone'=>'456','email'=>'456@.com'],
['name'=>'jack','phone'=>'789','email'=>'789@.com'],
];
foreach ($data as $key => $value) {
$data = array_values( $value );
$length = count($data);
$data[] = '';
$name = ['姓名','电话','邮箱',''];
reset($name);
foreach ($data as $k => $v) {
$line = ($num-1)*$length + $k+1;
if ($k ==0) {
$objProps->setCellValue("A" . $line, $num);
} else {
$objProps->setCellValue("A" . $line, '');
}
if ($k ==$length-1) {
$objProps->setCellValue("B" . $line, '');
$objProps->setCellValue("C" . $line, '');
} else {
$objProps->setCellValue("B" . $line, current($name));
$objProps->setCellValue("C" . $line, $v);
}
next($name);
}
$num ++;
}
//设输出格式
$write = new PHPExcel_Writer_Excel5($objExcel);
// $write = new PHPExcel_Writer_Excel2007($objExcel);
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="横向(' . date('d/m/Y', time()) . '-' . date('d/m/Y', time()) . ').xls"');
header("Content-Transfer-Encoding:binary");
// 保存
$write->setPreCalculateFormulas(false);
$write->save('php://output');
}
3.导入
public function import()
{
//引用PHPexcel 类
require_once '/www/assets/PHPExcel/Classes/PHPExcel.php';
require_once '/www/assets/PHPExcel/Classes/PHPExcel/IOFactory.php';
//接收前台文件
$ex = $_FILES['excel'];
$game = $_POST['game'];
//重设置文件名
$suffix = substr($ex['name'],stripos($ex['name'],'.'));
$filename = time().'-'.rand(10,99).$suffix;
$path = '/www/data/excel/'.$filename;//设置移动路径
move_uploaded_file($ex['tmp_name'],$path);
if ($suffix=='.xlsx') {
$type = 'Excel2007';
} else if ($suffix=='.xls'){
$type = 'Excel5';//设置为Excel5代表支持2003或以下版本,Excel2007代表2007版
} else {
header('Content-type: application/json');
echo json_encode(['msg'=>'BAD_PARAM','echo'=>'']);
exit;
}
// $type = 'Excel5';
$xlsReader = PHPExcel_IOFactory::createReader($type);
$xlsReader->setReadDataOnly(true);
$xlsReader->setLoadSheetsOnly(true);
//测试用路径
// $path = '/www/data/excel/1492046414-94.xls';
$Sheets = $xlsReader->load($path);
//开始读取上传到服务器中的Excel文件,返回一个二维数组
$dataArray = $Sheets->getSheet(0)->toArray();
$key_filed = ['游戏'=>'game','区服'=>'server','账号'=>'user','角色名'=>'role_name','角色ID'=>'role_id','充值金额'=>'pay_amount','充值日期'=>'pay_time','平台'=>'platform','是否分成'=>'profit','联系方式'=>'contact','备注'=>'info','提交状态'=>'sub_status','回访状态'=>'visit_status','录入人'=>'oprater','录入时间'=>'create_time','最后修改'=>'update_time'];
//第一个数组为文档首行字段
$excelField = [];//排序后的字段
foreach ( $dataArray[0] as $key=>$value) {
$excelField[] = $key_filed[$value];
}
for ($i=1;$i<count($dataArray);$i++) {
$check_data = array_combine($excelField, $dataArray[$i]);
$check_result = $this->checkField($check_data);
if ($check_result) {
//PHPExcel类读取Excel文件得到数组数据类型可能存在number,用array_map函数转换每个值成string
$string_data = array_map([$this, "valueToString"], $dataArray[$i]);
$data = array_combine($excelField, $string_data);
$this->mongodb->selectDB("welfare_rebate");
$rebateCollection = $this->mongodb->getCollection('rebate_record');
$data['game'] = $game;
$data['create_time'] = time();
$data['update_time'] = time();
$data['oprater'] = $this->authorize->getId2();
$rebateCollection->insert($data);
}
}
header('Content-type: application/json');
echo json_encode(['msg'=>'OK']);
}