Foxtable(狐表)用户栏目专家坐堂 → 多余空白行


  共有2105人关注过本帖树形打印复制链接

主题:多余空白行

帅哥哟,离线,有人找我吗?
dang106
  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:婴狐 帖子:4 积分:91 威望:0 精华:0 注册:2021/1/12 14:30:00
多余空白行  发帖心情 Post By:2021/1/14 11:33:00 [只看该作者]

按帮助写的导入代码,不知道为什么导入时多了好多空白行.
http://www.foxtable.com/webhelp/index.htm?page=2492.htm


代码如下:
Dim Book As New XLS.Book ("c:\po.tracking.xls")
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("Po_Tracking").StopRedraw()
'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致
Dim nms() As String = {"Customer_Country","ship_to_country","Sales_Order_No","Sales_Order_Date","Customer_PO_number","Customer_PO_Date","Customer_code","Customer_Name","Ship_to_code","SO_remarks","Category","大类","Item_code","Item_description","Customer_item_code","Customer_request_date","Sales_price_with_tax","Sales_order_value","Purchase_Order_number","Purchase_Order_date","采购订单审批日期","Vendor_code","Vendor_Name_ZH","Vendor_Name_EN","PI_number","Pur_Order_currency","PO_Remarks","Pur_Order_booked_QTY","PO_discount","PO_price_with_tax","Discount_PO_price","金额","Ex_works_date","Revised_ex_works_date","Acutal_ex_date","Delay_reason","Forwarder","Acutal_FOB_date","Vendor_invoice_number","入库单号","采购fa piao单据号","销售fa piao号","Remarks","提单号"}
'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题
For n As Integer = 1 To Sheet.Rows.Count -1
    Dim r As Row = Tables("Po_Tracking").AddNew()
    For m As Integer = 0 To nms.Length - 1
         r(nms(m)) = Sheet(n,m).Value
    Next
Next
Tables("Po_Tracking").ResumeRedraw()

 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:105495 积分:536466 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2021/1/14 11:37:00 [只看该作者]

execl本身就有空行,加个判断

For n As Integer = 1 To Sheet.Rows.Count -1
if Sheet(n,0).text.trim() > "" 如果第一列有值
    Dim r As Row = Tables("Po_Tracking").AddNew()
    For m As Integer = 0 To nms.Length - 1
         r(nms(m)) = Sheet(n,m).Value
    Next
end if
Next

 回到顶部
帅哥哟,离线,有人找我吗?
dang106
  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:婴狐 帖子:4 积分:91 威望:0 精华:0 注册:2021/1/12 14:30:00
  发帖心情 Post By:2021/1/14 14:12:00 [只看该作者]

以下是引用有点蓝在2021/1/14 11:37:00的发言:
execl本身就有空行,加个判断

For n As Integer = 1 To Sheet.Rows.Count -1
if Sheet(n,0).text.trim() > "" 如果第一列有值
    Dim r As Row = Tables("Po_Tracking").AddNew()
    For m As Integer = 0 To nms.Length - 1
         r(nms(m)) = Sheet(n,m).Value
此主题相关图片如下:53f4c2ea02a965e0881434c1e479570.png
按此在新窗口浏览图片
    Next
end if
Next

你好,我添加了这判断,不知道为啥数据多导了一次,两个相同数据间,还是存在空白行.


 回到顶部
帅哥哟,离线,有人找我吗?
dang106
  4楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:婴狐 帖子:4 积分:91 威望:0 精华:0 注册:2021/1/12 14:30:00
  发帖心情 Post By:2021/1/14 14:15:00 [只看该作者]

或者,可不可以添加一个删除空白行的按钮?但不知道代码如何写

 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  5楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:105495 积分:536466 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2021/1/14 14:16:00 [只看该作者]

贴出完整代码说明

 回到顶部
帅哥哟,离线,有人找我吗?
dang106
  6楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:婴狐 帖子:4 积分:91 威望:0 精华:0 注册:2021/1/12 14:30:00
  发帖心情 Post By:2021/1/14 14:18:00 [只看该作者]

Dim Book As New XLS.Book ("c:\po.tracking.xls")
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("Po_Tracking").StopRedraw()
'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致
Dim nms() As String = {"Customer_Country","ship_to_country","Sales_Order_No","Sales_Order_Date","Customer_PO_number","Customer_PO_Date","Customer_code","Customer_Name","Ship_to_code","SO_remarks","Category","大类","Item_code","Item_description","Customer_item_code","Customer_request_date","Sales_price_with_tax","Sales_order_value","Purchase_Order_number","Purchase_Order_date","采购订单审批日期","Vendor_code","Vendor_Name_ZH","Vendor_Name_EN","PI_number","Pur_Order_currency","PO_Remarks","Pur_Order_booked_QTY","PO_discount","PO_price_with_tax","Discount_PO_price","金额","Ex_works_date","Revised_ex_works_date","Acutal_ex_date","Delay_reason","Forwarder","Acutal_FOB_date","Vendor_invoice_number","入库单号","采购fa piao单据号","销售fa piao号","Remarks","提单号"}
'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题
For n As Integer = 1 To Sheet.Rows.Count -1
    Dim r As Row = Tables("Po_Tracking").AddNew()
    For m As Integer = 0 To nms.Length - 1
         r(nms(m)) = Sheet(n,m).Value
    Next
Next
Tables("Po_Tracking").ResumeRedraw()

 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  7楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:105495 积分:536466 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2021/1/14 14:21:00 [只看该作者]

这个代码不可能会重复导入,除非您又调用了一次这个代码。每点击一次按钮肯定就导入一次数据,如果不想导入已经存在的数据,查询判断一下:http://www.foxtable.com/webhelp/topics/2334.htm

 回到顶部