I am trying to open an excel spreadsheet using vb.net. The data has to be fetched from a datagridview..
the datagridview has colored data in it and i want it to appear colored on excel spreadsheet too.. How do i get it colored ?
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()
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("created")
End Sub