简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

将EXCEL文件上传到系统，逐行读取数据并写入或者更新数据表

Original 2019-01-20 18:24:07 724
abstract：以下是我根据学习的知识做的一个批量将EXCEL文件上传到系统，逐行读取数据并写入或者更新数据表

以下是我根据学习的知识做的一个批量将EXCEL文件上传到系统，逐行读取数据并写入或者更新数据表


header("content-type:text/html;charset=utf-8"); //设置编码

//导入db和接口配置

require_once $_SERVER['DOCUMENT_ROOT'] . '/system/Db.php';

require_once $_SERVER['DOCUMENT_ROOT'] . '/system/config.php';

//实例化接口和db

$db = new Db();

$api = new sys_config();

header("Content-type: text/html; charset=utf-8");

date_default_timezone_set('PRC');

ini_set('max_execution_time', '0');

//读取sys_ag_after-sales表中的数据

// $where = array('',)(退款状态='等待买家退货' OR 退款状态='等待卖家确认收货')

// "MB001>='1211200002' AND MB001<'1211200009'"

// $where = "退款状态='等待买家退货' OR 退款状态='等待卖家确认收货'";

// ("MB001>='1211200002' AND MB001<'1211200009'")

$info = $db->table('sys_ag_after_sales')->where("HZ12='等待买家退货' OR HZ12='等待卖家确认收货'")->lists();

$goods= $db->table('api_shopex_goods')->lists();

echo '

