Phpexcel importing Excel data using a method instance _php tutorial

Source: Internet
Author: User
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 ...

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.