51Testing软件测试论坛

 找回密码
 (注-册)加入51Testing

QQ登录

只需一步,快速开始

微信登录,快人一步

手机号码,快捷登录

查看: 4818|回复: 19
打印 上一主题 下一主题

[原创] 关于操作Excel的Vbs问题

[复制链接]

该用户从未签到

跳转到指定楼层
1#
发表于 2010-3-31 14:40:23 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
我这有一个5万行数据的Excel。里边有重复的数据。
我想做一个vbs,自动执行来完成 删除里边重复的数据(重复的数据只留一行,每条数据是一行)
Excel的格式是下面
产品名称    产地     联络方式
苹果        山东     xxxxxxx
芒果        海南     aaaaaaa
芒果        海南     aaaaaaa
芒果        海南     aaaaaaa

我希望运行完vbs后Excel中的内容变成下边这样
产品名称    产地     联络方式
苹果        山东     xxxxxxx
芒果        海南     aaaaaaa

为了满足上边的要求,我编写了下面的vbs代码。
dim oExcel,mySheet,GetCellValue
set oExcel = CreateObject( "Excel.Application" )
set myExcl = oExcel.WorkBooks.Open( "E:\exl\tzl.xls" )
Set excelSheet = myExcl.ActiveSheet
cnt = 1
baseValue = "0"
myCellValue = "1"
while baseValue <> ""
        baseValue = excelSheet.Cells(cnt, 1)
        myCellValue = "1"
        subCnt = cnt + 1
        While myCellValue <> ""
                myCellValue = excelSheet.Cells(subCnt, 1)
                if baseValue = myCellValue then
                        excelSheet.Rows(subCnt).Delete
                        myExcl.Save
                else
                        subCnt = subCnt + 1
                end if
        Wend
        cnt = cnt + 1
Wend
myExcl.Save
oExcel.WorkBooks.Close
oExcel.Quit
Set excelSheet = nothing
Set myExcl = nothing
Set oExcel = nothing

这个代码我对于一个十多条重复的数据做过测试,好用,但是对于5万行的Excel就不好用了,执行一天也执行不完,而且不能删掉重复数据。
不知道我这个代码是不是有问题,希望各位高手指教。急啊!!谢谢了!
分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏
回复

使用道具 举报

该用户从未签到

2#
发表于 2010-3-31 15:11:21 | 只看该作者
你查一下把EXCEL作为数据库来访问,而不是作为对象~~
回复 支持 反对

使用道具 举报

该用户从未签到

3#
发表于 2010-3-31 15:27:04 | 只看该作者
问题应该是出在两层循环的设计上。
按照你的程序,实际上执行的次数是excel里行数的平方,也就是5w的平方。。。所以你执行一天也执行不完。
感觉你应该重新修改一下设计,然后先将excel排序之后再处理。
回复 支持 反对

使用道具 举报

该用户从未签到

4#
 楼主| 发表于 2010-3-31 15:30:06 | 只看该作者
>你查一下把EXCEL作为数据库来访问,而不是作为对象~~

不好意思,我查了关于作为数据库访问,但是具体操作还是不知道如何编写,
我只查到了下边的代码类似于数据库访问。但是这样应该如何设置Excel文件啊,怎么设置列名,再有最下边那行代码是怎么取的数据,什么意思啊?
strFileName   =   "1.xls"   
  conn.ConnectionString   =   "Provider=Microsoft.Jet.OLEDB.4.0;Data   Source="   &   strFileName   &   ";   Extended   Properties=   Excel   8.0;"   
  conn.Open   
  rs.Open   "select   *   from   [123-CD$]",   conn,   3,   3
回复 支持 反对

使用道具 举报

该用户从未签到

5#
 楼主| 发表于 2010-3-31 15:36:24 | 只看该作者
原帖由 shotstar 于 2010-3-31 15:27 发表
问题应该是出在两层循环的设计上。
按照你的程序,实际上执行的次数是excel里行数的平方,也就是5w的平方。。。所以你执行一天也执行不完。
感觉你应该重新修改一下设计,然后先将excel排序之后再处理。

