php读取excel数据保存到mysql_php读取excel并存入数据库

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);

}

}

}

?>