简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）日本語（JA）한국어（KO）Melayu（MS）Français（FR）Deutsch（DE）
Home
Article
Q&A
Course
Topic
Download
Game
Dictionary
Recent Updates

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

Sample code for phpexcel to import excel data

WBOY
WBOYOriginal
2016-07-25 08:55:23727browse

  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<$highestColumnIndex;$i++){
  37. $cValue = trim($objWorksheet->getCellByColumnAndRow($i,1)->getValue());
  38. switch ($cValue) {
  39. case self ::PRODUCT_SAP_CODE : $colums[$i] = "sap_code"; break;
  40. case self::PRODUCT_NAME : $colums[$i] = "pname"; break;
  41. case self::PRODUCT_GROUP : $colums[$i] = "product_group"; break;
  42. case self::PRODUCT_BRAND : $colums[$i] = 'product_brand'; break;
  43. case self::PRODUCT_PROXY_FLAG : $colums[$i] = "product_proxy_flag"; break;
  44. case self ::PRODUCT_BINNING : $colums[$i] = "product_binning"; break;
  45. case self::PRODUCT_SELL_PICK : $colums[$i] = "product_sell_pick"; break;
  46. case self::PRODUCT_ATTRIBUTE : $colums[$i] = "product_attribute"; break;
  47. case self::PRODUCT_SUPPLIER_CODE : $colums[$i] = "vendor_code"; break;
  48. case self::PRODUCT_SUPPLY_ADDRESS : $colums[$i] = "zzwerk_code"; break;
  49. case self ::PRODUCT_BATCH : $colums[$i] = "zzlgort_code"; break;
  50. default : $error[3][] = $cValue; break;
  51. }
  52. }
  53. //Detect whether basic information in Excel exists
  54. $ dataCount = $highestRow - 1;
  55. if(count($colums) == 0) {
  56. $error[5] = "No header";
  57. }
  58. else if(!in_array('sap_code',$colums)) {
  59. $error[2] = "The product SAP code in the header does not exist";
  60. }
  61. else if($dataCount <= 0){
  62. $error[6] = "There is no data in the Excel file";
  63. }
  64. else if(count($error)==0){
  65. for ($i=2;$i<=$highestRow;$i++){
  66. $colkey = array_search('sap_code');
  67. $shopLinkedIdValue = trim( $objWorksheet->getCellByColumnAndRow($colkey,$i)->getValue());
  68. if(!$shopLinkedIdValue) {
  69. continue;
  70. }
  71. if(in_array($shopLinkedIdValue,$execlAllShopLinkedId)){
  72. $error [7][$shopLinkedIdValue]['duplicate'] = true;
  73. $error[7][$shopLinkedIdValue]['excelRow'][] = $i;
  74. $execlAllShopLinkedId[$i] = $shopLinkedIdValue;
  75. $error [7][$shopLinkedIdValue]['noId'] = true;
  76. }else {
  77. $excelIdRow[$shopLinkedIdValue] = $i;
  78. $execlAllShopLinkedId[$i] = $shopLinkedIdValue;
  79. }
  80. }
  81. $dealMultiple = ceil($dataCount / 1000);
  82. $allProduct = array();
  83. for($i=0;$i<$dealMultiple;$i++){
  84. $offset = $i*1000+2;
  85. $max = ($i+1)*1000+1;
  86. $max = ($max > $dataCount) ? $highestRow : $max;
  87. $allShopLinkedId = array();
  88. for($j=$offset; $j<=$max;$j++){
  89. if($execlAllShopLinkedId[$j]){
  90. $allShopLinkedId[] = $execlAllShopLinkedId[$j];
  91. }
  92. }
  93. // Query in the library based on SAP product code The number of records.
  94. $dbShopProducts = $this->getShopLinkedByIds($allShopLinkedId);

  95. for($j=$offset;$j<=$max;$j++){

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

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

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

  322. /*

  323. * // Log operation.
  324. * $content = 'Batch new product import operation successful: imported supply product codes are ->';
  325. * $logData['content'] = $content. $logIds;
  326. */
  327. }
  328. return $resultInfo;
  329. }

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