엑셀 스타일 부분 질문입니다.
본문
안녕하세요? 항상 도움 받고 있습니다. 감사합니다.
지금 엑셀 파일 다운로드 기능을 해서 데이터를 표시하는건 성공을 했는데 a1열 부터 데이터를 출력하다보니 a1:a2 를 병합해서 데이터가 출력이 되는 부분과 헤더가 겹쳐서 데이터 제일 처음 부분이 헤더에 가려서 보이지 않습니다. 데이터 부분을 a5 부터 가져오게 하려면 어떻게 해야 하나요?
그리고 전체 중앙 정렬을 하고 싶고 테두리를 추가하고 싶은데 이 부분도 어떻게 해야 하나요?
<?php
include_once('./_common.php');
include_once(G5_LIB_PATH.'/PHPExcel.php');
$year = isset($_POST['year']) ? $_POST['year'] : '';
$start_month = isset($_POST['start_month']) ? $_POST['start_month'] : '';
$end_month = isset($_POST['end_month']) ? $_POST['end_month'] : '';
$keyword = isset($_POST['keyword']) ? $_POST['keyword'] : '';
$start_date = $year . '-' . $start_month . '-01';
$end_date = $year . '-' . $end_month . '-31';
$keyword_condition = !empty($keyword) ? " AND (c.mb_name = '$keyword' OR c.mb_company = '$keyword')" : '';
$order_name = ($order_name)? $order_name:'ct_id';
$order_sort = ($order_sort)? $order_sort:'asc';
$sql = "SELECT c.mb_name,
c.mb_company,
c.mb_hp,
c.mb_tel,
SUM(b.wr_8) AS total_wr_8,
SUM(b.wr_9) AS total_wr_9,
SUM(b.wr_8 + b.wr_9) AS total_sum,
COUNT(*) AS total_cnt
FROM {$g5['g5_shop_cart_table']} AS a
LEFT JOIN g5_write_menu02_1 AS b ON a.it_id = b.it_id
LEFT JOIN {$g5['member_table']} AS c ON a.mb_id = c.mb_id
WHERE a.mb_id != 'admin'
AND a.ct_status = '입금'
AND a.ct_time BETWEEN '$start_date 00:00:00' AND '$end_date 23:59:59'
$keyword_condition
GROUP BY a.mb_id
ORDER BY $order_name $order_sort";
$result = sql_query($sql);
if (!function_exists('column_char')) {
function column_char($i)
{
return chr(65 + $i);
}
}
$headers = array(
array('월별-신청자별 통계'),
array($year . "년" . $start_month . "월~" . $end_month . "월"),
array('연번', '신청자', '신청건수', '허가수수료', '도로점용료', '소계', '비고')
);
$widths = array(5, 30, 20, 20, 20, 20, 40);
$last_char = column_char(count($headers[2]) - 1);
$data = array_merge($headers, array());
$rowCounter = 1;
while ($row = sql_fetch_array($result)) {
$mb_name = $row['mb_name'];
$mb_company = $row['mb_company'];
$mb_hp = $row['mb_hp'];
$mb_tel = $row['mb_tel'];
$total_wr_8 = $row['total_wr_8'];
$total_wr_9 = $row['total_wr_9'];
$total_cnt = $row['total_cnt'];
$row_data = array(
$rowCounter++,
(!empty($mb_name) && !empty($mb_company)) ? $mb_name . " / " . $mb_company : $mb_name . $mb_company, // 신청자
(!empty($total_cnt)) ? $total_cnt . "건" : $total_cnt, // 신청건수
number_format($total_wr_8), // 허가수수료
number_format($total_wr_9), // 도로점용료
number_format($total_wr_8 + $total_wr_9), // 소계
(!empty($mb_hp) && !empty($mb_tel)) ? $mb_hp . " / " . $mb_tel : $mb_hp . $mb_tel // 비고
);
$data[] = $row_data;
// 각 항목을 누적하여 총합 계산
$grand_total_wr_8 += $total_wr_8;
$grand_total_wr_9 += $total_wr_9;
$grand_total += ($total_wr_8 + $total_wr_9);
$grand_total_cnt += $total_cnt;
}
$excel = new PHPExcel();
$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에서 G2 까지 병합
// 볼드체와 중앙 정렬 적용
$excel->getActiveSheet()->mergeCells("A1:G2");
$excel->getActiveSheet()->getStyle('A1:G2')->getFont()->setBold(true)->setSize(14);
$excel->getActiveSheet()->getStyle('A1:G2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->setCellValue('A1', '월별-신청자별 통계');
// A3 에서 G3 까지 병합
// 볼드체와 중앙 정렬 적용
$excel->getActiveSheet()->mergeCells("A3:G3");
$excel->getActiveSheet()->getStyle('A3:G3')->getFont()->setBold(true);
$excel->getActiveSheet()->getStyle('A3:G3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->setCellValue('A3', "$year 년 $start_month 월 ~ $end_month 월");
// A4에서 G4까지 회색으로 배경색
$excel->getActiveSheet()->getStyle("A4:G4")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('d3d3d3');
$excel->getActiveSheet()->setCellValue('A4', '연번');
$excel->getActiveSheet()->setCellValue('B4', '신청자');
$excel->getActiveSheet()->setCellValue('C4', '신청건수');
$excel->getActiveSheet()->setCellValue('D4', '허가수수료');
$excel->getActiveSheet()->setCellValue('E4', '도로점용료');
$excel->getActiveSheet()->setCellValue('F4', '소계');
$excel->getActiveSheet()->setCellValue('G4', '비고');
// 마지막 행에 합계 및 값 추가 (콤마 포함)
$excel->getActiveSheet()->setCellValueByColumnAndRow($last_col + 1, $last_row + 1, '합계');
$excel->getActiveSheet()->setCellValueByColumnAndRow($last_col + 2, $last_row + 1, number_format($grand_total_cnt) . "건");
$excel->getActiveSheet()->setCellValueByColumnAndRow($last_col + 3, $last_row + 1, number_format($grand_total_wr_8));
$excel->getActiveSheet()->setCellValueByColumnAndRow($last_col + 4, $last_row + 1, number_format($grand_total_wr_9));
$excel->getActiveSheet()->setCellValueByColumnAndRow($last_col + 5, $last_row + 1, number_format($grand_total));
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($month, 2, '0', STR_PAD_LEFT);
$formatted_day = str_pad(date('d'), 2, '0', STR_PAD_LEFT);
$filename = "월별통계관리-신청자별(" . date("ym", time()) . $formatted_month . $formatted_day . ").xls";
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Cache-Control: max-age=0");
$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$writer->save('php://output');
?>
답변을 작성하시기 전에 로그인 해주세요.