<?php

header("Content-Type:text/html;charset=utf-8");
$dir = dirname(__FILE__);
require $dir . '/Excel/PHPExcel/IOFactory.php'; //引入PHP EXCEL类
$filename = $dir . "/storage_num.xls";
$fileType = PHPExcel_IOFactory::identify($filename); //自动获取php的类型给phpExcel用
$objReader = PHPExcel_IOFactory::createReader($fileType); //获取文件读取操作对象
$objPHPExcel = PHPExcel_IOFactory::load($filename); //加载整个Excel表格
//获取工作表的数目
$sheet = $objPHPExcel->getSheet(0); // 读取第一個工作
$allRow = $sheet->getHighestRow(); // 取得总行数
$allColumn = $sheet->getHighestColumn(); // 取得总列数
$sheetCount = $objPHPExcel->getSheetCount();
$data = array();
for ($rowIndex = 1; $rowIndex <= $allRow; $rowIndex++) {
  //循环读取每个单元格的内容。注意行从1开始，列从A开始
  for ($colIndex = 'A'; $colIndex <= $allColumn; $colIndex++) {
      $addr = $colIndex . $rowIndex;
      $cell = (string) $sheet->getCell($addr)->getValue();
      if ($cell instanceof PHPExcel_RichText) { //富文本转换字符
          $cell = strval($cell->__toString());
      }
      if (!empty($cell)) {
          $data[$rowIndex][$colIndex] = $cell;
      }
   }
}
$all_key = $data[1]; //取出第一个数组的所有值作为新数组的key
$arr = array();
$ret = array();
$allAttr = array();
foreach ($data as $key => $value) {
    if (!empty($value)) {//&& !empty($key)
        if ($key != 0 && $key != 1) {
            for ($colIndex = 'A'; $colIndex <= $allColumn; $colIndex++) {

            if (!empty($all_key) && !empty($value)) {

                if (!empty($value["$colIndex"])) {//去除空的单元格
                    $arr[$all_key["$colIndex"]] = $value["$colIndex"];
                }
            }
        }
    }
}
  if (!empty($arr)) {
      array_push($ret, $arr); //组合成新数组放到$ret
  }
}

//循环新数组
foreach ($ret as $attr => $prodVal) {

    if (!empty($prodVal)) {
        //print_r($prodVal);
        foreach ($prodVal as $attrname => $attrValue) {

            //$STORAGE_NUM='';
            if ($attrname == '货位号' && !empty($attrValue)) {//获取货位号
                $STORAGE_NUM = $attrValue;
            }
            //$proCode='';
            if ($attrname == '商品编号' && !empty($attrValue)) {//获取商品编号
                $proCode = $attrValue;
            }
            $tns = 'oci:dbname=//192.168.1.9:1521/orcl';
            $db_username = "test_product";
            $db_password = "123456";
            $sth = new PDO($tns, $db_username, $db_password);
            //修改单条的货位号
            //$sql="update T_PRODUCT set STORAGE_NUM=:STORAGE_NUM WHERE PROD_CODE='170020001118019'";
            //修改所有的货位号
            $sql = "update T_PRODUCT set STORAGE_NUM=:STORAGE_NUM WHERE PROD_CODE=:PROD_CODE";
            $result = $sth->prepare($sql);
            $result->bindParam(':STORAGE_NUM', $STORAGE_NUM);
            $result->bindParam(':PROD_CODE', $proCode);
            $bool = $result->execute();
            var_dump($bool);
        }
      }
    }
?>