>백엔드 개발 >PHP 튜토리얼 >Excel 데이터를 가져오는 phpexcel의 샘플 코드

Excel 데이터를 가져오는 phpexcel의 샘플 코드

WBOY
WBOY원래의
2016-07-25 08:55:23772검색
  1. /**

  2. * 기본 제품 정보 가져오기
  3. * 편집: bbs.it-home.org
  4. */
  5. 공용 함수 importProductBasicInfo($data){
  6. include_once 'PHPExcel.php';
  7. include_once 'PHPExcel/IOFactory.php';
  8. include_once 'PHPExcel/Reader/Excel5.php';
  9. // 오류 컬렉션을 정의합니다.
  10. $error = array ();
  11. $resultInfo = null;
  12. $needNext = true;
  13. //서버의 지정된 위치에 파일 업로드
  14. $fileName = $_FILES["productinfo"]['name' ];
  15. $filePath = CBase_Common_UploadPicture::uploadFile($data["productinfo"], 'product');
  16. //파일이 성공적으로 업로드되면 Excel 가져오기 작업을 실행합니다.
  17. if($filePath == 1) {
  18. $error[1] = "업로드된 파일이 php.ini의 upload_max_filesize 옵션 제한을 초과했습니다.";
  19. }else if($filePath == 4){
  20. $error [4] = " 업로드된 파일이 없습니다.";
  21. }else{
  22. $objReader = PHPExcel_IOFactory::createReader('Excel5');
  23. $objReader->setReadDataOnly(true);
  24. $ objPHPExcel = $objReader- >load($filePath);
  25. $objWorksheet = $objPHPExcel->getActiveSheet();
  26. $highestRow = $objWorksheet->getHighestRow();
  27. $highestColumn = $ objWorksheet->getHighestColumn ();
  28. $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
  29. $colums = array();
  30. $data = array();
  31. $excelAllId = array ();
  32. $excelIdRow = array();
  33. $execlAllShopLinkedId = array();
  34. for($i=0;$i<$highestColumnIndex;$i ){
  35. $cValue = 트림 ($objWorksheet-> ;getCellByColumnAndRow($i,1)->getValue());
  36. 스위치($cValue) {
  37. 케이스 self::PRODUCT_SAP_CODE : $colums[$i] = "sap_code"; break;
  38. case self::PRODUCT_NAME : $colums[$i] = "pname"; break;
  39. case self::PRODUCT_GROUP : $colums[$i] = "product_group" break;
  40. 케이스 self::PRODUCT_BRAND : $colums[$i] = 'product_brand'; break;
  41. 케이스 self::PRODUCT_PROXY_FLAG : $colums[$i] = "product_proxy_flag";
  42. 케이스 self::PRODUCT_BINNING : $ colums[$i] = "product_binning"; break;
  43. case self::PRODUCT_SELL_PICK : $colums[$i] = "product_sell_pick";
  44. case self::PRODUCT_ATTRIBUTE : $colums[$i] = "product_attribute"; break ;
  45. case self::PRODUCT_SUPPLIER_CODE : $colums[$i] = "vendor_code"; break;
  46. case self::PRODUCT_SUPPLY_ADDRESS : $colums[$i] = "zzwerk_code";
  47. 케이스 자체 ::PRODUCT_BATCH : $colums[$i] = "zzlgort_code"; break;
  48. 기본값: $error[3][] = $cValue; break;
  49. }
  50. }
  51. //Excel에 기본 정보가 있는지 감지
  52. $dataCount = $highestRow - 1;
  53. if(count($colums) == 0) {
  54. $error[5] = "No header" ;
  55. }
  56. else if(!in_array('sap_code',$colums)){
  57. $error[2] = "헤더에 제품 SAP 코드가 존재하지 않습니다.";
  58. }
  59. else if($ dataCount <= 0){
  60. $error[6] = "Excel 파일에 데이터가 없습니다.";
  61. }
  62. else if(count($error)== 0){
  63. for ($ i=2;$i<=$highestRow;$i ){
  64. $colkey = array_search('sap_code');
  65. $shopLinkedIdValue = Trim($objWorksheet-> getCellByColumnAndRow($colkey,$i)- >getValue());
  66. if(!$shopLinkedIdValue) {
  67. 계속;
  68. }
  69. if(in_array($shopLinkedIdValue,$execlAllShopLinkedId)){
  70. $error[7][ $shopLinkedIdValue]['duplicate'] = true;
  71. $error[7][$shopLinkedIdValue]['excelRow'][] = $i;
  72. $execlAllShopLinkedId[$ i] = $shopLinkedIdValue;
  73. $error[7][$shopLinkedIdValue]['noId'] = true;
  74. }else {
  75. $excelIdRow[$shopLinkedIdValue] = $i;
  76. $execlAllShopLinkedId [$i] = $shopLinkedIdValue;
  77. }
  78. }
  79. $dealMultiple = ceil($dataCount / 1000);
  80. $allProduct = array();
  81. for($i=0;$i<$dealMultiple;$i ){
  82. $offset = $i*1000 2;
  83. $max = ($i 1)*1000 1;
  84. $max = ($max > $dataCount) ? $highestRow : $max;
  85. $allShopLinkedId = array( );
  86. for($j=$offset;$j<=$max;$j ){
  87. if($execlAllShopLinkedId[$j]){
  88. $allShopLinkedId[] = $execlAllShopLinkedId[$j ];
  89. }
  90. }
  91. // SAP 제품 코드에 따라 데이터베이스의 레코드 개수를 쿼리합니다.
  92. $dbShopProducts = $this->getShopLinkedByIds($allShopLinkedId);
  93. for($j=$offset;$j<=$max;$j ){

  94. $product = array();
  95. for($k=0;$k< ;$highestColumnIndex ;$k ){
  96. $tempV = Trim($objWorksheet->getCellByColumnAndRow($k,$j)->getValue());
  97. if($tempV && $tempV != ' ') {
  98. $product[$colums[$k]] = $tempV;
  99. }
  100. }
  101. //파일에서 SAP 코드 가져오기
  102. $id = $product[' sap_code'];
  103. if(!$id){
  104. continue;
  105. }
  106. //제품 SAP 코드가 이미 존재하는지 확인
  107. if(!in_array($id,$dbShopProducts) ){
  108. $allProduct[$id] = $product;
  109. }else{
  110. $error[7][$id]['hasId'] = true;
  111. }
  112. // 제품 이름이 비어 있는지 여부
  113. if(!isset($product['pname'])){
  114. $error[7][$id]['emptyName'] = true;
  115. }
  116. //제품 카테고리 (제품 그룹)이 비어 있는지 여부
  117. if(!isset($product['product_group'])){
  118. $error[7][$id]['emptyProductGroup'] = true;
  119. }
  120. //상품레벨(브랜드)이 비어 있는지 여부
  121. if(!isset($product['product_brand'])){
  122. $error[7][$id][' emptyProductBrand'] = true;
  123. }
  124. //대행사 판매 플래그가 비어 있는지 여부
  125. if(!isset($product['product_proxy_flag'])){
  126. $error[7][$ id]['emptyProductProxyFlag'] = true;
  127. }
  128. // 패킹리스트가 비어 있는지 여부
  129. if(!isset($product['product_binning'])){
  130. $error[7 ][$id]['emptyProductBinning'] = true;
  131. }
  132. //먼저 판매, 나중에 구매 플래그가 비어 있습니다
  133. if(!isset($product['product_sell_pick'])){
  134. $error[7][ $id]['emptyProductSellPick'] = true;
  135. }
  136. //제품 속성이 비어 있는지 여부
  137. if(!isset($product['product_attribute'] )){
  138. $error[7 ][$id]['emptyProductAttribute'] = true;
  139. }
  140. //공급업체 코드가 비어 있는지 여부
  141. if(!isset($product[' Vendor_code'])){
  142. $ error[7][$id]['emptyVendorCode'] = true;
  143. }
  144. //공급 위치가 비어 있는지 여부
  145. if(!isset($ product['zzwerk_code'])){
  146. $error[7][$id]['emptyZzwerkCode'] = true;
  147. }
  148. //라이브러리 영역이 비어 있는지
  149. if(!isset ($product['zzlgort_code'])){
  150. $error[7][$id]['emptyZzlgortCode'] = true;
  151. }
  152. if(isset($error[7][$id ])){
  153. $error[7 ][$id]['excelRow'] = $j;
  154. }
  155. }
  156. }
  157. }
  158. }
  159. $resultInfo ['fileName'] = $fileName;
  160. //오류 정보 반환
  161. if(count($error)>0){
  162. if(isset($error[1])){
  163. $resultInfo['type'] = 1;
  164. $resultInfo['msg'] = $error[1];
  165. }else if(isset($error[2])){
  166. $resultInfo[ '유형'] = 2;
  167. $ resultInfo['msg'] = $error[2];
  168. }else if(isset($error[3])){
  169. $resultInfo['유형' ] = 3;
  170. $resultInfo[' msg'] = '헤더 ['.implode(',',$error[3]).']가 존재하지 않습니다.';
  171. }else if(isset($error[4])){
  172. $resultInfo['type'] = 4;
  173. $resultInfo['msg'] = $error[4];
  174. }else if( isset($error[6])){
  175. $resultInfo['type'] = 6;
  176. $resultInfo['msg'] = $error[6];
  177. }else if(isset($ error[7])){
  178. $excelName = null;
  179. $objPHPWriteExcel = new PHPExcel();
  180. $objPHPWriteExcel->getProperties()->setCreator("yuer")
  181. - >setLastModifiedBy("yuer")->setTitle("")->setSubject("")
  182. ->setDescription("")->setKeywords("")->setCategory("" );
  183. $prefix = substr($fileName,0,strrpos($fileName,'.'));
  184. $suffix = substr($fileName,strrpos($fileName,'.'));
  185. $excelName = 날짜("Y_m_d_H_i_s").'_'.mt_rand(1,99).'_'.$prefix.'ErrorReport'.$suffix;
  186. $excelName = Base_Tool_Pinyin::getPinyin($excelName) ;
  187. $objPHPWriteExcel->setActiveSheetIndex(0);
  188. $activeSheet = $objPHPWriteExcel->getActiveSheet();
  189. $activeSheet->setTitle('错误报告');
  190. $activeSheet ->setCellValueByColumnAndRow(0,1,self::PRODUCT_SAP_CODE);
  191. $activeSheet->setCellValueByColumnAndRow(1,1,'haraexcel行号');
  192. $activeSheet->setCellValueByColumnAndRow(2,1 ,'第几行编码存재중재');
  193. $activeSheet->setCellValueByColumnAndRow(3,1,self::PRODUCT_NAME);
  194. $activeSheet->setCellValueByColumnAndRow(4,1,self::PRODUCT_GROUP) ;
  195. $activeSheet->setCellValueByColumnAndRow(5,1,self::PRODUCT_BRAND);
  196. $activeSheet->setCellValueByColumnAndRow(6,1,self::PRODUCT_PROXY_FLAG);
  197. $activeSheet->setCellValueByColumnAndRow (7,1,self::PRODUCT_BINNING);
  198. $activeSheet->setCellValueByColumnAndRow(8,1,self::PRODUCT_SELL_PICK);
  199. $activeSheet->setCellValueByColumnAndRow(9,1,self::PRODUCT_ATTRIBUTE) ;
  200. $activeSheet->setCellValueByColumnAndRow(10,1,self::PRODUCT_SUPPLIER_CODE);
  201. $activeSheet->setCellValueByColumnAndRow(11,1,self::PRODUCT_SUPPLY_ADDRESS);
  202. $activeSheet->setCellValueByColumnAndRow (12,1,self::PRODUCT_BATCH);
  203. $activeSheet->setCellValueByColumnAndRow(13,1,'其他原因');
  204. $activeSheet->getColumnDimensionByColumn(0)->setWidth(15) ;
  205. $activeSheet->getColumnDimensionByColumn(1)->setWidth(20);
  206. $activeSheet->getColumnDimensionByColumn(2)->setWidth(20);
  207. $activeSheet->getColumnDimensionByColumn (3)->setWidth(20);
  208. $activeSheet->getColumnDimensionByColumn(4)->setWidth(20);
  209. $activeSheet->getColumnDimensionByColumn(5)->setWidth(20) ;
  210. $activeSheet->getColumnDimensionByColumn(6)->setWidth(20);
  211. $activeSheet->getColumnDimensionByColumn(7)->setWidth(20);
  212. $activeSheet->getColumnDimensionByColumn (8)->setWidth(20);
  213. $activeSheet->getColumnDimensionByColumn(9)->setWidth(20);
  214. $activeSheet->getColumnDimensionByColumn(10)->setWidth(20) ;
  215. $activeSheet->getColumnDimensionByColumn(11)->setWidth(20);
  216. $activeSheet->getColumnDimensionByColumn(12)->setWidth(20);
  217. $activeSheet->getColumnDimensionByColumn (13)->setWidth(20);
  218. $writeExcelIndex = 2;
  219. foreach ($error[7] as $pId=>$pInfo){
  220. if(isset($pInfo[' hasId'])){
  221. $activeSheet->setCellValueByColumnAndRow(0,$writeExcelIndex,$pId.'-此供应商编码已经存재');
  222. } else {
  223. $activeSheet->setCellValueByColumnAndRow (0,$writeExcelIndex,$pId);
  224. }
  225. $activeSheet->setCellValueByColumnAndRow(1,$writeExcelIndex,$pInfo['excelRow']);
  226. if(isset($pInfo['duplicate) '])){
  227. $activeSheet->setCellValueByColumnAndRow(2,$writeExcelIndex,$excelIdRow[$pId]);
  228. }
  229. if(isset($pInfo['emptyName'])){
  230. $activeSheet->setCellValueByColumnAndRow(3,$writeExcelIndex,'-为空');
  231. }
  232. if(isset($pInfo['emptyProductGroup'])){
  233. $activeSheet->setCellValueByColumnAndRow(4,$writeExcelIndex,'-为空');
  234. }
  235. if(isset( $pInfo['emptyProductBrand'])){
  236. $activeSheet->setCellValueByColumnAndRow(5,$writeExcelIndex,'-为空');
  237. }
  238. if(isset($pInfo['emptyProductProxyFlag'] )){
  239. $activeSheet->setCellValueByColumnAndRow(6,$writeExcelIndex,'-为空');
  240. }
  241. if(isset($pInfo['emptyProductBinning'])){
  242. $ activeSheet->setCellValueByColumnAndRow(7,$writeExcelIndex,'-为空');
  243. }
  244. if(isset($pInfo['emptyProductSellPick'])){
  245. $activeSheet->setCellValueByColumnAndRow(8 ,$writeExcelIndex,'-为空');
  246. }
  247. if(isset($pInfo['emptyProductAttribute'])){
  248. $activeSheet->setCellValueByColumnAndRow(9,$writeExcelIndex,'-为공');
  249. }
  250. if(isset($pInfo['emptyVendorCode'])){
  251. $activeSheet->setCellValueByColumnAndRow(10,$writeExcelIndex,'-为空');
  252. }
  253. if(isset($pInfo['emptyZzwerkCode'])){
  254. $activeSheet->setCellValueByColumnAndRow(11,$writeExcelIndex,'-为空');
  255. }
  256. if(isset ($pInfo['emptyZzlgortCode'])){
  257. $activeSheet->setCellValueByColumnAndRow(12,$writeExcelIndex,'-为空');
  258. }

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

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

  320. /*

  321. * // 로그 작업.
  322. * $content = '신제품 일괄 가져오기 작업 성공: 공급품 가져오기 제품 코드는 ->';
  323. * $logData['content'] = $content.$logIds;
  324. */
  325. }
  326. return $resultInfo;
  327. }
코드 복사


성명:
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.