PHP PHPExcel导入excel表格订单

最新推荐文章于 2025-06-19 10:29:52 发布
原创最新推荐文章于 2025-06-19 10:29:52 发布 · 1.3k 阅读
0 ·
CC 4.0 BY-SA版权
PHP 专栏收录该内容
38 篇文章
订阅专栏
本文介绍了一个使用PHPExcel库从Excel文件中读取订单数据并将其存入数据库的方法。该方法能够处理多种订单信息，并能将商品列表等详细信息一并保存。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
require '../phpexcel/Classes/PHPExcel/IOFactory.php';

//PHPExcel git：https://github.com/PHPOffice/PHPExcel

//获取文件类型后缀 
function extend($file_name){ 
    $extend = pathinfo($file_name); 
    $extend = strtolower($extend["extension"]); 
    return $extend; 
} 

$path = "../public/upfile/excel/"; 
$extArr = array("xlsx"); 

if(isset($_POST) and $_SERVER['REQUEST_METHOD'] == "POST"){ 
    $name = $_FILES['file_xls']['name']; 
    $size = $_FILES['file_xls']['size']; 

    if(empty($name)){ 
        echo '请选择要上传的文件！'; 
        exit; 
    } 
    $ext = extend($name); 
    if(!in_array($ext,$extArr)){ 
        echo '格式错误！'; 
        exit; 
    } 
    if($size>(10000*1024)){ 
        echo '大小限制'; 
        exit; 
    } 
    $xls_name = time().rand(100,999).".".$ext; 
    $tmp = $_FILES['file_xls']['tmp_name']; 

    if(move_uploaded_file($tmp, $path.$xls_name)){ 
        //echo $path.$xls_name;
        $filename =  $path.$xls_name;

        $objPHPExcel = PHPExcel_IOFactory::load($filename);//加载刚上传的excel
        $sheet = $objPHPExcel->getSheet(0); 
        $highestRow = $sheet->getHighestRow(); // 取得总行数 
        $highestColumn = $sheet->getHighestColumn(); // 取得总列数

        //循环读取excel文件
        $excel_array = array(); //创建数组保存表格数据
        $excel_i = 0; //创建的订单数组索引，
        for($i=0;$i<=$highestRow;$i++){
         
            $a = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();//获取A列的值
            $b = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();//获取B列的值
            $c = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();//获取B列的值
            $d = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();//获取B列的值
            $e = $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();//获取A列的值
            $f = $objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue();//获取B列的值
            $g = $objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue();//获取B列的值
            $h = $objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue();//获取B列的值
            //echo $a;
            //echo $b;
            //echo $c;
            //echo $d;
            //echo $e;
            //echo $f;
            //echo $g;
            //echo $h;
            //echo '<br/>';

            switch ($a) {
            	case '订单号：':
            		$excel_i++; //创建的订单数组索引，当有新订单号的行出现时索引+1。
            		$excel_array[$excel_i]['order_sn']=$b;
            		$excel_array[$excel_i]['is_pickup']=$d;
            		$excel_array[$excel_i]['order_status']=$f;
            		$excel_array[$excel_i]['consignee']=$h;
            		break;

            	case '送货地址：':
            		$excel_array[$excel_i]['address']=$b;
            		$excel_array[$excel_i]['pay_name']=$d;
            		$excel_array[$excel_i]['shipping_name']=$f;
            		$excel_array[$excel_i]['add_time']=$h;
            		break;

            	case '备注：':
            		$excel_array[$excel_i]['postscript']=$b;
            		break;

            	case '商品列表':
            		# code...
            		break;

            	case '【商品】':
            		$excel_array[$excel_i]['shangpin_list'][$b]['goods_sn']=$c;
            		$excel_array[$excel_i]['shangpin_list'][$b]['goods_name']=$d;
            		$excel_array[$excel_i]['shangpin_list'][$b]['goods_attr']=$e;
            		$excel_array[$excel_i]['shangpin_list'][$b]['goods_price']=$f;
            		$excel_array[$excel_i]['shangpin_list'][$b]['goods_number']=$g;
            		break;
            	
            	default:
            		# code...
            		break;
            }
        }

        //var_dump($excel_array);

        foreach ($excel_array as $value) {

        	$order_sn      = $value['order_sn'];       //订单号
        	$is_pickup     = $value['is_pickup']; 	   //订单类型
        	$order_status  = $value['order_status'];   //订单状态
        	$consignee     = $value['consignee'];      //收货人
        	$address       = $value['address'];        //送货地址
        	$pay_name      = $value['pay_name'];       //支付方式
        	$shipping_name = $value['shipping_name'];  //配送方式
        	$add_time      = $value['add_time'];       //下单时间
        	$postscript    = $value['postscript'];     //备注

        	$sql = "INSERT INTO " .$GLOBALS['ecs']->table('order_info'). " (order_sn, is_pickup, order_status, consignee,address,pay_name,shipping_name,add_time,postscript)". "VALUES ('$order_sn', '$is_pickup', '$order_status', '$consignee','$address','$pay_name','$shipping_name','$add_time','$postscript')";

        	$GLOBALS['db']->query($sql);
        	$order_id = $GLOBALS['db']->insert_id(); //返回插入的ID
        	//$order_id = mysql_insert_id();
        	foreach ($value['shangpin_list'] as $sp) {

        		$goods_sn     = $sp['goods_sn'];     //货号
        		$goods_name   = $sp['goods_name']; 	 //商品名称
        		$goods_attr   = $sp['goods_attr'];   //属性
        		$goods_price  = $sp['goods_price'];  //价格
        		$goods_number = $sp['goods_number']; //购买数量
                
                //根据商品货号，查询商品ID
        		$sp_id = "SELECT goods_id from " . $GLOBALS['ecs']->table('goods') ."where goods_sn='$goods_sn'";
				$result_sp_id = $GLOBALS['db']->getOne($sp_id);
				$goods_id = $result_sp_id['goods_id'];
                
        		$sp_sql = "INSERT INTO " .$GLOBALS['ecs']->table('order_goods'). " (goods_id, order_id, goods_sn, goods_name, goods_attr,goods_price,goods_number)". "VALUES ('$goods_id','$order_id', '$goods_sn', '$goods_name', '$goods_attr','$goods_price','$goods_number')";

        		$GLOBALS['db']->query($sp_sql);
        	}
        	
        }

        echo "<script>alert('上传成功!');</scritp>";

    }else{ 
        echo '上传出错了！';
        exit;  
    } 
    
}
excel表格结构图。