jqphpexcel,phpExcel导入

php Excel 导入

//数据导入

public function storeSql()

{

$file &＃61; input(&＃39;file.excel&＃39;);

$path &＃61; ROOT_PATH . &＃39;public&＃39; . DS . &＃39;uploads&＃39;;

if ($file) {

$info &＃61; $file->move($path);

if ($info) {

$this->dataStore($info->getPathname());

} else {

$this->error($file->getError());

}

}

}

//数据导入

public function dataStore($filePath)

{

import(&＃39;phpoffice.phpexcel.Classes.PHPExcel&＃39;);

import(&＃39;phpoffice.phpexcel.Classes.IOFactory&＃39;);

import(&＃39;phpoffice.phpexcel.Classes.Reader.Excel2007&＃39;);

$PHPExcel &＃61; new \PHPExcel();

$PHPReader &＃61; new \PHPExcel_Reader_Excel2007();

if (!$PHPReader->canRead($filePath)) {

$PHPReader &＃61; new \PHPExcel_Reader_Excel5();

if (!$PHPReader->canRead($filePath)) {

$this->error(&＃39;上传失败!&＃39;);

}

}

//读取Excel文件

$PHPExcel &＃61; $PHPReader->load($filePath);

//读取excel文件中的第一个工作表

$sheet &＃61; $PHPExcel->getSheet(0);

//取得最大的列号

$allColumn &＃61; $sheet->getHighestColumn();

//取得最大的行号

$allRow &＃61; $sheet->getHighestRow();

$user &＃61; new UserOff;

$phones &＃61; $user->where(&＃39;merchant_id&＃39;, $this->userID)->column(&＃39;phone&＃39;);

$all &＃61; [];

//从第二行开始插入,第一行是列名

for ($currentRow &＃61; 2; $currentRow <&＃61; $allRow; $currentRow&＃43;&＃43;) {

$data[&＃39;phone&＃39;] &＃61; $PHPExcel->getActiveSheet()->getCell("A" . $currentRow)->getValue();

$data[&＃39;point&＃39;] &＃61; $PHPExcel->getActiveSheet()->getCell("B" . $currentRow)->getValue();

$data[&＃39;growth&＃39;] &＃61; $PHPExcel->getActiveSheet()->getCell("C" . $currentRow)->getValue();

$data[&＃39;card_num&＃39;] &＃61; $PHPExcel->getActiveSheet()->getCell("D" . $currentRow)->getValue();

$data[&＃39;user_name&＃39;] &＃61; $PHPExcel->getActiveSheet()->getCell("E" . $currentRow)->getValue();

$data[&＃39;merchant_id&＃39;] &＃61; $this->userID;

$data[&＃39;add_time&＃39;] &＃61; time();

$data[&＃39;phone_no&＃39;] &＃61; $data[&＃39;phone&＃39;] . $this->userID . "AcDE"; //编号

empty($data[&＃39;card_num&＃39;]) && $data[&＃39;card_num&＃39;] &＃61; 0;

empty($data[&＃39;user_name&＃39;]) && $data[&＃39;user_name&＃39;] &＃61; "";

empty($data[&＃39;phone&＃39;]) && $data[&＃39;user_name&＃39;] &＃61; "";

empty($data[&＃39;point&＃39;]) && $data[&＃39;point&＃39;] &＃61; 0;

empty($data[&＃39;growth&＃39;]) && $data[&＃39;growth&＃39;] &＃61; 0;

array_push($all,$data);

}

$allData &＃61;$this->diffArr($all,$phones);

$update &＃61; $user->saveAll($allData[&＃39;allDataUp&＃39;], true);

//$update &＃61; true;

$insert &＃61; $user->saveAll($allData[&＃39;allDataIn&＃39;], false);

if ($update || $insert) {

$this->success(&＃39;数据导入成功!&＃39;, url(&＃39;dump/index&＃39;));

} else {

$this->error(&＃39;数据导入失败!&＃39;);

}

}