那么要先怎么排序呢,还有什么设计方案呢,怎么都得让数据都比较一下吧,哎,没头绪。
回复 支持 反对

使用道具 举报

该用户从未签到

6#
发表于 2010-3-31 15:37:01 | 只看该作者

回复 4# 的帖子

你要重新考虑你的算法。目前的算法效率太低,所以才会在数据量大的时候执行很慢。
即使你采用对象库访问,你的算法仍然还是会出现效率低的问题,所以用vbs还是用数据库访问其实不是最本质的问题。
回复 支持 反对

使用道具 举报

该用户从未签到

7#
发表于 2010-3-31 15:42:05 | 只看该作者

回复 5# 的帖子

我初步的想法是先排序,让excel自己排序就好了。你直接拿已经排序好的excel去处理,我觉得这样比较方便一些。
至于设计方案,因为已经排序完成了,只要上下两行的单元格进行比较就可以了,用到2个变量,初始值First,比较值Second,比如先获取到第二行和第三行的数据保存到First和Second,然后进行比较,如果相同就删除第三行,然后重新获取Second;如果不同就把不同的那个值给First,再去获取新的Second。
这样的话,大概执行次数应该是5w-1次左右吧。
唯一我担心的问题就是在删除的时候重新获取下一行的数据会不会出错。
回复 支持 反对

使用道具 举报

该用户从未签到

8#
 楼主| 发表于 2010-3-31 15:42:21 | 只看该作者

回复 6# 的帖子

朋友,我也知道这个算法效率低啊,可是我也只能想到这个算法,您有更好的算法吗,请多多指教,谢谢了。
回复 支持 反对

使用道具 举报

该用户从未签到

9#
 楼主| 发表于 2010-3-31 16:00:28 | 只看该作者

回复 7# 的帖子

我把程序改了,做了简单的测试,好像可以,不知道5万条好不好用了。
dim oExcel,mySheet,GetCellValue
set oExcel = CreateObject( "Excel.Application" )
set myExcl = oExcel.WorkBooks.Open( "D:\wl\1.xls" )
Set excelSheet = myExcl.ActiveSheet
cnt = 2
cmpFlag = false
secValue = "1"
while secValue <> ""
        fstValue = excelSheet.Cells(cnt, 1)
        secValue = excelSheet.Cells(cnt+1, 1)
        if fstValue = secValue then
                excelSheet.Rows(cnt+1).Delete
        else
                cnt = cnt + 1
        end if
Wend
myExcl.Save
oExcel.WorkBooks.Close
oExcel.Quit
Set excelSheet = nothing
Set myExcl = nothing
Set oExcel = nothing
回复 支持 反对

使用道具 举报

该用户从未签到

10#
 楼主| 发表于 2010-3-31 16:07:51 | 只看该作者
终于成功了。非常感谢shotstar的帮助!!
回复 支持 反对

使用道具 举报

该用户从未签到

11#
发表于 2010-3-31 16:11:36 | 只看该作者
客气。其实这个问题说明了不要把所有的问题都交给代码来解决,结果是搞的很复杂。
现在这样简单化处理不是很方便么,呵呵。

因为俺是懒人,所以一般都喜欢找比较懒的办法来解决问题。
不是有句话么,就是因为懒人的存在,才有科技的进步,哈哈。
回复 支持 反对

使用道具 举报

该用户从未签到

12#
发表于 2010-3-31 16:25:06 | 只看该作者
原帖由 crazyoldman 于 2010-3-31 16:00 发表
我把程序改了,做了简单的测试,好像可以,不知道5万条好不好用了。
dim oExcel,mySheet,GetCellValue
set oExcel = CreateObject( "Excel.Application" )
set myExcl = oExcel.WorkBooks.Open( "D:\wl\1.xls" )
...



这段代码有问题吧,我执行你的代码,也模拟了你的数据
结果却是这样的

