Example of using phpexcel to import excel data

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;
}