Reads the Excel file data and returns the wrong information
Copy CodeThe code is as follows:
/**
* Import Commodity basic information
*/
Public Function Importproductbasicinfo ($data) {
Include_once ' phpexcel.php ';
Include_once ' phpexcel/iofactory.php ';
Include_once ' phpexcel/reader/excel5.php ';
Defines a collection of errors.
$error = Array ();
$resultInfo = null;
$needNext = true;
Uploading files to the server specified location
$fileName = $_files["ProductInfo" [' Name '];
$filePath = Cbase_common_uploadpicture::uploadfile ($data ["ProductInfo"], ' product ');
If the upload file is successful, perform the import Excel operation
if ($filePath = = 1) {
$error [1] = "The uploaded file exceeds the value of the Upload_max_filesize option limit in php.ini";
}else if ($filePath = = 4) {
$error [4] = "No files are uploaded";
}else{
$objReader = Phpexcel_iofactory::createreader (' Excel5 ');
$objReader->setreaddataonly (TRUE);
$objPHPExcel = $objReader->load ($filePath);
$objWorksheet = $objPHPExcel->getactivesheet ();
$highestRow = $objWorksheet->gethighestrow ();
$highestColumn = $objWorksheet->gethighestcolumn ();
$highestColumnIndex = phpexcel_cell::columnindexfromstring ($highestColumn);
$colums = Array ();
$data = Array ();
$excelAllId = Array ();
$excelIdRow = Array ();
$execlAllShopLinkedId = Array ();
for ($i =0; $i < $highestColumnIndex; $i + +) {
$cValue = Trim ($objWorksheet->getcellbycolumnandrow ($i, 1)->getvalue ());
Switch ($cValue) {
Case self::P roduct_sap_code: $colums [$i] = "Sap_code"; Break
Case self::P roduct_name: $colums [$i] = "PName"; Break
Case self::P roduct_group: $colums [$i] = "Product_group"; Break
Case self::P roduct_brand: $colums [$i] = ' Product_brand '; Break
Case self::P roduct_proxy_flag: $colums [$i] = "Product_proxy_flag"; Break
Case self::P roduct_binning: $colums [$i] = "product_binning"; Break
Case self::P roduct_sell_pick: $colums [$i] = "Product_sell_pick"; Break
Case self::P roduct_attribute: $colums [$i] = "Product_attribute"; Break
Case self::P roduct_supplier_code: $colums [$i] = "Vendor_code"; Break
Case self::P roduct_supply_address: $colums [$i] = "Zzwerk_code"; Break
Case self::P roduct_batch: $colums [$i] = "Zzlgort_code"; Break
Default: $error [3][] = $cValue; Break
}
}
Detect the presence of basic information in Excel
$dataCount = $highestRow-1;
if (count ($colums) = = 0) {
$error [5] = "No table header";
}
else if (!in_array (' Sap_code ', $colums)) {
$error [2] = "Product SAP code does not exist in the table header";
}
else if ($dataCount <= 0) {
$error [6] = "No data in Excel file";
}
else if (count ($error) ==0) {
for ($i =2; $i <= $highestRow; $i + +) {
$colkey = Array_search (' Sap_code ');
$shopLinkedIdValue = Trim ($objWorksheet->getcellbycolumnandrow ($colkey, $i)->getvalue ());
if (! $shopLinkedIdValue) {
Continue
}
if (In_array ($shopLinkedIdValue, $execlAllShopLinkedId)) {
$error [7][$shopLinkedIdValue] [' duplicate '] = true;
$error [7][$shopLinkedIdValue] [' Excelrow '] [] = $i;
$execlAllShopLinkedId [$i] = $shopLinkedIdValue;
$error [7][$shopLinkedIdValue] [' noid '] = true;
}else {
$excelIdRow [$shopLinkedIdValue] = $i;
$execlAllShopLinkedId [$i] = $shopLinkedIdValue;
}
}
$dealMultiple = Ceil ($dataCount/1000);
$allProduct = Array ();
for ($i =0; $i < $dealMultiple; $i + +) {
$offset = $i *1000+2;
$max = ($i + 1) *1000+1;
$max = ($max > $dataCount)? $highestRow: $max;
$allShopLinkedId = Array ();
for ($j = $offset; $j <= $max; $j + +) {
if ($execlAllShopLinkedId [$j]) {
$allShopLinkedId [] = $execlAllShopLinkedId [$j];
}
}
Query the number of records in the library based on the SAP product code.
$dbShopProducts = $this->getshoplinkedbyids ($allShopLinkedId);
for ($j = $offset; $j <= $max; $j + +) {
$product = Array ();
for ($k =0; $k < $highestColumnIndex; $k + +) {
$tempV = Trim ($objWorksheet->getcellbycolumnandrow ($k, $j)->getvalue ());
if ($tempV && $tempV! = ") {
$product [$colums [$k]] = $tempV;
}
}
Get SAP encoding in a file
$id = $product [' Sap_code '];
if (! $id) {
Continue
}
Detect if the product SAP code already exists
if (!in_array ($id, $dbShopProducts)) {
$allProduct [$id] = $product;
}else{
$error [7][$id] [' hasid '] = true;
}
Whether the product name is empty
if (!isset ($product [' pname '])) {
$error [7][$id] [' emptyname '] = true;
}
Whether the commodity category (commodity group) is empty
if (!isset ($product [' Product_group '])) {
$error [7][$id] [' emptyproductgroup '] = true;
}
Whether the product hierarchy (brand) is empty
if (!isset ($product [' Product_brand '])) {
$error [7][$id] [' emptyproductbrand '] = true;
}
Whether the consignment flag is empty
if (!isset ($product [' Product_proxy_flag '])) {
$error [7][$id] [' emptyproductproxyflag '] = true;
}
Whether the packing list is empty
if (!isset ($product [' product_binning '])) {
$error [7][$id] [' emptyproductbinning '] = true;
}
Whether the mark is empty after the first sale
if (!isset ($product [' Product_sell_pick '])) {
$error [7][$id] [' emptyproductsellpick '] = true;
}
Whether the commodity attribute is empty
if (!isset ($product [' Product_attribute '])) {
$error [7][$id] [' emptyproductattribute '] = true;
}
Whether the vendor encoding is empty
if (!isset ($product [' Vendor_code '])) {
$error [7][$id] [' emptyvendorcode '] = true;
}
Whether the supply location is empty
if (!isset ($product [' Zzwerk_code '])) {
$error [7][$id] [' emptyzzwerkcode '] = true;
}
Whether the library area is empty
if (!isset ($product [' Zzlgort_code '])) {
$error [7][$id] [' emptyzzlgortcode '] = true;
}
if (Isset ($error [7][$id])) {
$error [7][$id] [' excelrow '] = $j;
}
}
}
}
}
$resultInfo [' fileName '] = $fileName;
return error message
if (count ($error) >0) {
if (Isset ($error [1])) {
$resultInfo [' type '] = 1;
$resultInfo [' msg '] = $error [1];
}else if (Isset ($error [2])) {
$resultInfo [' type '] = 2;
$resultInfo [' msg '] = $error [2];
}else if (Isset ($error [3])) {
$resultInfo [' type '] = 3;
$resultInfo [' msg '] = ' table header '. Implode (', ', $error [3]). ' "does not exist";
}else if (Isset ($error [4])) {
$resultInfo [' type '] = 4;
$resultInfo [' msg '] = $error [4];
}else if (Isset ($error [6])) {
$resultInfo [' type '] = 6;
$resultInfo [' msg '] = $error [6];
}else if (Isset ($error [7])) {
$excelName = null;
$objPHPWriteExcel = new Phpexcel ();
$objPHPWriteExcel->getproperties ()->setcreator ("Yuer")
->setlastmodifiedby ("Yuer")->settitle ("")->setsubject ("")
->setdescription ("")->setkeywords ("")->setcategory ("");
$prefix = substr ($fileName, 0,strrpos ($fileName, '. '));
$suffix = substr ($fileName, Strrpos ($fileName, '. '));
$excelName = Date ("y_m_d_h_i_s"). ' _ '. Mt_rand (1,99). ' _ '. $prefix. ' Errorreport '. $suffix;
$excelName = Base_tool_pinyin::getpinyin ($excelName);
$objPHPWriteExcel->setactivesheetindex (0);
$activeSheet = $objPHPWriteExcel->getactivesheet ();
$activeSheet->settitle (' Error report ');
$activeSheet->setcellvaluebycolumnandrow (0,1,self::P roduct_sap_code);
$activeSheet->setcellvaluebycolumnandrow (1, 1, ' original Excel line number ');
$activeSheet->setcellvaluebycolumnandrow (2,1, ' The number of lines of code exists in duplicate ');
$activeSheet->setcellvaluebycolumnandrow (3,1,self::P roduct_name);
$activeSheet->setcellvaluebycolumnandrow (4,1,self::P roduct_group);
$activeSheet->setcellvaluebycolumnandrow (5,1,self::P roduct_brand);
$activeSheet->setcellvaluebycolumnandrow (6,1,self::P roduct_proxy_flag);
$activeSheet->setcellvaluebycolumnandrow (7,1,self::P roduct_binning);
$activeSheet->setcellvaluebycolumnandrow (8,1,self::P roduct_sell_pick);
$activeSheet->setcellvaluebycolumnandrow (9,1,self::P roduct_attribute);
$activeSheet->setcellvaluebycolumnandrow (10,1,self::P roduct_supplier_code);
$activeSheet->setcellvaluebycolumnandrow (11,1,self::P roduct_supply_address);
$activeSheet->setcellvaluebycolumnandrow (12,1,self::P roduct_batch);
$activeSheet->setcellvaluebycolumnandrow (13,1, ' other reasons ');
$activeSheet->getcolumndimensionbycolumn (0)->setwidth (15);
$activeSheet->getcolumndimensionbycolumn (1)->setwidth (20);
$activeSheet->getcolumndimensionbycolumn (2)->setwidth (20);
$activeSheet->getcolumndimensionbycolumn (3)->setwidth (20);
$activeSheet->getcolumndimensionbycolumn (4)->setwidth (20);
$activeSheet->getcolumndimensionbycolumn (5)->setwidth (20);
$activeSheet->getcolumndimensionbycolumn (6)->setwidth (20);
$activeSheet->getcolumndimensionbycolumn (7)->setwidth (20);
$activeSheet->getcolumndimensionbycolumn (8)->setwidth (20);
$activeSheet->getcolumndimensionbycolumn (9)->setwidth (20);
$activeSheet->getcolumndimensionbycolumn (->setwidth) (20);
$activeSheet->getcolumndimensionbycolumn (one)->setwidth (20);
$activeSheet->getcolumndimensionbycolumn (->setwidth) (20);
$activeSheet->getcolumndimensionbycolumn (->setwidth) (20);
$writeExcelIndex = 2;
foreach ($error [7] as $pId = + $pInfo) {
if (Isset ($pInfo [' Hasid '])) {
$activeSheet->setcellvaluebycolumnandrow (0, $writeExcelIndex, $pId. '-This Supplier code already exists ');
} else {
$activeSheet->setcellvaluebycolumnandrow (0, $writeExcelIndex, $pId);
}
$activeSheet->setcellvaluebycolumnandrow (1, $writeExcelIndex, $pInfo [' Excelrow ']);
if (Isset ($pInfo [' Duplicate '])) {
$activeSheet->setcellvaluebycolumnandrow (2, $writeExcelIndex, $excelIdRow [$pId]);
}
if (Isset ($pInfo [' emptyname '])) {
$activeSheet->setcellvaluebycolumnandrow (3, $writeExcelIndex, '-empty ');
}
if (Isset ($pInfo [' Emptyproductgroup '])) {
$activeSheet->setcellvaluebycolumnandrow (4, $writeExcelIndex, '-empty ');
}
if (Isset ($pInfo [' Emptyproductbrand '])) {
$activeSheet->setcellvaluebycolumnandrow (5, $writeExcelIndex, '-empty ');
}
if (Isset ($pInfo [' Emptyproductproxyflag '])) {
$activeSheet->setcellvaluebycolumnandrow (6, $writeExcelIndex, '-empty ');
}
if (Isset ($pInfo [' emptyproductbinning '])) {
$activeSheet->setcellvaluebycolumnandrow (7, $writeExcelIndex, '-empty ');
}
if (Isset ($pInfo [' Emptyproductsellpick '])) {
$activeSheet->setcellvaluebycolumnandrow (8, $writeExcelIndex, '-empty ');
}
if (Isset ($pInfo [' Emptyproductattribute '])) {
$activeSheet->setcellvaluebycolumnandrow (9, $writeExcelIndex, '-empty ');
}
if (Isset ($pInfo [' Emptyvendorcode '])) {
$activeSheet->setcellvaluebycolumnandrow ($writeExcelIndex, '-empty ');
}
if (Isset ($pInfo [' Emptyzzwerkcode '])) {
$activeSheet->setcellvaluebycolumnandrow (one, $writeExcelIndex, '-empty ');
}
if (Isset ($pInfo [' Emptyzzlgortcode '])) {
$activeSheet->setcellvaluebycolumnandrow ($writeExcelIndex, '-empty ');
}
if (Isset ($pInfo [' other ')]) {
$activeSheet->setcellvaluebycolumnandrow ($writeExcelIndex, $PINFP [' other ']);
}
$writeExcelIndex + +;
}
$objWriter = Phpexcel_iofactory::createwriter ($objPHPWriteExcel, ' Excel5 ');
$excelPath = File_path. DS. ' Feedback '. DS. $excelName;
$objWriter->save ($excelPath);
$resultInfo [' type '] = 7;
$resultInfo [' msg '] = $fileName. " There is an error in the file ";
$resultInfo [' errorreport '] = $excelName;
Log operation, temporarily empty
}
}else{
Import data
$logIds = ";
$i = 0;
foreach ($allProduct as $pId = = $pInfo) {
$UPDATEPRODUCTSQL = ' INSERT into yr_product set ';
if (Isset ($pInfo [' pname ']) && trim ($pInfo [' pname ']) {
$UPDATEPRODUCTSQL = $updateProductSql. ' pname=\ '. Str_replace (' \ ', ' \ ' \ ', $pInfo [' pname ']). ' \',';
}
If the SAP code is less than 18 bits, complete with 0 from left
if (Isset ($pInfo [' Sap_code '])) {
if (strlen ($pInfo [' Sap_code ']) <18) {
$pInfo [' sap_code '] = Str_pad ($pInfo [' Sap_code '], "0", str_pad_left);
$UPDATEPRODUCTSQL = $updateProductSql. ' sap_code=\ '. Str_replace (' \ ', ' \ ' \ ', $pInfo [' Sap_code ']). ' \',';
}
}
if (Isset ($pInfo [' Product_group '])) {
$UPDATEPRODUCTSQL = $updateProductSql. ' product_group=\ '. $pInfo [' Product_group ']. ' \',';
}
if (Isset ($pInfo [' Product_brand '])) {
$UPDATEPRODUCTSQL = $updateProductSql. ' product_brand=\ '. $pInfo [' Product_brand ']. ' \',';
}
if (Isset ($pInfo [' Product_proxy_flag '])) {
$UPDATEPRODUCTSQL = $updateProductSql. ' product_proxy_flag=\ '. $pInfo [' Product_proxy_flag ']. ' \',';
}
if (Isset ($pInfo [' product_binning '])) {
$UPDATEPRODUCTSQL = $updateProductSql. ' product_binning=\ '. $pInfo [' product_binning ']. ' \',';
}
if (Isset ($pInfo [' Product_sell_pick '])) {
$UPDATEPRODUCTSQL = $updateProductSql. ' product_sell_pick=\ '. $pInfo [' Product_sell_pick ']. ' \',';
}
if (Isset ($pInfo [' Product_attribute '])) {
$UPDATEPRODUCTSQL = $updateProductSql. ' product_attribute=\ '. $pInfo [' Product_attribute ']. ' \',';
}
if (Isset ($pInfo [' Vendor_code '])) {
$UPDATEPRODUCTSQL = $updateProductSql. ' vendor_code=\ '. $pInfo [' Vendor_code ']. ' \',';
}
if (Isset ($pInfo [' Zzwerk_code '])) {
$UPDATEPRODUCTSQL = $updateProductSql. ' zzwerk_code=\ '. $pInfo [' Zzwerk_code ']. ' \',';
}
if (Isset ($pInfo [' Zzlgort_code '])) {
$UPDATEPRODUCTSQL = $updateProductSql. ' zzlgort_code=\ '. $pInfo [' Zzlgort_code ']. ' \'';
}
The final SQL statement
$result = $this->excutemultiinsertsql ($UPDATEPRODUCTSQL);
}
$resultInfo [' type '] = 8;
$resultInfo [' msg '] = "Import commodity basic information Success";
/*
*//log operation.
* $content = ' Batch new product import operation succeeded: Import supply Product Code has, ';
* $logData [' content '] = $content. $logIds;
*/
}
return $resultInfo;
}
http://www.bkjia.com/PHPjc/621672.html www.bkjia.com true http://www.bkjia.com/PHPjc/621672.html techarticle read the Excel file data and return the wrong information copy the code code as follows:/** * Import commodity basic information */Public Function Importproductbasicinfo ($data) {include_o ...
