formatting problems while exporting to Excel

[El Corazon]

Hello there,
Using vb.net, I am pulling data from SQL Server through the use of a datagridview and exporting to excel. Problem is I cannot format the columns. columns D-H are all date/time fields and I cant seem to modify anything. Here is the code I am using to export to excel. Any help would be greatly appreciated!

If DataGridView1.Rows.Count = 0 Then
MessageBox.Show("There are no records to display")
Else

Dim wapp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
Dim wsheet As Microsoft.Office.Interop.Excel.Worksheet
Dim wbook As Microsoft.Office.Interop.Excel.Workbook

wapp.Visible = True
wbook = wapp.Workbooks.Add()
wsheet = wbook.ActiveSheet

Dim iX AsInteger
Dim iY AsInteger
Dim iC AsInteger
For iC = 0 To DataGridView1.Columns.Count - 1
wsheet.Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
wsheet.Cells(1, iC + 1).font.bold = True
Next
wsheet.Rows(2).select()

For iX = 0 To DataGridView1.Rows.Count - 1
For iY = 0 To DataGridView1.Columns.Count - 1
wsheet.Cells(iX + 2, iY + 1).value = DataGridView1(iY, iX).Value.ToString
Next


Next



wapp.Visible = True
wapp.Columns.AutoFit()
wapp.Rows.AutoFit()
wapp.UserControl = True
EndIf
EndSub
EndClass

[fixo]

Originally Posted by El Corazon

Hello there,
Using vb.net, I am pulling data from SQL Server through the use of a datagridview and exporting to excel. Problem is I cannot format the columns. columns D-H are all date/time fields and I cant seem to modify anything. Here is the code I am using to export to excel. Any help would be greatly appreciated!

If DataGridView1.Rows.Count = 0 Then
MessageBox.Show("There are no records to display")
Else

Dim wapp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
Dim wsheet As Microsoft.Office.Interop.Excel.Worksheet
Dim wbook As Microsoft.Office.Interop.Excel.Workbook

wapp.Visible = True
wbook = wapp.Workbooks.Add()
wsheet = wbook.ActiveSheet

Dim iX AsInteger
Dim iY AsInteger
Dim iC AsInteger
For iC = 0 To DataGridView1.Columns.Count - 1
wsheet.Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
wsheet.Cells(1, iC + 1).font.bold = True
Next
wsheet.Rows(2).select()

For iX = 0 To DataGridView1.Rows.Count - 1
For iY = 0 To DataGridView1.Columns.Count - 1
wsheet.Cells(iX + 2, iY + 1).value = DataGridView1(iY, iX).Value.ToString
Next


Next



wapp.Visible = True
wapp.Columns.AutoFit()
wapp.Rows.AutoFit()
wapp.UserControl = True
EndIf
EndSub
EndClass

Try change NumberFormat property for particular cells:

 
If iY = 3 Or iY= 4  Then '<--change on columns you need
wsheet.Cells(iX + 2, iY + 1).NumberFormat ="DD.MM.YYYY" '<--change format to suit
....
End If

~'J'~