当前位置：Gxlcms > PHP教程 > phpexcel导入excel数据的示例代码

phpexcel导入excel数据的示例代码

时间：2021-07-01 10:21:17 帮助过：13人阅读

  3. /**

  4. * 导入商品基本信息
  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. // 定义一个错误集合.
  12. $error = array();
  13. $resultInfo = null;
  14. $needNext = true;
  15. //上传文件到服务器指定位置
  16. $fileName = $_FILES["productinfo"]['name'];
  17. $filePath = CBase_Common_UploadPicture::uploadFile($data["productinfo"], 'product');
  18. //如果上传文件成功，就执行导入excel操作
  19. if($filePath == 1) {
  20. $error[1] = "上传的文件超过了 php.ini 中 upload_max_filesize 选项限制的值";
  21. }else if($filePath == 4){
  22. $error[4] = "没有文件被上传";
  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. //检测Excel中的基本信息是否存在
  54. $dataCount = $highestRow - 1;
  55. if(count($colums) == 0) {
  56. $error[5] = "没有表头";
  57. }
  58. else if(!in_array('sap_code',$colums)){
  59. $error[2] = "表头中商品SAP编码不存在";
  60. }
  61. else if($dataCount <= 0){
  62. $error[6] = "Excel文件中没有数据";
  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. // 根据SAP商品编码查询在库中的记录数.
  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. //获取文件中的SAP编码
  104. $id = $product['sap_code'];
  105. if(!$id){
  106. continue;
  107. }
  108. //检测商品SAP编码是否已经存在
  109. if(!in_array($id,$dbShopProducts)){
  110. $allProduct[$id] = $product;
  111. }else{
  112. $error[7][$id]['hasId'] = true;
  113. }
  114. //商品名是否为空
  115. if(!isset($product['pname'])){
  116. $error[7][$id]['emptyName'] = true;
  117. }
  118. //商品类目（商品组）是否为空
  119. if(!isset($product['product_group'])){
  120. $error[7][$id]['emptyProductGroup'] = true;
  121. }
  122. //产品层次（品牌）是否为空
  123. if(!isset($product['product_brand'])){
  124. $error[7][$id]['emptyProductBrand'] = true;
  125. }
  126. //经代销标志是否为空
  127. if(!isset($product['product_proxy_flag'])){
  128. $error[7][$id]['emptyProductProxyFlag'] = true;
  129. }
  130. //装箱清单是否为空
  131. if(!isset($product['product_binning'])){
  132. $error[7][$id]['emptyProductBinning'] = true;
  133. }
  134. //先销后采标识是否为空
  135. if(!isset($product['product_sell_pick'])){
  136. $error[7][$id]['emptyProductSellPick'] = true;
  137. }
  138. //商品属性是否为空
  139. if(!isset($product['product_attribute'])){
  140. $error[7][$id]['emptyProductAttribute'] = true;
  141. }
  142. //供应商编码是否为空
  143. if(!isset($product['vendor_code'])){
  144. $error[7][$id]['emptyVendorCode'] = true;
  145. }
  146. //供应地点是否为空
  147. if(!isset($product['zzwerk_code'])){
  148. $error[7][$id]['emptyZzwerkCode'] = true;
  149. }
  150. //库区是否为空
  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. //返回错误信息
  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'] = '表头【'.implode(',',$error[3]).'】不存在';
  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. //最终的SQL语句
  318. $result = $this->excuteMultiInsertSql($updateProductSql);
  319. }
  320. $resultInfo['type'] = 8;
  321. $resultInfo['msg'] = "导入商品基本信息成功";

  322. /*

  323. * // 日志操作.
  324. * $content = '批量新建商品导入操作成功：导入的供应商品编码有->';
  325. * $logData['content'] = $content.$logIds;
  326. */
  327. }
  328. return $resultInfo;
  329. }

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！