특히 html 속성 지정부터 xml형식으로 지정을 해서 엑셀 양식을 잘 맞추었음.
----------------------------------------------------------------------------------
정확히 말하면 Excel 파일이 아니라 엑셀에서 읽을 수 있는 HTML 파일로 출력한
다음 자동으로 다운로드되게 하는 방법이다...
소스가 길기 때문에 줄임.
<%
'파일 이름 설정
Dim tmp_filename
tmp_filename = "Members_List.xls"
' 헤더설정
Response.ContentType = "application/unknown"
Response.AddHeader "Content-Disposition","attachment; filename=" & tmp_filename
%>
<!-- #include virtual="/include/db_conn.asp" -->
<%
' 회원 데이터베이스 불러오기
Dim SQL, rs
SQL="Select * From wj_members Order By memwritedate Desc"
Set rs=CreateObject("ADODB.Recordset")
rs.Open SQL, Conn, 1
' 회원 정보 다시구성을 위한 변수 선언
Dim jumin, post, phone, handphone, val_class, job
' 엑셀파일 작성
Dim fso, act, objStream, download
Set fso = Server.CreateObject("Scripting.FileSystemObject")
Set act = fso.CreateTextFile(Server.MapPath("\upload") & "\" & tmp_filename, true)
act.WriteLine "<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">"
act.WriteLine " <head>"
act.WriteLine " <!--[if gte mso 9]><xml>"
act.WriteLine " <x:ExcelWorkbook>"
act.WriteLine " <x:ExcelWorksheets>"
act.WriteLine " <x:ExcelWorksheet>"
act.WriteLine " <x:Name>Members</x:Name>"
act.WriteLine " <x:WorksheetOptions>"
act.WriteLine " <x:Print>"
act.WriteLine " <x:ValidPrinterInfo/>"
act.WriteLine " </x:Print>"
act.WriteLine " </x:WorksheetOptions>"
act.WriteLine " </x:ExcelWorksheet>"
act.WriteLine " </x:ExcelWorksheets>"
act.WriteLine " </x:ExcelWorkbook>"
act.WriteLine " </xml>"
act.WriteLine " <![endif]--> "
act.WriteLine " <style>"
act.WriteLine " <!--"
act.WriteLine " .xl24"
act.WriteLine " {mso-style-parent:style0;"
act.WriteLine " font-size:12.0pt;"
act.WriteLine " font-weight:700;"
act.WriteLine " font-family:돋움, monospace;"
act.WriteLine " mso-font-charset:129;"
act.WriteLine " border-top:none;"
act.WriteLine " border-right:1.0pt solid windowtext;"
act.WriteLine " border-bottom:1.0pt solid windowtext;"
act.WriteLine " border-left:none;"
act.WriteLine " background:#CCFFCC;"
act.WriteLine " mso-pattern:auto none;"
act.WriteLine " white-space:normal;}"
act.WriteLine " .xl25"
act.WriteLine " {mso-style-parent:style0;"
act.WriteLine " font-size:12.0pt;"
act.WriteLine " font-weight:700;"
act.WriteLine " font-family:돋움, monospace;"
act.WriteLine " mso-font-charset:129;"
act.WriteLine " border-top:none;"
act.WriteLine " border-right:none;"
act.WriteLine " border-bottom:1.0pt solid windowtext;"
act.WriteLine " border-left:none;"
act.WriteLine " background:#CCFFCC;"
act.WriteLine " mso-pattern:auto none;"
act.WriteLine " white-space:normal;}"
act.WriteLine " -->"
act.WriteLine " </style>"
act.WriteLine " </head>"
act.WriteLine " <body>"
act.WriteLine " <table>"
act.WriteLine " <tr height=25>"
act.WriteLine " <td class=xl24>"
act.WriteLine " 아이디"
act.WriteLine " </td>"
act.WriteLine " <td class=xl24>"
act.WriteLine " 비밀번호"
act.WriteLine " </td>"
act.WriteLine " <td class=xl24>"
act.WriteLine " 회원 이름"
act.WriteLine " </td>"
.
.
. (계속 필드 이름을 써주면 됨)
.
act.WriteLine " </tr>"
Do until rs.Eof
act.WriteLine " <tr height=18>"
act.WriteLine " <td>"
act.WriteLine " " & rs( "memid" )
act.WriteLine " </td>"
act.WriteLine " <td>"
act.WriteLine " " & rs( "mempassword" )
act.WriteLine " </td>"
act.WriteLine " <td>"
act.WriteLine " " & rs( "memname" )
act.WriteLine " </td>"
.
.
. (계속 각 튜플들의 필드저장값을 써주면 됨)
.
act.WriteLine " </tr>"
rs.movenext
Loop
act.WriteLine " </table>"
act.WriteLine " </body>"
act.WriteLine "</html>"
act.close
' 파일 자동 다운로드
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = 1
objStream.LoadFromFile Server.MapPath("\upload") & "\" & tmp_filename
download = objStream.Read
Response.BinaryWrite download
Set objstream = nothing
' 임시저장파일 삭제
If fso.FileExists(Server.MapPath("\upload") & "\" & tmp_filename) = "True" Then
fso.DeleteFile(Server.MapPath("\upload") & "\" & tmp_filename)
End If
%>
[출처] http://seira.pe.kr/26
