主题：[求助]填充年月列（已解决）

要求从excel表分别导入三个月的数据，并将控件选择的年月填充到年月列，现在是后填充的年月将原来填充的年月覆盖掉了，如何修改代码来实现要求的效果，请各位老师帮助指教，谢谢！！

 

Dim mydate As Date = Forms("余额明细").Controls("DateTimePicker1").Text
Dim ny As String = format( mydate,"yyyy-MM")
Dim Book As New XLS.Book(ProjectPath & "银行余额.xls")
'Dim bh As String = Book.Sheets(0)(1,7).Text
If ny >  "" AndAlso DataTables("余额明细").Find("年月 = '" & ny & "'") IsNot Nothing Then
    messagebox.show("当月数据已导入","提示")
    Return
End If
For i As Integer = 0 To Book.Sheets.Count -1
    Dim a As String = Book.Sheets(i).Name
    If a = ny  Then
        Dim mg As New Merger
        mg.SourcePath = ProjectPath & "银行余额.xls"
        mg.Format = "excel" '指定格式
        mg.SourceTableName = "" & ny &"$" '指定要合并的表
        mg.DataTableName = "余额明细" '指定接收数据的表
        mg.Merge() '开始合并
        DataTables("余额明细").ReplaceFor("年月", ny ,"True") ‘将控件选择的年月填充到年月列
        DataTables("余额明细").Save()
        Return
    End If
Next
Messagebox.show("需导入的数据不存在!","提示")

 

 

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:管理项目177.rar

For i As Integer = 0 To Book.Sheets.Count -1
    Dim a As String = Book.Sheets(i).Name
    If a = ny  Then
        Dim mg As New Merger
        mg.SourcePath = ProjectPath & "银行余额.xls"
        mg.Format = "excel" '指定格式
        mg.SourceTableName = "" & ny &"$" '指定要合并的表
        mg.DataTableName = "余额明细" '指定接收数据的表
        mg.Merge() '开始合并
        For Each dr As DataRow In DataTables("余额明细").DataRows
            If dr.IsNull("年月") Then
                dr("年月") = ny
            End If
        Next
        Return
    End If
Next
Messagebox.show("需导入的数据不存在!","提示")

请仔细体会一下：

 

Dim mydate As Date = Forms("余额明细").Controls("DateTimePicker1").Text
Dim ny As String = format( mydate,"yyyy-MM")
Dim Book As New XLS.Book(ProjectPath & "银行余额.xls")
If ny >  "" AndAlso DataTables("余额明细").Find("年月 = '" & ny & "'") IsNot Nothing Then
    messagebox.show("当月数据已导入","提示")
    Return
End If
Dim id As Integer = -1
If DataTables("余额明细").DataRows.Count > 0 Then
    id = DataTables("余额明细").Compute("Max([_Identify])")
End If
For i As Integer = 0 To Book.Sheets.Count -1
    Dim a As String = Book.Sheets(i).Name
    If a = ny  Then
        Dim mg As New Merger
        mg.SourcePath = ProjectPath & "银行余额.xls"
        mg.Format = "excel" '指定格式
        mg.SourceTableName = "" & ny &"$" '指定要合并的表
        mg.DataTableName = "余额明细" '指定接收数据的表
        mg.Merge() '开始合并
        DataTables("余额明细").ReplaceFor("年月", ny ,"[_Identify] > " & id)
        DataTables("余额明细").Save()
        Return
    End If
Next
Messagebox.show("需导入的数据不存在!","提示")

其实方法还是很多的，套用老六的话，仔细体会吧。

 

For i As Integer = 0 To Book.Sheets.Count -1
    DataTables("余额明细").DataCols("年月").DefaultValue = """" & ny & """"
    Dim a As String = Book.Sheets(i).Name
    If a = ny  Then
        Dim mg As New Merger
        mg.SourcePath = ProjectPath & "银行余额.xls"
        mg.Format = "excel" '指定格式
        mg.SourceTableName = "" & ny &"$" '指定要合并的表
        mg.DataTableName = "余额明细" '指定接收数据的表
        mg.Merge() '开始合并
        Return
    End If
Next
Messagebox.show("需导入的数据不存在!","提示")

谢谢czy老师和狐爸老师的热心指教，问题得到解决，再次谢谢两位老师。

DataTables("余额明细").ReplaceFor("年月", ny ,"[年月] is null")   '就可以了吧。

呵呵，还是mr725兄的简单直接。