01.Option Explicit On
02.Option Strict On
03.Imports System.Data
04.Imports System.Data.OleDb
05.Imports Excel
06.Imports System.IO
07.Imports System.Windows.Forms
08.Public Class FrmExcel
09. Inherits System.Windows.Forms.Form
10.
11. Private Sub FrmExcel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
12. Dim Today As New Date
13. Today = Date.Now
14. BeforeDate.Value = Today.AddMonths(-1)
15. End Sub
16.
17. Private Sub cmdOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOK.Click
18. Dim sqlSelect As String = ""
19. Dim dadpter As New OleDbDataAdapter
20. sqlSelect = "SELECT Jobref,ReqtoPlant,Mode,ShipperName,PO,INV,ETD,ATD,ETA,ATA,FWDR,GrossWeight,Quantity,Unit,"
21. sqlSelect &= "ICLCBM,[1x20],[1x40],[1x40HQ],ContrnCBM,Port,Country,BL_no,DO,Rcvd_Doc,Starting,Released,PlanDelivery,Delivery,Delivery_Place,Remark,"
22. sqlSelect &= "BG_Date,BG_Amount,ImportCustomer,TypeOfEntry,SKUNumber,DetailOfGoods,CIF,HSCode,TarrifRate,Duty,Vat,Eprivilege,Eduty,Vat2,CostSaving,"
23. sqlSelect &= "KPI_LeadTime,Billing,KPI_Billing,Duty2,DutyExcludedVat,DueDateDuty,"
24. sqlSelect &= "Freight,Exwork,DO1,OtherCharge,Total,ConsolBill,FrtBillNo,FrtBillDate,TranSportation,TotalAmount,BillingMonth,FrtFromUS,SavingFrtCost"
25. sqlSelect &= " FROM Detail"
26. sqlSelect &= " WHERE (ETD BETWEEN #" & CDate(BeforeDate.Value) & "# AND #" & CDate(UpToDate.Value) & "#) AND"
27. If rdoImport.Checked = True Then
28. sqlSelect &= " IsTypeImport = '1' AND IstypeExport = '0'"
29. ElseIf rdoExport.Checked = True Then
30. sqlSelect &= " IsTypeImport = '0' AND IstypeExport = '1'"
31. End If
32. dadpter = New OleDbDataAdapter(sqlSelect, ConnectAccess)
33. dadpter.Fill(dt)
34.
35.
36. Dim FileName As String = "Kholer/FileExcel.xls"
37.
38.
39. Dim xlApp As Excel.Application = New Excel.Application()
40. Dim xlSheet1 As Excel.Worksheet
41. Dim xlBook As Excel.Workbook
42. Dim i As Integer
43. Dim intRows As Integer
44.
45. xlBook = xlApp.Workbooks.Add()
46. xlBook.Application.Visible = False
47.
48.
49. xlSheet1 = CType(xlBook.Worksheets(1), Worksheet)
50. xlSheet1.Name = "My Sheet1"
51.
52.
53. With xlApp.ActiveSheet
54. .Value = "JOB REF"
55. End With
56.
57.
58.
59. End Sub
60.End Class
