技术放送!从数据库中生成电子表格文件完整程序,但还差表格样式设置,请各位高手指教

hchxxzx 2002-07-22 06:31:23
加精
大放送!从数据库中生成电子表格文件完整程序,但还差表格样式设置,请各位高手指教
以下是一个完整的程序,就差表格样式设置了,不知道如何写,请有经验的同行指导一下。

<%
'定义数据库链接
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&server.MapPath("winpy.mdb")
set rs=server.createobject("adodb.recordset")


'删除曾经创建的文件
function delfile(filenm)
set objfso=server.createobject("scripting.filesystemobject")
if objfso.FileExists(filenm) then
objfso.deletefile filenm,false
end if
set objfso=nothing
end function

'关闭电子表格链接
function xls_close()
set xlbook=nothing
xlApp.Quit
set xlapp=nothing
end function

'操作宏使其对表格进行格式定义
'问题就在这里,这个宏是从电子表格中直接贴出来的,但不能直接用,必须做一定的修改。
Sub set_hong()
Range("A20:D45").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "黑体"
.FontStyle = "加粗"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

on error resume next


Set xlApp = CreateObject("Excel.Application") '创建对象
xlApp.Visible = true '隐藏电子表格
Set xlBook = xlApp.Workbooks.Open(server.mappath("dh_excel.xls")) '打开文件
Set xlSheet = xlBook.Worksheets(1)'打开第一个表

'设置格式,如果出错则马上关闭电子表格链接,不使滞留在内存中
'就是设置格式出错
call set_hong()
if err<>0 then
call xls_close()
end if

'打开数据库内容
sql="select * from dhhm order by id"
rs.open sql,cn,1,1
rsl=rs.recordcount
j=0

'该模板从第四行第1列写起,数据库中每行共有四列,行数不定
do while not rs.eof
for k=1 to 4
xlSheet.Cells(j+4,k) = rs(k-1)
next
j=j+1
rs.movenext
loop
rs.close
set rs=nothing
set cn=nothing

xlApp.DisplayAlerts=false
filename=server.mappath("./")&"\txt1.xls"
'删除存在的相同文件名的文件
call delfile(filename)

xlBook.Saveas filename '将此文件另存
xlbook.close'关闭连接


if err<>0 then
response.write "操作失败!"
err.clear
else
response.write "操作成功!"
end if

'关闭电子表格连接,一定要关闭,否则有问题
call xls_close()
%>
<a href="<%=filename%>" target="_blank">下载文件</a>
...全文
69 点赞 收藏 18
写回复
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
digime 2002-08-02
学习
回复
hchxxzx 2002-08-01
谢谢大家支持,问题还是没有解决,但可以用别的方式暂时解决一下。以后再看看啦。
结账
回复
MeXP 2002-07-23
长了点,让我也来学习学习
回复
hchxxzx 2002-07-23
实际上上面文档的错误不在于设置成何种格式
如果直接运行上述文档并把on error resume next及if err<>0 then 这两句去除,运行结果将是
“类型不匹配: 'Range'”或“缺少对象: 'Selection'”,这是因为格式设置的函数里面的参数与当前页面中的数据库源(?表达不清晰,如XLAPP等,通俗讲就是Selection.Borders这一句到底是哪个数据库源的呢?我记得是要激活某个链接对象)不相匹配导致的,如下面这一句
Sub set_hong()
Range("A20:D45").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
。。。。
直接运行将报告-类型不匹配: 'Range',但我修改一下
xlSheet.Range("A20:D45").Select
它就不报这一句错误了,开始报告Selection.Borders(xlDiagonalDown).LineStyle = xlNone这一句“缺少对象”

上述的格式设置函数是我直接从生成的电子表格中设置宏之后拷贝出来的,我想是可以直接应用,不需要填写别的参数(除了从哪行到哪行选定之外),现在主要是将当前的“对象”与格式设置里面的“对象”联系起来

但也可能是我理解不够深刻从而导致方向错误,还请有经验者指导
回复
tripofdream 2002-07-23
好久没见高质量的贴了,加精华!
回复
tripofdream 2002-07-23
好久没见高质量的贴了,加精华!
回复
tripofdream 2002-07-23
这是Foxpro中的相关常量定义,供参考:
*-- Code begins here.
*-- msgraph.h
*-- Microsoft Graph defines for Microsoft Graph constants.