苹果&nbsp;        &nbsp;山东&nbsp;        xxxxxxx
芒果         海南        aaaaaaa
苹果&nbsp;        &nbsp;山东&nbsp;        xxxxxxx
芒果         海南        aaaaaaa
桔子        成都         bbbbbbb
回复 支持 反对

使用道具 举报

该用户从未签到

13#
发表于 2010-3-31 16:27:46 | 只看该作者
楼主的第一段代码,结果才是正确了,我刚测试了
回复 支持 反对

使用道具 举报

该用户从未签到

14#
发表于 2010-3-31 16:41:43 | 只看该作者

回复 12# 的帖子

第二段代码的前提条件是已排序。你的内容未排序。
第一段可以成功那是肯定的,因为他是完全遍历了一遍。。
回复 支持 反对

使用道具 举报

该用户从未签到

15#
发表于 2010-3-31 17:13:21 | 只看该作者
确实是这样的,第二段代码,使用排序后的excel,结果就是正确的

不过这个算法的效率低,我用1万条数据来测试了一下,目前还没有出结果
回复 支持 反对

使用道具 举报

该用户从未签到

16#
发表于 2010-3-31 17:18:21 | 只看该作者

回复 15# 的帖子

我在上面已经说了,那个遍历大概是行数的平方,不过后来想想应该是行数的阶乘。
也就是说要比较的次数为你用的1w的阶乘,想想这个数值有多大,所以一时半会用第一个方法是很难算出结果的。即使完全用代码来解决,我想第一步做的也是要排序,所以还不如先排序好再说。
至于无序的excel,那肯定需要修改算法了,原先的算法效率太低。要改进算法,我懒得想了,太麻烦了。。
回复 支持 反对

使用道具 举报

  • TA的每日心情
    奋斗
    2014-12-8 10:55
  • 签到天数: 1 天

    连续签到: 1 天

    [LV.1]测试小兵

    17#
    发表于 2010-3-31 20:51:05 | 只看该作者
    算法果然很重要!
    回复 支持 反对

    使用道具 举报

    该用户从未签到

    18#
    发表于 2010-4-10 19:02:06 | 只看该作者
    可以试试通过ADO访问Excel,执行SQL语句来做:
      ' 创建ADO
      Set conn= createobject("ADODB.Connection")
      Set conn2= createobject("ADODB.Connection")

      ' 打开链接
      conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test.xls;Extended

    Properties=Excel 8.0"
      conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test2.xls;Extended

    Properties=Excel 8.0"

      ' 查询
      sql= "SELECT item,address,value FROM [Sheet1$] Group by item,address,value"
      Set RecSet = conn.Execute(sql)
      RecSet.MoveFirst
      While Not RecSet.EOF
         'Msgbox RecSet.Fields("item").Value & " , " & RecSet.Fields.Item(1).Value

      ' 插入
      sql_insert = "insert into [Sheet1$] Values('" & RecSet.Fields("item").Value & "','"&RecSet.Fields

    ("address").Value & "','" & RecSet.Fields("value").Value & "')"
      conn2.Execute( sql_insert )

         RecSet.MoveNext
      Wend

    ' 关闭链接
      conn.close
      conn2.close

    按这种方法需要:
    1、先把Excel的第一行数据改成列名,例如:item、address、value
    2、新建一个excel表,只包含表头不包含数据
    回复 支持 反对

    使用道具 举报

    该用户从未签到

    19#
    发表于 2010-4-12 12:00:37 | 只看该作者
    另存为csv格式,然后直接用 sort 和uniq 处理即可
    我测试了一个近4W行的文本,效率不错。
    回复 支持 反对

    使用道具 举报

    该用户从未签到

    20#
    发表于 2010-4-15 10:42:55 | 只看该作者
    有时间验证一下,学习中。。
    回复 支持 反对

    使用道具 举报

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    小黑屋|手机版|Archiver|51Testing软件测试网 ( 沪ICP备05003035号 关于我们

    GMT+8, 2024-11-10 18:23 , Processed in 0.079790 second(s), 28 queries .

    Powered by Discuz! X3.2

    © 2001-2024 Comsenz Inc.

    快速回复 返回顶部 返回列表