Shulou(Shulou.com)06/03 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.