ASP로 EXCEL 불러오기


 Classic ASP Excel / asp Excel / asp Excel read 


엑셀 파일을 불러와서 정보를 가지고 오는 class.

업무상 필요한 일이 생겨서, 이렇게 class를 만들어 두고 사용하고자 한다.





1. myClass.asp

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
<% 
 
Class TestClass
 
    Public  objConn, strSQL
    Public x 
    Public FieldCount,FieldTitle,ColCount  
 
 
    '생성자 
    Private Sub Class_initialize()     
        Set objConn = Server.CreateObject("ADODB.Connection")  
        objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_  "Excel 8.0; DBQ=" & Server.MapPath(fileName) & "; "  
        strSQL = "SELECT * FROM A1:G1000"  
    
        Set objRS = objConn.Execute(strSQL)  FieldCount = objRS.Fields.Count    
 
        For x=0 To objRS.Fields.Count-1    
            FieldTitle = FieldTitle + objRS.Fields(x).Name & "/"  
        Next    
 
        Do Until objRS.EOF   z= z + 1   
            objRS.movenext  
        Loop  
        ColCount = z 
    End Sub 
 
    '소멸자 
    Private Sub class_terminate()  
        Set objRS = Nothing  
    End Sub  
 
    '출력 
    Public Sub writeln(ByVal message)  
        Response.Write(message & VbCrlf) 
    End Sub 
 
    '필드 수 
    Public Function getFieldNum()  
        getFieldNum = FieldCount 
    End Function 
 
    
    '필드 명(배열로 리턴) 
    Public Function getFieldName()  
        tmpStr = FieldTitle  tmpStr = Split(FieldTitle,"/")  
        getFieldName = tmpStr 
    End Function 
 
 
 
    '행의 길이 
    Public Function getColNum()  
        getColNum = ColCount 
    End Function 
 
 
 
    '내용 
    Public Function getContents()  
        Dim tmpArr(50,1000)  
        Set objConn = Server.CreateObject("ADODB.Connection")  
        objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_  "Excel 8.0; DBQ=" & Server.MapPath(fileName) & "; "  
        strSQL = "SELECT * FROM A1:G1000"  
        
        Set objRS = objConn.Execute(strSQL)  z = 0  
        
        Do Until objRS.EOF   
        
        For y = 0 To objRs.Fields.Count-1     
            tmpArr(y,z) = objRS.Fields(y).Value     
            ' response.write "tmpArr("&y&","&z&") -> " &objRS.Fields(y).Value    
        Next 
 
        z = z + 1  
         'response.write " <br>"   
        objRs.movenext  Loop  getContents = tmpArr 
 
    End FunctionEnd 
 
Class
 
%>
cs






2. readXml.asp


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
<!-- #include file="myClass.asp" --> 
<html>
<title>엑셀파일읽기</title>
<style>
.wrap{width:100%; text-align:center;}
.con{ width:900px;}
.sub_con{float:left; width:500px;}
.info_div{float:left; width:250px; text-align:left; margin-left:40px; color:#1065e7; font-weight:bold;}
#fileName{width:300px; height:50px; line-height:50px; font-size:20pt;}
#nameBtn{width:100px; height:50px; margin-left:50px;  }
.contents{margin-top:20px;}
.wrap{width:100%; float:left;}
 
</style>
<head>
</head>
<script type="text/javascript"></script>
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<body>
<div class="wrap">
    <div class="con">
        <div class="sub_con">
            <form name="fForm" action="http://ke.kaltour.com/test/sg/excel_func/excelPage1.asp" method="get">
                <input type="text" id="fileName" name="fileName" value=""></input>
                <input type="submit" id="nameBtn" value="파일로드"></input>
            </form>
        </div>
        <div class="info_div">
    <%
        Dim fileName
        Dim conArr
        Set fileName = request("fileName")
 
        If fileName <> "" Then
            response.write "<div class='txtArea1'>불러온 파일 : " & fileName & "</div>"
            Dim lib
            Set lib = new TestClass
 
 
            response.write "필드 수 :  " & lib.getFieldNum() & "  </br>"
            fieldNameStr = lib.getFieldName()
            response.write "result :  " & lib.getColNum() & "  </br>"
    %>
        </div>
    </div>
    
    <div class="con_div">
    <%
            conArr = lib.getContents()
            response.write "<table border='1'><thead>"
            response.write "<tr><th>seq</th>"
            For cnt =0 To UBound(fieldNameStr)-1
                If cnt < 4 then
                    response.write "<th>"&fieldNameStr(cnt) & " </th> "
                End if
            Next 
            response.write "</tr>"
            response.write "</thead><tbody>"
 
 
            response.write "<tbody>"
            seq = 1
            For i = 0 To lib.getColNum()-1
                response.write "<tr><td bgcolor='yellow' align='center'>"& seq &"</td>"
                For j = 0 To lib.getFieldNum()-1
                    If j < 4 then
                        response.write "<td>" & conArr(j,i) & "</td>"
                    End If 
                Next
                seq = seq +1
                response.write "</tr>"
            Next 
 
            response.write "</tbody></table>"
 
        Else
            response.write "<div class='txtArea1'>파일 이름을 확인해주세요~</div>"
        End If 
        Set lib = Nothing     
    %>
    </div>
    
</div>
</body>
 
</html>
 
 
cs







저작자표시

'웹프로그래밍 > ASP' 카테고리의 다른 글

[ ASP ] Dim 변수만 사용하도록 설정  (0) 2019.11.13
[ ASP ] 쿼리 결과 배열에 복사해서 사용 / GetRows()  (0) 2019.11.13
[ ASP ] POST  (0) 2016.05.09
[ ASP ] ASP JSON / ASP JSON 출력 / ASP JSON 생성  (0) 2015.08.21

+ Recent posts

티스토리툴바