게시판에서 회원목록을 엑셀로 출력하는 소스는?
2014.02.15 22:18
아래 내용을 php파일로 만들어 주소창에서 출력하면 해당하는 정보를 입력시 원하는 출력 내용을 선택하여 출력이 가능합니다.
같은 내용이 하단에 파일로 있으니 다운받아 사용하세요.
<?php
session_start();
if(filter_has_var(INPUT_POST, "default")) unset($_SESSION['host'],$_SESSION['user'],$_SESSION['pass'],$_SESSION['db'],$_SESSION['con_db'],$_SESSION['prefix']);
$_SESSION['host'] = $_SESSION['host']?$_SESSION['host']:filter_input(INPUT_POST, "host");
$_SESSION['user'] = $_SESSION['user']?$_SESSION['user']:filter_input(INPUT_POST, "user");
$_SESSION['pass'] = $_SESSION['pass']?$_SESSION['pass']:filter_input(INPUT_POST, "pass");
$_SESSION['db'] = $_SESSION['db']?$_SESSION['db']:filter_input(INPUT_POST, "db");
$_SESSION['prefix'] = $_SESSION['prefix']?$_SESSION['prefix']:filter_input(INPUT_POST, "prefix");
$info = array(
"Mtable" => $_SESSION['prefix']."_member",
"MGtable" => $_SESSION['prefix']."_member_group",
"MGMtable" => $_SESSION['prefix']."_member_group_member"
);
$top =<<<TOP
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html lang="EN" dir="ltr" xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv=Content-Type content=text/html; charset="utf-8">
<style type="text/css">
.center {text-align:center}
.border {border:2px green solid}
.right {text-align:right}
.marginAuto {margin:0 auto}
.blueBorder {border:1px blue solid}
.bold {font-weight:bold; color:green}
.format {mso-number-format:'0_ '}
.infoForm input {width:100px}
.infoForm label {color:blue}
legend {color:red; font-weight:bold}
table {border:1px gray solid}
</style>
</head>
<body>\r\n
TOP;
$userTitle = "DB에 접속할 때\r\n사용하는 아이디";
$passTitle = "DB에 접속할 때\r\n사용하는 비밀번호";
$dbTitle = "DB에 접속해서\r\nDB명을 확인할 수 있다";
$prefixTitle = "예)DB의 테이블에서 보면\r\nxe_members 라는\r\n테이블이 있으면 이 경우의\r\nprefix는 xe 이것이다.";
$infoForm =<<<FORM
<form method="post" action="" class="infoForm">
<input type="hidden" name="default" value="1" />
<label>Host</label><input type="text" name="host" value="{$_SESSION['host']}" />
<label>User</label><input type="text" name="user" value="{$_SESSION['user']}" title="{$userTitle}" />
<label>Pass</label><input type="password" name="pass" value="{$_SESSION['pass']}" title="{$passTitle}" />
<label>DB</label><input type="text" name="db" value="{$_SESSION['db']}" title="{$dbTitle}" />
<label>Prefix</label><input type="text" name="prefix" value="{$_SESSION['prefix']}" title="{$prefixTitle}" />
<button type="submit">연결</button>
</form>
FORM;
$bottom = "\r\n</body>\r\n</html>";
if(!$_SESSION['con_db']||!$_SESSION['host']||!$_SESSION['user']||!$_SESSION['pass']||!$_SESSION['db']||!$_SESSION['prefix'])
{
echo $top;
echo $infoForm;
$display = true;
}
if($_SESSION['host']&&$_SESSION['user']&&$_SESSION['pass']&&$_SESSION['db']&&$_SESSION['prefix'])
{
$con = mysql_connect($_SESSION['host'], $_SESSION['user'], $_SESSION['pass']);
mysql_query("set names utf8");
$con_db = mysql_select_db($_SESSION['db']);
if(mysql_error())
{
echo "<meta http-equiv=Content-Type content=text/html; charset='utf-8'><div style='font-size:30px;font-weight:bold'>연결실패!!\r\nDB정보를 확인하고 다시 시도하세요.</div>";
exit;
}
$fields = mysql_list_fields($_SESSION['db'], "$info[Mtable]");
$columns = mysql_num_fields($fields);
if($columns) $_SESSION['con_db'] = true;
}
$mode = filter_input(INPUT_POST, "mode");
if(!$mode&&$_SESSION['con_db'])
{
$groupTitle = mysql_query("select group_srl, title from ".$info['MGtable']);
while($row = mysql_fetch_assoc($groupTitle))
{
$checkedGroup .= sprintf('<input type="checkbox" id="%1$s" name="targetGroup[%2$s]" value="%1$s" /><label for="%1$s">%1$s</label>', $row['title'], $row['group_srl']);
$title[$row['group_srl']] = $row['title'];
}
$title = serialize($title);
if(!$display)
{
echo $top;
echo $infoForm;
}
echo <<<FIRST
<fieldset class="border">
<legend>회원목록을 엑셀파일로 출력하기</legend>
<form method="post" action="">
<input type="hidden" name="mode" value="2" />
<input type="hidden" name="title" value=$title />
<div class="center">
<div class="bold">출력할 파일명(기본값:member)</div><input type="text" name="xlsName" class="right blueBorder" />.xls
<div class="bold">출력할 그룹 선택(기본값:전체)</div>$checkedGroup
<div class="bold">출력할 항목 선택(기본값:전체)</div>
</div>
<table class="marginAuto">
<tr>
FIRST;
for( $i=0 ; $i<$columns ; $i++ )
{
$j++;
echo "<td>";
$field[$i]=mysql_field_name($fields, $i);
echo sprintf('<input type="checkbox" id="%1$s" name="checkFields[]" value="%1$s" /><label for="%1$s">%1$s</label>', $field[$i]);
echo "</td>";
if($j%3==0&&$j<$columns) echo "</tr><tr>";
$mField[$i] = $field[$i];
}
$mField = serialize($mField);
echo '<input type="hidden" name="mField" value='.$mField.' />';
echo '</tr></table><div class="center"><input type="reset" value="reset" /><input type="submit" value="출력하기" /></div></form></fieldset>';
echo $bottom;
}
else if($mode==2)
{
$xlsName = filter_input(INPUT_POST, "xlsName")?filter_input(INPUT_POST, "xlsName").'.xls':'member.xls';
$checkbox = filter_input_array(INPUT_POST);
$checkFields = $checkbox['checkFields'];
$targetGroup = $checkbox['targetGroup']?$checkbox['targetGroup']:unserialize(filter_input(INPUT_POST, "title"));
$mField = unserialize(filter_input(INPUT_POST, "mField"));
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$xlsName");
if(!$display) echo $top;
echo "<table border=1>";
echo "<tr align=center>";
for( $i=0 ; $i<$columns ; $i++ )
{
$field[$i]=mysql_field_name($fields, $i);
if($checkFields&&in_array($field[$i], $checkFields)) echo "<th>".$field[$i]."</th>";
else if(!$checkFields) echo "<th>".$field[$i]."</th>";
}
echo "</tr>";
if($checkbox['targetGroup'])
{
$i = 0;
foreach($targetGroup as $key=>$val)
{
$i++;
$where .= $info['MGtable'].".group_srl=".$key;
if($i<count($targetGroup)) $where .= " or ";
}
}
if(!$checkbox['targetGroup']||count($checkbox['targetGroup'])==count(unserialize(filter_input(INPUT_POST, "title"))))
$sql = mysql_query("select * from ".$info['Mtable']);
else
$sql = mysql_query("select * from ".$info['Mtable'].",".$info['MGtable'].",".$info['MGMtable']." where ".$info['Mtable'].".member_srl=".$info['MGMtable'].".member_srl and ".$info['MGMtable'].".group_srl=".$info['MGtable'].".group_srl and (".$where.")");
while($row = mysql_fetch_assoc($sql))
{
echo "<tr>";
foreach($row as $key=>$val)
{
if($key=='regdate'||$key=='last_login'||$key=='change_password_date') $td = "<td class='format'>";
else $td = "<td>";
if($checkFields&&in_array($key, $checkFields)&&$key!='extra_vars') echo $td.$val."</td>";
else if(!$checkFields&&in_array($key, $mField)&&$key!='extra_vars') echo $td.$val."</td>";
else if($key=='extra_vars'&&($checkFields&&in_array($key, $checkFields)||!$checkFields&&in_array($key, $mField)))
{
$extra_vars = unserialize($val);
echo $td;
if($val!='N;'&&$val)
{
foreach($extra_vars as $k=>$v)
{
if(is_array($v))
{
echo "<div>".$k." : ";
foreach($v as $k1=>$v1)
{
echo $v1." ";
}
echo "</div>";
}
else echo "<div>".$k." : ".$v."</div>";
}
}
echo "</td>";
}
}
echo "</tr>";
}
echo "</table>";
echo $bottom;
}
if($display&&!$_SESSION['con_db']) echo $bottom;
?>
댓글 0
서비스 링크
전북 남원시 산내면 천왕봉로 607번지. 010-6306-0494. 063-636-0494. Fax:02-6280-6584. 상표등록.제45-0039447호.
