mssql_엑셀파일로 저장/다운받기

by 조쉬 posted Feb 27, 2014
?

단축키

Prev이전 문서

Next다음 문서

ESC닫기

크게 작게 위로 아래로 댓글로 가기 인쇄
<%
function makeExcelSave_byXena( t_Timeout, saveContents, SaveFilename)
'Auth : shkim (isunnyk@empal.com / idxzone@naver.com)
'Start Date : 2004.02.07.Sat
'Last Date :
'Description : 
 'SaveFilename = "xenaTestFile.xls" '확장자도 지정할것..
dim errMsg
 if saveContents="" then '파일에 저장할 내용이 없다면.
errMsg = "<p>[Wrong] : 파일에 저장할 내용이 없으므로, 엑셀파일을 만들지 않습니다."
response.write errMsg
exit function
end if
 if t_Timeout = "" then
errMsg = "<p>[Error] : 시간을 지정하시오."
response.write errMsg
exit function
end if
if SaveFilename = "" then
dim y, m, d
y = year(now())
m = month(now() )
if m <10 then
m = "0" & m 
end if
d = day(now())
if d <10 then
d = "0" & d
end if
SaveFilename = y & "_" & m & "_" & d
SaveFilename = SaveFilename & ".xls"
end if

 'SaveFilename = Request("SaveFilename")
Server.ScriptTimeout = t_Timeout 
response.contenttype="application/vnd.ms-excel" 
Response.AddHeader "Content-Disposition","attachment;filename=" & SaveFilename

response.write saveContents

makeExcelSave_byXena = true
end function
%>
 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
 ''''''' filename : fts_xena_QueryTable.asp

 <%
function get_Data_fromTable_byXena(dbconn, table, title, sel_top, where)
'Auth : shkim (isunnyk@empal.com / idxzone@naver.com)
'Start Date : 2004.02.07.Sat
'Last Date :
'Description : 
 dim errMsg
 if not isObject(dbconn) then '객체가 없다면...
errMsg = "<br>[Error] : DB 객체가 없습니다."
response.write errMsg
exit function
end if
'response.write dbconn
 if table="" then
errMsg = "<br>[Error] : table 이 지정되지 않았습니다."
response.write errMsg
exit function
end if

'''''''' 
dim xenaNL
xenaNL = vbCrLf '개행.... chr(10), chr(13)..

 '''''''' 쿼리하기```````````
dim sql, RS
sql = "SELECT " & sel_top & " * FROM " & table & where
set RS = Server.CreateObject("ADODB.Recordset")
RS.Open sql, dbconn

 'response.write RS.Fields.Count & "<hr>" 'mysql_field_count() 칼럼의 갯수..
dim TBL_Rtn
TBL_Rtn = ""
 if ( RS.BOF and RS.EOF ) then '레코드셋에 아무값도 존재하지 않는다.
response.write "등록된 자료가 없습니다.<hr>"
else
dim i
 ''''' 타이틀 적기..
if title="" then
title = table
end if
title = "<TR><TD colspan='" & RS.Fields.Count & "'>" & title & "</TD></TR>"
''''' 타이틀 적기..

''''칼럼명 추출하기..
dim TR_head, col_name
TR_head = ""
for i=0 To RS.Fields.Count-1
col_name = RS.Fields.Item(i).Name 
TR_head = TR_head & "<TH>"& col_name & "</TH>"
 'response.write col_name & "<br>"
next
if TR_head<>"" then '값이 있다면..
TR_head = xenaNL &_
"<TR>" & TR_head & "</TR>" & xenaNL
end if
''''칼럼명 추출하기..
 ''''내용 추출하기.
dim TR_BODY, row_val, TDs
TR_BODY = ""
DO Until RS.EOF
'''td..td'''''''''
TDs = ""
for i=0 To RS.Fields.Count-1
row_val = RS.Fields.Item(i)
'response.write row_val & " " & chr(9)
TDs = TDs & "<TD valign='top'>" & row_val & "</TD>"
next
if TDs<>"" then
TDs = "<TR>" & TDs & "</TR>" & xenaNL
end if
'''td..td'''''''''
 TR_BODY = TR_BODY & TDs & xenaNL
'response.write "<br>"
 RS.MoveNext
Loop
''''내용 추출하기.

if ( TR_head<>"" or TR_BODY<>"" ) then
TBL_Rtn = "<TABLE BORDER=1>" &_
title &_
TR_head & TR_BODY & "</TABLE>" & xenaNL
end if
end if

 'response.write TBL_Rtn
'리소스 해제
RS.Close
set RS = Nothing
'''''''' 쿼리하기```````````

