工欲善其事,必先利其器。
如何用VBA提高我们的效率。这次列举几个实际的例子。
单体测试阶段,我们可能需要写大量的重复代码。有时候需要为整理代码注释而精疲力竭。这个阶段我们可以利用VBA来导入导出数据,生产入力数据,生成断言,制造全满位数据等等。
在此之前我要先介绍一下excel中数据的格式约定。
物理表名 | 表的显示名 | |
字段1的汉字名 | 字段2的汉字名 | 字段n的汉字名 |
字段1的物理名 | 字段2的物理名 | 字段n的物理名 |
字段1的类型 | 字段2的类型 | 字段n的类型 |
字段1的notnull属性 | 字段2的notnull属性 | 字段n的notnull属性 |
字段1的数据1 | 字段2的数据2 | 字段n的数据n |
之后的所有工具都是根据这个格式去实现的。还有操作的时候需要把焦点放在物理表名单元格,这是起始位置,这个很重要。
下面开始实际的例子。
第一个功能:根据表名获取表结构和数据
1,定义
Set sh=ActiveSheet '获取正在操作的sheet页
Set cl=ActiveCell '获取光标所在的单元格,这就是为什么我要求操作的时候,光标一定要停在表名上。这样我们可以更好做相对偏移取其他的单元格数据。
line_comment=cl.row+1 '字段的显示名所在的行
line_column=cl.row+2 '字段物理名所在的行
line_type=cl.row+3 '代表字段类型所在的行
set cn=数据连接字
set rs=结果集
2,表结构的取得。连接数据的语句,直接百度吧,很容易找到。我就不赘述了。
select b.comments,a.column_name,a.data_type,case when a.nullable='N' then 'NotNull' else '' end from user_tab_columns a left join user_col_comments b on a.table_name=b.table_name and a.column_name=b.column_name where a.table_name =表名 order by a.column_id
rs.open 上记sql cn
iCol = cl.column'列的开始位置
do while not rs.eof
for i=0 to rs.fields.count
sh.cells(i+line_comment,icol)=“‘”+cstr(rs.fields(i).value)
next i
icol=icol+1
rs.movenext
loop
3,取数据。数据区是表名所在行往下数5行,即cl.row+5
do while not rs.eof
for i=0 to rs.fields.count -1
set cl_t = sh.range(sh.cells(cl.row+1,1),sh.cells(cl.row+3,sh.usedrange.columns.count)).find(rs.fields(i).name)'特定数据所在列,数据有可能是乱序的,为了保证数据和列对应。
sh.cells(cl.row+5,row.cl_t)=rs.fidlds(i).value
next i
loop
至此,表结构和数据都已经全部取下来了。接下来就是根据测试式样修改数据,根据业务做一份预想数据。
第二个功能就是把数据生成insert文。再通过@sql或者其他方法导入数据库中。下面给出部分生产sql的部分代码。
1,获取表结构和数据的区域大小。这里要注意保证区域的连续性。
data_row=cl.end(4).row'获得最大行数
data_col=sh.cells(cl.row+1,cl.column).end(2).column’获得最大列数
talbename=cl.value
redim preserve arr(data_row)
for i=cl.row+5 to data_row
strsql_column=“insert into ”+tablename+” (”
strsql_value=“(”
first_flg=true
for j=cl.column to data_col
if first_flg then
if sh.cells(i,j).value<>”” and instr(sh.cells(i.j).value,”NULL”)=0 then
if sh.cells(line_type,j).value=“NUMBER” or sh.cells(line_type,j).value=“NUMERIC” then
strsql_column=strsql_column+sh.cells(line_column,j).value
strsql_value=strsql_value+sh.cells(i,j).value
else if sh.cells(line_type,j).value=“DATE” or sh.cells(line_type,j).value=“TIMESTAMP(6)” then
strsql_column=strsql_column+sh.cells(line_column,j).value
strsql_value=strsql_value+”to_date(‘”+left(cstr(sh.cells(i,j).value),10)+”’,’yyyy-mm-dd’)”
else
strsql_column=strsql_column+sh.cells(line_column,j).value
strsql_value=strsql_value+”’”+sh.cells(i,j).value+”’”
end if
end if
first_flg=false
else
if sh.cells(i,j).value<>”” and instr(sh.cells(i.j).value,”NULL”)=0 then
if sh.cells(line_type,j).value=“NUMBER” or sh.cells(line_type,j).value=“NUMERIC” then
strsql_column=strsql_column+”,”+sh.cells(line_column,j).value
strsql_value=strsql_value+”,”+sh.cells(i,j).value
else if sh.cells(line_type,j).value=“DATE” or sh.cells(line_type,j).value=“TIMESTAMP(6)” then
strsql_column=strsql_column+”,”+sh.cells(line_column,j).value
strsql_value=strsql_value+”,”+”to_date(‘”+left(cstr(sh.cells(i,j).value),10)+”’,’yyyy-mm-dd’)”
else
strsql_column=strsql_column+sh.cells(line_column,j).value
strsql_value=strsql_value+”,’”+sh.cells(i,j).value+”’”
end if
end if
end if
do events
next j
if not(strsql_column=“insert into ”+tablename+” (” ) then
strV=strsql_column+”) value ”+strsql_value+”);”
arr(i)=strV
end if
next i
2,把上面的数组arr出力到.sql的文件中即可。
pth=thisworkbook.path
open pth&”|”&tablename&”.sql” for output as #1
for i=0 to ubound(arr)
if not(arr(i)=“” or isnull(arr(i))) then
print #1,arr(i)
end if
close #1
shell “explorer.exe” & pth, vbnormalfocus
还有功能3,生成断言。功能4,把数据绑定到对象list中。下回再接着整理。以前写的代码都打印在A4之上,这次整理全靠手打的,速度太慢了。以后考虑直接上图片。
因为都是手打的那面有打错的地方,请见谅。重要的是领会精神。