版权声明:本文为原创文章,未经允许不得转载。
这是tp5.0 的excel导入;
前提是已经安装phpexcel扩展;
public function import()
{
$result = array('code'=>0,'status' => 'error', 'msg' => '导入数据失败,请稍后再试.');
try {
$files = isset($_FILES['file']) ? $_FILES['file'] : [];
if ($files) {
if ($files['error'] == 0) {
$jobName = $files['name'];
$temp = explode('.', $jobName);
$filetype = strtolower(end($temp)); //后缀
//只能处理csv,xls,xlsx
$fileTypeArr = ['xls', 'xlsx', 'csv'];
$docTypeArr = ['application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
if (in_array($files['type'], $docTypeArr) && in_array($filetype, $fileTypeArr)) {
//加载phpexcel处理类
if ($filetype == 'xls') {
$reader = new \PHPExcel_Reader_Excel5();
} elseif ($filetype == 'xlsx') {
$reader = new \PHPExcel_Reader_Excel2007();
} else {
$reader = \PHPExcel_IOFactory::createReader('CSV')->setInputEncoding('GBK');
}
//读取
$PHPExcel = $reader->load($files['tmp_name'], 'utf-8');
$currentSheet = $PHPExcel->getSheet(0); //第一个工作簿
$allColumn = $currentSheet->getHighestColumn(); //最高的列 比如AU 从A开始
$allRow = $currentSheet->getHighestRow(); //最大行 比如12980 行从0开始
//取数据
$tempDataArr = [];
//第2行开始才是我们要的数据 [ 0是系统抬头(A,B,C,D...), 1是我们数据的抬头(产品编码,产品名称....) ]
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
$tempDataArr[$currentRow][$currentColumn] = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue();
}
}
//入库
$total = count($tempDataArr);
// todo $tempDataArr的数据键还是A B C这样的
if ($total > 0) {
$new_arr = [];
foreach ($tempDataArr as $key => $val){
$new_arr[] = array(
'name_1' => $val['A'],
'name_2' => $val['B'],
'name_3' => $val['C'],
'create_time' => time()
);
}
$res = Db::table('table')->insertAll($new_arr);
if(empty($res)){
$result['code'] = 0;
$result['msg'] = "失败";
}else{
$result['code'] = 200;
$result['msg'] = "成功";
}
} else {
$result['code'] = 0;
$result['msg'] = '文件中没有数据,请重新上传.';
}
} else {
$result['code'] = 0;
$result['msg'] = '文件格式不对,仅支持后缀为csv,xls,xlsx的文件';
}
} else {
//上传失败,给出失败原因(1: 上传的文件超过了 php.ini 中 upload_max_filesize 选项限制的值, 2:上传文件的大小超过了 HTML 表单中 MAX_FILE_SIZE 选项指定的值, 3文件只有部分被上传, 4:没有文件被上传, 5:上传文件大小为0)
$result['code'] = 0;
$result['msg'] = $files['error'];
}
} else {
$result['code'] = 0;
$result['msg'] = '没有上传任何文件.';
}
} catch (\Exception $e) {
$result['code'] = 0;
$result['msg'] = '系统异常,请稍后再试.(' . $e->getMessage() . ')';
}
return json_encode(['data' => $result]);
}