#define xlZero 2
#define xlY 1
#define xlXYScatter -4169
#define xlX -4168
#define xlWizardDisplayNever 2
#define xlWizardDisplayDefault 0
#define xlWizardDisplayAlways 1
#define xlVertical -4166
#define xlValue 2
#define xlUpward -4171
#define xlUp -4162
#define xlTriangle 3
#define xlTransparent 2
#define xlTop -4160
#define xlThin 2
#define xlThick 4
#define xlStretch 1
#define xlStError 4
#define xlStDev -4155
#define xlStar 5
#define xlStack 2
#define xlSquare 1
#define xlSolid 1
#define xlSingleAccounting 4
#define xlSingle 2
#define xlShowValue 2
#define xlShowPercent 3
#define xlShowLabelAndPercent 5
#define xlShowLabel 4
#define xlSeries 3
#define xlSemiGray 7510
#define xlSecondary 2
#define xlScale 3
#define xlRows 1
#define xlRight -4152
#define xlRadar -4151
#define xlPrimary 1
#define xlPower 4
#define xlPolynomial 3
#define xlPlusValues 2
#define xlPlus 9
#define xlPie 5
#define xlPicture -4147
#define xlPercent 2
#define xlOutside 3
#define xlOpaque 3
#define xlNotPlotted 1
#define xlNormal -4143
#define xlNone -4142
#define xlNoCap 2
#define xlNextToAxis 4
#define xlMovingAvg 6
#define xlMinusValues 3
#define xlMinimum 4
#define xlMinimized -4140
#define xlMedium -4138
#define xlMaximum 2
#define xlMaximized -4137
#define xlLow -4134
#define xlLogarithmic -4133
#define xlLinear -4132
#define xlLine 4
#define xlLightVertical 12
#define xlLightUp 14
#define xlLightHorizontal 11
#define xlLightDown 13
#define xlLeft -4131
#define xlJustify -4130
#define xlInterpolated 3
#define xlInside 2
#define xlHorizontal -4128
#define xlHigh -4127
#define xlHairline 1
#define xlGrid 15
#define xlGray8 18
#define xlGray75 -4126
#define xlGray50 -4125
#define xlGray25 -4124
#define xlGray16 17
#define xlFixedValue 1
#define xlExponential 5
#define xlDownward -4170
#define xlDown -4121
#define xlDoughnut -4120
#define xlDoubleAccounting 5
#define xlDouble -4119
#define xlDot -4118
#define xlDistributed -4117
#define xlDiamond 2
#define xlDefaultAutoFormat -1
#define xlDashDotDot 5
#define xlDashDot 4
#define xlDash -4115
#define xlCustom -4114
#define xlCross 4
#define xlCrissCross 16
#define xlCorner 2
#define xlContinuous 1
#define xlCombination -4111
#define xlColumns 2
#define xlColumn 3
#define xlCircle 8
#define xlChecker 9
#define xlCenter -4108
#define xlCategory 1
#define xlCap 1
#define xlBuiltIn 0
#define xlBottom -4107
#define xlBoth 1
#define xlBar 2
#define xlAutomatic -4105
#define xlArea 1
#define xl3DSurface -4103
#define xl3DPie -4102
#define xl3DLine -4101
#define xl3DColumn -4100
#define xl3DBar -4099
#define xl3DArea -409
*-- Code ends here
回复
zt371 2002-07-23
我那里有一个文档,详细的谈了这个问题.不过现在不在我身边.给我发短消息,告诉我你的信箱.回头我发给你.
:( 还要打字
回复
tripofdream 2002-07-23
象Selection.Borders(xlDiagonalDown).LineStyle = xlNone中
xlNone以及xlContinuous、xlThin等常量要用相应的数值代替或加入这些常量的定义。
目录尚未知道是否有相应的常量表可查,但可以在VBA中利用msgbox函数得到其值,如msgbox xlNone 结果:-4142
回复
hwstu 2002-07-23
建议再写一个生成图表,和把宏一起写进去就牛了,呵呵
图表可以写,宏我以前见到一个但是现在找不到了
回复
coffeegirl 2002-07-23
GZ!
回复
tripofdream 2002-07-23
楼上正确
回复
hchxxzx 2002-07-23
各位,标题招风了一点,不过主要是要大家来帮我解决问题啊。
请各位写点意见吧,主要是从EXCEL中的宏取出来的内容不能直接用在这里的问题,这类东西较少人说,我想大部分原因是一、技术保密;二、没有用过。还请各位大方一点,贡献出来吧。尤其是各位小星星们,在我们的眼里你们可是权威啊!
回复
walkingpoison 2002-07-23
楼主,以我的经验来看,vb或者vbscript是不支持selection对象的。如果要进行操作,就把selection直接替换为对应的操作对象。
比如你这里的操作对象是xlSheet.Range("A20:D45"),那么就改成xlSheet.Range("A20:D45").Borders(xlDiagonalDown).LineStyle = xlNone

还有要注意的一点就是,xlNone属于excel的常量,在vb中能够正常执行,但是在vbscript中是不能正常执行的,需要自己定义。方法可以在vb或者excel的宏中打出这个常量,然后光标定位在上面,按Ctrl+I,就可以看到具体的数值是多少了
回复
meizz 2002-07-23
收藏先,你问得那个问题我没做过,Sorry!
回复
dgz01 2002-07-23


*****
打工好辛苦
*****
钞票好难赚
*****
编程好伤神
*****
光阴好易混
*****
回复
apple749769 2002-07-23
学习呀!!!
回复
spgoal 2002-07-22
关注,学习。
回复
发动态
发帖子
ASP
创建于2007-09-28

2.8w+

社区成员

ASP即Active Server Pages,是Microsoft公司开发的服务器端脚本环境。
申请成为版主
社区公告
暂无公告