";
if (!move_uploaded_file($_FILES["userfile"]["tmp_name"],$filePath)) {
echo "移动失败";
}else{
echo "移动到".$filePath."成功";echo "
";
//调用解析方法 返回数组
$ExcleArr=analysisExcel($filePath);
echo "";
//print_r($ExcleArr);
echo "
";
delFile($filePath);
header("Location: /CarRepair.gds?SystemMessageType=3&SMT=7");exit;
}
} else {
echo "上传临时文件失败";
}
}else{
echo "文件上传格式不正确，请重新上传";exit;
}
*/
function analysisExcel($filePath){
global $adminID;
global $conn;
$file_type=explode('.',$filePath)[1];
$objReader='';
//根据上传类型做不同处理
if ($file_type == 'xls') {
$objReader = \PHPExcel_IOFactory::createReader('Excel5');//创建读取实例
}
if ($file_type == 'xlsx') {
$objReader = new \PHPExcel_Reader_Excel2007();
}
$objPHPExcel = $objReader->load($filePath,$encode='utf-8');//加载文件
$sheet = $objPHPExcel->getSheet(0);//取得sheet(0)表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
++$highestColumn;
$data=[];
$columnInt=1;
if ($highestRow>1){
//echo $highestColumn;
for ($column = 'A'; $column != $highestColumn; $column++) {
$columnData = $sheet->getCell($column . 1)->getValue();
switch (trim($columnData))
{
case "序号":
$column1 = $column;
$columnInt=$columnInt-1;
break;
case "核对方式":
$column2 = $column;
break;
case "关联交易标识码":
$column3 = $column;
break;
case "本方单位":
$column4 = $column;
break;
case "对方单位":
$column5 = $column;
break;
case "科目":
$column6 = $column;
break;
case "业务类型":
$column7 = $column;
break;
case "借方金额":
$column8 = $column;
break;
case "贷方金额":
$column9 = $column;
break;
case "核对状态":
$column10 = $column;
break;
case "核对时间":
$column11 = $column;
break;
case "核对金额":
$column12 = $column;
break;
case "凭证字":
$column13 = $column;
break;
case "凭证号":
$column14 = $column;
break;
case "年度":
$column15 = $column;
break;
case "制证人":
$column16 = $column;
break;
case "期间":
$column17 = $column;
break;
case "摘要":
$column18 = $column;
break;
case "凭证币种":
$column19 = $column;
break;
case "主资产号":
$column20 = $column;
break;
case "凭证币金额":
$column21 = $column;
break;
case "采购订单流水号":
$column22 = $column;
break;
case "是否汇兑损益":
$column23 = $column;
break;
case "ECC收入项目代码":
$column24 = $column;
break;
case "ECC收入项目名称":
$column25 = $column;
break;
case "项目编号":
$column26 = $column;
break;
case "分录编号":
$column27 = $column;
break;
case "ECC科目代码":
$column28 = $column;
break;
case "ECC科目名称":
$column29 = $column;
break;
case "合同编号":
$column30 = $column;
break;
case "功能范围代码":
$column31 = $column;
break;
case "功能范围名称":
$column32 = $column;
break;
case "项目名称":
$column33 = $column;
break;
case "利润中心代码":
$column34 = $column;
break;
case "利润中心名称":
$column35 = $column;
break;
}
}
}
if ($columnInt==0){
for($i=2;$i<=$highestRow;$i++)
{
$nvarchar1 = $sheet->getCell($column1 . $i)->getValue();
$nvarchar2 = $sheet->getCell($column2 . $i)->getValue();
$nvarchar3 = $sheet->getCell($column3 . $i)->getValue();
$nvarchar4 = $sheet->getCell($column4 . $i)->getValue();
$nvarchar5 = $sheet->getCell($column5 . $i)->getValue();
$nvarchar6 = $sheet->getCell($column6 . $i)->getValue();
$nvarchar7 = $sheet->getCell($column7 . $i)->getValue();
$nvarchar8 = $sheet->getCell($column8 . $i)->getValue();
$nvarchar9 = $sheet->getCell($column9 . $i)->getValue();
$nvarchar10 = $sheet->getCell($column10 . $i)->getValue();
$nvarchar11 = $sheet->getCell($column11 . $i)->getValue();
/*
$cell = $sheet->getCell($column11 . $i)->getValue();
$cvalue = $cell->getValue();
if($cell->getDataType()==\PHPExcel_Cell_DataType::TYPE_NUMERIC){
$cellstyleformat = $cell->getStyle($cell->getCoordinate())->getNumberFormat();
$formatcode = $cellstyleformat->getFormatCode();
if (preg_match('/^(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy]/i', $formatcode)) {
$nvarchar11 = gmdate("Y-m-d H:i:s", \PHPExcel_Shared_Date::ExcelToPHP($cvalue));
}else{
$nvarchar11=\PHPExcel_Style_NumberFormat::toFormattedString($cvalue,$formatcode);
}
if (is_numeric($nvarchar11)){$nvarchar11 = gmdate("Y-m-d H:i:s", \PHPExcel_Shared_Date::ExcelToPHP($cvalue));}
}
*/
$nvarchar12 = $sheet->getCell($column12 . $i)->getValue();
$nvarchar13 = $sheet->getCell($column13 . $i)->getValue();
$nvarchar14 = $sheet->getCell($column14 . $i)->getValue();
$nvarchar15 = $sheet->getCell($column15 . $i)->getValue();
$nvarchar16 = $sheet->getCell($column16 . $i)->getValue();
$nvarchar17 = $sheet->getCell($column17 . $i)->getValue();
$nvarchar18 = $sheet->getCell($column18 . $i)->getValue();
$nvarchar19 = $sheet->getCell($column19 . $i)->getValue();
$nvarchar20 = $sheet->getCell($column20 . $i)->getValue();
$nvarchar21 = $sheet->getCell($column21 . $i)->getValue();
$nvarchar22 = $sheet->getCell($column22 . $i)->getValue();
$nvarchar23 = $sheet->getCell($column23 . $i)->getValue();
$nvarchar24 = $sheet->getCell($column24 . $i)->getValue();
$nvarchar25 = $sheet->getCell($column25 . $i)->getValue();
$nvarchar26 = $sheet->getCell($column26 . $i)->getValue();
$nvarchar27 = $sheet->getCell($column27 . $i)->getValue();
$nvarchar28 = $sheet->getCell($column28 . $i)->getValue();
$nvarchar29 = $sheet->getCell($column29 . $i)->getValue();
$nvarchar30 = $sheet->getCell($column30 . $i)->getValue();
$nvarchar31 = $sheet->getCell($column31 . $i)->getValue();
$nvarchar32 = $sheet->getCell($column32 . $i)->getValue();
$nvarchar33 = $sheet->getCell($column33 . $i)->getValue();
$nvarchar34 = $sheet->getCell($column34 . $i)->getValue();
$nvarchar35 = $sheet->getCell($column35 . $i)->getValue();
if (empty($nvarchar8)) {$nvarchar8=0;}
if (empty($nvarchar9)) {$nvarchar9=0;}
if (empty($nvarchar12)) {$nvarchar12=0;}
if (empty($nvarchar21)) {$nvarchar21=0;}
//echo $nvarchar1." ".$nvarchar2." ".$nvarchar3." (".$nvarchar8." ".$nvarchar9.") ".$nvarchar11."
";
//exit;
if (!empty($nvarchar1)) {
$sql="insert into vic_fiona (序号,核对方式,关联交易标识码,本方单位,对方单位,科目,业务类型,借方金额,贷方金额,核对状态,核对时间,核对金额,凭证字,凭证号,年度,制证人,期间,摘要,凭证币种,凭证币金额,主资产号,采购订单流水号,是否汇兑损益,ECC收入项目代码,ECC收入项目名称,项目编号,分录编号,ECC科目代码,ECC科目名称,合同编号,功能范围代码,功能范围名称,项目名称,利润中心代码,利润中心名称) values ('".$nvarchar1."','".$nvarchar2."','".$nvarchar3."','".$nvarchar4."','".$nvarchar5."','".$nvarchar6."','".$nvarchar7."',".$nvarchar8.",".$nvarchar9.",'".$nvarchar10."','".$nvarchar11."',".$nvarchar12.",'".$nvarchar13."','".$nvarchar14."','".$nvarchar15."','".$nvarchar16."','".$nvarchar17."','".$nvarchar18."','".$nvarchar19."','".$nvarchar20."',".$nvarchar21.",'".$nvarchar22."','".$nvarchar23."','".$nvarchar24."','".$nvarchar25."','".$nvarchar26."','".$nvarchar27."','".$nvarchar28."','".$nvarchar29."','".$nvarchar30."','".$nvarchar31."','".$nvarchar32."','".$nvarchar33."','".$nvarchar34."','".$nvarchar35."')";
//echo $sql."
";
$sqldata = sqlsrv_query($conn,$sql);
}
}
}else{
echo "".$columnInt."文件内容不正确，请重新上传";exit;
}
//return $data;
}
function delFile($path)
{
$url = iconv('utf-8', 'gbk', $path);
if (PATH_SEPARATOR == ':') { //linux
unlink($path);
} else { //Windows
unlink($url);
}
}
?>