";
if (!move_uploaded_file($_FILES["userfile"]["tmp_name"],$filePath)) {
echo "移动失败";
}else{
echo "移动到".$filePath."成功";echo "
";
//调用解析方法 返回数组
$ExcleArr=analysisExcel($filePath);
echo "";
print_r($ExcleArr);
echo "
";
//delFile($filePath);
}
} 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(); // 取得总列数
$data=[];
$columnInt=12;
if ($highestRow>1){
for ($column = 'A'; $column != $highestColumn; $column++) {
$columnData = $sheet->getCell($column . 1)->getValue();
switch ($columnData)
{
case "维修厂家":
$MaintenancePlace_column = $column;
$columnInt=$columnInt-1;
break;
case "车牌号":
$RepairCarLicense_column = $column;
$columnInt=$columnInt-1;
break;
case "车架号":
$RepairCarFrame_column = $column;
$columnInt=$columnInt-1;
break;
case "发动机号":
$RepairCarVIN_column = $column;
$columnInt=$columnInt-1;
break;
case "进场时间":
$RepairDate_column = $column;
$columnInt=$columnInt-1;
break;
case "进场公里数":
$RepairMileage_column = $column;
$columnInt=$columnInt-1;
break;
case "工时项目":
$MaintenanceItem_column = $column;
$columnInt=$columnInt-1;
break;
case "机修配件":
$ReplacementParts_column = $column;
$columnInt=$columnInt-1;
break;
case "单价":
$UnitPrice_column = $column;
$columnInt=$columnInt-1;
break;
case "数量":
$UnitInt_column = $column;
$columnInt=$columnInt-1;
break;
case "单位":
$UnitName_column = $column;
$columnInt=$columnInt-1;
break;
case "金额":
$MaterialMoney_column = $column;
$columnInt=$columnInt-1;
break;
}
}
}
if ($columnInt==0){
for($i=2;$i<=$highestRow;$i++)
{
$RepairCarID = 0;
$MaintenancePlace = $sheet->getCell($MaintenancePlace_column . $i)->getValue();
$RepairCarLicense = $sheet->getCell($RepairCarLicense_column . $i)->getValue();
$RepairCarFrame = $sheet->getCell($RepairCarFrame_column . $i)->getValue();
$RepairCarVIN = $sheet->getCell($RepairCarVIN_column . $i)->getValue();
$cell = $sheet->getCell($RepairDate_column . $i);
$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)) {
$RepairDate = gmdate("Y-m-d H:i:s", \PHPExcel_Shared_Date::ExcelToPHP($cvalue));
}else{
$RepairDate=\PHPExcel_Style_NumberFormat::toFormattedString($cvalue,$formatcode);
}
}
$RepairMileage = $sheet->getCell($RepairMileage_column . $i)->getValue();
$MaintenanceItem = $sheet->getCell($MaintenanceItem_column . $i)->getValue();
$ReplacementParts = $sheet->getCell($ReplacementParts_column . $i)->getValue();
$UnitPrice = $sheet->getCell($UnitPrice_column . $i)->getValue();
$UnitInt = $sheet->getCell($UnitInt_column . $i)->getValue();
$UnitName = $sheet->getCell($UnitName_column . $i)->getValue();
$MaterialMoney = $sheet->getCell($MaterialMoney_column . $i)->getValue();
if (!empty($RepairCarLicense) and !empty($RepairDate)) {
$sql="select top 1 CarID from CarData where CarLicense='".$RepairCarLicense."' or CarLicense='".$RepairCarLicense."（奔驰）'";
//echo $sql;exit;
$sqldata = sqlsrv_query($conn,$sql);
if($sqldata == true){
if (sqlsrv_rows_affected($sqldata)!=0) {
while($rs = sqlsrv_fetch_array( $sqldata, SQLSRV_FETCH_ASSOC) ) {
$RepairCarID=$rs['CarID'];
}
}
}
$sql="insert into CarRepair (RepairCarID,MaintenancePlace,RepairCarLicense,RepairCarFrame,RepairCarVIN,RepairDate,RepairMileage,MaintenanceItem,ReplacementParts,UnitPrice,UnitInt,UnitName,MaterialMoney,OAID) values (".$RepairCarID.",'".$MaintenancePlace."','".$RepairCarLicense."','".$RepairCarFrame."','".$RepairCarVIN."','".$RepairDate."','".$RepairMileage."','".$MaintenanceItem."','".$ReplacementParts."',".$UnitPrice.",".$UnitInt.",'".$UnitName."',".$MaterialMoney.",".$adminID.")";
//echo $sql;exit;
$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);
}
}
?>