User730785594 posted
I used different code entirely because I realized I needed to open Excel with more than one tab. Once I did that, the warning prompt was no longer displayed. Here's my code if you'd like to give it a try:
Sub GenerateThreeTabs(ByVal inParam
As Integer)
Dim ds As
New DataSet
Dim connString
As String = _
ConfigurationManager.ConnectionStrings(
"ConnectToMyDb").ConnectionString
Dim conn As
New SqlConnection(connString)Dim command
As New SqlCommand()
command.CommandType = CommandType.StoredProcedure
command.Connection = conn
command.CommandText =
"up_MyStoredProcedure"
command.Parameters.Add(New SqlParameter("@param", Data.SqlDbType.Int))
command.Parameters(
"@param").Value = inParam
Dim adapter As
New SqlDataAdapter(command)
Try
adapter.Fill(ds)
Catch ex As ExceptionResponse.Write("Submit() Exception "
+ ex.Message)
conn.Close()
conn.Dispose()
conn =
Nothing
Exit Sub
End Try
' Create Excel Application, Workbook, and WorkSheets
Dim xlExcel
As New Excel.Application
Dim xlBooks
As Excel.Workbooks
Dim xlBook As Excel.Workbook
Dim xlSheets
As Excel.Sheets
Dim stdSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim sFile As
String
Dim rescSheet
As Excel.Worksheet
Dim rescSheet2
As Excel.Worksheet
sFile = Server.MapPath(Request.ApplicationPath) &
"\Excel.xls"
xlExcel.Visible =
False : xlExcel.DisplayAlerts =
False
' Get all workbooks and open first workbook
xlBooks = xlExcel.Workbooks
xlBooks.Open(Server.MapPath(Request.ApplicationPath) & "\Commissions.xls")
xlBook = xlBooks.Item(1)
' Get all sheets available in first book
xlSheets = xlBook.Worksheets
' Process the summary sheet with results from stored proc's first query - tables(0).
stdSheet = CType(xlSheets.Item(1), Excel.Worksheet)
xlCells = stdSheet.Cells
GenerateSummaryTab(ds.Tables(0), xlCells)
' Get the month details sheet with results from stored proc's second query - tables(1).
rescSheet = CType(xlSheets.Item(2), Excel.Worksheet)
xlCells = rescSheet.Cells
GenerateMonthDetails(ds.Tables(1), xlCells)
' Get the history sheet with results from stored proc's third query - tables(2).
' Also pass in the agent's name from tables(0).
rescSheet2 = CType(xlSheets.Item(3), Excel.Worksheet)
xlCells = rescSheet2.Cells
GenerateMeterSignupHistoryTab(ds.Tables(0).Rows(0).Item("Agent").ToString, ds.Tables(2), xlCells) xlExcel.Visible =
True
End Sub
Private
Sub GenerateMonthDetails(ByRef table
As DataTable,
ByVal xlCells As Excel.Range)
Dim dr As DataRow, ary()
As Object
Dim iRow As
Integer, iCol
As Integer
'Output Column Headers
For iCol = 0
To table.Columns.Count - 1
xlCells(1, iCol + 1) = table.Columns(iCol).ToString
Next
'Output Data
Try
For iRow = 0
To table.Rows.Count - 1
dr = table.Rows.Item(iRow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
xlCells(iRow + 2, iCol + 1) = ary(iCol).ToString
Next
Next
Catch ex
As Exception
Response.Write(ex.Message)
End Try
End Sub
Private
Sub GenerateSummaryTab(ByRef table
As DataTable,
ByVal xlCells As Excel.Range)
xlCells(1, 2) = table.Rows(0).Item(
"Agent").ToString
xlCells(5, 2) = table.Rows(0).Item("Commission").ToString
xlCells(5, 3) = table.Rows(0).Item(
"Usage").ToString
xlCells(5, 4) = table.Rows(0).Item("Storage").ToString xlCells(5, 5) = table.Rows(0).Item("Margin").ToString
End Sub
Private
Sub GenerateMeterSignupHistoryTab(ByVal sAgentName
As String,
ByRef table As DataTable,
ByVal xlCells
As Excel.Range)
Dim iRow As
Integer
xlCells(1, 1) = sAgentName
For iRow = 0 To table.Rows.Count - 1
xlCells(iRow + 2, 2) = table.Rows(iRow).Item(
"mnth").ToString
xlCells(iRow + 2, 3) = table.Rows(iRow).Item("cnt").ToString
Next
End Sub
Public
Overrides Sub VerifyRenderingInServerForm(ByVal control
As System.Web.UI.Control)
End Sub
HTH and good luck. What a pain this problem was!
Export GridView to Excel (xlsx, Excel 2007 Format)
developed a tool (a dll) which allows an asp.net developer to export data to xls and xlsx starting from sqldatasource,
sqldatareader, ecc with or without the use of a gridview.