Example of using phpexcel to import excel data

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

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.