<%@ Page Language="vb" AutoEventWireup="false" Codebehind="Excel.aspx.vb" Inherits="Test.Excel"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>Exporting Data To Excel, using COM Interop and OWC</title>
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
Export File Name:
<asp:textbox id="xlfile" Runat="server"></asp:textbox><asp:button id="export2excel" Runat="server" Text="Export to Excel"></asp:button><br>
<br>
<asp:datagrid id="DataGrid1" runat="server"></asp:datagrid>
<asp:Button id="Button1" runat="server" Text="Button"></asp:Button></form>
</body>
</HTML>
Imports System.Data.SqlClient
Imports OWC
Public Class Excel
Inherits System.Web.UI.Page
Protected WithEvents xlfile As System.Web.UI.WebControls.TextBox
Protected WithEvents export2excel As System.Web.UI.WebControls.Button
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Private sql As SqlCommand
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Private conn As SqlConnection
#Region " Web 窗体设计器生成的代码 "
'该调用是 Web 窗体设计器所必需的。
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: 此方法调用是 Web 窗体设计器所必需的
'不要使用代码编辑器修改它。
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'在此处放置初始化页的用户代码
BindGrid()
End Sub
Private Sub BindGrid()
conn = New SqlConnection("Initial Catalog=Northwind;Data Source=127.0.0.1;uid=sa;pwd=sa")
sql = New SqlCommand("select * from products", conn)
conn.Open()
Dim reader As SqlDataReader
reader = sql.ExecuteReader()
DataGrid1.DataSource = reader
DataGrid1.DataBind()
reader.Close()
conn.Close()
End Sub
Private Sub WriteDataGrid2Excel()
Dim xlsheet As New SpreadsheetClass()
conn.Open()
Dim reader As SqlDataReader
reader = sql.ExecuteReader()
Dim numbercols As Int16
numbercols = reader.FieldCount
Dim row As Int16 = 1
Dim i As Int16
While reader.Read()
For i = 0 To numbercols - 1
xlsheet.ActiveSheet.Cells(row, i + 1) = reader.GetValue(i).ToString()
Next
row += 1
End While
reader.Close()
conn.Close()
'xlsheet.ActiveSheet.Export()
Try
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\wx.xls", OWC.SheetExportActionEnum.ssExportActionNone)
Catch e As Exception
Dim wx As String
wx = e.Message
End Try
Response.Redirect("wx.xls")
End Sub
Private Sub export2excel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles export2excel.Click
Dim xlApp, xlBook, XlSheet
给你一个 DataList In datagrid example
Imports System.Data.SqlClient
Public Class DataListInGrid
Inherits System.Web.UI.Page
Protected WithEvents Authors_DataGrid As System.Web.UI.WebControls.DataGrid
Private conn As SqlClient.SqlConnection
#Region " Web 窗体设计器生成的代码 "
'该调用是 Web 窗体设计器所必需的。
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: 此方法调用是 Web 窗体设计器所必需的
'不要使用代码编辑器修改它。
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'在此处放置初始化页的用户代码
If Not IsPostBack Then
conn = New SqlConnection("Server=fyp;UID=sa;PWD=sa;Database=Northwind")
conn.Open()
BindData()
End If
End Sub
Sub BindData()
Dim cmdSql As SqlCommand
Dim dapSql As SqlDataAdapter = New SqlDataAdapter()
Dim dtCustomer As New DataSet()
Dim strQ As String
Try
strQ = "select top 10 * from Categories"
cmdSql = New SqlCommand(strQ, conn)
Private Sub Authors_DataGrid_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles Authors_DataGrid.ItemDataBound
If (e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item) Then
Dim Datalist1 As DataList
Dim drv As DataRowView
Dim strID As String
Dim strQ As String
Datalist1 = CType(e.Item.FindControl("Titles_DataList"), DataList)
drv = CType(e.Item.DataItem, DataRowView)
strID = drv("CategoryID").ToString
strQ = "Select * from Products where CategoryID = " & strID
Datalist1.DataSource = GetDataSet(strQ)
Datalist1.DataBind()
End If
End Sub
Function GetDataSet(ByVal strQ As String) As DataSet
Dim cmdSQL As SqlCommand
Dim dapSQL As SqlDataAdapter = New SqlDataAdapter()
Dim dtOrder As New DataSet()
cmdSQL = New SqlCommand(strQ, conn)
dapSQL.SelectCommand = cmdSQL
dapSQL.Fill(dtOrder)
Return dtOrder
End Function
End Class