PHP Excel 다운 (PHPExcel)

 

<?php
/*
  brief - 원하는 날짜, 값 old 주문건 뽑아 엑셀 저장하는 스크립트
  author - ila
  date - 2022-05-20
*/
ini_set('memory_limit','-1');
error_reporting(~E_ALL & ~E_NOTICE);


require_once("/home/admin/common/conf/dbconn.conf.php");
mysql_query('set names euckr',$conn);
require_once ('/home/admin/script/lib/synclib.php');
require_once ('/home/admin/script/lib/PHPExcel.php');

$ccnt=0;

// createWriter를 Excel2007버전 이용해 작성
$selecttxt = "id, order, name,  phone, email, addr, date";


$cid = '2022';
$damcd = 'ila';
$tdate=date('Ymd');
$wheretxt = "1 AND (date between '2022-01-01' and '2022-01-31') AND addr = '1'";
// print_r($wheretxt);
// 1 AND (date between '2022-05-16' and '2022-05-19')  AND d = '128'

$order_chk="SELECT {$selecttxt} FROM iladb.orders WHERE {$wheretxt}";
echo "WHERE {$wheretxt}\n";



$result_chk = mysql_query($order_chk,$conn_slave);   //rds reader로 변경
$rescnt_chk = mysql_num_rows($result_chk);

//다운로드 한도 설정
if($rescnt_chk > 100000){
	$bigo = "엑셀 요청 한도 초과({$rescnt_chk}건)";
	break;
}else{
	$bigo = "{$damcd}({$cid}) {$tdate} 엑셀 요청 ({$rescnt_chk}건)";
	echo "{$bigo}\n\n";
	$title = $tdate.$damcd.$cid;
	//echo $title = mb_convert_encoding($title,'UTF-8', 'CP949' );

  //폴더 생성
	$tempdir="/home/admin/script/total/{$title}";
	if(!is_dir($tempdir)){ // 동일 디렉토리검사
		$r1 = mkdir("$tempdir",0755); //디렉토리 생성
		$r2 = exec("chmod 777 $tempdir"); // 퍼미션 변경
	}

	$loof = true;
	$excelcnt = 1;
	$limit1 = 0;
	$limit2 = 100000;

	while($loof){
		$filename = $title."_".$excelcnt.".xlsx";
		//$filename = $title."_".$excelcnt.".csv";

		echo "{$filename}\n";
		$objPHPExcel = new PHPExcel();
		$objWorksheet = new PHPExcel_Worksheet($objPHPExcel);

		$ecnt=0;
		if($ecnt ==0){
			$objWorksheet=$objPHPExcel->getSheet(0);
		}else{
			$objPHPExcel->addSheet($objWorksheet);
		}
		$objWorksheet->setTitle($title);

		echo $order_excel="SELECT {$selecttxt} FROM iladb.orders WHERE {$wheretxt} limit {$limit1},{$limit2}";
		echo "WHERE {$wheretxt} limit {$limit1},{$limit2}\n";


		$result = mysql_query($order_excel,$conn_slave);     //rds reader로 변경
		$rescnt = mysql_num_rows($result);

		if($rescnt < 1){

		  $loof = false;

		}else{
			$bigo = "{$excelcnt}/{$rescnt}/{$rescnt_chk} 엑셀 생성";
			echo "{$bigo}\n";
      $s = "'"; //16자리 넘어가는 숫자가 지수(E)로 나오게되면 텍스트(')를 앞에 붙여줘서 텍스트화
      // $selecttxt = "id, order, name,  phone, email, addr, date";
      $ei=1;
      $objWorksheet->setCellValue("A".$ei, "주문","PHPExcel_Cell_DataType::TYPE_STRING");
      $objWorksheet->setCellValue("B".$ei, "이름","PHPExcel_Cell_DataType::TYPE_STRING"); // 고유값은 판매채널명+PCMS코드(itemmt_id)
      $objWorksheet->setCellValue("C".$ei, "전화번호","PHPExcel_Cell_DataType::TYPE_STRING");
      $objWorksheet->setCellValue("D".$ei, "이메일","PHPExcel_Cell_DataType::TYPE_STRING");
      $objWorksheet->setCellValue("E".$ei, "주소","PHPExcel_Cell_DataType::TYPE_STRING");
      $objWorksheet->setCellValue("F".$ei, "날짜","PHPExcel_Cell_DataType::TYPE_STRING");
     
      $ei++;

      while($row=mysql_fetch_array($result)){
          echo "{$ei} {$row[1]} \n";
          $objWorksheet->setCellValue("A".$ei, $row[0],"PHPExcel_Cell_DataType::TYPE_STRING");
          $objWorksheet->setCellValue("B".$ei, $row[2].$row[7],"PHPExcel_Cell_DataType::TYPE_STRING");
              $objPHPExcel->getActiveSheet()->setCellValueExplicit("C".$ei, "'".$row[1], PHPExcel_Cell_DataType::TYPE_STRING);
          $objWorksheet->setCellValue("C".$ei, $s.$row[1],"PHPExcel_Cell_DataType::TYPE_STRING");
          $objWorksheet->setCellValue("D".$ei, $row[2],"PHPExcel_Cell_DataType::TYPE_STRING");
          $objWorksheet->setCellValue("E".$ei, $s.$row[3],"PHPExcel_Cell_DataType::TYPE_STRING");
          $objWorksheet->setCellValue("F".$ei, $s.$row[4],"PHPExcel_Cell_DataType::TYPE_STRING"); //바코드 값의 텍스트서식화 - ila 2022.02.07
         
      $ei++;
      }
			$ecnt++;
			echo "SAVE EXCEL {$tempdir}/{$filename}\n";
			$objPHPExcel->setActiveSheetIndex();
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
              $objWriter->setPreCalculateFormulas(false);
			$objWriter->save("{$tempdir}/$filename");

		}//else
		$excelcnt++;
		if($limit1 == 0) $limit1++;
		$limit1 = $limit1 + $limit2;
	}//while
}



function convert_encoding($text){
	return mb_convert_encoding($text,'UTF-8', 'CP949' );
}

?>