ms-sql 엑셀 파일로 저장

아직 검증되지는 않음!

출처 : http://levin01.tistory.com/787

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

''''''' filename : fts_xena_makeExcel.asp

 

<%
functionmakeExcelSave_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

 

<%
functionget_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.writeRS.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
%>

 

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

 

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$