16,554
社区成员
发帖
与我相关
我的任务
分享
Imports System.Data
Imports System.Data.Common
Imports System.Configuration
Imports System.Reflection.MethodBase
Imports System.Text
Imports System.Data.SqlClient '引用數據庫服務
Imports System.Reflection
Imports Microsoft.Office.Interop
Imports vbTest02.DBHelper
Public Class BizExcelEmpInfo
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim myDataSet As New DataSet
Dim strSQL As String = ""
Dim strMsg As String = ""
Dim dbHelper As New DBHelper '引用其他類------資料庫的查詢
Dim i As Integer = 4
Dim j As Integer
Public Sub getDataToExcel()
Try
'创建Excel 对象
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
'打印报表头
xlSheet.Range("A1:G1").Select()
With xlSheet.Range("A1:G1").Select
xlSheet.Range("A1:G1").MergeCells = True
'xlSheet.Range("A1:G1").HorizontalAlignment = xlCenter
'xlSheet.Range("A1:G1").VerticalAlignment = .xlCenter
End With
xlSheet.Range("A1:G1").Value = "員工信息报表"
'打印页眉
xlSheet.Cells(3, 1) = "員工ID"
xlSheet.Cells(3, 2) = "中文名"
xlSheet.Cells(3, 3) = "英文名"
xlSheet.Cells(3, 4) = "部門"
xlSheet.Cells(3, 5) = "公司Email"
xlSheet.Cells(3, 6) = "聯系方式"
xlSheet.Cells(3, 7) = "工作年限"
Dim sbSql As StringBuilder = New StringBuilder
'將查出的數據中部門編號轉換為對應部門
sbSql.Append(" SELECT ID AS '員工ID',NAME_CHS AS '中文名', NAME_ENG AS '英文名',")
sbSql.Append(" case DEPART when 1 then '1 - 開發一部' ")
sbSql.Append(" when 2 then '2 - 開發二部' ")
sbSql.Append(" when 3 then '3 - 開發三部' ")
sbSql.Append(" when 4 then '4 - 開發四部' ")
sbSql.Append(" when 5 then '5 - 研發部' ")
sbSql.Append(" when 6 then '6 - 品管部' ")
sbSql.Append(" when 7 then '7 - 嵌入式開發部' ")
sbSql.Append(" end as '部門',")
sbSql.Append(" ENTER_DATE AS '入職日期',EMAIL AS '公司Email', ")
sbSql.Append(" PHONE AS '聯系方式',WK_YEARS AS '工作年限' ")
sbSql.Append(" from SHUJUKU")
'查詢全部記錄
myDataSet.Clear()
myDataSet = dbHelper.querySql(sbSql.ToString) '連接數據庫,開始查詢
MessageBox.Show(myDataSet.Tables(0).Rows.Count)
''循环打印报表内容
For Me.j = 0 To myDataSet.Tables(0).Rows.Count - 1
xlSheet.Cells(i, 1) = myDataSet.Tables(0).Rows(j)("員工ID").ToString()
xlSheet.Cells(i, 2) = myDataSet.Tables(0).Rows(j)("中文名").ToString()
xlSheet.Cells(i, 3) = myDataSet.Tables(0).Rows(j)("英文名").ToString()
xlSheet.Cells(i, 4) = myDataSet.Tables(0).Rows(j)("部門").ToString()
xlSheet.Cells(i, 5) = myDataSet.Tables(0).Rows(j)("入職日期").ToString()
xlSheet.Cells(i, 6) = myDataSet.Tables(0).Rows(j)("公司Email").ToString()
xlSheet.Cells(i, 7) = myDataSet.Tables(0).Rows(j)("聯系方式").ToString()
xlSheet.Cells(i, 8) = myDataSet.Tables(0).Rows(j)("工作年限").ToString()
i = i + 1
Next
'打印报表尾
i = i + 3
xlSheet.Cells(i, 6) = "制表人"
xlSheet.Cells(i, 7) = "***"
'对象释放
xlSheet.Application.Visible = True
xlSheet = Nothing
xlBook = Nothing
xlApp = Nothing
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Sub
End Class