本文为【ASP操作Excel技术总结】的汉字拼音对照版显示拼音
目mu录lu
一yi、 环huan境jing配pei置zhi
二er、 ASP对duiExcel的de基ji本ben操cao作zuo
三san、 ASP操cao作zuoExcel生sheng成cheng数shu据ju表biao
四si、 ASP操cao作zuoExcel生sheng成chengChart图tu
五wu、 服fu务wu器qi端duanExcel文wen件jian浏liu览lan、下xia载zai、删shan除chu方fang案an
六liu、 附fu录lu
正zheng文wen
一yi、 环huan境jing配pei置zhi
服fu务wu器qi端duan的de环huan境jing配pei置zhi从cong参shen考kao资zi料liao上shang看kan,微wei软ruan系xi列lie的de配pei置zhi应ying该gai都dou行xing,即ji:
1.Win9x+PWS+Office
2.Win2000 Professional+PWS+Office
3.Win2000 Server+IIS+Office
目mu前qian笔bi者zhe测ce试shi成cheng功gong的de环huan境jing是shi后hou二er者zhe。Office的de版ban本ben没mei有you特te殊shu要yao求qiu,考kao虑lv到dao客ke户hu机ji配pei置zhi的de不bu确que定ding性xing和he下xia兼jian容rong特te性xing,建jian议yi服fu务wu器qi端duanOffice版ban本ben不bu要yao太tai高gao,以yi防fang止zhi客ke户hu机ji下xia载zai后hou无wu法fa正zheng确que显xian示shi。
服fu务wu器qi端duan环huan境jing配pei置zhi还huan有you两liang个ge偶ou然ran的de发fa现xian是shi:
1. 笔bi者zhe开kai发fa机ji器qi上shang原yuan来lai装zhuang有you金jin山shan的deWPS2002,结jie果guoExcel对dui象xiang创chuang建jian始shi终zhong出chu现xian问wen题ti,卸xie载zaiWPS2002后hou,错cuo误wu消xiao失shi。
2. 笔bi者zhe开kai发faASP代dai码ma喜xi欢huan用yongFrontPage,结jie果guo发fa现xian如ru果guoFrontPage打da开kai(服fu务wu器qi端duan),对dui象xiang创chuang建jian出chu现xian不bu稳wen定ding现xian象xiang,时shi而er成cheng功gong时shi而er不bu成cheng功gong。扩kuo展zhan考kao察cha后hou发fa现xian,Office系xi列lie的de软ruan件jian如ru果guo在zai服fu务wu器qi端duan运yun行xing,则zeExcel对dui象xiang的de创chuang建jian很hen难nan成cheng功gong。
服fu务wu器qi端duan还huan必bi须xu要yao设she置zhi的de一yi点dian是shiCOM组zu件jian的de操cao作zuo权quan限xian。在zai命ming令ling行xing键jian入ru“DCOMCNFG”,则ze进jin入ruCOM组zu件jian配pei置zhi界jie面mian,选xuan择zeMicrosoft Excel后hou点dian击ji属shu性xing按an钮niu,将jiang三san个ge单dan选xuan项xiang一yi律lv选xuan择ze自zi定ding义yi,编bian辑ji中zhong将jiangEveryone加jia入ru所suo有you权quan限xian。保bao存cun完wan毕bi后hou重chong新xin启qi动dong服fu务wu器qi。
客ke户hu端duan的de环huan境jing配pei置zhi没mei发fa现xian什shen么me特te别bie讲jiang究jiu的de地di方fang,只zhi要yao装zhuang有youOffice和heIE即ji可ke,版ban本ben通tong用yong的de好hao象xiang都dou可ke以yi。
二er、 ASP对duiExcel的de基ji本ben操cao作zuo
1、 建jian立liExcel对dui象xiang
set objExcelApp = CreateObject("Excel.Application")
objExcelApp.DisplayAlerts = false 不bu显xian示shi警jing告gao
objExcelApp.Application.Visible = false 不bu显xian示shi界jie面mian
2、 新xin建jianExcel文wen件jian
objExcelApp.WorkBooks.add
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(1)
3、 读du取qu已yi有youExcel文wen件jian
strAddr = Server.MapPath(".")
objExcelApp.WorkBooks.Open(strAddr & "\Templet\Table.xls")
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(1)
4、 另ling存cunExcel文wen件jian
objExcelBook.SaveAs strAddr & "\Temp\Table.xls"
5、 保bao存cunExcel文wen件jian
objExcelBook.Save (笔bi者zhe测ce试shi时shi保bao存cun成cheng功gong,页ye面mian报bao错cuo。)
6、 退tui出chuExcel操cao作zuo
objExcelApp.Quit 一yi定ding要yao退tui出chu
set objExcelApp = Nothing
三san、 ASP操cao作zuoExcel生sheng成cheng数shu据ju表biao
1、 在zai一yi个ge范fan围wei内nei插cha入ru数shu据ju
objExcelSheet.Range("B3:k3").Value = Array("67", "87", "5", "9", "7", "45", "45", "54", "54", "10")
2、 在zai一yi个ge单dan元yuan格ge内nei插cha入ru数shu据ju
objExcelSheet.Cells(3,1).Value="Internet Explorer"
3、 选xuan中zhong一yi个ge范fan围wei
4、 单dan元yuan格ge左zuo边bian画hua粗cu线xian条tiao
5、 单dan元yuan格ge右you边bian画hua粗cu线xian条tiao
6、 单dan元yuan格ge上shang边bian画hua粗cu线xian条tiao
7、 单dan元yuan格ge下xia边bian画hua粗cu线xian条tiao
8、 单dan元yuan格ge设she定ding背bei景jing色se
9、 合he并bing单dan元yuan格ge
10、 插cha入ru行xing
11、 插cha入ru列lie
四si、 ASP操cao作zuoExcel生sheng成chengChart图tu
1、 创chuang建jianChart图tu
objExcelApp.Charts.Add
2、 设she定dingChart图tu种zhong类lei
objExcelApp.ActiveChart.ChartType = 97
注zhu:二er维wei折zhe线xian图tu,4;二er维wei饼bing图tu,5;二er维wei柱zhu形xing图tu,51
3、 设she定dingChart图tu标biao题ti
objExcelApp.ActiveChart.HasTitle = True
objExcelApp.ActiveChart.ChartTitle.Text = "A test Chart"
4、 通tong过guo表biao格ge数shu据ju设she定ding图tu形xing
objExcelApp.ActiveChart.SetSourceData objExcelSheet.Range("A1:k5"),1
5、 直zhi接jie设she定ding图tu形xing数shu据ju(推tui荐jian)
objExcelApp.ActiveChart.SeriesCollection.NewSeries
objExcelApp.ActiveChart.SeriesCollection(1).Name = "=""333"""
objExcelApp.ActiveChart.SeriesCollection(1).Values = "={1,4,5,6,2}"
6、 绑bang定dingChart图tu
objExcelApp.ActiveChart.Location 1
7、 显xian示shi数shu据ju表biao
objExcelApp.ActiveChart.HasDataTable = True
8、 显xian示shi图tu例li
objExcelApp.ActiveChart.DataTable.ShowLegendKey = True
五wu、 服fu务wu器qi端duanExcel文wen件jian浏liu览lan、下xia载zai、删shan除chu方fang案an
浏liu览lan的de解jie决jue方fang法fa很hen多duo,“Location.href=”,“Navigate”,“Response.Redirect”都dou可ke以yi实shi现xian,建jian议yi用yong客ke户hu端duan的de方fang法fa,原yuan因yin是shi给gei服fu务wu器qi更geng多duo的de时shi间jian生sheng成chengExcel文wen件jian。
下xia载zai的de实shi现xian要yao麻ma烦fan一yi些xie。用yong网wang上shang现xian成cheng的de服fu务wu器qi端duan下xia载zai组zu件jian或huo自zi己ji定ding制zhi开kai发fa一yi个ge组zu件jian是shi比bi较jiao好hao的de方fang案an。另ling外wai一yi种zhong方fang法fa是shi在zai客ke户hu端duan操cao作zuoExcel组zu件jian,由you客ke户hu端duan操cao作zuo服fu务wu器qi端duanExcel文wen件jian另ling存cun至zhi客ke户hu端duan。这zhe种zhong方fang法fa要yao求qiu客ke户hu端duan开kai放fang不bu安an全quanActiveX控kong件jian的de操cao作zuo权quan限xian,考kao虑lv到dao通tong知zhi每mei个ge客ke户hu将jiang服fu务wu器qi设she置zhi为wei可ke信xin站zhan点dian的de麻ma烦fan程cheng度du建jian议yi还huan是shi用yong第di一yi个ge方fang法fa比bi较jiao省sheng事shi。
删shan除chu方fang案an由you三san部bu分fen组zu成cheng:
A: 同tong一yi用yong户hu生sheng成cheng的deExcel文wen件jian用yong同tong一yi个ge文wen件jian名ming,文wen件jian名ming可ke用yong用yong户huID号hao或huoSessionID号hao等deng可ke确que信xin不bu重chong复fu字zi符fu串chuan组zu成cheng。这zhe样yang新xin文wen件jian生sheng成cheng时shi自zi动dong覆fu盖gai上shang一yi文wen件jian。
B: 在zaiGlobal.asa文wen件jian中zhong设she置zhiSession_onEnd事shi件jian激ji发fa时shi,删shan除chu这zhe个ge用yong户hu的deExcel暂zan存cun文wen件jian。
C: 在zaiGlobal.asa文wen件jian中zhong设she置zhiApplication_onStart事shi件jian激ji发fa时shi,删shan除chu暂zan存cun目mu录lu下xia的de所suo有you文wen件jian。
注zhu:建jian议yi目mu录lu结jie构gou \Src 代dai码ma目mu录lu \Templet 模mo板ban目mu录lu \Temp 暂zan存cun目mu录lu
六liu、 附fu录lu
出chu错cuo时shiExcel出chu现xian的de死si进jin程cheng出chu现xian是shi一yi件jian很hen头tou疼teng的de事shi情qing。在zai每mei个ge文wen件jian前qian加jia上shang“On Error Resume Next”将jiang有you助zhu于yu改gai善shan这zhe种zhong情qing况kuang,因yin为wei它ta会hui不bu管guan文wen件jian是shi否fou产chan生sheng错cuo误wu都dou坚jian持chi执zhi行xing到dao“Application.Quit”,保bao证zheng每mei次ci程cheng序xu执zhi行xing完wan不bu留liu下xia死si进jin程cheng。
【原文】