search

Home >Backend Development >PHP Tutorial >Sample code for phpexcel to import excel data

Sample code for phpexcel to import excel data

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal
2016-07-25 08:55:23865browse

  3. /**

  4. * Import basic product information
  5. * edit: bbs.it-home.org
  6. */
  7. public function importProductBasicInfo($data){
  8. include_once 'PHPExcel.php';
  9. include_once 'PHPExcel/IOFactory.php';
  10. include_once 'PHPExcel/Reader/Excel5.php';
  11. //Define an error collection.
  12. $error = array();
  13. $resultInfo = null;
  14. $needNext = true;
  15. //Upload files to the specified location on the server
  16. $ fileName = $_FILES["productinfo"]['name'];
  17. $filePath = CBase_Common_UploadPicture::uploadFile($data["productinfo"], 'product');
  18. //If the file is uploaded successfully, execute the import excel operation
  19. if($filePath == 1) {
  20. $error[1] = "The uploaded file exceeds the value limited by the upload_max_filesize option in php.ini";
  21. }else if($filePath == 4){
  22. $error[ 4] = "No file was uploaded";
  23. }else{
  24. $objReader = PHPExcel_IOFactory::createReader('Excel5');
  25. $objReader->setReadDataOnly(true);
  26. $objPHPExcel = $objReader->load( $filePath);
  27. $objWorksheet = $objPHPExcel->getActiveSheet();
  28. $highestRow = $objWorksheet->getHighestRow();
  29. $highestColumn = $objWorksheet->getHighestColumn();
  30. $highestColumnIndex = PHPExcel_Cell:: columnIndexFromString($highestColumn);
  31. $colums = array();
  32. $data = array();
  33. $excelAllId = array();
  34. $excelIdRow = array();
  35. $execlAllShopLinkedId = array();
  36. for($ i=0;$i $cValue = trim($objWorksheet->getCellByColumnAndRow($i,1)->getValue());
  37. switch ($cValue) {
  38. case self ::PRODUCT_SAP_CODE : $colums[$i] = "sap_code"; break;
  39. case self::PRODUCT_NAME : $colums[$i] = "pname"; break;
  40. case self::PRODUCT_GROUP : $colums[$i] = "product_group"; break;
  41. case self::PRODUCT_BRAND : $colums[$i] = 'product_brand'; break;
  42. case self::PRODUCT_PROXY_FLAG : $colums[$i] = "product_proxy_flag"; break;
  43. case self ::PRODUCT_BINNING : $colums[$i] = "product_binning"; break;
  44. case self::PRODUCT_SELL_PICK : $colums[$i] = "product_sell_pick"; break;
  45. case self::PRODUCT_ATTRIBUTE : $colums[$i] = "product_attribute"; break;
  46. case self::PRODUCT_SUPPLIER_CODE : $colums[$i] = "vendor_code"; break;
  47. case self::PRODUCT_SUPPLY_ADDRESS : $colums[$i] = "zzwerk_code"; break;
  48. case self ::PRODUCT_BATCH : $colums[$i] = "zzlgort_code"; break;
  49. default : $error[3][] = $cValue; break;
  50. }
  51. }
  52. //Detect whether basic information in Excel exists
  53. $ dataCount = $highestRow - 1;
  54. if(count($colums) == 0) {
  55. $error[5] = "No header";
  56. }
  57. else if(!in_array('sap_code',$colums)) {
  58. $error[2] = "The product SAP code in the header does not exist";
  59. }
  60. else if($dataCount $error[6] = "There is no data in the Excel file";
  61. }
  62. else if(count($error)==0){
  63. for ($i=2;$i $colkey = array_search('sap_code');
  64. $shopLinkedIdValue = trim( $objWorksheet->getCellByColumnAndRow($colkey,$i)->getValue());
  65. if(!$shopLinkedIdValue) {
  66. continue;
  67. }
  68. if(in_array($shopLinkedIdValue,$execlAllShopLinkedId)){
  69. $error [7][$shopLinkedIdValue]['duplicate'] = true;
  70. $error[7][$shopLinkedIdValue]['excelRow'][] = $i;
  71. $execlAllShopLinkedId[$i] = $shopLinkedIdValue;
  72. $error [7][$shopLinkedIdValue]['noId'] = true;
  73. }else {
  74. $excelIdRow[$shopLinkedIdValue] = $i;
  75. $execlAllShopLinkedId[$i] = $shopLinkedIdValue;
  76. }
  77. }
  78. $dealMultiple = ceil($dataCount / 1000);
  79. $allProduct = array();
  80. for($i=0;$i $offset = $i*1000+2;
  81. $max = ($i+1)*1000+1;
  82. $max = ($max > $dataCount) ? $highestRow : $max;
  83. $allShopLinkedId = array();
  84. for($j=$offset; $j if($execlAllShopLinkedId[$j]){
  85. $allShopLinkedId[] = $execlAllShopLinkedId[$j];
  86. }
  87. }
  88. // Query in the library based on SAP product code The number of records.
  89. $dbShopProducts = $this->getShopLinkedByIds($allShopLinkedId);

  90. for($j=$offset;$j $ product = array();

  91. for($k=0;$k $tempV = trim($objWorksheet->getCellByColumnAndRow($k,$j)->getValue()) ;
  92. if($tempV && $tempV != '') {
  93. $product[$colums[$k]] = $tempV;
  94. }
  95. }
  96. //Get the SAP encoding in the file
  97. $id = $product[ 'sap_code'];
  98. if(!$id){
  99. continue;
  100. }
  101. //Check whether the product SAP code already exists
  102. if(!in_array($id,$dbShopProducts)){
  103. $allProduct[$id] = $product;
  104. }else{
  105. $error[7][$id]['hasId'] = true;
  106. }
  107. //Whether the product name is empty
  108. if(!isset($product['pname'])) {
  109. $error[7][$id]['emptyName'] = true;
  110. }
  111. //Whether the product category (product group) is empty
  112. if(!isset($product['product_group'])){
  113. $error[7][$id]['emptyProductGroup'] = true;
  114. }
  115. //Whether the product level (brand) is empty
  116. if(!isset($product['product_brand'])){
  117. $error [7][$id]['emptyProductBrand'] = true;
  118. }
  119. //Whether the proxy sales flag is empty
  120. if(!isset($product['product_proxy_flag'])){
  121. $error[7][$ id]['emptyProductProxyFlag'] = true;
  122. }
  123. //Whether the packing list is empty
  124. if(!isset($product['product_binning'])){
  125. $error[7][$id]['emptyProductBinning '] = true;
  126. }
  127. //Whether the sell first, buy later indicator is empty
  128. if(!isset($product['product_sell_pick'])){
  129. $error[7][$id]['emptyProductSellPick'] = true;
  130. }
  131. //Whether the product attribute is empty
  132. if(!isset($product['product_attribute'])){
  133. $error[7][$id]['emptyProductAttribute'] = true;
  134. }
  135. / /Whether the supplier code is empty
  136. if(!isset($product['vendor_code'])){
  137. $error[7][$id]['emptyVendorCode'] = true;
  138. }
  139. //Whether the supply location is Empty
  140. if(!isset($product['zzwerk_code'])){
  141. $error[7][$id]['emptyZzwerkCode'] = true;
  142. }
  143. //Whether the library area is empty
  144. if(!isset ($product['zzlgort_code'])){
  145. $error[7][$id]['emptyZzlgortCode'] = true;
  146. }
  147. if(isset($error[7][$id])){
  148. $ error[7][$id]['excelRow'] = $j;
  149. }
  150. }
  151. }
  152. }
  153. }
  154. $resultInfo['fileName'] = $fileName;
  155. //Return error message
  156. if(count( $error)>0){
  157. if(isset($error[1])){
  158. $resultInfo['type'] = 1;
  159. $resultInfo['msg'] = $error[1];
  160. }else if(isset($error[2])){
  161. $resultInfo['type'] = 2;
  162. $resultInfo['msg'] = $error[2];
  163. }else if(isset($error[3] )){
  164. $resultInfo['type'] = 3;
  165. $resultInfo['msg'] = 'Header ['.implode(',',$error[3]).'] does not exist';
  166. }else if(isset($error[4])){
  167. $resultInfo['type'] = 4;
  168. $resultInfo['msg'] = $error[4];
  169. }else if(isset($error[6])){
  170. $resultInfo['type'] = 6;
  171. $resultInfo['msg'] = $error[6];
  172. }else if(isset($error[7])){
  173. $excelName = null;
  174. $objPHPWriteExcel = new PHPExcel();
  175. $objPHPWriteExcel->getProperties()->setCreator("yuer")
  176. ->setLastModifiedBy("yuer")->setTitle("")->setSubject("")
  177. ->setDescription("")->setKeywords("")->setCategory("");
  178. $prefix = substr($fileName,0,strrpos($fileName,'.'));
  179. $suffix = substr($fileName,strrpos($fileName,'.'));
  180. $excelName = date("Y_m_d_H_i_s").'_'.mt_rand(1,99).'_'.$prefix.'ErrorReport'.$suffix;
  181. $excelName = Base_Tool_Pinyin::getPinyin($excelName);
  182. $objPHPWriteExcel->setActiveSheetIndex(0);
  183. $activeSheet = $objPHPWriteExcel->getActiveSheet();
  184. $activeSheet->setTitle('错误报告');
  185. $activeSheet->setCellValueByColumnAndRow(0,1,self::PRODUCT_SAP_CODE);
  186. $activeSheet->setCellValueByColumnAndRow(1,1,'原excel行号');
  187. $activeSheet->setCellValueByColumnAndRow(2,1,'第几行编码存在重复');
  188. $activeSheet->setCellValueByColumnAndRow(3,1,self::PRODUCT_NAME);
  189. $activeSheet->setCellValueByColumnAndRow(4,1,self::PRODUCT_GROUP);
  190. $activeSheet->setCellValueByColumnAndRow(5,1,self::PRODUCT_BRAND);
  191. $activeSheet->setCellValueByColumnAndRow(6,1,self::PRODUCT_PROXY_FLAG);
  192. $activeSheet->setCellValueByColumnAndRow(7,1,self::PRODUCT_BINNING);
  193. $activeSheet->setCellValueByColumnAndRow(8,1,self::PRODUCT_SELL_PICK);
  194. $activeSheet->setCellValueByColumnAndRow(9,1,self::PRODUCT_ATTRIBUTE);
  195. $activeSheet->setCellValueByColumnAndRow(10,1,self::PRODUCT_SUPPLIER_CODE);
  196. $activeSheet->setCellValueByColumnAndRow(11,1,self::PRODUCT_SUPPLY_ADDRESS);
  197. $activeSheet->setCellValueByColumnAndRow(12,1,self::PRODUCT_BATCH);
  198. $activeSheet->setCellValueByColumnAndRow(13,1,'其他原因');
  199. $activeSheet->getColumnDimensionByColumn(0)->setWidth(15);
  200. $activeSheet->getColumnDimensionByColumn(1)->setWidth(20);
  201. $activeSheet->getColumnDimensionByColumn(2)->setWidth(20);
  202. $activeSheet->getColumnDimensionByColumn(3)->setWidth(20);
  203. $activeSheet->getColumnDimensionByColumn(4)->setWidth(20);
  204. $activeSheet->getColumnDimensionByColumn(5)->setWidth(20);
  205. $activeSheet->getColumnDimensionByColumn(6)->setWidth(20);
  206. $activeSheet->getColumnDimensionByColumn(7)->setWidth(20);
  207. $activeSheet->getColumnDimensionByColumn(8)->setWidth(20);
  208. $activeSheet->getColumnDimensionByColumn(9)->setWidth(20);
  209. $activeSheet->getColumnDimensionByColumn(10)->setWidth(20);
  210. $activeSheet->getColumnDimensionByColumn(11)->setWidth(20);
  211. $activeSheet->getColumnDimensionByColumn(12)->setWidth(20);
  212. $activeSheet->getColumnDimensionByColumn(13)->setWidth(20);
  213. $writeExcelIndex = 2;
  214. foreach ($error[7] as $pId=>$pInfo){
  215. if(isset($pInfo['hasId'])){
  216. $activeSheet->setCellValueByColumnAndRow(0,$writeExcelIndex,$pId.'-此供应商编码已经存在');
  217. } else {
  218. $activeSheet->setCellValueByColumnAndRow(0,$writeExcelIndex,$pId);
  219. }
  220. $activeSheet->setCellValueByColumnAndRow(1,$writeExcelIndex,$pInfo['excelRow']);
  221. if(isset($pInfo['duplicate'])){
  222. $activeSheet->setCellValueByColumnAndRow(2,$writeExcelIndex,$excelIdRow[$pId]);
  223. }
  224. if(isset($pInfo['emptyName'])){
  225. $activeSheet->setCellValueByColumnAndRow(3,$writeExcelIndex,'-为空');
  226. }
  227. if(isset($pInfo['emptyProductGroup'])){
  228. $activeSheet->setCellValueByColumnAndRow(4,$writeExcelIndex,'-为空');
  229. }
  230. if(isset($pInfo['emptyProductBrand'])){
  231. $activeSheet->setCellValueByColumnAndRow(5,$writeExcelIndex,'-为空');
  232. }
  233. if(isset($pInfo['emptyProductProxyFlag'])){
  234. $activeSheet->setCellValueByColumnAndRow(6,$writeExcelIndex,'-为空');
  235. }
  236. if(isset($pInfo['emptyProductBinning'])){
  237. $activeSheet->setCellValueByColumnAndRow(7,$writeExcelIndex,'-为空');
  238. }
  239. if(isset($pInfo['emptyProductSellPick'])){
  240. $activeSheet->setCellValueByColumnAndRow(8,$writeExcelIndex,'-为空');
  241. }
  242. if(isset($pInfo['emptyProductAttribute'])){
  243. $activeSheet->setCellValueByColumnAndRow(9,$writeExcelIndex,'-为空');
  244. }
  245. if(isset($pInfo['emptyVendorCode'])){
  246. $activeSheet->setCellValueByColumnAndRow(10,$writeExcelIndex,'-为空');
  247. }
  248. if(isset($pInfo['emptyZzwerkCode'])){
  249. $activeSheet->setCellValueByColumnAndRow(11,$writeExcelIndex,'-为空');
  250. }
  251. if(isset($pInfo['emptyZzlgortCode'])){
  252. $activeSheet->setCellValueByColumnAndRow(12,$writeExcelIndex,'-为空');
  253. }

  254. if(isset($pInfo['other'])){

  255. $activeSheet->setCellValueByColumnAndRow(13,$writeExcelIndex,$pInfp['other']);
  256. }
  257. $writeExcelIndex++;
  258. }
  259. $objWriter = PHPExcel_IOFactory::createWriter($objPHPWriteExcel, 'Excel5');
  260. $excelPath = FILE_PATH.DS.'feedback'.DS.$excelName;
  261. $objWriter->save($excelPath);
  262. $resultInfo['type'] = 7;
  263. $resultInfo['msg'] = $fileName."文件中存在错误";
  264. $resultInfo['errorReport'] = $excelName;
  265. // 日志操作，暂时空着
  266. }
  267. }else{
  268. //导入数据
  269. $logIds = '';
  270. $i = 0;
  271. foreach ($allProduct as $pId => $pInfo){
  272. $updateProductSql = 'insert into yr_product set ';
  273. if(isset($pInfo['pname']) && trim($pInfo['pname'])){
  274. $updateProductSql = $updateProductSql.'pname=''.str_replace(''','''',$pInfo['pname']).'',';
  275. }
  276. //如果SAP编码不足18位，则用0从左开始补全
  277. if(isset($pInfo['sap_code'])){
  278. if(strlen($pInfo['sap_code']) $pInfo['sap_code'] = str_pad($pInfo['sap_code'], 18, "0", STR_PAD_LEFT);
  279. $updateProductSql = $updateProductSql.'sap_code=''.str_replace(''','''',$pInfo['sap_code']).'',';
  280. }
  281. }
  282. if(isset($pInfo['product_group'])){
  283. $updateProductSql = $updateProductSql.'product_group=''.$pInfo['product_group'].'',';
  284. }
  285. if(isset($pInfo['product_brand'])){
  286. $updateProductSql = $updateProductSql.'product_brand=''.$pInfo['product_brand'].'',';
  287. }
  288. if(isset($pInfo['product_proxy_flag'])){
  289. $updateProductSql = $updateProductSql.'product_proxy_flag=''.$pInfo['product_proxy_flag'].'',';
  290. }
  291. if(isset($pInfo['product_binning'])){
  292. $updateProductSql = $updateProductSql.'product_binning=''.$pInfo['product_binning'].'',';
  293. }
  294. if(isset($pInfo['product_sell_pick'])){
  295. $updateProductSql = $updateProductSql.'product_sell_pick=''.$pInfo['product_sell_pick'].'',';
  296. }
  297. if(isset($pInfo['product_attribute'])){
  298. $updateProductSql = $updateProductSql.'product_attribute=''.$pInfo['product_attribute'].'',';
  299. }
  300. if(isset($pInfo['vendor_code'])){
  301. $updateProductSql = $updateProductSql.'vendor_code=''.$pInfo['vendor_code'].'',';
  302. }
  303. if(isset($pInfo['zzwerk_code'])){
  304. $updateProductSql = $updateProductSql.'zzwerk_code=''.$pInfo['zzwerk_code'].'',';
  305. }
  306. if(isset($pInfo['zzlgort_code'])){
  307. $updateProductSql = $updateProductSql.'zzlgort_code=''.$pInfo['zzlgort_code'].''';
  308. }
  309. //Final SQL statement
  310. $result = $this->excuteMultiInsertSql($updateProductSql);
  311. }
  312. $resultInfo['type'] = 8;
  313. $resultInfo['msg'] = "Importing basic product information successfully";

  314. /*

  315. * // Log operation.
  316. * $content = 'Batch new product import operation successful: imported supply product codes are ->';
  317. * $logData['content'] = $content. $logIds;
  318. */
  319. }
  320. return $resultInfo;
  321. }
Copy code


Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：css update class implemented by phpNext article：css update class implemented by php

Related articles

See more