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
011.
Dim
bXlsFile,FileName,Ext,ExlName
012.
013.
014.
FileName =
"MyXls/MyChart.Gif"
015.
Ext =
"Gif"
016.
017.
018.
ExlName =
"MyXls/MyChart.xls"
019.
020.
Set
Conn = Server.Createobject(
"ADODB.Connection"
)
021.
Conn.Open
"DRIVER=Microsoft Access Driver (*.mdb);DBQ="
& Server.MapPath(
"mydatabase.mdb"
),
""
,
""
022.
strSQL =
"SELECT Name,Used FROM customer "
023.
Set
objRec = Server.CreateObject(
"ADODB.Recordset"
)
024.
objRec.Open strSQL, Conn, 1,3
025.
026.
If
Not
objRec.EOF and
Not
objRec.BOF
Then
027.
arrCus = objRec.GetRows()
028.
End
If
029.
030.
intStartRows = 3
031.
intEndRows =
CInt
(intStartRows)+
CInt
(Ubound(arrCus,2))
032.
033.
objRec.Close
034.
Conn.Close
035.
Set
objRec =
Nothing
036.
Set
Conn =
Nothing
037.
038.
039.
Set
xlApp = Server.CreateObject(
"Excel.Application"
)
040.
Set
xlBook = xlApp.Workbooks.Add
041.
Set
xlSheet = xlBook.Worksheets(1)
042.
043.
xlApp.Application.Visible =
False
044.
045.
046.
xlBook.Worksheets(2).
Select
047.
xlBook.Worksheets(2).Delete
048.
xlBook.Worksheets(2).
Select
049.
xlBook.Worksheets(2).Delete
050.
051.
052.
xlBook.Worksheets(1).Name =
"MyReport"
053.
xlBook.Worksheets(1).
Select
054.
055.
With
xlBook.ActiveSheet.Cells(1,1)
056.
.Value =
"My Customer"
057.
.Font.Bold =
True
058.
.Font.Name =
"Tahoma"
059.
.Font.Size = 16
060.
End
With
061.
062.
With
xlBook.ActiveSheet.Cells(2,1)
063.
.Value =
"Customer Name"
064.
.Font.Name =
"Tahoma"
065.
.BORDERS.Weight = 1
066.
.Font.Size = 10
067.
.MergeCells =
True
068.
End
With
069.
070.
With
xlBook.ActiveSheet.Cells(2,2)
071.
.Value =
"Used"
072.
.BORDERS.Weight = 1
073.
.Font.Name =
"Tahoma"
074.
.Font.Size = 10
075.
.MergeCells =
True
076.
End
With
077.
078.
For
i = 0
To
Ubound(arrCus,2)
079.
xlBook.ActiveSheet.Cells(intStartRows+i,1).Value = arrCus(0,i)
080.
xlBook.ActiveSheet.Cells(intStartRows+i,2).Value = arrCus(1,i)
081.
xlBook.ActiveSheet.Cells(intStartRows+i,2).NumberFormat =
"$#,##0.00"
082.
Next
083.
084.
085.
086.
xlBook.Charts.Add
087.
xlBook.ActiveChart.SetSourceData (xlBook.Sheets(
"MyReport"
).Range(
"A"
&intStartRows&
":B"
&intEndRows&
""
))
088.
089.
090.
xlBook.ActiveChart.Location 2,
"MyReport"
091.
092.
093.
With
xlBook.ActiveChart
094.
.ChartType = 55
095.
.PlotBy = 1
096.
.HasTitle =
True
097.
.HasAxis(1) = 0
098.
.ChartTitle.Characters.Text =
"Customer Report"
099.
.ChartTitle.Font.Name =
"Tahoma"
100.
.ChartTitle.Font.FontStyle =
"Bold"
101.
.ChartTitle.Font.Size = 15
102.
.ChartTitle.Font.ColorIndex = 3
103.
End
With
104.
105.
106.
xlBook.ActiveSheet.Shapes(
"Chart 1"
).IncrementLeft 20
107.
xlBook.ActiveSheet.Shapes(
"Chart 1"
).IncrementTop -97.5
108.
109.
110.
111.
112.
113.
114.
115.
Set
Fso = CreateObject(
"Scripting.FileSystemObject"
)
116.
117.
118.
If
(Fso.FileExists(Server.MapPath(FileName)))
Then
119.
Set
MyFile = Fso.GetFile(Server.MapPath(FileName))
120.
MyFile.Delete
121.
End
If
122.
123.
xlApp.ActiveChart.Export Server.MapPath(FileName),Ext
124.
125.
126.
If
(Fso.FileExists(Server.MapPath(ExlName)))
Then
127.
Set
MyFile = Fso.GetFile(Server.MapPath(ExlName))
128.
MyFile.Delete
129.
End
If
130.
xlSheet.SaveAs Server.MapPath(ExlName)
131.
132.
xlApp.Application.Quit
133.
134.
Set
xlSheet =
Nothing
135.
Set
xlBook =
Nothing
136.
Set
xlApp =
Nothing
137.
%>
138.
<strong>Charts Created</strong><br><br><img src=
"<%=FileName%>"
><br><br>
139.
Excel Created <a href=
"<%=ExlName%>"
>Click here</a> to Download.
140.
</body>
141.
</html>