';

       


       
// print_r($info);

       


       
//逐行生成退货单

       
//逐行更新退货单

       
if (!empty($info)) {

       
// $orders=$info['response']['lists'];

       
$log = "
";

       
$log .= "
售后数据写入售后系统
 返回";

       
$log .= "
";

       
echo $log;

       
echo '
';

       
foreach ($info as $data) {

       
unset($indata);

       
unset($indata_item);

       
unset($where);

       
$indata['CREATOR']='system';

       
$indata['CREATE_DATE']=date('Y-m-d H:i:s', time());

       
$indata['MODI_USER']='';

       
$indata['MODI_DATE']='';

       
$indata['HZ01']='4002';

       
$indata['HZ02']=str_replace("'",'=',str_replace('"','“',$data['HZ02']));

       
$indata['HZ03']=str_replace("'",'=',str_replace('"','“',$data['HZ01']));

       
$indata['HZ04']='是';

       
$indata['HZ05']='';

       
$indata['HZ06']=str_replace("'",'=',str_replace('"','“',$data['HZ00']));

       
$indata['HZ07']='';

       
$indata['HZ08']=str_replace("'",'=',str_replace('"','“',$data['HZ03']));

       
$indata['HZ09']=str_replace("'",'=',str_replace('"','“',$data['HZ05']));

       
$indata['HZ10']=str_replace("'",'=',str_replace('"','“',$data['HZ10']));

       
$indata['HZ11']='';

       
$indata['HZ12']='system';

       
$indata['HZ13']=str_replace("'",'=',str_replace('"','“',$data['HZ19']));

       
$indata['HZ14']=str_replace("'",'=',str_replace('"','“',$data['HZ18']));

       
$indata['HZ15']=str_replace("'",'=',str_replace('"','“',$data['HZ17']));

       
$indata['HZ16']='0';

       
$indata['HZ17']=str_replace("'",'=',str_replace('"','“',$data['HZ20']));

       
$indata['HZ18']=str_replace("'",'=',str_replace('"','“',$data['HZ16']));

       
$indata['HZ19']='0';

       
$indata['HZ20']=str_replace("'",'=',str_replace('"','“',$data['HZ21']));

       
$indata['HZ21']='0';

       
$indata['HZ22']='';

       
$indata['HZ23']='';

       
$indata['HZ24']='';

       
$indata['HZ25']='';

       
$indata['HZ26']='';

       
$indata['HZ27']='';

       
$indata['HZ28']='';

       
$indata['HZ29']='';

       
$indata['HZ30']='';

       
$indata['State']=0;

       


       
$indata_item['CREATOR']='system';

       
$indata_item['CREATE_DATE']=date('Y-m-d H:i:s', time());

       
$indata_item['MODI_USER']='';

       
$indata_item['MODI_DATE']='';

       
$indata_item['HZ01']='4002';

       
$indata_item['HZ02']=str_replace("'",'=',str_replace('"','“',$data['HZ02']));

       
$indata_item['HZ03']=1;

       
$indata_item['HZ04']=str_replace("'",'=',str_replace('"','“',$data['HZ04']));

       
$indata_item['HZ05']=str_replace("'",'=',str_replace('"','“',$data['HZ07']));

       
$indata_item['HZ06']=str_replace("'",'=',str_replace('"','“',$data['HZ07']));

       
$indata_item['HZ07']=1;

       
$indata_item['HZ08']='个';

       
$indata_item['HZ09']=str_replace("'",'=',str_replace('"','“',$data['HZ08']));

       
$indata_item['HZ10']='';

       
$indata_item['HZ11']='';

       
$indata_item['HZ12']=0;

       
$indata_item['HZ13']=0;

       
$indata_item['HZ14']='';

       
$indata_item['HZ15']='';

       
$indata_item['HZ16']=0;

       
$indata_item['HZ17']=0;

       
$indata_item['HZ18']=0;

       
$indata_item['HZ19']='';

       
$indata_item['HZ20']='';

       
$indata_item['HZ21']='';

       
$indata_item['HZ22']='';

       
$indata_item['HZ23']='';

       
$indata_item['HZ24']='';

       
$indata_item['HZ25']='';

       
$indata_item['HZ26']='';

       
$indata_item['HZ27']='';

       
$indata_item['HZ28']='';

       
$indata_item['HZ29']='';

       
$indata_item['HZ30']='';

       
$indata_item['State']=0;

       
$indata_item['Payment']=0;

       
// print_r($indata);

       
// print_r($indata_item);

       
// die();

       
$where['HZ01'] = 4002;

       
$where['HZ02'] = str_replace("'", '=', str_replace('"', '“', $data['HZ02']));

       
$exist = $db->table('Hz_SH_BXDJA')->where($where)->item();

       


       
//如果存在则更新

       


       
if ($exist) {

       
$item_str = $db->array_string($indata);//将数组转换为字符,传入update条件用

       
// print_r($item_str);

       
// echo '更新成功';

       
$db->table('Hz_SH_BXDJA')->where($where)->update(array('HZ14'=>$indata['HZ14'],'HZ15'=>$indata['HZ15'],'HZ18'=>$indata['HZ18']));

       
//更新单身

       
echo "售后更新单身:{$indata['HZ02']} 
";

       

       
} else {

       
$db->table('Hz_SH_BXDJA')->insert($indata);

       
$db->table('Hz_SH_BXDJB')->insert($indata_item);

       
echo "售后单号:{$indata['HZ02']} 
";

       
}

       
}

       
}

       
echo '
';

       
echo "全部生成完毕
";

       


       
?>

       


       


       
//-----------------------------------------------------------------------

       

       
//导入db和接口配置

       
require_once $_SERVER['DOCUMENT_ROOT'] . '/system/Db.php';

       
require_once $_SERVER['DOCUMENT_ROOT'] . '/system/config.php';

       
header("Content-type:text/html;charset=utf-8");

       
//实例化接口和db

       
$db = new Db();

       
$api = new sys_config();

       
$db->table('sys_ag_after_sales')->delete();

       
header("Content-type: text/html; charset=utf-8");

       
date_default_timezone_set('PRC');

       
ini_set('max_execution_time', '0');

       
function upExecel(){

       
//判断是否选择了要上传的表格

       
if (empty($_POST['myfile'])) {

       
echo "";

       
}

       
//获取表格的大小，限制上传表格的大小5M

       
$file_size = $_FILES['myfile']['size'];

       
if ($file_size>5*1024*1024) {

       
echo "";

       
exit();

       
}

       
//限制上传表格类型

       
$file_type = $_FILES['myfile']['type'];

       
//application/vnd.ms-excel 为xls文件类型

       
if ($file_type!='application/vnd.ms-excel') {

       
echo "";

       
exit();

       
}

       
//判断表格是否上传成功

       
if (is_uploaded_file($_FILES['myfile']['tmp_name'])) {

       
require_once $_SERVER['DOCUMENT_ROOT'] . '/ext/PHPExcel-1.8/Classes/PHPExcel.php';

       
require_once $_SERVER['DOCUMENT_ROOT'] . '/ext/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';

       
require_once $_SERVER['DOCUMENT_ROOT'] . '/ext/PHPExcel-1.8/Classes/PHPExcel/Reader/Excel5.php';

       
//以上三步加载phpExcel的类

       
$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format

       
//接收存在缓存中的excel表格

       
$filename = $_FILES['myfile']['tmp_name'];

       
$objPHPExcel = $objReader->load($filename); //$filename可以是上传的表格，或者是指定的表格

       
$sheet = $objPHPExcel->getSheet(0);

       
$highestRow = $sheet->getHighestRow(); // 取得总行数

       
// $highestColumn = $sheet->getHighestColumn(); // 取得总列数

       
//循环读取excel表格,读取一条,插入一条

       
//j表示从哪一行开始读取 从第二行开始读取，因为第一行是标题不保存

       
//$a表示列号

       
for($j=2;$j<=$highestRow;$j++)

       
{

       
$excel['HZ00'] = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//获取(店铺)列的值

       
$excel['HZ01'] = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//获取(订单编号)列的值

       
$excel['HZ02'] = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//获取(退款编号)列的值

       
$excel['HZ03'] = $objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ04'] = $objPHPExcel->getActiveSheet()->getCell("E".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ05'] = $objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ06'] = $objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ07'] = $objPHPExcel->getActiveSheet()->getCell("H".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ08'] = $objPHPExcel->getActiveSheet()->getCell("I".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ09'] = $objPHPExcel->getActiveSheet()->getCell("J".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ10'] = $objPHPExcel->getActiveSheet()->getCell("K".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ11'] = $objPHPExcel->getActiveSheet()->getCell("L".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ12'] = $objPHPExcel->getActiveSheet()->getCell("M".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ13'] = $objPHPExcel->getActiveSheet()->getCell("N".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ14'] = $objPHPExcel->getActiveSheet()->getCell("O".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ15'] = $objPHPExcel->getActiveSheet()->getCell("P".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ16'] = $objPHPExcel->getActiveSheet()->getCell("Q".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ17'] = $objPHPExcel->getActiveSheet()->getCell("R".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ18'] = $objPHPExcel->getActiveSheet()->getCell("S".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ19'] = $objPHPExcel->getActiveSheet()->getCell("T".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ20'] = $objPHPExcel->getActiveSheet()->getCell("U".$j)->getValue();//获取(订单付款时间)列的值

       
// $excel['HZ21'] = json_encode($objPHPExcel->getActiveSheet()->getCell("V".$j)->getValue());//获取(订单付款时间)列的值

       
$excel['HZ21'] = str_replace("'",'=',(str_replace('"','“',(str_replace(array("\r\n", "\r", "\n"),"",($objPHPExcel->getActiveSheet()->getCell("V".$j)->getValue()))))));//获取(订单付款时间)列的值

       
// str_replace(array("\r\n", "\r", "\n"), "", $str);

       
$excel['HZ22'] = $objPHPExcel->getActiveSheet()->getCell("W".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ23'] = $objPHPExcel->getActiveSheet()->getCell("X".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ24'] = $objPHPExcel->getActiveSheet()->getCell("Y".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ25'] = $objPHPExcel->getActiveSheet()->getCell("Z".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ26'] = $objPHPExcel->getActiveSheet()->getCell("AA".$j)->getValue();//获取(订单付款时间)列的值

       
$excel['HZ27'] = $objPHPExcel->getActiveSheet()->getCell("AB".$j)->getValue();//获取(订单付款时间)列的值

       
// $db

       
// null 为主键id，自增可用null表示自动添加

       
$GLOBALS['db']->table('sys_ag_after_sales')->insert($excel);

       
echo "";

       
}

       
}

       
}

       


       
//调用

       
upExecel();

       
?>

       


       
Correcting teacher：韦小宝Correction time：2019-01-21 09:09:55
Teacher's summary：可以可以 写的非常的不错 学习就是要这样学以致用

Popular Entries

php.cn：Public welfare online PHP training，Help PHP learners grow quickly！ICP License:苏B2-20210495

Copyright 2014-2024 //m.sbmmt.com/ All Rights ReservedSu ICP Filing No 2020058653号-1About usDisclaimer