Friday, December 20, 2013

Create Excel File in vb.net

Private Sub cmdexcel_Click()
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

Dim row As Long
Set xlapp = New Excel.Application
Set xlbook = xlapp.Workbooks.Add
Set xlsheet = xlbook.Worksheets(1)
row = 4
With xlsheet

If optdatewise.Value = True Then
.Cells(1, 1) = "Report of Complaints"
.Cells(2, 1) = "Date : " & Format(date1.Value, "dd/MMM/yyyy") & "  To : " & Format(date2.Value, "dd/MMM/yyyy")
.Rows(2).Font.Bold = True
.Rows(2).Font.Size = 12
Else
.Cells(1, 1) = "Report of Complaints Received Till  '" & Now() & "'"
End If
.Rows(1).Font.Bold = True
.Rows(1).Font.Size = 18

.Cells(3, 1) = "Total Number Of Complaints: "
.Rows(3).Font.Bold = True
.Rows(3).Font.Size = 18

.Columns(1).ColumnWidth = 9
.Columns(2).ColumnWidth = 12
.Columns(3).ColumnWidth = 11
.Columns(4).ColumnWidth = 11
.Columns(5).ColumnWidth = 15
.Columns(6).ColumnWidth = 12
.Columns(7).ColumnWidth = 12
.Columns(8).ColumnWidth = 15
.Columns(9).ColumnWidth = 18
.Columns(10).ColumnWidth = 21

.Rows(row).Font.Bold = True
For i = 0 To grid.Rows - 1
For j = 1 To grid.Cols - 1
If j = 2 And i > 0 Then
.Cells(row, j).Select
 xlapp.ActiveSheet.Hyperlinks.Add Anchor:=xlapp.Selection, Address:=mpath & "\Log Files\" & grid.TextMatrix(i, j) & "\Log.doc", TextToDisplay:=grid.TextMatrix(i, j)
Else
.Cells(row, j) = grid.TextMatrix(i, j)
End If
.Rows.Font.Name = "Centaur"
.Rows(row).WrapText = True
Next
row = row + 1
Next
.Cells(3, 1) = .Cells(3, 1) & row - 5

.Rows(4).AutoFilter
.Rows(5).Activate
xlapp.ActiveWindow.FreezePanes = True
.PageSetup.LeftMargin = 1
.PageSetup.RightMargin = 1
.PageSetup.PrintGridlines = True
.PageSetup.Orientation = xlLandscape
.PageSetup.Zoom = 90
.PageSetup.PrintTitleRows = "$4:$4"
.PageSetup.CenterFooter = "&P of &N"

xlapp.Visible = True
End With
End Sub