DataTables("通勤车乘车人员名单").DeleteFor("[序号] <> 0")
DataTables("扣款打印").DeleteFor("[单位名称] <> ''")
Dim dlg As New OpenFileDialog '定义一个新的OpenFileDialog
dlg.Filter= "excel文件|*.xlsx" '设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then '如果用户单击了确定按钮
Dim Bk As New XLS.Book(dlg.FileName)
Dim St1 As XLS.Sheet = Bk.Sheets(0)
'排序
Dim App As New MSExcel.Application
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open(dlg.filename)
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
Dim Rg As MSExcel.Range = Ws.Columns("A:P")
ws.range("a2:P" & St1.Rows.Count).sort(key1:=Ws.Range("G3"),header:= MSExcel.XlYesNoGuess.xlyes,Orientation:= MSExcel.XlSortOrientation.xlSortColumns)
wb.save
wb.close
'开始导入
Dim Book As New XLS.Book(dlg.FileName)
Dim Sheet1 As XLS.Sheet = Book.Sheets(0)
Dim PBar1 As WinForm.ProgressBar = e.Form.Controls("ProgressBar1")
PBar1.Maximum = Sheet1.Rows.Count
Dim ygxm,bmmc,xm,yggh,zhiwei As String
Dim r As Integer
r = 2
Dim xh As Integer
xh = 0
Do While Sheet1(r,2).Text <> ""
Dim dr As DataRow
dr = DataTables("通勤车乘车人员名单").AddNew()
If bmmc <> Sheet1(r,6).Text Then
Dim drr As DataRow
drr = DataTables("扣款打印").AddNew()
drr("单位名称") = sheet1(r,6).text
xh = 1
Else
xh = xh + 1
End If
yggh = Sheet1(r,0).Text
ygxm = Sheet1(r,2).Text
bmmc = Sheet1(r,6).Text
zhiwei = Sheet1(r,7).Text
xm = ygxm.Replace(bmmc,"")
dr("序号") = xh
dr("部门名称") = bmmc
dr("姓名") = xm
dr("员工工号") = yggh
dr("职位") = zhiwei
PBar1.Value = r
r = r + 1
Loop
DataTables("通勤车乘车人员名单").Save()
DataTables("扣款打印").DataRows(0).Delete() '删除第一行空行
'统计人数
Dim i As Integer
Dim dr1 As DataRow
For i = 0 To DataTables("扣款打印").DataRows.Count - 1
dr1 = DataTables("扣款打印").DataRows(i)
Dim dwmc As String
dwmc = dr1("单位名称")
dr1("人数") = DataTables("通勤车乘车人员名单").Compute("Count(部门名称)","[部门名称] = '" & dwmc & "'")
dr1("大写金额") = CUMoney(dr1("人数") * 10)
Next
DataTables("扣款打印").Save()
MessageBox.Show("导入完成!", "提示")
End If
加好友 
发短信
Post By：2021/5/26 11:07:00 [只看该作者]
