0

I have the code for transferring data from the datagridview to the excel spreadhseet.. my datagridview has colored data in it and I want the excel spreadsheet also to have the colored data. i get the error "property access must assign to the property or use its value vb.net error" on the line "
wsheet.Range("A1", "A5").Interior.ColorIndex()


" please assist.. !

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then

Exit Sub

End If

Dim dset As New DataSet

dset.Tables.Add()

For i As Integer = 0 To DataGridView1.ColumnCount - 1

dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)

Next

Dim dr1 As DataRow

For i As Integer = 0 To DataGridView1.RowCount - 1

dr1 = dset.Tables(0).NewRow

For j As Integer = 0 To DataGridView1.Columns.Count - 1

dr1(j) = DataGridView1.Rows(i).Cells(j).Value

Next

dset.Tables(0).Rows.Add(dr1)

Next

Dim excel As New Microsoft.Office.Interop.Excel.Application

Dim wbook As Microsoft.Office.Interop.Excel.Workbook

Dim wsheet As Microsoft.Office.Interop.Excel.Worksheet




wbook = excel.Workbooks.Add()

wsheet = wbook.ActiveSheet()




Dim dt As System.Data.DataTable = dset.Tables(0)

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()

wsheet.Range("A1", "A5").Interior.ColorIndex()

Dim strfilename As String = "C:\Documents and Settings\bashkark\Desktop\MCAD.xls"

Dim blnfileopen As Boolean = True

wbook.SaveAs(strfilename)

excel.Workbooks.Open(strfilename)

excel.Visible = True

MsgBox("Report Created")

End Sub
1
Contributor
1
Reply
3
Views
8 Years
Discussion Span
Last Post by kavithabhaskar
0

tried it. It is like coloring the cells manually.. i want all the data to be colored that are colored in the datagridview.. if i said wsheet.range("A1","A5").interior.colorindex = 3, it colors the first 5 items (including the fieldname) red in the first column.. i dont want that to happen.. can some thing more be added to make the excel pick the color on its own (detect the color) and bring it to excel on the same values ?
--------------------------------------------------------------------------------
KB - Knowledge is Power!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.