How to Use phpexcel to import excel Data _ php instance

How to Use phpexcel to import excel data. For more information, see use it to read Excel file data and return incorrect information.

The Code is as follows:

/**
* Import basic product information
*/
Public function importProductBasicInfo ($ data ){
Include_once 'phpexcel. php ';
Include_once 'phpexcel/IOFactory. php ';
Include_once 'phpexcel/Reader/excel5.php ';
// Define an error set.
$ Error = array ();
$ ResultInfo = null;
$ NeedNext = true;
// Upload the file to the specified location on the server
$ FileName = $ _ FILES ["productinfo"] ['name'];
$ FilePath = CBase_Common_UploadPicture: uploadFile ($ data ["productinfo"], 'product ');
// If the file is successfully uploaded, execute the excel import operation.
If ($ filePath = 1 ){
$ Error [1] = "the uploaded file exceeds the limit of the upload_max_filesize option in php. ini ";
} Else if ($ filePath = 4 ){
$ Error [4] = "no file is 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: PRODUCT_SAP_CODE: $ colums [$ I] = "sap_code"; break;
Case self: PRODUCT_NAME: $ colums [$ I] = "pname"; break;
Case self: PRODUCT_GROUP: $ colums [$ I] = "product_group"; break;
Case self: PRODUCT_BRAND: $ colums [$ I] = 'product _ brand'; break;
Case self: PRODUCT_PROXY_FLAG: $ colums [$ I] = "product_proxy_flag"; break;
Case self: PRODUCT_BINNING: $ colums [$ I] = "product_binning"; break;
Case self: PRODUCT_SELL_PICK: $ colums [$ I] = "product_sell_pick"; break;
Case self: PRODUCT_ATTRIBUTE: $ colums [$ I] = "product_attribute"; break;
Case self: PRODUCT_SUPPLIER_CODE: $ colums [$ I] = "vendor_code"; break;
Case self: PRODUCT_SUPPLY_ADDRESS: $ colums [$ I] = "zzwerk_code"; break;
Case self: PRODUCT_BATCH: $ colums [$ I] = "zzlgort_code"; break;
Default: $ error [3] [] = $ cValue; break;
}
}
// Check whether the basic information in Excel exists
$ DataCount = $ highestRow-1;
If (count ($ colums) = 0 ){
$ Error [5] = "No Header ";
}
Else if (! In_array ('sap _ Code', $ colums )){
$ Error [2] = "the SAP code of the product in the header does not exist ";
}
Else if ($ dataCount <= 0 ){
$ Error [6] = "no data in the 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) x 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 database 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;
}
}
// Obtain the SAP code in the file
$ Id = $ product ['sap _ Code'];
If (! $ Id ){
Continue;
}
// Check whether the SAP code of the product already exists
If (! In_array ($ id, $ dbShopProducts )){
$ AllProduct [$ id] = $ product;
} Else {
$ Error [7] [$ id] ['hasid'] = true;
}
// Whether the product name is blank
If (! Isset ($ product ['pname']) {
$ Error [7] [$ id] ['emptyname'] = true;
}
// Whether the product category (product group) is empty
If (! Isset ($ product ['product _ group']) {
$ Error [7] [$ id] ['emptyproductgroup'] = true;
}
// Whether the product level (brand) is empty
If (! Isset ($ product ['product _ brand']) {
$ Error [7] [$ id] ['emptyproductbrand'] = true;
}
// Whether the consignment mark is blank
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;
}
// Check whether the identification is empty after the first pin
If (! Isset ($ product ['product _ sell_pick ']) {
$ Error [7] [$ id] ['emptyproductsellpick'] = true;
}
// Whether the item property is null
If (! Isset ($ product ['product _ attribute']) {
$ Error [7] [$ id] ['emptyproductattribute'] = true;
}
// Whether the supplier code is null
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 reservoir 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 '] = 'header ['. implode (',', $ error [3]). '] nonexistent ';
} 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: PRODUCT_SAP_CODE );
$ ActiveSheet-> setCellValueByColumnAndRow (, 'original excel line number ');
$ ActiveSheet-> setCellValueByColumnAndRow (2, 1, 'Repeat the first line of encoding ');
$ ActiveSheet-> setCellValueByColumnAndRow (3, 1, self: PRODUCT_NAME );
$ ActiveSheet-> setCellValueByColumnAndRow (4,1, self: PRODUCT_GROUP );
$ ActiveSheet-> setCellValueByColumnAndRow (5, 1, self: PRODUCT_BRAND );
$ ActiveSheet-> setCellValueByColumnAndRow (6, 1, self: PRODUCT_PROXY_FLAG );
$ ActiveSheet-> setCellValueByColumnAndRow (7,1, self: PRODUCT_BINNING );
$ ActiveSheet-> setCellValueByColumnAndRow (8, 1, self: PRODUCT_SELL_PICK );
$ ActiveSheet-> setCellValueByColumnAndRow (9,1, self: PRODUCT_ATTRIBUTE );
$ ActiveSheet-> setCellValueByColumnAndRow (10, 1, self: PRODUCT_SUPPLIER_CODE );
$ ActiveSheet-> setCellValueByColumnAndRow (11,1, self: PRODUCT_SUPPLY_ADDRESS );
$ ActiveSheet-> setCellValueByColumnAndRow (12, 1, self: PRODUCT_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 (10)-> setWidth (20 );
$ ActiveSheet-> getColumnDimensionByColumn (11)-> setWidth (20 );
$ ActiveSheet-> getColumnDimensionByColumn (12)-> setWidth (20 );
$ ActiveSheet-> getColumnDimensionByColumn (13)-> 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, '-blank ');
}
If (isset ($ pInfo ['emptyproductgroup']) {
$ ActiveSheet-> setCellValueByColumnAndRow (4, $ writeExcelIndex, '-blank ');
}
If (isset ($ pInfo ['emptyproductbrand']) {
$ ActiveSheet-> setCellValueByColumnAndRow (5, $ writeExcelIndex, '-blank ');
}
If (isset ($ pInfo ['emptyproductproxyflag']) {
$ ActiveSheet-> setCellValueByColumnAndRow (6, $ writeExcelIndex, '-blank ');
}
If (isset ($ pInfo ['emptyproductbinning ']) {
$ ActiveSheet-> setCellValueByColumnAndRow (7, $ writeExcelIndex, '-blank ');
}
If (isset ($ pInfo ['emptyproductsellpick']) {
$ ActiveSheet-> setCellValueByColumnAndRow (8, $ writeExcelIndex, '-blank ');
}
If (isset ($ pInfo ['emptyproductattribute']) {
$ ActiveSheet-> setCellValueByColumnAndRow (9, $ writeExcelIndex, '-blank ');
}
If (isset ($ pInfo ['emptyendorcode']) {
$ ActiveSheet-> setCellValueByColumnAndRow (10, $ writeExcelIndex, '-blank ');
}
If (isset ($ pInfo ['emptyzzwerkcode']) {
$ ActiveSheet-> setCellValueByColumnAndRow (11, $ writeExcelIndex, '-blank ');
}
If (isset ($ pInfo ['emptyzzlgortcode']) {
$ ActiveSheet-> setCellValueByColumnAndRow (12, $ writeExcelIndex, '-blank ');
}

If (isset ($ pInfo ['other']) {
$ ActiveSheet-> setCellValueByColumnAndRow (13, $ 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. "An error exists in the file ";
$ ResultInfo ['errorreport'] = $ excelName;
// Log operation.
}
} 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, use 0 to complete from left.
If (isset ($ pInfo ['sap _ Code']) {
If (strlen ($ pInfo ['sap _ Code']) <18 ){
$ PInfo ['sap _ Code'] = str_pad ($ pInfo ['sap _ Code'], 18, "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 '] = "the basic information of the imported product is successful ";

/*
* // Log operation.
* $ Content = 'batch create Commodity Import succeeded: The imported supplied Commodity Code includes-> ';
* $ LogData ['content'] = $ content. $ logIds;
*/
}
Return $ resultInfo;
}