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

/**
 * 기본 제품 정보 가져오기
 * 편집: bbs.it-home.org
*/
 공용 함수 importProductBasicInfo($data){
 include_once 'PHPExcel.php';
 include_once 'PHPExcel/IOFactory.php';
 include_once 'PHPExcel/Reader/Excel5.php';
 // 오류 컬렉션을 정의합니다.
 $error = array ();
 $resultInfo = null;
 $needNext = true;
 //서버의 지정된 위치에 파일 업로드
 $fileName = $_FILES["productinfo"]['name' ];
 $filePath = CBase_Common_UploadPicture::uploadFile($data["productinfo"], 'product');
 //파일이 성공적으로 업로드되면 Excel 가져오기 작업을 실행합니다.
 if($filePath == 1) {
 $error[1] = "업로드된 파일이 php.ini의 upload_max_filesize 옵션 제한을 초과했습니다.";
 }else if($filePath == 4){
 $error [4] = " 업로드된 파일이 없습니다.";
 }else{
 $objReader = PHPExcel_IOFactory::createReader('Excel5');
 $objReader->setReadDataOnly(true);
 $ objPHPExcel = $objReader- >load($filePath);
 $objWorksheet = $objPHPExcel->getActiveSheet();
 $highestRow = $objWorksheet->getHighestRow();
 $highestColumn = $ objWorksheet->getHighestColumn ();
 $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
 $colums = array();
 $data = array();
 $excelAllId = array ();
 $excelIdRow = array();
 $execlAllShopLinkedId = array();
 for($i=0;$i<$highestColumnIndex;$i ){
 $cValue = 트림 ($objWorksheet-> ;getCellByColumnAndRow($i,1)->getValue());
 스위치($cValue) {
 케이스 self::PRODUCT_SAP_CODE : $colums[$i] = "sap_code"; break;
 case self::PRODUCT_NAME : $colums[$i] = "pname"; break;
 case self::PRODUCT_GROUP : $colums[$i] = "product_group" break;
 케이스 self::PRODUCT_BRAND : $colums[$i] = 'product_brand'; break;
 케이스 self::PRODUCT_PROXY_FLAG : $colums[$i] = "product_proxy_flag";
 케이스 self::PRODUCT_BINNING : $ colums[$i] = "product_binning"; break;
 case self::PRODUCT_SELL_PICK : $colums[$i] = "product_sell_pick";
 case self::PRODUCT_ATTRIBUTE : $colums[$i] = "product_attribute"; break ;
 case self::PRODUCT_SUPPLIER_CODE : $colums[$i] = "vendor_code"; break;
 case self::PRODUCT_SUPPLY_ADDRESS : $colums[$i] = "zzwerk_code";
 케이스 자체 ::PRODUCT_BATCH : $colums[$i] = "zzlgort_code"; break;
 기본값: $error[3][] = $cValue; break;
 }
 }
 //Excel에 기본 정보가 있는지 감지
 $dataCount = $highestRow - 1;
 if(count($colums) == 0) {
 $error[5] = "No header" ;
 }
 else if(!in_array('sap_code',$colums)){
 $error[2] = "헤더에 제품 SAP 코드가 존재하지 않습니다.";
 }
 else if($ dataCount <= 0){
 $error[6] = "Excel 파일에 데이터가 없습니다.";
 }
 else if(count($error)== 0){
 for ($ i=2;$i<=$highestRow;$i ){
 $colkey = array_search('sap_code');
 $shopLinkedIdValue = Trim($objWorksheet-> getCellByColumnAndRow($colkey,$i)- >getValue());
 if(!$shopLinkedIdValue) {
 계속;
 }
 if(in_array($shopLinkedIdValue,$execlAllShopLinkedId)){
 $error[7][ $shopLinkedIdValue]['duplicate'] = true;
 $error[7][$shopLinkedIdValue]['excelRow'][] = $i;
 $execlAllShopLinkedId[$ i] = $shopLinkedIdValue;
 $error[7][$shopLinkedIdValue]['noId'] = true;
 }else {
 $excelIdRow[$shopLinkedIdValue] = $i;
 $execlAllShopLinkedId [$i] = $shopLinkedIdValue;
 }
 }
 $dealMultiple = ceil($dataCount / 1000);
 $allProduct = array();
 for($i=0;$i<$dealMultiple;$i ){
 $offset = $i*1000 2;
 $max = ($i 1)*1000 1;
 $max = ($max > $dataCount) ? $highestRow : $max;
 $allShopLinkedId = array( );
 for($j=$offset;$j<=$max;$j ){
 if($execlAllShopLinkedId[$j]){
 $allShopLinkedId[] = $execlAllShopLinkedId[$j ];
 }
 }
 // SAP 제품 코드에 따라 데이터베이스의 레코드 개수를 쿼리합니다.
 $dbShopProducts = $this->getShopLinkedByIds($allShopLinkedId);
 for($j=$offset;$j<=$max;$j ){
 $product = array();
 for($k=0;$k< ;$highestColumnIndex ;$k ){
 $tempV = Trim($objWorksheet->getCellByColumnAndRow($k,$j)->getValue());
 if($tempV && $tempV != ' ') {
 $product[$colums[$k]] = $tempV;
 }
 }
 //파일에서 SAP 코드 가져오기
 $id = $product[' sap_code'];
 if(!$id){
 continue;
 }
 //제품 SAP 코드가 이미 존재하는지 확인
 if(!in_array($id,$dbShopProducts) ){
 $allProduct[$id] = $product;
 }else{
 $error[7][$id]['hasId'] = true;
 }
 // 제품 이름이 비어 있는지 여부
 if(!isset($product['pname'])){
 $error[7][$id]['emptyName'] = true;
 }
 //제품 카테고리 (제품 그룹)이 비어 있는지 여부
 if(!isset($product['product_group'])){
 $error[7][$id]['emptyProductGroup'] = true;
 }
 //상품레벨(브랜드)이 비어 있는지 여부
 if(!isset($product['product_brand'])){
 $error[7][$id][' emptyProductBrand'] = true;
 }
 //대행사 판매 플래그가 비어 있는지 여부
 if(!isset($product['product_proxy_flag'])){
 $error[7][$ id]['emptyProductProxyFlag'] = true;
 }
 // 패킹리스트가 비어 있는지 여부
 if(!isset($product['product_binning'])){
 $error[7 ][$id]['emptyProductBinning'] = true;
 }
 //먼저 판매, 나중에 구매 플래그가 비어 있습니다
 if(!isset($product['product_sell_pick'])){
 $error[7][ $id]['emptyProductSellPick'] = true;
 }
 //제품 속성이 비어 있는지 여부
 if(!isset($product['product_attribute'] )){
 $error[7 ][$id]['emptyProductAttribute'] = true;
 }
 //공급업체 코드가 비어 있는지 여부
 if(!isset($product[' Vendor_code'])){
 $ error[7][$id]['emptyVendorCode'] = true;
 }
 //공급 위치가 비어 있는지 여부
 if(!isset($ product['zzwerk_code'])){
 $error[7][$id]['emptyZzwerkCode'] = true;
 }
 //라이브러리 영역이 비어 있는지
 if(!isset ($product['zzlgort_code'])){
 $error[7][$id]['emptyZzlgortCode'] = true;
 }
 if(isset($error[7][$id ])){
 $error[7 ][$id]['excelRow'] = $j;
 }
 }
 }
 }
 }
 $resultInfo ['fileName'] = $fileName;
 //오류 정보 반환
 if(count($error)>0){
 if(isset($error[1])){
 $resultInfo['type'] = 1;
 $resultInfo['msg'] = $error[1];
 }else if(isset($error[2])){
 $resultInfo[ '유형'] = 2;
 $ resultInfo['msg'] = $error[2];
 }else if(isset($error[3])){
 $resultInfo['유형' ] = 3;
 $resultInfo[' msg'] = '헤더 ['.implode(',',$error[3]).']가 존재하지 않습니다.';
 }else if(isset($error[4])){
 $resultInfo['type'] = 4;
 $resultInfo['msg'] = $error[4];
 }else if( isset($error[6])){
 $resultInfo['type'] = 6;
 $resultInfo['msg'] = $error[6];
 }else if(isset($ error[7])){
 $excelName = null;
 $objPHPWriteExcel = new PHPExcel();
 $objPHPWriteExcel->getProperties()->setCreator("yuer")
 - >setLastModifiedBy("yuer")->setTitle("")->setSubject("")
 ->setDescription("")->setKeywords("")->setCategory("" );
 $prefix = substr($fileName,0,strrpos($fileName,'.'));
 $suffix = substr($fileName,strrpos($fileName,'.'));
 $excelName = 날짜("Y_m_d_H_i_s").'_'.mt_rand(1,99).'_'.$prefix.'ErrorReport'.$suffix;
 $excelName = Base_Tool_Pinyin::getPinyin($excelName) ;
 $objPHPWriteExcel->setActiveSheetIndex(0);
 $activeSheet = $objPHPWriteExcel->getActiveSheet();
 $activeSheet->setTitle('错误报告');
 $activeSheet ->setCellValueByColumnAndRow(0,1,self::PRODUCT_SAP_CODE);
 $activeSheet->setCellValueByColumnAndRow(1,1,'haraexcel行号');
 $activeSheet->setCellValueByColumnAndRow(2,1 ,'第几行编码存재중재');
 $activeSheet->setCellValueByColumnAndRow(3,1,self::PRODUCT_NAME);
 $activeSheet->setCellValueByColumnAndRow(4,1,self::PRODUCT_GROUP) ;
 $activeSheet->setCellValueByColumnAndRow(5,1,self::PRODUCT_BRAND);
 $activeSheet->setCellValueByColumnAndRow(6,1,self::PRODUCT_PROXY_FLAG);
 $activeSheet->setCellValueByColumnAndRow (7,1,self::PRODUCT_BINNING);
 $activeSheet->setCellValueByColumnAndRow(8,1,self::PRODUCT_SELL_PICK);
 $activeSheet->setCellValueByColumnAndRow(9,1,self::PRODUCT_ATTRIBUTE) ;
 $activeSheet->setCellValueByColumnAndRow(10,1,self::PRODUCT_SUPPLIER_CODE);
 $activeSheet->setCellValueByColumnAndRow(11,1,self::PRODUCT_SUPPLY_ADDRESS);
 $activeSheet->setCellValueByColumnAndRow (12,1,self::PRODUCT_BATCH);
 $activeSheet->setCellValueByColumnAndRow(13,1,'其他原因');
 $activeSheet->getColumnDimensionByColumn(0)->setWidth(15) ;
 $activeSheet->getColumnDimensionByColumn(1)->setWidth(20);
 $activeSheet->getColumnDimensionByColumn(2)->setWidth(20);
 $activeSheet->getColumnDimensionByColumn (3)->setWidth(20);
 $activeSheet->getColumnDimensionByColumn(4)->setWidth(20);
 $activeSheet->getColumnDimensionByColumn(5)->setWidth(20) ;
 $activeSheet->getColumnDimensionByColumn(6)->setWidth(20);
 $activeSheet->getColumnDimensionByColumn(7)->setWidth(20);
 $activeSheet->getColumnDimensionByColumn (8)->setWidth(20);
 $activeSheet->getColumnDimensionByColumn(9)->setWidth(20);
 $activeSheet->getColumnDimensionByColumn(10)->setWidth(20) ;
 $activeSheet->getColumnDimensionByColumn(11)->setWidth(20);
 $activeSheet->getColumnDimensionByColumn(12)->setWidth(20);
 $activeSheet->getColumnDimensionByColumn (13)->setWidth(20);
 $writeExcelIndex = 2;
 foreach ($error[7] as $pId=>$pInfo){
 if(isset($pInfo[' hasId'])){
 $activeSheet->setCellValueByColumnAndRow(0,$writeExcelIndex,$pId.'-此供应商编码已经存재');
 } else {
 $activeSheet->setCellValueByColumnAndRow (0,$writeExcelIndex,$pId);
 }
 $activeSheet->setCellValueByColumnAndRow(1,$writeExcelIndex,$pInfo['excelRow']);
 if(isset($pInfo['duplicate) '])){
 $activeSheet->setCellValueByColumnAndRow(2,$writeExcelIndex,$excelIdRow[$pId]);
 }
 if(isset($pInfo['emptyName'])){
 $activeSheet->setCellValueByColumnAndRow(3,$writeExcelIndex,'-为空');
 }
 if(isset($pInfo['emptyProductGroup'])){
 $activeSheet->setCellValueByColumnAndRow(4,$writeExcelIndex,'-为空');
 }
 if(isset( $pInfo['emptyProductBrand'])){
 $activeSheet->setCellValueByColumnAndRow(5,$writeExcelIndex,'-为空');
 }
 if(isset($pInfo['emptyProductProxyFlag'] )){
 $activeSheet->setCellValueByColumnAndRow(6,$writeExcelIndex,'-为空');
 }
 if(isset($pInfo['emptyProductBinning'])){
 $ activeSheet->setCellValueByColumnAndRow(7,$writeExcelIndex,'-为空');
 }
 if(isset($pInfo['emptyProductSellPick'])){
 $activeSheet->setCellValueByColumnAndRow(8 ,$writeExcelIndex,'-为空');
 }
 if(isset($pInfo['emptyProductAttribute'])){
 $activeSheet->setCellValueByColumnAndRow(9,$writeExcelIndex,'-为공');
 }
 if(isset($pInfo['emptyVendorCode'])){
 $activeSheet->setCellValueByColumnAndRow(10,$writeExcelIndex,'-为空');
 }
 if(isset($pInfo['emptyZzwerkCode'])){
 $activeSheet->setCellValueByColumnAndRow(11,$writeExcelIndex,'-为空');
 }
 if(isset ($pInfo['emptyZzlgortCode'])){
 $activeSheet->setCellValueByColumnAndRow(12,$writeExcelIndex,'-为空');
 }
                    if(isset($pInfo['other'])){
 $activeSheet->setCellValueByColumnAndRow(13,$writeExcelIndex,$pInfp['other']);
 }
 $writeExcelIndex ;
 }
 $objWriter = PHPExcel_IOFactory::createWriter($objPHPWriteExcel, 'Excel5');
 $excelPath = FILE_PATH.DS.'feedback'.DS.$excelName;
 $objWriter->save( $excelPath);
 $resultInfo['type'] = 7;
 $resultInfo['msg'] = $fileName."文件中存재错误";
 $resultInfo['errorReport'] = $ excelName;
 // 日志操작품,暂时空着
 }
 }else{
 //导入数据
 $logIds = '';
 $i = 0;
 foreach ($allProduct as $pId => $pInfo){
 $updateProductSql = 'yr_product 세트에 삽입';
 if(isset($pInfo['pname']) && Trim($pInfo['pname '])){
 $updateProductSql = $updateProductSql.'pname=''.str_replace(''','''',$pInfo['pname']).'',';
 }
 //如果SAP编码不足18位，则用0从左开始补全
 if(isset($pInfo['sap_code'])){
 if(strlen($pInfo['sap_code'] )<18){
 $pInfo['sap_code'] = str_pad($pInfo['sap_code'], 18, "0", STR_PAD_LEFT);
 $updateProductSql = $updateProductSql.'sap_code='' .str_replace(''','''',$pInfo['sap_code']).'',';
 }
 }
 if(isset($pInfo['product_group'])) {
 $updateProductSql = $updateProductSql.'product_group=''.$pInfo['product_group'].'',';
 }
 if(isset($pInfo['product_brand'])){
 $updateProductSql = $updateProductSql.'product_brand=''.$pInfo['product_brand'].'',';
 }
 if(isset($pInfo['product_proxy_flag'])){
 $updateProductSql = $updateProductSql.'product_proxy_flag=''.$pInfo['product_proxy_flag'].'',';
 }
 if(isset($pInfo['product_binning'])){
 $updateProductSql = $updateProductSql.'product_binning=''.$pInfo['product_binning'].'',';
 }
 if(isset($pInfo['product_sell_pick'])){
 $updateProductSql = $updateProductSql.'product_sell_pick=''.$pInfo['product_sell_pick'].'',';
 }
 if(isset($pInfo['product_attribute'])){
 $ updateProductSql = $updateProductSql.'product_attribute=''.$pInfo['product_attribute'].'',';
 }
 if(isset($pInfo['vendor_code'])){
 $updateProductSql = $updateProductSql.'vendor_code=''.$pInfo['vendor_code'].'',';
 }
 if(isset($pInfo['zzwerk_code'])){
 $updateProductSql = $updateProductSql.'zzwerk_code=''.$pInfo['zzwerk_code'].'',';
 }
 if(isset($pInfo['zzlgort_code'])){
 $updateProductSql = $updateProductSql.'zzlgort_code=''.$pInfo['zzlgort_code'].''';
 }
 //최종 SQL문
 $result = $this->excuteMultiInsertSql($updateProductSql);
 }
 $resultInfo['type'] = 8;
 $resultInfo['msg' ] = "기본 상품 정보 가져오기 성공";
 /*
 * // 로그 작업.
 * $content = '신제품 일괄 가져오기 작업 성공: 공급품 가져오기 제품 코드는 ->';
 * $logData['content'] = $content.$logIds;
 */
 }
 return $resultInfo;
 }