Please help with this code...probably simple for an expert

[jonny]

Hi I am trying to get this code to work so I can export gridview data
to excel. However I am running into a problem with this code:

Here is my immediate problem...VB.net 2008 does not like where I have
"GridView1(j, i).Value.ToString()"...see below.


For i = 0 To GridView1.Rows.Count - 2
For j = 0 To GridView1.Columns.Count - 1
xlWorkSheet.Cells(i + 1, j + 1) = _
GridView1(j, i).Value.ToString()
Next
Next



Error says "Class 'System.Web.UI.WebControls.GridView' cannot be
indexed because it has no default property."



Here is the entire code below where I am trying to export gridview to
excel...any help on this is greatly appreciated:


Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
EventArgs) Handles Button1.Click

Dim cnn As SqlConnection
Dim connectionString As String
Dim sql As String

connectionString = ConfigurationManager.ConnectionStrings
("JohnsConnection").ConnectionString
cnn = New SqlConnection(connectionString)
cnn.Open()
sql = "SELECT * FROM ProductivityData"
Dim dscmd As New SqlDataAdapter(sql, cnn)
Dim ds As New DataSet
dscmd.Fill(ds)
GridView1.DataSource = ds.Tables(0)

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer

xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")



For i = 0 To GridView1.Rows.Count - 2
For j = 0 To GridView1.Columns.Count - 1
xlWorkSheet.Cells(i + 1, j + 1) = _
GridView1(j, i).Value.ToString()
Next
Next



xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
xlWorkBook.Close()
xlApp.Quit()

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)

MsgBox("You can find the file C:\vbexcel.xlsx")

End Sub

 

[Miro]

I am not sure why you are havign problem with yours, but I have posted my
code here that works for me.
( the sub of the program has a #2 on it as I found this one worked better
than my old one.)

I create it from a "datatable", but you should be able to modify it to use
your datagrid.
( Unless your datagrid is displaying a datatable...then just pop it in )

I have also noticed that you are converting all your "cells" to string. You
would not have to worry about this if you use the datatable.

Hope this helps,

Cheers'

Miro

============Code below========
Imports System.Data.OleDb 'You may not need this as I have a class with
"excel stuff" and probably need this in another sub.
Imports Excel = Microsoft.Office.Interop.Excel


Sub WriteDataTableToExcel2(ByVal myNewXLS As String, ByVal myTable As
DataTable, ByVal OpenExcel As Boolean)

Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

wBook = excel.Workbooks.Add()
'wSheet = wBook.ActiveSheet()
wSheet = CType(wBook.ActiveSheet(),
Microsoft.Office.Interop.Excel.Worksheet)

Dim dt As DataTable = myTable
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0

For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(1, colIndex) = dc.ColumnName
Next

For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

Next
Next

wSheet.Columns.AutoFit()
Dim strFileName As String = myNewXLS '"D:\ss.xls"
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream =
System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try

If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If

wBook.SaveAs(strFileName)
If OpenExcel Then
excel.Workbooks.Open(strFileName)
excel.Visible = True
End If

End Sub
===============End Code=======