51Testing软件测试论坛

标题: 关于操作Excel的Vbs问题 [打印本页]

作者: crazyoldman 时间: 2010-3-31 14:40
标题: 关于操作Excel的Vbs问题
我这有一个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就不好用了，执行一天也执行不完，而且不能删掉重复数据。
不知道我这个代码是不是有问题，希望各位高手指教。急啊！！谢谢了！

作者: k4124k 时间: 2010-3-31 15:11
你查一下把EXCEL作为数据库来访问，而不是作为对象~~

作者: shotstar 时间: 2010-3-31 15:27
问题应该是出在两层循环的设计上。
按照你的程序，实际上执行的次数是excel里行数的平方，也就是5w的平方。。。所以你执行一天也执行不完。
感觉你应该重新修改一下设计，然后先将excel排序之后再处理。

作者: crazyoldman 时间: 2010-3-31 15:30
>你查一下把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

作者: crazyoldman 时间: 2010-3-31 15:36

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

那么要先怎么排序呢，还有什么设计方案呢，怎么都得让数据都比较一下吧，哎，没头绪。

作者: shotstar 时间: 2010-3-31 15:37
标题: 回复 4# 的帖子
你要重新考虑你的算法。目前的算法效率太低，所以才会在数据量大的时候执行很慢。
即使你采用对象库访问，你的算法仍然还是会出现效率低的问题，所以用vbs还是用数据库访问其实不是最本质的问题。

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

作者: crazyoldman 时间: 2010-3-31 15:42
标题: 回复 6# 的帖子
朋友，我也知道这个算法效率低啊，可是我也只能想到这个算法，您有更好的算法吗，请多多指教，谢谢了。

作者: crazyoldman 时间: 2010-3-31 16:00
标题: 回复 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

作者: crazyoldman 时间: 2010-3-31 16:07
终于成功了。非常感谢shotstar的帮助！！

作者: shotstar 时间: 2010-3-31 16:11

客气。其实这个问题说明了不要把所有的问题都交给代码来解决，结果是搞的很复杂。
现在这样简单化处理不是很方便么，呵呵。

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

作者: chinafree 时间: 2010-3-31 16:25

原帖由 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" )
...

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

苹果   山东  xxxxxxx
芒果海南 aaaaaaa
苹果   山东  xxxxxxx
芒果海南 aaaaaaa
桔子成都 bbbbbbb

作者: chinafree 时间: 2010-3-31 16:27
楼主的第一段代码，结果才是正确了，我刚测试了

作者: shotstar 时间: 2010-3-31 16:41
标题: 回复 12# 的帖子
第二段代码的前提条件是已排序。你的内容未排序。
第一段可以成功那是肯定的，因为他是完全遍历了一遍。。

作者: chinafree 时间: 2010-3-31 17:13
确实是这样的，第二段代码，使用排序后的excel，结果就是正确的

不过这个算法的效率低，我用1万条数据来测试了一下，目前还没有出结果

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

作者: x35064347 时间: 2010-3-31 20:51
算法果然很重要！

作者: TIB 时间: 2010-4-10 19:02
可以试试通过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表，只包含表头不包含数据

作者: hongewuyan 时间: 2010-4-12 12:00
另存为csv格式，然后直接用 sort 和uniq 处理即可
我测试了一个近4W行的文本，效率不错。

作者: 小新弦子 时间: 2010-4-15 10:42
有时间验证一下，学习中。。

欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/)