各位可以参考此方法生成任意格式的Excel,可能很多人直接用程序来一行行的写,想想这是多复杂的事情啊,想设置Excel格式就更加复杂了.而且程序循环效率也极慢,照我方法可以直接下载后就直接打印,格式全部已设置好.好了,废话不多说,觉得好用多多推广,转载请注示一下来自http://www.cnbolgs.com/xiaobier,谢谢.以下是生成后的效果图:
我这里用的数据库是SQL server.说一下思路:
先制作一个excel样式,如上图,我这里标题是固定的,部门和日期是动态的,列名是固定的,中间那块数据是动态的,部门考勤员名字是动态的,其他都是静态.
把样式先做好.以下图是我先做好的样式:
8,9为什么要留两行,是为了第10行的统计函数设置,合计那行是已经设置好excel公式的,如C10是Sum(C8:C9)依此类推,如果往8,9中间插入行那excel
会自动扩展第10行的公式,如原是Sum(C8:C9)那插入一行就自动变成sum(C8:C10),这个大家应该都知道.而且往8,9中间插入行他的格式是根前一行
相同的,所以就这里就设置好了动态数据区的格式了.其他几个地方也是一样.
第三行的日期我是让他引用sheet2中的A2,部门是B2,考勤员是C2
下图是Sheet2,其中sheetdate,department,oper是列名,用来向sheet2这三个字段插入记录的
效果图中的主要数据区我是放在sheet3中,以下是sheet3样式,同样是列名,以效果图中是对应的.
sheet2只有一条记录,所以直接在sheet1对应处引用即可,如何让sheet1中引用sheet3中的数据,因为sheet3中的记录是动态的,
也不知道有多少行,所以要利用宏了.写宏其实也很简单,我这里也没写多少行代码.
Code
SubMacro1()
'
'Macro1Macro
'宏由XiaoBier录制，时间:2008-9-24
'
'快捷键:Ctrl+q
'
DimiAsInteger
DimcountAsInteger
DimrownumAsInteger
count=Sheet3.UsedRange.Rows.count-1'这里是获取sheet3中的记录行数,减掉列名首行
Sheets("Sheet1").Select '选中sheet1
'在sheet1中的8和9行之间插入行数
Fori=3TocountStep1
Sheet1.Range("A9").Select
Selection.EntireRow.Insert
Nexti
rownum=1
'将sheet1中数据行引用sheet3中的数据
Fori=8Tocount+7Step1
Range("A"&i)=rownum
Range("B"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Sheet3!R[-6]C[-1],"""")"
Range("C"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("D"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("E"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("F"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("G"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("H"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("I"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("J"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("K"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("L"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("M"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("N"&i).FormulaR1C1="=IF(Sheet3!R[-6]C[-1]>0,Value(Sheet3!R[-6]C[-1]),"""")"
Range("O"&i)=Null
rownum=rownum+1
Nexti
EndSub
'这个子程序让一打开excel就自动调用宏
Subauto_open()
CallMacro1
EndSub
是不是很简单,如果你不会写宏就录制吧,要什么操作就录制.然后把录制的代码copy下来就可以了.
样式文件建好.要生成excel的时候调用System.IO.File.Copy复制成目标名.没有其他操作.
接着是利用SQl openrowset往目标文件名的sheet3,和sheet2插入记录.
Code
set@sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel8.0;HDR=YES;DATABASE='+@path+@fname+''',[sheet3$])'
exec('insertinto'+@sql+'(name,late,patient,privacy,cyesis,suckle,children,relatives,delay,rest,law,adjust,year)select*from#sheetrst3')
--注意上面的列名好和sheet3中是对应的
set@sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel8.0;HDR=YES;DATABASE='+@path+@fname+''',[sheet2$])'
exec('insertinto'+@sql+'(sheetdate,department,oper)select'''+@date+''','''+@deptname+''','''+@operator+'''')
大功告成...