I am displaying the contents of a dataset to my customer
as an excel spreadsheet and giving them the opportunity to
save the spreadsheet to their local machine. The
spreadsheet has approx 650 rows and 75 columns. The
following code works fine (without the two lines following
the response.end), as far as generating the spreadsheet,
but I added those two lines (the ActiveWindow lines)to try
to freeze the first row so that the headers are always
visible.
I need to do this or flip my columns and rows. Any help
will be appreciated.
Thanks
Jack
Imports System.Data.OleDb
Imports Excel.ApplicationClass
Public Class MSView
Inherits System.Web.UI.Page
Protected WithEvents Label1 As
System.Web.UI.WebControls.Label
Protected WithEvents btnView As
System.Web.UI.WebControls.Button
Protected WithEvents OleDbSelectCommand1 As
System.Data.OleDb.OleDbCommand
Protected WithEvents cnMasterSched As
System.Data.OleDb.OleDbConnection
Protected WithEvents daMasterSched As
System.Data.OleDb.OleDbDataAdapter
Protected WithEvents dsMasterSched As
MasterSchedule2003.dsMasterSched
Protected WithEvents dgMS As
System.Web.UI.WebControls.DataGrid
Dim dsMSched As DataSet
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Dim configurationAppSettings As
System.Configuration.AppSettingsReader = New
System.Configuration.AppSettingsReader()
Me.cnMasterSched = New
System.Data.OleDb.OleDbConnection()
Me.daMasterSched = New
System.Data.OleDb.OleDbDataAdapter()
Me.OleDbSelectCommand1 = New
System.Data.OleDb.OleDbCommand()
Me.dsMasterSched = New
MasterSchedule2003.dsMasterSched()
CType(Me.dsMasterSched,
System.ComponentModel.ISupportInitialize).BeginIni t()
'
'cnMasterSched
'
Me.cnMasterSched.ConnectionString
= "Provider=OraOLEDB.Oracle.1;Persist Security
Info=False;User ID=master_schedule;Da" & _
"ta Source=ftest920;Extended Properties="""""
'
'daMasterSched
'
Me.daMasterSched.AcceptChangesDuringFill = CType
(configurationAppSettings.GetValue
("daMasterSched.AcceptChangesDuringFill", GetType
(System.Boolean)), Boolean)
Me.daMasterSched.SelectCommand =
Me.OleDbSelectCommand1
Me.daMasterSched.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "Table", New
System.Data.Common.DataColumnMapping(-1) {})})
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT" &
Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW
(10) & " DIR_MASTER.*" & Microsoft.VisualBasic.ChrW(13)
& Microsoft.VisualBasic.ChrW(10) & "FROM" &
Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW
(10) & " DIR_MASTER" & Microsoft.VisualBasic.ChrW(13) &
Microsoft.VisualBasic.ChrW(10)
Me.OleDbSelectCommand1.Connection =
Me.cnMasterSched
'
'dsMasterSched
'
Me.dsMasterSched.DataSetName = "dsMasterSched"
Me.dsMasterSched.Locale = New
System.Globalization.CultureInfo("en-US")
Me.dsMasterSched.Namespace
= "http://www.tempuri.org/dsMasterSched.xsd"
CType(Me.dsMasterSched,
System.ComponentModel.ISupportInitialize).EndInit( )
End Sub
Private Sub Page_Init(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web
Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
Dim ActiveWindow As Excel.Window
Dim strsql As String
Dim dgMS As New DataGrid()
Dim cnMasterSched As New OleDbConnection(Session
("ConnectionString"))
Try
cnMasterSched.Open()
strsql = "Select * from dir_master"
daMasterSched.SelectCommand = New OleDbCommand
(strsql, cnMasterSched)
daMasterSched.Fill(dsMasterSched)
Dim oView As New DataView(dsMasterSched.Tables
(0))
dgMS.DataSource = oView
dgMS.DataBind()
Me.btnView.Visible = False
Response.ContentType = "application/vnd.ms-
excel"
Response.Charset = ""
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
dgMS.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
ActiveWindow.RangeFromPoint("A1", "A1")
ActiveWindow.FreezePanes = True
Catch err As System.SystemException
Finally
If cnMasterSched.State = ConnectionState.Open
Then
cnMasterSched.Dispose()
cnMasterSched = Nothing
End If
End Try
End Sub
Private Sub btnView_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnView.Click
End Sub
Private Sub Page_Unload(ByVal sender As Object, ByVal
e As System.EventArgs) Handles MyBase.Unload
cnMasterSched.Dispose()
cnMasterSched = Nothing
End Sub
End Class