摘要：以下是我根据学习的知识做的一个批量将EXCEL文件上传到系统，逐行读取数据并写入或者更新数据表<?phpheader("content-type:text/html;charset=utf-8"); //设置编码//导入db和接口配置 require_once $_SERVER['DOCUMENT_
以下是我根据学习的知识做的一个批量将EXCEL文件上传到系统，逐行读取数据并写入或者更新数据表
<?php
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 '<pre>';
// print_r($info);
//逐行生成退货单
//逐行更新退货单
if (!empty($info)) {
// $orders=$info['response']['lists'];
$log = "<div class='container-fluid' style='border: 1px solid #FCFEFF;box-shadow: 1px 1px 1px rgba(0, 0, 0, 0.1);background-color: #FCFEFF;'>";
$log .= "<h3 style='text-align: center'>售后数据写入售后系统</h3> <a href='./index.php'>返回</a>";
$log .= "<div style='text-align: center;'>";
echo $log;
echo '<hr>';
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 "<span style='color: #0c7cd5'>售后更新单身:{$indata['HZ02']} </span><br>";
} else {
$db->table('Hz_SH_BXDJA')->insert($indata);
$db->table('Hz_SH_BXDJB')->insert($indata_item);
echo "<span style='color: green;text-align: left'>售后单号:{$indata['HZ02']} </span><br>";
}
}
}
echo '<hr>';
echo "<span style='color: #0c7cd5'>全部生成完毕</span><br>";
?>
//-----------------------------------------------------------------------
<?php
//导入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 "<script>alert(您未选择表格);history.go(-1);</script>";
}
//获取表格的大小，限制上传表格的大小5M
$file_size = $_FILES['myfile']['size'];
if ($file_size>5*1024*1024) {
echo "<script>alert('上传失败，上传的表格不能超过5M的大小');history.go(-1);</script>";
exit();
}
//限制上传表格类型
$file_type = $_FILES['myfile']['type'];
//application/vnd.ms-excel 为xls文件类型
if ($file_type!='application/vnd.ms-excel') {
echo "<script>alert('上传失败，只能上传excel2003的xls格式!');history.go(-1)</script>";
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 "<script>alert('添加成功！');window.location.href='./index.php';</script>";
}
}
}
//调用
upExecel();
?>
批改老师：韦小宝批改时间：2019-01-21 09:09:55
老师总结：可以可以 写的非常的不错 学习就是要这样学以致用
