How to import excel data using phpexcel
This article focuses on "how to use phpexcel to import excel data", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to import excel data using phpexcel.
Read Excel file data and return error information
The copy code is as follows:
/ * *
* Import basic information of goods
, /
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 files to the specified location on the server
$fileName = $_ FILES ["productinfo"] ['name']
$filePath = CBase_Common_UploadPicture::uploadFile ($data ["productinfo"], 'product')
/ / if the file is uploaded successfully, import excel is performed.
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 files have been 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 ($iMagne1)-> 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] = "item SAP code does not exist in header"
}
Else if ($dataCount 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 ($iTun0witz / I $dataCount)? $highestRow: $max
$allShopLinkedId = array ()
ByIds ($allShopLinkedId) (for)
For ($jtrees off set / jgetValue ())
If ($tempV & & $tempV! =') {
$product [$colums [$k]] = $tempV
}
}
/ / get the SAP encoding in the file
$id = $product ['sap_code']
If (! $id) {
Continue
}
/ / check whether 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 level (brand) is empty
If (! isset ($product ['product_brand'])) {
$error [7] [$id] ['emptyProductBrand'] = true
}
/ / whether the consignment logo 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 logo is empty after selling first
If (! isset ($product ['product_sell_pick'])) {
$error [7] [$id] ['emptyProductSellPick'] = true
}
/ / whether the product attribute is empty
If (! isset ($product ['product_attribute'])) {
$error [7] [$id] ['emptyProductAttribute'] = true
}
/ / whether the vendor code is empty
If (! isset ($product ['vendor_code'])) {
$error [7] [$id] ['emptyVendorCode'] = true
}
/ / whether the supply site is empty
If (! isset ($product ['zzwerk_code'])) {
$error [7] [$id] ['emptyZzwerkCode'] = true
}
/ / whether the reservoir 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'] =' 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 (1Magne99).'_'. $prefix.'ErrorReport'.$suffix
$excelName = Base_Tool_Pinyin::getPinyin ($excelName)
$objPHPWriteExcel- > setActiveSheetIndex (0)
$activeSheet = $objPHPWriteExcel- > getActiveSheet ()
$activeSheet- > setTitle ('error reporting')
$activeSheet- > setCellValueByColumnAndRow (0PerfGreguetGetWord (SEFG)
$activeSheet- > setCellValueByColumnAndRow (1, 1, 1, 1, 1, 1, excel, line number)
$activeSheet- > setCellValueByColumnAndRow (2, 1, 1, 5, 1, 5, 5, 5, 5, 5, 3, 3, 2, 1, 5, 3, 3, 3, 3, 2, 3, 4, 3, 4, 3, 4, 3, 4, 4, 4, 4, 4, 2, 4, 4, 4, 4, 6, 4, 4, 4, 4, 6, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 6, 6, 6, 6, 6, 6, 6, 6, 6
$activeSheet- > setCellValueByColumnAndRow (3Phone1, SelfGrane, PRODUCTNAME)
$activeSheet- > setCellValueByColumnAndRow (4 recorder 1, SelfGroup)
$activeSheet- > setCellValueByColumnAndRow (5, 5, 1, 7, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 1, 5, 5, 5, 5, 5, 5, 5, 1, 5, 5, 5, 5, 5, 5, 5, 1, 5, 5, 5, 5, 1, 5, 5, 5, 5, 5, 1, 5, 5, 5, 5, 5, 5, 1, 5, 5, 5, 5, 1, 5, 5, 5, 5, 5, 1, 5, 5, 5, 5, 5, 5, 1, 5, 5, 5, 5, 1, 5, 5, 5, 5, 1, 5, 5, 5, 1, 5, 5, 5, 5, 5, 5, 5, 5, 1, 5, 5, 5, 5, 5, 5, 5, 5
$activeSheet- > setCellValueByColumnAndRow (6, self 1, recorder 1, SelfParade, PRODUCTS, PROXYY flag)
$activeSheet- > setCellValueByColumnAndRow. (7, 7, 1, 7, 5, 5, 5, 5, 5, 5, 5, 5, 7, 5, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
$activeSheet- > setCellValueByColumnAndRow (8, 7, 7, 7, 7, 7, 7, 7, 7, 7, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6
$activeSheet- > setCellValueByColumnAndRow (9, 7, 5, 7, 7, 9, 14, 14, 9, 14, 14, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, and 12, respectively.). $activeSheet- > setCellValueByColumnAndRow (9.
$activeSheet- > setCellValueByColumnAndRow (10, 10, 1, 7, 5, 5, 5, 5, 5, 5, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6
$activeSheet- > setCellValueByColumnAndRow (11PPLYDRESS)
$activeSheet- > setCellValueByColumnAndRow (12, 12, 1, 7, 7, 5, 7, 7, 12, 12, 14, 14, 12, 14, 12, 14, 12, 14, 12, 14, 12, 14, 12, 14, 12, 12, 14, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12, 12, 14, 12
$activeSheet- > setCellValueByColumnAndRow (13pc1pm '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 (0quotation writeExcelIndex.pId.codes-this vendor code already exists')
} else {
$activeSheet- > setCellValueByColumnAndRow (0quotation writeExcelIndexgrapId)
}
$activeSheet- > setCellValueByColumnAndRow (1 marked writeExcelIndextempInfo ['excelRow'])
If (isset ($pInfo ['duplicate'])) {
$activeSheet- > setCellValueByColumnAndRow (2) excelIdRow [$pId])
}
If (isset ($pInfo ['emptyName'])) {
$activeSheet- > setCellValueByColumnAndRow (3 recordings writeExcelIndex pages-empty')
}
If (isset ($pInfo ['emptyProductGroup'])) {
$activeSheet- > setCellValueByColumnAndRow (4 recordings writeExcelIndex pages-empty')
}
If (isset ($pInfo ['emptyProductBrand'])) {
$activeSheet- > setCellValueByColumnAndRow (5 recording writeExcelIndexJi-empty')
}
If (isset ($pInfo ['emptyProductProxyFlag'])) {
$activeSheet- > setCellValueByColumnAndRow (6 recordings writeExcelIndex pages-empty')
}
If (isset ($pInfo ['emptyProductBinning'])) {
$activeSheet- > setCellValueByColumnAndRow (7 recordings writeExcelIndex pages-empty')
}
If (isset ($pInfo ['emptyProductSellPick'])) {
$activeSheet- > setCellValueByColumnAndRow (8 recordings writeExcelIndex pages-empty')
}
If (isset ($pInfo ['emptyProductAttribute'])) {
$activeSheet- > setCellValueByColumnAndRow (9 recordings writeExcelIndex pages-empty')
}
If (isset ($pInfo ['emptyVendorCode'])) {
$activeSheet- > setCellValueByColumnAndRow (10 recordings writeExcelIndexJournal-empty')
}
If (isset ($pInfo ['emptyZzwerkCode'])) {
$activeSheet- > setCellValueByColumnAndRow (11 references writeExcelIndex pages-empty')
}
If (isset ($pInfo ['emptyZzlgortCode'])) {
$activeSheet- > setCellValueByColumnAndRow (12 recordings writeExcelIndex pages-empty')
}
If (isset ($pInfo ['other'])) {
$activeSheet- > setCellValueByColumnAndRow (13writeExcelIndextempInfp ['other'])
}
$writeExcelIndex++
}
$objWriter = PHPExcel_IOFactory::createWriter ($objPHPWriteExcel, 'Excel5')
$excelPath = FILE_PATH.DS.'feedback'.DS.$excelName
$objWriter- > save ($excelPath)
$resultInfo ['type'] = 7
$resultInfo ['msg'] = $fileName. "error in 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, use 0 to complete it from the left.
If (isset ($pInfo ['sap_code'])) {
If (strlen ($pInfo ['sap_code'])'
* $logData ['content'] = $content.$logIds
, /
}
Return $resultInfo
}
At this point, I believe you have a deeper understanding of "how to import excel data using phpexcel". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
