<?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/'.time().'.'.$suffixName;
|
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=12;
|
|
if ($highestRow>1){
|
echo $highestColumn;
|
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);
|
}
|
if (is_numeric($RepairDate)){$RepairDate = gmdate("Y-m-d H:i:s", \PHPExcel_Shared_Date::ExcelToPHP($cvalue));}
|
}
|
$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();
|
|
$MaintenancePlace = trim(str_replace('?', '', $MaintenancePlace));
|
$RepairCarLicense = trim(str_replace('?', '', $RepairCarLicense));
|
$RepairCarFrame = trim(str_replace('?', '', $RepairCarFrame));
|
$RepairCarVIN = trim(str_replace('?', '', $RepairCarVIN));
|
$RepairMileage = trim(str_replace('?', '', $RepairMileage));
|
$MaintenanceItem = trim(str_replace('?', '', $MaintenanceItem));
|
$ReplacementParts = trim(str_replace('?', '', $ReplacementParts));
|
$UnitPrice = trim(str_replace('?', '', $UnitPrice));
|
$UnitInt = trim(str_replace('?', '', $UnitInt));
|
$UnitName = trim(str_replace('?', '', $UnitName));
|
$MaterialMoney = trim(str_replace('?', '', $MaterialMoney));
|
if (empty($MaterialMoney)) {$MaterialMoney=0;}
|
//echo $RepairCarLicense." ".$RepairDate." ".$MaterialMoney." (".$MaintenanceItem." ".$ReplacementParts.")<br>";
|
//exit;
|
if (!empty($RepairCarLicense) and !empty($RepairDate) and (!empty($MaintenanceItem) or !empty($ReplacementParts))) {
|
$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."<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);
|
}
|
}
|
?>
|
