doandinhtam > 19-12-16, 11:30 AM
cpucloi > 19-12-16, 11:47 AM
Che_Guevara > 19-12-16, 01:29 PM
doandinhtam > 19-12-16, 02:13 PM
doandinhtam > 20-12-16, 01:48 PM
Option Compare Database
Dim Obj As Object
Dim CommandString As String
Dim excelObj As Excel.Application
Dim excelBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim conn As New OleDbConnection(Login.ACConnectionString)
excelObj = CreateObject("Excel.Application")
System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
excelBook = excelObj.Workbooks.Open(String.Format("{0}Baocao\Baocaotonghop.xls", My_Path), [ReadOnly]:=False)
ToolStripProgressBar1.Value = 5
Application.DoEvents()
xlSheet = excelObj.Worksheets(1)
xlSheet.DisplayPageBreaks = True
xlSheet.Cells(2, 1) = " "
xlSheet.Cells(3, 1) = " "
Dim Month_Num As String = Today().Month
If (Month_Num < 3) Then Month_Num = "0" & Month_Num
Dim Ky_BC As String = String.Format("(T? ngày ...... tháng ...... nam {0:yyyy} d?n ngày 15 tháng {1} nam {0:yyyy})", Today(), Month_Num)
xlSheet.Cells(3, 23) = Ky_BC
ToolStripProgressBar1.Value = 10
Application.DoEvents()
conn.Open()
'S? li?u 1:
CommandString = "SELECT Count(DOITUONG.ID) AS TS FROM 1"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(35, 73) = Obj(0)
ToolStripProgressBar1.Value = 20
Application.DoEvents()
xlSheet = excelObj.Worksheets(2)
xlSheet.DisplayPageBreaks = True
xlSheet.Cells(40, 54) = Ngay_Thang
xlSheet.Cells(41, 54) = "THONG KE " & MyModule.GetInfo("Villages").ToUpper & " " & MyModule.GetInfo("Vil_Name").ToUpper
xlSheet = excelObj.Worksheets(3)
xlSheet.DisplayPageBreaks = True
'S? li?u thu?ng trú theo danh sách:
CommandString = "SELECT Count(HKT1.ID) AS TS FROM HKT1.ID"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(21, 1) = Obj(0)
ToolStripProgressBar1.Value = 35
Application.DoEvents()
CommandString = "SELECT Count(CHITIET_NKT1.ID) AS TS, Sum(CHITIET_NKT1.SO_NU) AS NU, Sum(CHITIET_NKT1.TREN_14) AS TR14 FROM CHITIET_NKT1"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(21, 2) = Obj(0)
xlSheet.Cells(21, 3) = Obj(1)
xlSheet.Cells(21, 4) = Obj(2)
ToolStripProgressBar1.Value = 60
Application.DoEvents()
'S? li?u thu?ng trú di ngoài t?nh:
CommandString = "SELECT Count(HKT1_NT.ID) AS TS FROM HKT1_NT"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(21, 5) = Obj(0)
ToolStripProgressBar1.Value = 65
Application.DoEvents()
CommandString = "SELECT Count(NKT1_NT.ID) AS TS, Sum(NKT1_NT.SO_NU) AS NU, Sum(NKT1_NT.TREN_14) AS TR14 FROM NKT1_NT"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(21, 6) = Obj(0)
xlSheet.Cells(21, 7) = NTZ(Obj(1))
xlSheet.Cells(21, 8) = NTZ(Obj(2))
ToolStripProgressBar1.Value = 70
Application.DoEvents()
'S? li?u thu?ng trú di ngoài huy?n:
CommandString = "SELECT Count(HKT1_NQ.ID) AS TS FROM HKT1_NQ"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(21, 9) = Obj(0)
ToolStripProgressBar1.Value = 75
Application.DoEvents()
CommandString = "SELECT Count(NKT1_NQ.ID) AS TS, Sum(NKT1_NQ.SO_NU) AS NU, Sum(NKT1_NQ.TREN_14) AS TR14 FROM NKT1_NQ"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(21, 10) = Obj(0)
xlSheet.Cells(21, 11) = NTZ(Obj(1))
xlSheet.Cells(21, 12) = NTZ(Obj(2))
ToolStripProgressBar1.Value = 80
Application.DoEvents()
'S? li?u thu?ng trú di ngoài xã:
CommandString = "SELECT Count(HKT1_NP.ID) AS TS FROM HKT1_NP"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(21, 13) = Obj(0)
ToolStripProgressBar1.Value = 85
Application.DoEvents()
CommandString = "SELECT Count(NKT1_NP.ID) AS TS, Sum(NKT1_NP.SO_NU) AS NU, Sum(NKT1_NP.TREN_14) AS TR14 FROM NKT1_NP"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(21, 14) = Obj(0)
xlSheet.Cells(21, 15) = NTZ(Obj(1))
xlSheet.Cells(21, 16) = NTZ(Obj(2))
ToolStripProgressBar1.Value = 90
Application.DoEvents()
'S? li?u t?m trú KT3
CommandString = "SELECT Count(HOKT3_DS.ID) AS TS FROM HOKT3_DS"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(26, 1) = Obj(0)
CommandString = "SELECT Count(KHKT3_DS.ID) AS TS, Sum(KHKT3_DS.TONGNU) AS NU, Sum(KHKT3_DS.TREN_14) AS TR14 FROM KHKT3_DS"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(26, 2) = Obj(0)
xlSheet.Cells(26, 3) = NTZ(Obj(1))
xlSheet.Cells(26, 4) = NTZ(Obj(2))
ToolStripProgressBar1.Value = 94
Application.DoEvents()
'S? li?u t?m trú KT2
CommandString = "SELECT Count(HOKT2_KH.ID) AS TS FROM HOKT2_KH"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(26, 5) = Obj(0)
CommandString = "SELECT Count(KHKT2_KH.ID) AS TS, Sum(KHKT2_KH.TONGNU) AS NU, Sum(KHKT2_KH.TREN_14) AS TR14 FROM KHKT2_KH"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(26, 6) = Obj(0)
xlSheet.Cells(26, 7) = NTZ(Obj(1))
xlSheet.Cells(26, 8) = NTZ(Obj(2))
ToolStripProgressBar1.Value = 96
Application.DoEvents()
CommandString = "SELECT Count(HOKT2_CH.ID) AS TS FROM HOKT2_CH"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(26, 9) = Obj(0)
CommandString = "SELECT Count(KHKT2_CH.ID) AS TS, Sum(KHKT2_CH.TONGNU) AS NU, Sum(KHKT2_CH.TREN_14) AS TR14 FROM KHKT2_CH"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(26, 10) = Obj(0)
xlSheet.Cells(26, 11) = NTZ(Obj(1))
xlSheet.Cells(26, 12) = NTZ(Obj(2))
ToolStripProgressBar1.Value = 98
Application.DoEvents()
'S? li?u luu trú
CommandString = "SELECT Count(LUTRU_TS.ID) AS TS FROM LUTRU_TS"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(26, 13) = Obj(0)
ToolStripProgressBar1.Value = 99
Application.DoEvents()
CommandString = "SELECT Count(LUTRU_NU.ID) AS TS FROM LUTRU_NU"
Obj = Get_Reader(CommandString, conn)
xlSheet.Cells(26, 15) = Obj(0)
ToolStripProgressBar1.Value = 100
Application.DoEvents()
Obj = Nothing
conn.Close()
conn = Nothing
excelBook.SaveAs(String.Format(My_Path & "Temp\Baocaotonghop_{0}.xls", Now.Ticks))ongke0711 > 20-12-16, 03:01 PM
doandinhtam > 20-12-16, 03:39 PM
ongke0711 > 20-12-16, 03:53 PM
doandinhtam > 20-12-16, 06:39 PM