- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- 上载excel数据如何实现多行数据相加 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=161364)
-- 发布时间：2021/3/15 11:09:00
-- 上载excel数据如何实现多行数据相加
If User.Group = "财务" OrElse User.name = "开发者" OrElse User.Group = "行政"
If CurrentTable.name.contains("综合所得申报")
Dim Book As New XLS.Book("C:\\综合所得申报.xls")
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("综合所得申报").StopRedraw()
Dim nms() As String = {"","","","身份证码","","","","","","","","","","","","","","","","","","","","","上月税务局累计子女教育","上月税务局累计赡养老人","上月税务局累计房贷利息","上月税务局累计租房租金","上月税务局累计继续教
育","","","","","","","","","","上月税务局实际扣税"}
For n As Integer = 8 To Sheet.Rows.Count -1
Dim sfz As String = sheet(n,3).Text
Dim znjy As String = sheet(n,24).Text
Dim sylr As String = sheet(n,25).Text
Dim zfdk As String = sheet(n,26).Text
Dim zfzj As String = sheet(n,27).Text
Dim jxjy As String = sheet(n,28).Text
Dim ybts As String = sheet(n,38).Text
Dim dr As DataRow
dr = DataTables("综合所得申报").AddNew()
For m As Integer = 0 To nms.Length - 1
If nms(m) = "" Then Continue For \'跳过空标题(这个if不用加end if)
dr(nms(m)) = Sheet(n,m).Value
Next
Next
Tables("综合所得申报").ResumeRedraw()
End If
End If
上述代码我在上载excel数据至foxtable时，如果excel表格里面同一"身份证号"有相同的多条记录，那么“上月税务局实际扣税” 这栏数据如何实现多行数据的相加，累计到foxtable里面的一行数据里面去?谢谢。
-- 发布时间：2021/3/15 11:19:00
--
也就是说，如果foxtable里面已经有某个身份证号的数据行了，现在从excel再上载的话，就要检查一下是否有同身份证号的记录，如何有，就将“上月税务局实际扣税” 这个数据叠加上去（相加），其他数据(比如"上月税务局累计子女教育"等)就是代替上去（不相加）。
-- 发布时间：2021/3/15 11:22:00
--
http://www.foxtable.com/webhelp/topics/2334.htm，看示例3
Dim dr As DataRow = DataTables("综合所得申报").find("身份证码=\'" & sheet(n,3).Text & "\'")
if dr isnot nothing
dr("上月税务局实际扣税") = dr("上月税务局实际扣税") + val(Sheet(n,上月税务局实际扣税列索引).Value)
else
dr = DataTables("综合所得申报").AddNew()
For m As Integer = 0 To nms.Length - 1
If nms(m) = "" Then Continue For \'跳过空标题(这个if不用加end if)
dr(nms(m)) = Sheet(n,m).Value
Next
end if
Next
Tables("综合所得申报").ResumeRedraw()
-- 发布时间：2021/3/15 13:01:00
--
If CurrentTable.name.contains("综合所得申报")
Dim Book As New XLS.Book("C:\\综合所得申报.xls")
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("综合所得申报").StopRedraw()
Dim nms() As String = {"","","","身份证号","","","","","","","","","","","","","","","","","","","","","上月税务局累计子女教育","上月税务局累计赡养老人","上月税务局累计房贷利息","上月税务局累计租房租金","上月税务局累计继续教育","","","","","","","","","","上月税务局实际扣税"}
For n As Integer = 8 To Sheet.Rows.Count -1
Dim dr As DataRow = DataTables("综合所得申报").find("身份证号=\'" & sheet(n,3).Text & "\'")
\' Dim dr As DataRow = Tables("综合所得申报").find("身份证码=\'" & sheet(n,3).Text & "\'")
If dr IsNot Nothing
dr("上月税务局实际扣税") = dr("上月税务局实际扣税") + val(Sheet(n,38).Value)
Else
dr = DataTables("综合所得申报").AddNew()
For m As Integer = 0 To nms.Length - 1
If nms(m) = "" Then Continue For \'跳过空标题(这个if不用加end if)
dr(nms(m)) = Sheet(n,m).Value
Next
End If
Next
Tables("综合所得申报").ResumeRedraw()
End If
现在代码改了。附页是excel表格，但是执行起来没有反应，不知道是哪里的问题？
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:综合所得申报.rar |
-- 发布时间：2021/3/15 13:40:00
--
调试技巧：http://www.foxtable.com/webhelp/scr/1485.htm
For n As Integer = 8 To Sheet.Rows.Count -1
Dim dr As DataRow = DataTables("综合所得申报").find("身份证号=\'" & sheet(n,3).Text & "\'")
msgbox(dr IsNot Nothing)
If dr IsNot Nothing
msgbox(dr("上月税务局实际扣税"))
msgbox(Sheet(n,38).Value)
dr("上月税务局实际扣税") = dr("上月税务局实际扣税") + val(Sheet(n,38).Value)
msgbox(dr("上月税务局实际扣税"))
Else
dr = DataTables("综合所得申报").AddNew()
For m As Integer = 0 To nms.Length - 1
If nms(m) = "" Then Continue For \'跳过空标题(这个if不用加end if)
dr(nms(m)) = Sheet(n,m).Value
Next
End If
Next
-- 发布时间：2021/3/15 14:03:00
--
加上4句msgbox代码，执行起来也是没有任何反应，没有任何警告，也没有上传数据。是不是要把excel里面的文本格式数据转化成数字格式，如何转化？
-- 发布时间：2021/3/15 14:07:00
--
没有任何反应，说明这个循环代码根本就没有执行，检查这个嗲之上的其它代码的各种条件是否符合
-- 发布时间：2021/3/15 20:21:00
--
If User.Group = "财务" OrElse User.name = "开发者" OrElse User.Group = "行政"
If CurrentTable.name.contains("工资计算")
Dim Book As New XLS.Book("C:\\综合所得申报.xls")
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("工资计算").StopRedraw()
Dim nms() As String = {"","","","身份证号","","","","","","","","","","","","","","","","","","","","","上月税务局累计子女教育","上月税务局累计赡养老人","上月税务局累计房贷利息","上月税务局累计租房租金","上月税务局累计继续教育","","","","","","","","","","上月税务局实际扣税",""}
For n As Integer = 1 To Sheet.Rows.Count -1
Dim dr As DataRow = DataTables("工资计算").find("身份证号=\'" & sheet(n,3).Text & "\'")
If dr IsNot Nothing
dr("上月税务局实际扣税") = dr("上月税务局实际扣税") + val(Sheet(n,38).Value)
Else
dr = DataTables("工资计算").AddNew()
For m As Integer = 0 To nms.Length - 1
If nms(m) = "" Then Continue For \'跳过空标题(这个if不用加end if)
dr(nms(m)) = Sheet(n,m).Value
Next
End If
Next
Tables("工资计算").ResumeRedraw()
End If
End If
如果 excel 里面的记录在 foxtable 里面找不到而要新增数据行，上述代码就可以执行，上传的数据也没错。但是，如果 foxtable 里面已经存在某个身份证号的记录，而 excel 表格里面如果有两个相同身份证号的记录，那么，第二条记录应该覆盖第一条记录，现在不会覆盖掉；"上月税务局实际扣税" 应该累加的，现在也不会累加。请问什么原因？
-- 发布时间：2021/3/15 20:33:00
--
For n As Integer = 1 To Sheet.Rows.Count -1
msgbox(sheet(n,3).Text)
Dim dr As DataRow = DataTables("工资计算").find("身份证号=\'" & sheet(n,3).Text & "\'")
msgbox(dr IsNot Nothing)
If dr IsNot Nothing
msgbox(Sheet(n,38).Value)
dr("上月税务局实际扣税") = dr("上月税务局实际扣税") + val(Sheet(n,38).Value)
Else
-- 发布时间：2021/3/16 15:15:00
--
会依次弹出身份证号码、True 和 Sheet(n,38) 的值来，但是 Sheet(n,38) 的值没有填入 foxtable 的 cell里面去。