搜索

首页 >后端开发 >php教程 >phpexcel导入excel数据的示例代码

phpexcel导入excel数据的示例代码

WBOY
WBOY原创
2016-07-25 08:55:23772浏览

  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 $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. //检测Excel中的基本信息是否存在
  53. $dataCount = $highestRow - 1;
  54. if(count($colums) == 0) {
  55. $error[5] = "没有表头";
  56. }
  57. else if(!in_array('sap_code',$colums)){
  58. $error[2] = "表头中商品SAP编码不存在";
  59. }
  60. else if($dataCount $error[6] = "Excel文件中没有数据";
  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. // 根据SAP商品编码查询在库中的记录数.
  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. //获取文件中的SAP编码
  97. $id = $product['sap_code'];
  98. if(!$id){
  99. continue;
  100. }
  101. //检测商品SAP编码是否已经存在
  102. if(!in_array($id,$dbShopProducts)){
  103. $allProduct[$id] = $product;
  104. }else{
  105. $error[7][$id]['hasId'] = true;
  106. }
  107. //商品名是否为空
  108. if(!isset($product['pname'])){
  109. $error[7][$id]['emptyName'] = true;
  110. }
  111. //商品类目（商品组）是否为空
  112. if(!isset($product['product_group'])){
  113. $error[7][$id]['emptyProductGroup'] = true;
  114. }
  115. //产品层次（品牌）是否为空
  116. if(!isset($product['product_brand'])){
  117. $error[7][$id]['emptyProductBrand'] = true;
  118. }
  119. //经代销标志是否为空
  120. if(!isset($product['product_proxy_flag'])){
  121. $error[7][$id]['emptyProductProxyFlag'] = true;
  122. }
  123. //装箱清单是否为空
  124. if(!isset($product['product_binning'])){
  125. $error[7][$id]['emptyProductBinning'] = true;
  126. }
  127. //先销后采标识是否为空
  128. if(!isset($product['product_sell_pick'])){
  129. $error[7][$id]['emptyProductSellPick'] = true;
  130. }
  131. //商品属性是否为空
  132. if(!isset($product['product_attribute'])){
  133. $error[7][$id]['emptyProductAttribute'] = true;
  134. }
  135. //供应商编码是否为空
  136. if(!isset($product['vendor_code'])){
  137. $error[7][$id]['emptyVendorCode'] = true;
  138. }
  139. //供应地点是否为空
  140. if(!isset($product['zzwerk_code'])){
  141. $error[7][$id]['emptyZzwerkCode'] = true;
  142. }
  143. //库区是否为空
  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. //返回错误信息
  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'] = '表头【'.implode(',',$error[3]).'】不存在';
  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. //最终的SQL语句
  310. $result = $this->excuteMultiInsertSql($updateProductSql);
  311. }
  312. $resultInfo['type'] = 8;
  313. $resultInfo['msg'] = "导入商品基本信息成功";

  314. /*

  315. * // 日志操作.
  316. * $content = '批量新建商品导入操作成功：导入的供应商品编码有->';
  317. * $logData['content'] = $content.$logIds;
  318. */
  319. }
  320. return $resultInfo;
  321. }
复制代码


声明：
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
上一篇：php实现的css更新类 下一篇：phpExcel导出excel加超级链接的实例代码

相关文章

查看更多