将PHPExcel文件夹和PHPExcel.php文件拷贝到如下的目录位置
2. 将PHPExcel.php修改成PHPExcel.class.php
3.导入
public function push_excel()
{
$file = $_FILES[‘file’];
$data = $this->importExecl($file[‘tmp_name’]);
$company = M(“company”)->field(“company_id as id,company_name as name”)->select();
$company = $this->tarrTooarr($company);
$store = M(“store”)->field(“store_id as id,store_name as name”)->select();
$store = $this->tarrTooarr($store);
$time = time();
$insert = [];
foreach ($data as $key => $value) {
if(!false == $data[$key]){
$insert[] = [
‘company_id’ => array_search($value[‘A’],$company), //A 公司
‘store_id’ => array_search($value[‘B’],$store), //B 门店
‘position_name’ => $value[‘C’], //C 职位名称
‘add_time’ => $time,
];
}
}
// var_dump($insert);exit;
$res = M(‘position’)->addAll($insert);
if($res){
$this->success(‘添加完成！’);
}else{
$this->success(‘添加失败。’);
}
}
public function tarrTooarr($data)
{
$arr = [];
foreach($data as $k=>$v){
$arr[$v[‘id’]] = $v[‘name’];
}
return $arr;
}
/**
* 数据导入
* @param string $file excel文件
* @param string $sheet
* @return string 返回解析数据
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*/
public function importExecl($file=”, $sheet=0){
$file = iconv(“utf-8″,”gb2312”, $file); //转码
if(empty($file) OR !file_exists($file)) {
die(‘文件不存在请重新选择。’);
}
Vendor(“PHPExcel.Classes.PHPExcel”);
$objRead = new \PHPExcel_Reader_Excel2007(); //建立reader对象
if(!$objRead->canRead($file)){
$objRead = new \PHPExcel_Reader_Excel5();
if(!$objRead->canRead($file)){
die(‘此文件不是Excel指定格式。’);
}
}
$cellName = array(‘A’, ‘B’, ‘C’);
$obj = $objRead->load($file); //建立excel对象
$currSheet = $obj->getSheet($sheet); //获取指定的sheet表
$columnH = $currSheet->getHighestColumn(); //取得最大的列号
$columnCnt = array_search($columnH, $cellName);
$rowCnt = $currSheet->getHighestRow(); //获取总行数
$data = array();
for($_row=2; $_row<=$rowCnt; $_row++){ //读取内容
for($_column=0; $_column<=$columnCnt; $_column++){
$cellId = $cellName[$_column].$_row;
$cellValue = $currSheet->getCell($cellId)->getValue();
//$cellValue = $currSheet->getCell($cellId)->getCalculatedValue(); #获取公式计算的值
if($cellValue instanceof PHPExcel_RichText){ //富文本转换字符串
$cellValue = $cellValue->__toString();
}
$data[$_row][$cellName[$_column]] = $cellValue;
}
}
return $data;
}
/**
* 导出模板
* ‘company_id’ –> A 公司
* ‘store_id’ –> B 门店
* ‘position_name’ –> C 职位名称
*/
public function export(){
Vendor(“PHPExcel.Classes.IOFactory”);
Vendor(“PHPExcel.Classes.PHPExcel”);
$objExcel = new \PHPExcel_Reader_Excel2007(); //建立reader对象
$objExcel = new \PHPExcel();
$objWriter = \PHPExcel_IOFactory::createWriter($objExcel, ‘Excel2007’);
$objActSheet = $objExcel->getActiveSheet();
$objActSheet->setCellValue(‘A1′,’公司名称’);
$objActSheet->setCellValue(‘B1′,’门店名称’);
$objActSheet->setCellValue(‘C1′,’职位名称’);
$filename = ‘岗位表’.date(‘YmdHis’).’.xlsx’;
$filename = iconv(“utf-8”, “gb2312”, $filename);
header(“Content-Type: application/force-download”);
header(“Content-Type: application/octet-stream”);
header(“Content-Type: application/download”);
header(‘Content-Disposition:inline;filename=”‘.$filename.'”‘);
header(“Content-Transfer-Encoding: binary”);
header(“Cache-Control: must-revalidate, post-check=0, pre-check=0”);
header(“Pragma: no-cache”);
ob_end_clean();
$objWriter->save(‘php://output’);
exit;