001.<%@ Import Namespace="System.Data"%>
002.<%@ Import Namespace="System.Data.OleDb"%>
003.<%@ Import Namespace="Excel"%>
004.<%@ Import Namespace="System.IO"%>
005.<%@ Page Language="VB" %>
006.<script runat="server">
007. Sub Page_Load(sender As Object, e As EventArgs)
008. Dim objConn As New OleDbConnection
009. Dim dtAdapter As OleDbDataAdapter
010. Dim dt As New System.Data.DataTable
011.
012. Dim strConnString As String
013. strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database/mydatabase.mdb") & ";"
014. objConn = New OleDbConnection(strConnString)
015. objConn.Open()
016.
017. Dim strSQL As String
018. strSQL = "SELECT * FROM customer"
019.
020.
021. dtAdapter = New OleDbDataAdapter(strSQL, objConn)
022. dtAdapter.Fill(dt)
023.
024.
025. Dim FileName As String = "MyXls/MyExcel.xls"
026.
027.
028. Dim xlApp As New Excel.Application
029. Dim xlSheet As Excel.Worksheet
030. Dim xlBook As Excel.Workbook
031. Dim i, intStartRows As Integer
032.
033. xlBook = xlApp.Workbooks.Add()
034. xlSheet = xlBook.Worksheets(1)
035.
036. xlApp.Application.Visible = False
037.
038.
039. xlBook.Worksheets(2).Select()
040. xlBook.Worksheets(2).Delete()
041. xlBook.Worksheets(2).Select()
042. xlBook.Worksheets(2).Delete()
043.
044. xlBook.ActiveSheet.Name = "Customer"
045.
046. With xlBook.Sheets("Customer").Cells(1,1)
047. .Value = "Customer Name"
048. .Font.Name = "Tahoma"
049. .BORDERS.Weight = 1
050. .Font.Size = 10
051. .MergeCells = True
052. End With
053.
054. With xlBook.Sheets("Customer").Cells(1,2)
055. .Value = "Budget"
056. .BORDERS.Weight = 1
057. .Font.Name = "Tahoma"
058. .Font.Size = 10
059. .MergeCells = True
060. End With
061.
062. With xlBook.Sheets("Customer").Cells(1,3)
063. .Value = "Used"
064. .BORDERS.Weight = 1
065. .Font.Name = "Tahoma"
066. .Font.Size = 10
067. .MergeCells = True
068. End With
069.
070. intStartRows = 2
071. For i = 0 To dt.Rows.Count - 1
072. xlBook.Sheets("Customer").Cells(intStartRows+i,1).Value = dt.Rows(i)("Name")
073. xlBook.Sheets("Customer").Cells(intStartRows+i,2).Value = dt.Rows(i)("Budget")
074. xlBook.Sheets("Customer").Cells(intStartRows+i,2).NumberFormat = "$#,##0.00"
075. xlBook.Sheets("Customer").Cells(intStartRows+i,3).Value = dt.Rows(i)("Used")
076. xlBook.Sheets("Customer").Cells(intStartRows+i,3).NumberFormat = "$#,##0.00"
077.
078. Next
079.
080. Dim objRange,colCharts,objChart As Object
081. objRange = xlBook.Sheets("Customer").UsedRange
082. objRange.Select
083.
084. colCharts = xlApp.Charts
085. colCharts.Add()
086. objChart = colCharts(1)
087. xlApp.ActiveChart.Name = "MyChart"
088. With objChart
089. .ChartType = 92
090. .HasLegend = True
091. .HasTitle = 1
092. .ChartTitle.Text = "Customer Report"
093. .ChartTitle.Characters.Text = "Customer Report"
094. .ChartTitle.Font.Name = "Tahoma"
095. .ChartTitle.Font.FontStyle = "Bold"
096. .ChartTitle.Font.Size = 30
097. .ChartTitle.Font.ColorIndex = 3
098. .Activate
099. End With
100.
101.
102. Dim MyFile As New FileInfo(Server.MapPath(FileName))
103. If MyFile.Exists Then
104. MyFile.Delete()
105. End IF
106. MyFile = Nothing
107.
108.
109.
110. xlSheet.SaveAs(Server.MapPath(FileName))
111.
112.
113. xlApp.Application.Quit()
114. xlApp.Quit()
115.
116.
117. xlSheet = Nothing
118. xlBook = Nothing
119. xlApp = Nothing
120.
121.
122. Me.lblText.Text = "Charts Created <a href=" & FileName & ">Click here</a> to Download."
123.
124. dtAdapter = Nothing
125. objConn.Close()
126. objConn = Nothing
127. End Sub
128.</script>
129.<html>
130.<head>
131.<title>ThaiCreate.Com ASP.NET - Excel Application</title>
132.</head>
133.<body>
134. <form id="form1" runat="server">
135. <asp:Label id="lblText" runat="server"></asp:Label>
136. </form>
137.</body>
138.</html>
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
