本文为【asp生成excel报表】的汉字拼音对照版显示拼音
1. 前qian言yan
报bao表biao打da印yin通tong常chang是shi管guan理li信xin息xi系xi统tong中zhong的de一yi个ge重chong要yao模mo块kuai,而erExcel凭ping借jie它ta功gong能neng强qiang大da、应ying用yong灵ling活huo、通tong用yong性xing强qiang等deng的de优you势shi在zai报bao表biao打da印yin中zhong获huo得de了le广guang泛fan的de应ying用yong。
最zui初chu的de管guan理li信xin息xi系xi统tong基ji本ben上shang是shi采cai用yong客ke户hu机ji/服fu务wu器qi(C/S)模mo式shi开kai发fa的de,但dan随sui着zheWWW的de广guang泛fan应ying用yong,目mu前qian的de管guan理li信xin息xi系xi统tong已yi经jing逐zhu渐jian开kai始shi从congC/S模mo式shi向xiang浏liu览lan器qi/服fu务wu器qi(B/S)模mo式shi转zhuan变bian。B/S模mo式shi具ju有you传chuan统tongC/S模mo式shi所suo不bu及ji的de很hen多duo特te点dian,如ru更geng加jia开kai放fang、与yu软ruan硬ying件jian无wu关guan、应ying用yong扩kuo充chong和he系xi统tong维wei护hu升sheng级ji方fang便bian等deng等deng,目mu前qian已yi成cheng为wei企qi业ye网wang上shang首shou选xuan的de计ji算suan模mo式shi,原yuan先xian在zaiC/S下xia的de很hen多duo软ruan件jian都dou开kai始shi移yi植zhi到daoB/S模mo式shi下xia。由you于yuB/S模mo式shi的de特te殊shu性xing,在zaiC/S下xia相xiang对dui较jiao易yi实shi现xian的deExcel报bao表biao打da印yin功gong能neng在zaiB/S下xia却que成cheng为wei一yi个ge难nan点dian。本ben文wen根gen据ju在zai实shi际ji的de项xiang目mu中zhong总zong结jie的de经jing验yan,以yiASP为wei例li,给gei出chu了le一yi个ge较jiao好hao的de通tong用yong方fang法fa。
2. 功gong能neng实shi现xian
为wei了le说shuo明ming问wen题ti,这zhe里li举ju一yi个ge例li子zi。系xi统tong平ping台tai是shiWindows 2000+SQL Server 2000+IIS 5.0+ASP 3,报bao表biao采cai用yong的de是shiExcel,要yao求qiu按an照zhao给gei定ding的de报bao表biao格ge式shi生sheng成cheng图tu书shu销xiao售shou统tong计ji的de报bao表biao,并bing能neng够gou打da印yin。
2.1 Excel报bao表biao模mo板ban的de制zhi作zuo
首shou先xian根gen据ju给gei定ding的de报bao表biao格ge式shi,制zhi作zuo一yi个geExcel模mo板ban(就jiu是shi要yao打da印yin的de报bao表biao的de表biao格ge),当dang然ran其qi中zhong需xu要yao从cong数shu据ju库ku中zhong动dong态tai统tong计ji生sheng成cheng的de数shu据ju留liu着zhe空kong白bai。这zhe个ge报bao表biao先xian在zaiExcel中zhong画hua好hao,然ran后hou保bao存cun为wei模mo板ban,存cun放fang在zai起qi来lai,这zhe里li为wei\test\book1.xlt。
2.2 Excel报bao表biao的de生sheng成cheng与yu打da印yin
这zhe里li采cai用yong了leExcel的deApplication组zu件jian,该gai组zu件jian在zai安an装zhuangExcel时shi安an装zhuang到dao系xi统tong中zhong。我wo们men的de操cao作zuo也ye都dou是shi针zhen对dui该gai组zu件jian。
(1) 建jian立liExcel.Application对dui象xiang
set objExcel=CreateObject("Excel.Application")
(2) 打da开kaiExcel模mo板ban
objExcel.Workbooks.Open(server.mappath("\test")&"\book1.xlt") '打da开kaiExcel模mo板ban
objExcel.Sheets(1).select '选xuan中zhong工gong作zuo页ye
set sheetActive=objExcel.ActiveWorkbook.ActiveSheet
(3) Excel的de常chang规gui添tian加jia操cao作zuo
例li如rusheetActive.range("g4").value=date() ‘这zhe里li添tian加jia的de是shi时shi间jian,当dang然ran也ye可ke以yi是shi你ni指zhi定ding的de任ren何he数shu据ju
(4) Excel中zhong添tian加jia数shu据ju库ku中zhong的de纪ji录lu
这zhe里li假jia设she已yi有you一yi个ge数shu据ju集jiadoRset,存cun放fang由youSql操cao作zuo生sheng成cheng的de统tong计ji数shu据ju。
num=7 ‘从congExcel的de第di七qi行xing开kai始shi
do until adoRset.EOF ‘循xun环huan直zhi至zhi数shu据ju集ji中zhong的de数shu据ju写xie完wan
strRange="d"&num&":f"&num ‘设she定ding要yao填tian写xie内nei容rong的de单dan元yuan区qu域yu
sheetActive.range(strRange).font.size=10 ‘设she定ding字zi体ti大da小xiao
sheetActive.range(strRange).WrapText=false ‘设she定ding文wen字zi回hui卷juan
sheetActive.range(strRange).ShrinkToFit=true ‘设she定ding是shi否fou自zi动dong适shi应ying表biao格ge单dan元yuan大da小xiao
sheetActive.range(strRange).value=array(adoRset("bookid"),adoRset("bookname"),adoRset("author")) ‘把ba数shu据ju集ji中zhong的de数shu据ju填tian写xie到dao相xiang应ying的de单dan元yuan中zhong
num=num+1
adoRset.MoveNext
loop
(5) Excel临lin时shi报bao表biao文wen件jian的de保bao存cun及ji处chu理li
实shi际ji运yun行xing中zhong应ying该gai注zhu意yi每mei次ci一yi个ge用yong户hu进jin行xing报bao表biao打da印yin时shi都dou采cai用yong一yi个ge临lin时shi的deExcel文wen件jian,而er不bu是shi硬ying性xing规gui定ding文wen件jian名ming,因yin为wei如ru果guo用yong固gu定ding的de文wen件jian名ming的de话hua,只zhi有you第di一yi次ci生sheng成cheng是shi成cheng功gong的de,后hou面mian的de操cao作zuo都dou会hui因yin为wei已yi存cun在zai同tong名ming文wen件jian而er导dao致zhi失shi败bai。所suo以yi我wo们men需xu要yao每mei次ci都dou产chan生sheng一yi个ge临lin时shi的de而er且qie不bu重chong复fu的de文wen件jian名ming,这zhe里li可ke以yi采cai用yong自zi定ding义yi的degetTemporaryFile()函han数shu由you来lai生sheng成cheng,然ran后hou存cun放fang在zai变bian量liangfilename中zhong,用yong变bian量liangfilepos表biao示shi这zhe些xie临lin时shi文wen件jian的de路lu径jing。
此ci外wai如ru果guo这zhe些xie临lin时shi文wen件jian不bu处chu理li的de话hua久jiu而er久jiu之zhi会hui成cheng为wei文wen件jian垃la圾ji,因yin此ci在zai每mei个ge用yong户hu提ti交jiaoExcel报bao表biao打da印yin请qing求qiu时shi要yao先xian删shan除chu临lin时shi目mu录lu下xia所suo有you原yuan先xian产chan生sheng的de临lin时shi打da印yin文wen件jian。
临lin时shi文wen件jian的de处chu理li主zhu要yao代dai码ma如ru下xia:
function getTemporaryFile(myFileSystem)
dim tempFile,dotPos
tempFile=myFileSystem.getTempName
dotPos=instr(1,tempFile,".")
getTemporaryFile=mid(tempFile,1,dotPos)&"xls"
end function
set myFs=createObject("scripting.FileSystemObject")
filePos=server.mappath("\test") & "\tmp\" '要yao存cun放fang打da印yin临lin时shi文wen件jian的de临lin时shi目mu录lu
fileName=getTemporaryFile(myFs) '取qu得de一yi个ge临lin时shi文wen件jian名ming
myFs.DeleteFile filePos&"*.xls" '删shan除chu该gai目mu录lu下xia所suo有you原yuan先xian产chan生sheng的de临lin时shi打da印yin文wen件jian
set myFs=nothing
Excel临lin时shi文wen件jian的de保bao存cun代dai码ma为wei:
objExcel.ActiveWorkbook.saveas filePos&filename
(6) 退tui出chuExcel应ying用yong
objExcel.quit
set objExcel=Nothing
(7) Excel报bao表biao的de打da印yin
前qian面mian的de步bu骤zhou已yi经jing生sheng成cheng了leExcel报bao表biao,下xia一yi步bu进jin行xing打da印yin,采cai用yong的de策ce略lue可ke以yi有you两liang种zhong:
方fang案an一yi:提ti供gong上shang面mian生sheng成cheng的deExcel报bao表biao临lin时shi文wen件jian链lian接jie给gei用yong户hu,用yong户hu可ke以yi直zhi接jie点dian击ji在zai浏liu览lan器qi中zhong打da开kaiExcel报bao表biao并bing通tong过guo浏liu览lan器qi的de打da印yin功gong能neng进jin行xing打da印yin,也ye可ke以yi点dian击ji右you键jian然ran后hou另ling存cun到dao本ben地di后hou再zai作zuo打da印yin等deng相xiang关guan处chu理li。
方fang案an二er:生sheng成chengExcel报bao表biao后hou直zhi接jie在zai客ke户hu端duan加jia载zai到dao浏liu览lan器qi,当dang然ran在zai没mei有you完wan全quan加jia载zai时shi应ying该gai提ti示shi“正zheng在zai加jia载zai,请qing等deng待dai”等deng字zi样yang。
2.3 系xi统tong配pei置zhi与yu注zhu意yi事shi项xiang
虽sui然ran以yi上shang代dai码ma很hen简jian单dan,但dan实shi际ji应ying用yong不bu当dang经jing常chang会hui出chu现xian错cuo误wu,所suo以yi下xia面mian要yao讲jiang到dao的de系xi统tong配pei置zhi和he注zhu意yi事shi项xiang非fei常chang关guan键jian。
(1) 千qian万wan要yao保bao证zheng以yi上shang代dai码ma输shu入ru的de正zheng确que性xing,否fou则ze一yi旦dan运yun行xing错cuo误wu,Excel对dui象xiang会hui滞zhi留liu内nei存cun,难nan以yi消xiao除chu,导dao致zhi下xia一yi次ci调tiao用yong时shi速su度du狂kuang慢man,并bing产chan生sheng内nei存cun不bu可ke读du写xie的deWindows错cuo误wu。这zhe时shi的de解jie决jue方fang法fa就jiu是shi注zhu销xiao当dang前qian用yong户hu,如ru果guo还huan不bu行xing,就jiu只zhi能nengReset了le。
(2) 一yi定ding要yao设she置zhi好hao负fu责ze打da印yin功gong能neng的deasp文wen件jian的de权quan限xian。方fang法fa是shi:在zaiIIS管guan理li中zhong,选xuan择ze该gaiasp文wen件jian,右you键jian然ran后hou选xuan“属shu性xing”/“文wen件jian安an全quan性xing”/"匿ni名ming访fang问wen和he验yan证zheng控kong制zhi“,在zai这zhe里liIIS默mo认ren是shi匿ni名ming访fang问wen,应ying该gai选xuan择ze验yan证zheng访fang问wen(这zhe里li基ji本ben验yan证zheng和he集ji成chengWindows验yan证zheng两liang种zhong方fang式shi均yun可ke,但dan前qian者zhe不bu够gou安an全quan),这zhe一yi点dian无wu比bi重chong要yao,否fou则ze应ying用yong当dang中zhong会hui出chu错cuo的de。
(3) 有you的de时shi候hou报bao表biao分fen为wei多duo页ye,而er且qie我wo们men希xi望wang每mei一yi页ye有you相xiang同tong的de表biao头tou,要yao求qiu表biao头tou每mei页ye都dou自zi动dong打da印yin,可ke以yi在zaiExcel模mo板ban中zhong进jin行xing设she置zhi。方fang法fa如ru下xia:选xuan择ze菜cai单dan"文wen件jian"/"页ye面mian设she置zhi"/"工gong作zuo表biao",然ran后hou在zai"顶ding端duan标biao题ti行xing"输shu入ru你ni表biao头tou的de行xing数shu(如ru:表biao头tou为wei1-3行xing即ji填tian入ru:$1:$3)。
3.总zong结jie
以yi上shang我wo们men给gei出chu了le一yi个ge采cai用yongASP写xie的de在zaiB/S模mo式shi下xia实shi现xianEXCEL报bao表biao的de生sheng成cheng与yu打da印yin的de例li子zi,在zai实shi际ji当dang中zhong已yi经jing得de到dao了le良liang好hao的de应ying用yong。事shi实shi也ye证zheng明ming,这zhe个ge例li子zi的de代dai码ma虽sui然ran不bu难nan写xie,但dan一yi定ding要yao注zhu意yi系xi统tong的de配pei置zhi,这zhe也ye是shi无wu数shu次ci失shi败bai后hou得de出chu的de经jing验yan。
【原文】