<?php require '/inc/odbc.php';?>
|
<?php require '/inc/function.php';?>
|
<?php
|
require_once("PHPExcel/PHPExcel.php");
|
//$name=$_FILES['userfile']['name'];//名字
|
//$suffixName=explode('.', $name)[1];//后缀名
|
//$filePath='upload/'.$_FILES["userfile"]["name"];//文件存储路径
|
$filePath='upload/vic/2021.xlsx';
|
//调用解析方法 返回数组
|
$ExcleArr=analysisExcel($filePath);
|
echo "OK";
|
/*
|
if($suffixName=="xls" || $suffixName=="xlsx"){
|
if (is_uploaded_file($_FILES["userfile"]["tmp_name"])) {
|
echo "已经上传到临时文件夹";echo "<hr>";
|
if (!move_uploaded_file($_FILES["userfile"]["tmp_name"],$filePath)) {
|
echo "移动失败";
|
}else{
|
echo "移动到".$filePath."成功";echo "<hr>";
|
//调用解析方法 返回数组
|
$ExcleArr=analysisExcel($filePath);
|
echo "<pre>";
|
//print_r($ExcleArr);
|
echo "</pre>";
|
delFile($filePath);
|
header("Location: /CarRepair.gds?SystemMessageType=3&SMT=7");exit;
|
}
|
} else {
|
echo "上传临时文件失败";
|
}
|
}else{
|
echo "<a href='/CarRepair.gds'>文件上传格式不正确，请重新上传</a>";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."<br>";
|
//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."<br>";
|
$sqldata = sqlsrv_query($conn,$sql);
|
}
|
}
|
}else{
|
echo "<a href='/CarRepair.gds'>".$columnInt."文件内容不正确，请重新上传</a>";exit;
|
}
|
|
//return $data;
|
}
|
|
function delFile($path)
|
{
|
$url = iconv('utf-8', 'gbk', $path);
|
if (PATH_SEPARATOR == ':') { //linux
|
unlink($path);
|
} else { //Windows
|
unlink($url);
|
}
|
}
|
?>
|
