엑셀 다운로드 셀 병합 .. 채택완료

<?php

include_once('./_common.php');

include_once(G5_LIB_PATH.'/PHPExcel.php');

 

$year = isset($_POST['year']) ? $_POST['year'] : '';

$month = isset($_POST['month']) ? $_POST['month'] : '';

$wr_dong = isset($_POST['wr_dong']) ? $_POST['wr_dong'] : '';

 

$search_date = $year . "-" . $month;

 

$ca_name = '상업용';

 

$sql = "SELECT a.it_id,

              a.io_id,

              a.ct_status,

              a.ct_post,

              b.mb_name,

              b.mb_company,

              b.mb_nick,

              c.od_memo

        FROM g5_shop_cart AS a

        LEFT JOIN g5_member AS b ON a.mb_id = b.mb_id

        LEFT JOIN g5_shop_order AS c ON a.od_id = c.od_id

        WHERE a.mb_id != 'admin'

        AND a.ct_apply_date = '$search_date'  

        AND a.ca_name = '$ca_name'

        AND (a.ct_status = '입금' OR (a.ct_status = '쇼핑' AND TIMESTAMPDIFF(MINUTE, a.ct_time, NOW()) < 5));";

 

$result = sql_query($sql);

 

$banner_array = array();

while ($row = sql_fetch_array($result)) {

    $it_id = $row['it_id'];

    $io_id = explode(chr(30), $row['io_id']);

    $mb_name = $row['mb_name'];

    $mb_company = $row['mb_company'];

    $od_memo = $row['od_memo'];

 

    if ($row['ct_status'] == '입금') {

        if ($search_date <= date("Y-m"))

            $banner_array[$it_id][$io_id[0]][$io_id[1]][0] = "<span class='fixed'>[게첨]</span>";

        else

            $banner_array[$it_id][$io_id[0]][$io_id[1]][0] = "<span class='paid'>[입금완료]</span>";

    } else

        $banner_array[$it_id][$io_id[0]][$io_id[1]][0] = "<span class='wait'>[입금대기]</span>";

 

    $banner_array[$it_id][$io_id[0]][$io_id[1]][1] = "$mb_name ($mb_company - $od_memo)";

}

 

//전체 게시대

$sql_where = "WHERE ca_name = '$ca_name' ";

 

if($wr_dong){

    $sql_where .= "AND wr_dong = '$wr_dong' ";

}

 

$sql = "SELECT *

        FROM g5_write_menu02_1 AS d

        $sql_where

        ORDER BY wr_id";

 

$result = sql_query($sql);

 

if (!function_exists('column_char')) {

    function column_char($i)

    {

        return chr(65 + $i);

    }

}

 

$headers = array(

    array('현수막지정게시대 장소별 접수현황'),

    array($year . "년" . $month . "월 접수현황"),

    array('장소번호', '게시대명', '면', '1기', '2기', '3기')

);

 

$widths = array(20, 40, 10, 40, 40, 40);

$last_col = column_char(count($headers[2]) - 1);

 

$data = array_merge($headers, array());

 

$rowCounter = 1;

 

while ($row = sql_fetch_array($result)) {

    $it_id = $row['it_id'];

    $wr_1 = $row['wr_1'];

    $wr_subject = $row['wr_subject'];

    $wr_4 = $row['wr_4'];

    $mb_name = $row['mb_name'];

    $mb_company = $row['mb_company'];

    $od_memo = $row['od_memo'];

 

    // 면 열의 셀을 합병하기 위해 해당 행을 여러 번 추가

    for ($i = 0; $i < $wr_4; $i++) {

        $row_data = array(

            ($i === 0) ? $wr_1 : '', // 장소번호 (첫 번째 행에서만 표시)

            ($i === 0) ? $wr_subject : '', // 게시대명 (첫 번째 행에서만 표시)

            $rowCounter++, // 면

        );

 

        for ($j = 1; $j <= 3; $j++) {

            if ($j <= $wr_4) {

                $banner_data = $banner_array[$it_id][$j][$i + 1];

                if (!empty($banner_data) && isset($banner_data[1])) {

                    $row_data[] = $banner_data[1];

                } else {

                    $row_data[] = "-";

                }

            } else {

                // 기존 코드에서 주석 처리된 부분을 활성화하고 필요에 따라 내용을 채워주세요.

                // $row_data[] = ''; // 해당 셀의 내용

            }

        }

 

        $data[] = $row_data;

 

    }

 

    $rowCounter = 1;

}

 

$excel = new PHPExcel();

 

// $excel->setActiveSheetIndex(0)->getStyle("A:$last_col")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setWrapText(true);

// foreach ($widths as $i => $w) $excel->setActiveSheetIndex(0)->getColumnDimension(column_char($i))->setWidth($w);

// $excel->getActiveSheet()->fromArray($data, NULL, 'A1');

 

$last_row = count($data);

$last_col = count($data[0]) - 1; // 마지막 열의 인덱스

$last_col_char = column_char($last_col);

 

$excel->setActiveSheetIndex(0)->getStyle("A:$last_col_char")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setWrapText(true);

foreach ($widths as $i => $w) $excel->setActiveSheetIndex(0)->getColumnDimension(column_char($i))->setWidth($w);

$excel->getActiveSheet()->fromArray($data, NULL, 'A1');

 

// A1에서 F2 까지 병합

// 볼드체와 중앙 정렬 적용

$excel->getActiveSheet()->mergeCells("A1:F1");

$excel->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true)->setSize(14);

$excel->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$excel->getActiveSheet()->setCellValue('A1', '현수막지정게시대 장소별 접수현황');

 

// A3 에서 F3 까지 병합

// 볼드체와 중앙 정렬 적용

$excel->getActiveSheet()->mergeCells("A2:F2");

$excel->getActiveSheet()->getStyle('A2:F2')->getFont()->setBold(true);

$excel->getActiveSheet()->getStyle('A2:F2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$excel->getActiveSheet()->setCellValue('A2', "$year 년 $month 월 접수현황");

 

// A4에서 F4까지 회색으로 배경색

$excel->getActiveSheet()->getStyle("A3:F3")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('d3d3d3');

$excel->getActiveSheet()->getStyle("A3:F3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$excel->getActiveSheet()->setCellValue('A3', '장소번호');

$excel->getActiveSheet()->setCellValue('B3', '게시대명');

$excel->getActiveSheet()->setCellValue('C3', '면');

$excel->getActiveSheet()->setCellValue('D3', '1기');

$excel->getActiveSheet()->setCellValue('E3', '2기');

$excel->getActiveSheet()->setCellValue('F3', '3기');

 

// 전체 범위에 중앙 정렬 적용

for ($rowIndex = 1; $rowIndex <= $last_row + 1; $rowIndex++) {

    $excel->getActiveSheet()->getStyle("A$rowIndex:F$rowIndex")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

}

 

header("Content-Type: application/octet-stream");

// header("Content-Type: application/vnd.ms-excel");

// header("Content-Disposition: attachment; filename=\"월별 접수목록-" . date("ymd", time()) . ".xls\"");

 

// 엑셀 파일 이름 설정

$formatted_month = str_pad(date('m'), 2, '0', STR_PAD_LEFT);

$formatted_day = str_pad(date('d'), 2, '0', STR_PAD_LEFT);

$filename = "게시물접수현황(" . date("ymd", time()) . ").xls";

 

header("Content-Disposition: attachment; filename=\"$filename\"");

 

header("Cache-Control: max-age=0");

 

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');

$writer->save('php://output');

?>