Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop.Excel.XlMSApplication
Imports System.IO
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
con.Open()
Dim sqlst As String = "select * from Types"
Dim da As New SqlDataAdapter(sqlst, con)
Dim ds As New DataSet
da.Fill(ds, "type")
'Export to Excel
Dim filename As String = "MyXls/MyExcel.xls"
'create excel
Dim xlapp As New Microsoft.Office.Interop.Excel.Application
Dim xlsheet1 As New Microsoft.Office.Interop.Excel.Worksheet
Dim xlbook As New Microsoft.Office.Interop.Excel.Workbook
Dim i As Integer
Dim introw As Integer
xlbook = xlapp.Workbooks.Add()
xlbook.Application.Visible = False
'create sheet1
xlsheet1 = xlbook.Worksheets(1)
xlsheet1.Name = "My Sheet1"
'width & hight (A1:A1)
xlapp.ActiveSheet.range("A1:A1")
xlapp.ColumnWidth = 10.0
xlapp.ActiveSheet.range("B1:B1")
xlapp.ColumnWidth = 13.0
xlapp.ActiveSheet.range("C1:C1")
xlapp.ColumnsWidth = 23.0
xlapp.ActiveSheet.range("D1:D1")
xlapp.ColumnsWidth = 12.0
xlapp.ActiveSheet.range("E1:E1")
xlapp.ColumnsWidth = 13.0
xlapp.ActiveSheet.range("F1:F1")
xlapp.ColumnsWidth = 12.0
xlapp.ActiveSheet.range("A1:F1")
xlapp.BORDERS.Weight = 1
xlapp.MergeCells = True
xlapp.Font.Bold = True
xlapp.Font.Size = 20
xlapp.HorizontalAlignment = -4108
xlapp.ActiveSheet.Cells(1, 1)
xlapp.Values = "Type Report"
'Header
With xlapp.ActiveSheet.Cells(2, 1)
.Value = "รหัส"
.Font.Bold = True
.VerticalAlignment = -4108
.HorizontalAlignment = -4108
.BORDER.Weight = 1
End With
With xlapp.ActiveSheet.Cells(2, 2)
.Value = "ประเภท"
.Font.Bold = True
.VerticalAlignment = -4108
.HorizontalAlignment = -4108
.BORDER.Weight = 1
End With
''''''''''''
introw = 3
For i = 0 To ds.Tables("type").Rows.Count - 1
'detail
With xlapp.ActiveSheet.Cells(introw, 1)
.value = ds.Tables("type").Rows(i)
.BORDER.Weight = 1
.HorizontalAlignment = -4108
End With
With xlapp.ActiveSheet.Cells(introw, 2)
.value = ds.Tables("type").Rows(i)
.BORDER.Weight = 1
.HorizontalAlignment = -4108
End With
Next
Dim MyFile As New FileInfo(filename)
If MyFile.Exists Then
MyFile.Delete()
End If
MyFile = Nothing
'*** Save Excel ***'
'xlSheet1.PrintOut 1 '*** Print to printer ***'
xlsheet1.SaveAs(filename)
xlapp.Quit()
'*** Quit and Clear Object ***'
xlapp = Nothing
xlsheet1 = Nothing
xlbook = Nothing
con.Close()
End Sub