主题：求助！导入EXCEL表数据全部为文本

唉，昨晚折腾了好久，还是没自己解决。还是求大神来解决

1、

 

For Each r As Row In Tables("明细表").rows
    r("周六周日") = Nothing
    If cdate(r("交易时间")).DayOfWeek = 0 OrElse cdate(r("交易时间")).DayOfWeek = 6 Then
        r("周六周日") = format(cdate(r("交易时间")), "dddd")
        For Each dr As DataRow In DataTables("调休表").Select("")
            For i As Integer = 1 To 4
                Dim cname As String = "调为工作日" & i
                If dr(cname) <> Nothing AndAlso cdate(r("交易时间")).Date = dr(cname) Then
                    r("周六周日") = Nothing
                    goto label2
                End If
            Next
        Next
        label2:
    Else
        For Each dr As DataRow In DataTables("调休表").Select("")
            For i As Integer = 1 To 7
                Dim cname As String = "调为休假日" & i
                If dr(cname) <> Nothing AndAlso cdate(r("交易时间")).Date = dr(cname) Then
                    r("周六周日") = dr("调休原因")
                    goto label1
                End If
            Next
        Next
        label1:
    End If
Next

 

2、

 

For Each kh As String In DataTables("明细表").GetValues("卡号")
    Dim drs = DataTables("明细表").Select("卡号='" & kh & "' and 交易类型='购买'", "交易时间")
    For i As Integer = 1 To drs.count-1
        Dim sp As TimeSpan = drs(i)("交易时间") - drs(i-1)("交易时间")
        If sp.TotalHours <= 1 Then
            drs(i)("一小时内") = True
            drs(i)("三小时内") = Nothing
            drs(i)("一天内") = Nothing
        ElseIf sp.TotalHours <= 3 Then
            drs(i)("一小时内") = Nothing
            drs(i)("三小时内") = True
            drs(i)("一天内") = Nothing
        ElseIf sp.TotalHours <= 24 Then
            drs(i)("一小时内") = Nothing
            drs(i)("三小时内") = Nothing
            drs(i)("一天内") = True
        End If
    Next
Next

 

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

点击浏览该文件:销售表.xlsx

请看我这个销售表，我期间销售数量的公式在EXCEL表里的是=IF(COUNTIF($B$2:B2,B2)=1,"-",C2-LOOKUP(1,0/($B$1:B1=B2),$C$1:C1))，在狐表里可实现，如何用？非常感谢！

参考

 

http://www.foxtable.com/webhelp/topics/2136.htm

 

Select Case e.DataCol.Name

    Case "品名","累计销售数"

        Dim dr As DataRow

        Dim mr As DataRow = e.DataRow

        Dim drs As List(of DataRow)

        dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [品名] = '" & mr("品名") & "'", "[_SortKey] Desc")

        If dr Is Nothing Then

            mr("期间销售数") = mr("累计销售数") - mr("累计销售数")

            dr = mr

        End If

        drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [品名] = '" & dr("品名") & "'", "[_SortKey]")

        For i As Integer = 1 To drs.Count - 1

            drs(i)("累计销售数") = drs(i)("累计销售数") - drs(i-1)("累计销售数")

        Next

        If e.DataCol.Name = "品名" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then

            dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") & " And [品名] = '" & e.OldValue & "'", "[_SortKey] Desc")

            If dr Is Nothing Then

                dr = e.DataTable.Find("[品名] = '" & e.OldValue & "'", "[_SortKey]")

                If dr IsNot Nothing Then

                    dr("期间销售数") = dr("累计销售数") - dr("累计销售数")

                End If

            End If

            If dr IsNot Nothing Then

                drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") & " And [品名] = '" & dr("品名") & "'", "[_SortKey]")

                For i As Integer = 1 To drs.Count - 1

                    drs(i)("期间销售数") = drs(i)("累计销售数") - drs(i-1)("累计销售数")                Next

            End If

        End If

End Select

上传具体实例测试。

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

点击浏览该文件:测试项目 (2).table

我把原来执行的结果删除了，然后再输入数据，没变动得出那个计算结果来呀

 

重置列

 

即可重算所有行的C列值。

菜单的“日常工作”功能区，也有重置列的按钮：