'变量定义
Dim r1 As Integer = format(01, "00")
Dim zn As Integer '总库年份
Dim zy As Integer '总库月份
Dim dn As Integer '当月年份
Dim dy As Integer '当月月份
zn = Tables("wy_gt").Compute("Max(yy)") '提取总库最大的年份
zy = Tables("wy_gt").Compute("Max(yy1)", "yy=" & zn) '提取总库最大年份中的最大月份
zn = Tables("wy_gt").Compute("Max(yy)") '提取当月库最大的年份
dn = Tables("wy_gt0").Compute("Max(yy)") '提取当月最大年份中的最大月份
dy = Tables("wy_gt0").Compute("Max(yy1)", "yy=" & dn) '年份 + 月份以整数计算
Dim cmd As New SQLCommand
Dim dt1 As Date
cmd.C
cmd.CommandText = "Select GetDate()"
dt1 = cmd.ExecuteScalar()
dt1 = dt1.Date
Dim year1 As Integer = dt1.Year
Dim month1 As Integer = dt1.Month
Dim xtrq As String = year1 & format(month1, "00") & Format (r1, "00")
Dim zny As String = zn & format(zy, "00") & Format (r1, "00")
Dim dny As String = dn & format(dy, "00") & Format (r1, "00")
Dim dx As Date = xtrq.SubString(0, 4) & "-" & xtrq.SubString(4, 2) & "-" & xtrq.SubString(6, 2)
Dim dz As Date = zny.SubString(0, 4) & "-" & zny.SubString(4, 2) & "-" & zny.SubString(6, 2)
Dim dd As Date = dny.SubString(0, 4) & "-" & dny.SubString(4, 2) & "-" & dny.SubString(6, 2)
If dd = dx.AddMonths( - 1) Then
Tables("cbd").AllowEdit = True
DataTables("cbd").DeleteFor("")
Dim dlg As New OpenFileDialog
dlg.InitialDirectory = "C:\Users\Administrator\Downloads" '初始路径为桌面
dlg.Filter = "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog =DialogResult.OK Then
Dim t As Table = Tables("cbd")
t.StopRedraw()
Dim Book As New XLS.Book(dlg.FileName)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim newcount As Integer = 0
Dim oldcount As Integer = 0
For n As Integer = 1 To Sheet.Rows.Count -1
Dim r As DataRow = t.DataTable.Find("表地址= '" & sheet(n, 5).text & "'")
If r Is Nothing Then
r = t.DataTable.AddNew()
newcount += 1
Else
oldcount += 1
End If
For i As Integer = 0 To sheet.Cols.Count -1
Dim cname As String = sheet(0, i).text
If t.Cols.Contains(cname) Then
If t.Cols(cname).IsBoolean Then
If sheet(n, i).Text = 1 OrElse sheet(n, i).Text = "true" Then
r(cname) = True
Else
r(cname) = False
End If
Else
r(cname) = sheet(n, i).Text
End If
End If
Next
Next
msgbox("新增" & newcount & " " & "更新旧数据" & oldcount)
t.ResumeRedraw()
End If
DataTables("cbd").Save()
Tables("cbd").AllowEdit=False
'以下将遍历抄表单表地址并将累计使用量填入到CPD0中的bqds(本期度数)
Dim w As DataRow
For Each dr As DataRow In DataTables("wy_cpd0").DataRows
w = DataTables("cbd").Find("表地址= '"& dr("表地址") & "'")
If w IsNot Nothing Then
dr("bqds") = w("累计使用量")
End If
Next
msgbox("已完成" )
DataTables("wy_cpd0").Save
Else
加好友 
发短信
Post By：2024/2/1 15:09:00 [只看该作者]