get_Data_fromTable_byXena = TBL_Rtn ''값을 리턴하기..
end function
%>
 $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
 ''''''''''filename : this_makeExcel.asp

 <%
option explicit
%>

 <%
dim homeUrl
xena_local_addr = Request.ServerVariables("HTTP_HOST")
homeUrl = "http://" & xena_local_addr
 if "" = session("mUserID") then 
response.redirect homeUrl
end if
if "9999" <> session("mAdminCode") then '관리자가 아니라면..
response.redirect homeUrl
end if
%>

 <!--#include file="../inc/dbconn__2.asp"-->
 <!--#include file="fts_xena_makeExcel.asp"-->
<!--#include file="fts_xena_QueryTable.asp"-->
 <%
''''''''''''''''''''''''''''''''
dim y, m, d, curDate
y = year(now())
m = month(now() )
if m <10 then
m = "0" & m 
end if
d = day(now())
if d <10 then
d = "0" & d
end if
curDate = y & "_" & m & "_" & d
''''''''''''''''''''''''''''''''
 dim t_Timeout
t_Timeout = 90000

dim table, title, where, sel_top, SaveFilename 
table = Request("Xtable")
 '테이블명은 대소문자 구분한다.
if table="mBoard" then '답변글 순서대로..
 ''게시판 종류가....즉, 게시판 코드가.. 1001, 1002, 1004
dim boardCode 
boardCode = Request("XBcode")
if (boardCode="") then
else
select case boardCode
case 1
title = "영문법/토익"
SaveFilename = "xls_영문법토익" & curDate & ".xls"
boardCode = "100" & boardCode
case 2
title = "SCREEN영어"
SaveFilename = "xls_SCREEN영어" & curDate & ".xls"
boardCode = "100" & boardCode
case 4
title = "어학상담실 게시판"
SaveFilename = "xls_어학상담실게시판" & curDate & ".xls"
boardCode = "100" & boardCode
end select
end if
''게시판 종류가....즉, 게시판 코드가.. 1001, 1002, 1004
 where = " WHERE bbs_code='" & boardCode & "' ORDER BY bbs_num DESC , bbs_parentno ASC "
 elseif (table="mStudy") then 
title = "출석현황"
SaveFilename = "xls_수강생_출석현황" & curDate & ".xls"
where = " ORDER BY uid ASC, viewtitle ASC, viewdate ASC "
'where = " ORDER BY uname ASC, viewtitle ASC, viewdate ASC "
 t_Timeout = 200000 ''''출석현황...4000개가 넘는다... 시간을 넉넉히..
elseif (table="mUser") then 
title = "수강생현황"
SaveFilename = "xls_수강생현황" & curDate & ".xls"
else
table = ""
response.write "<p>[잘못된 접근입니다.]"
''exit...........................
end if
 dim xls_body_msg
dim saved, saveContents
if (table <> "") then
xls_body_msg = get_Data_fromTable_byXena(dbconn , table, title , sel_top, where)
 'response.write xls_body_msg
saveContents = xls_body_msg
 saved = makeExcelSave_byXena( t_Timeout, saveContents, SaveFilename)
'response.write saved
end if
%>