php获取excel表格内容,phpexcel获取excel表格内容

你看A-F就在第3行 第3到9列，在导入的时候做一个判断就行//用的是application/admin/library/traits/Backend.php 的import

public function import()

{

$file = $this->request->request('file');

if (!$file) {

$this->error(__('Parameter %s can not be empty', 'file'));

}

$filePath = ROOT_PATH . DS . 'public' . DS . $file;

if (!is_file($filePath)) {

$this->error(__('No results were found'));

}

//实例化reader

$ext = pathinfo($filePath, PATHINFO_EXTENSION);

if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {

$this->error(__('Unknown data format'));

}

if ($ext === 'csv') {

$file = fopen($filePath, 'r');

$filePath = tempnam(sys_get_temp_dir(), 'import_csv');

$fp = fopen($filePath, "w");

$n = 0;

while ($line = fgets($file)) {

$line = rtrim($line, "\n\r\0");

$encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);

if ($encoding != 'utf-8') {

$line = mb_convert_encoding($line, 'utf-8', $encoding);

}

if ($n == 0 || preg_match('/^".*"$/', $line)) {

fwrite($fp, $line . "\n");

} else {

fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");

}

$n++;

}

fclose($file) || fclose($fp);

$reader = new Csv();

} elseif ($ext === 'xls') {

$reader = new Xls();

} else {

$reader = new Xlsx();

}

//导入文件首行类型,默认是注释,如果需要使用字段名称请使用name

$importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

$table = $this->model->getQuery()->getTable();

$database = \think\Config::get('database.database');

$fieldArr = [];

$list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);

foreach ($list as $k => $v) {

if ($importHeadType == 'comment') {

$fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];

} else {

$fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];

}

}

//加载文件

$insert = [];

try {

if (!$PHPExcel = $reader->load($filePath)) {

$this->error(__('Unknown data format'));

}

$currentSheet = $PHPExcel->getSheet(0); //读取文件中的第一个工作表

$allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号

$allRow = $currentSheet->getHighestRow(); //取得一共有多少行

$maxColumnNumber = Coordinate::columnIndexFromString($allColumn);

$fields = [];

for ($currentRow = 1; $currentRow <= 1; $currentRow++) {

for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {

$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();

$fields[] = $val;

}

}

//test从第4行开始

for ($currentRow = 4; $currentRow <= $allRow; $currentRow++) {

$test = [];

$values = [];

for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {

$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();

//判断

if($currentColumn>=3&&$currentColumn<=9){

$key = $currentSheet->getCellByColumnAndRow($currentColumn, '3')->getValue();

$test[] =['key'=>$key,'values'=>$val];

}

$values[] = is_null($val) ? '' : $val;

}

//跳出第2个循环 file_put_contents("test/test.txt",var_export(json_encode($test),true),FILE_APPEND);

$row = [];

$temp = array_combine($fields, $values);

foreach ($temp as $k => $v) {

if (isset($fieldArr[$k]) && $k !== '') {

$row[$fieldArr[$k]] = $v;

}

}

if ($row) {

$insert[] = $row;

}

}

} catch (Exception $exception) {

$this->error($exception->getMessage());

}

if (!$insert) {

$this->error(__('No rows were updated'));

}

try {

//是否包含admin_id字段

$has_admin_id = false;

foreach ($fieldArr as $name => $key) {

if ($key == 'admin_id') {

$has_admin_id = true;

break;

}

}

if ($has_admin_id) {

$auth = Auth::instance();

foreach ($insert as &$val) {

if (!isset($val['admin_id']) || empty($val['admin_id'])) {

$val['admin_id'] = $auth->isLogin() ? $auth->id : 0;

}

}

}

$this->model->saveAll($insert);

} catch (PDOException $exception) {

$msg = $exception->getMessage();

if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {

$msg = "导入失败，包含【{$matches[1]}】的记录已存在";

};

$this->error($msg);

} catch (Exception $e) {

$this->error($e->getMessage());

}

$this->success();

}