001.<% Option Explicit %>
002.<html>
003.<head>
004.<title>ThaiCreate.Com ASP Excel.Application Tutorial</title>
005.</head>
006.<body>
007.<%
008. Dim xlApp,xlBook,xlSheet
009. Dim Conn,strSQL,objRec,arrCus,intStartRows,intEndRows,i
010. Dim Fso,MyFile,objRange,colCharts,objChart
011. Dim bXlsFile,FileName,Ext
012.
013.
014. FileName = "MyXls/MyChart.Gif"
015. Ext = "Gif"
016.
017. Set Conn = Server.Createobject("ADODB.Connection")
018. Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("mydatabase.mdb"),"" , ""
019. strSQL = "SELECT Name,Budget,Used FROM customer "
020. Set objRec = Server.CreateObject("ADODB.Recordset")
021. objRec.Open strSQL, Conn, 1,3
022.
023. If Not objRec.EOF and Not objRec.BOF Then
024. arrCus = objRec.GetRows()
025. End If
026.
027. intStartRows = 2
028. intEndRows = CInt(intStartRows)+CInt(Ubound(arrCus,2))
029.
030. objRec.Close
031. Conn.Close
032. Set objRec = Nothing
033. Set Conn = Nothing
034.
035.
036. Set xlApp = Server.CreateObject("Excel.Application")
037. Set xlBook = xlApp.Workbooks.Add
038. Set xlSheet = xlBook.Worksheets(1)
039.
040. xlApp.Application.Visible = False
041.
042.
043. xlBook.Worksheets(2).Select
044. xlBook.Worksheets(2).Delete
045. xlBook.Worksheets(2).Select
046. xlBook.Worksheets(2).Delete
047.
048.
049. xlBook.Worksheets(1).Name = "MyReport"
050. xlBook.Worksheets(1).Select
051.
052. With xlBook.ActiveSheet.Cells(1,1)
053. .Value = "Customer Name"
054. .Font.Name = "Tahoma"
055. .BORDERS.Weight = 1
056. .Font.Size = 10
057. .MergeCells = True
058. End With
059.
060. With xlBook.ActiveSheet.Cells(1,2)
061. .Value = "Budget"
062. .BORDERS.Weight = 1
063. .Font.Name = "Tahoma"
064. .Font.Size = 10
065. .MergeCells = True
066. End With
067.
068. With xlBook.ActiveSheet.Cells(1,3)
069. .Value = "Used"
070. .BORDERS.Weight = 1
071. .Font.Name = "Tahoma"
072. .Font.Size = 10
073. .MergeCells = True
074. End With
075.
076. For i = 0 To Ubound(arrCus,2)
077. xlBook.ActiveSheet.Cells(intStartRows+i,1).Value = arrCus(0,i)
078. xlBook.ActiveSheet.Cells(intStartRows+i,2).Value = arrCus(1,i)
079. xlBook.ActiveSheet.Cells(intStartRows+i,2).NumberFormat = "$#,##0.00"
080. xlBook.ActiveSheet.Cells(intStartRows+i,3).Value = arrCus(2,i)
081. xlBook.ActiveSheet.Cells(intStartRows+i,3).NumberFormat = "$#,##0.00"
082. Next
083.
084.
085. Set objRange = xlBook.Sheets("MyReport").UsedRange
086. objRange.Select
087.
088.
089. xlBook.Charts.Add
090.
091.
092. xlBook.ActiveChart.Location 2, "MyReport"
093.
094.
095. With xlBook.ActiveChart
096. .ChartType = 98
097. .HasLegend = True
098. .HasTitle = 1
099. .ChartTitle.Text = "Customer Report"
100. End With
101.
102.
103. With xlBook.ActiveChart.Legend
104. .Font.Name = "Arial"
105. .Font.Size = 5
106. End With
107.
108.
109.
110. xlBook.ActiveSheet.Shapes("Chart 1").IncrementLeft 20
111. xlBook.ActiveSheet.Shapes("Chart 1").IncrementTop -97.5
112.
113.
114.
115. xlBook.ActiveSheet.Shapes("Chart 1").ScaleHeight 2.0, 0,0
116. xlBook.ActiveSheet.Shapes("Chart 1").ScaleWidth 1.5, 0,0
117.
118.
119. Set Fso = CreateObject("Scripting.FileSystemObject")
120. If (Fso.FileExists(Server.MapPath(FileName))) Then
121. Set MyFile = Fso.GetFile(Server.MapPath(FileName))
122. MyFile.Delete
123. End If
124.
125.
126.
127. xlApp.ActiveChart.Export Server.MapPath(FileName),Ext
128.
129. xlApp.Application.Quit
130.
131. Set xlSheet = Nothing
132. Set xlBook = Nothing
133. Set xlApp = Nothing
134.%>
135.<strong>Charts Created</strong><br><br>
136.<img src="<%=FileName%>">
137.</body>
138.</html>
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
