主要代碼如下:
寫查詢字符串
rsAll為查詢字符串
rsAll.open sqlAll,conn,1,3
Set xlApplication =server.CreateObject( "Excel.Application") '調(diào)用excel對(duì)象
xlApplication.Visible = false '無需打開excel
Set xlWorkBook=xlApplication.Workbooks.Add '添加工作簿
j=1
//定義一些表格樣式
xlWorkBook.Worksheets(1).Columns(1).ColumnWidth=30
xlWorkBook.Worksheets(1).Columns(1).HorizontalAlignment=3
xlWorkBook.Worksheets(1).Columns(2).ColumnWidth=30
xlWorkBook.Worksheets(1).Columns(2).HorizontalAlignment=3
xlWorkBook.Worksheets(1).Columns(3).ColumnWidth=30
xlWorkBook.Worksheets(1).Columns(3).HorizontalAlignment=3
xlWorkBook.Worksheets(1).Range( "A2 ").font.bold=true '粗體
xlWorkBook.Worksheets(1).Range( "B2 ").font.bold=true '粗體
xlWorkBook.Worksheets(1).Range( "C2 ").font.bold=true '粗體
xlWorkBook.Worksheets(1).Range( "A1 ").HorizontalAlignment=3 '水平對(duì)齊
xlWorkBook.Worksheets(1).Range( "A1 ").VerticalAlignment=3 '垂直對(duì)齊
//寫excel表頭了
xlWorkBook.Worksheets(1).Cells(1,1).Value = "集團(tuán)新聞中心各記者站積分統(tǒng)計(jì)"
xlWorkBook.Worksheets(1).Cells(2,1).Value = "記者站名稱"
xlWorkBook.Worksheets(1).Cells(2,2).Value = "要聞文章數(shù)量"
xlWorkBook.Worksheets(1).Cells(2,3).Value = "要聞文章積分"
xlWorkBook.Worksheets(1).Cells(2,4).Value = "綜合新聞文章數(shù)量"
xlWorkBook.Worksheets(1).Cells(2,5).Value = "綜合新聞文章積分"
xlWorkBook.Worksheets(1).Cells(2,6).Value = "其它欄目文章數(shù)量"
xlWorkBook.Worksheets(1).Cells(2,7).Value = "其它欄目文章數(shù)量"
xlWorkBook.Worksheets(1).Cells(2,8).Value = "微信公眾號(hào)數(shù)量"
xlWorkBook.Worksheets(1).Cells(2,9).Value = "微信公眾號(hào)積分"
xlWorkBook.Worksheets(1).Cells(2,10).Value = "報(bào)刊雜志數(shù)量"
xlWorkBook.Worksheets(1).Cells(2,11).Value = "報(bào)刊雜志積分"
xlWorkBook.Worksheets(1).Cells(2,12).Value = "網(wǎng)絡(luò)電視臺(tái)數(shù)量"
xlWorkBook.Worksheets(1).Cells(2,13).Value = "網(wǎng)絡(luò)電視臺(tái)積分"
xlWorkBook.Worksheets(1).Cells(2,14).Value = "比賽獲獎(jiǎng)數(shù)量"
xlWorkBook.Worksheets(1).Cells(2,15).Value = "比賽獲獎(jiǎng)積分"
//循環(huán)輸出下面的數(shù)據(jù)
DO WHILE NOT rsAll.EOF
xlWorkBook.Worksheets(1).Cells(2+j,1).Value = rsAll("CopyFrom")
xlWorkBook.Worksheets(1).Cells(2+j,2).Value = rsAll("YWNo")
xlWorkBook.Worksheets(1).Cells(2+j,3).Value = rsAll("YWFS")
xlWorkBook.Worksheets(1).Cells(2+j,4).Value = rsAll("ZHNo")
xlWorkBook.Worksheets(1).Cells(2+j,5).Value = rsAll("ZHFS")
xlWorkBook.Worksheets(1).Cells(2+j,6).Value = rsAll("QTNo")
xlWorkBook.Worksheets(1).Cells(2+j,7).Value = rsAll("QTFS")
xlWorkBook.Worksheets(1).Cells(2+j,8).Value = rsAll("weixinno")
xlWorkBook.Worksheets(1).Cells(2+j,9).Value = rsAll("weixinfs")
xlWorkBook.Worksheets(1).Cells(2+j,10).Value = rsAll("baozishu")
xlWorkBook.Worksheets(1).Cells(2+j,11).Value = rsAll("baozifenshu")
xlWorkBook.Worksheets(1).Cells(2+j,12).Value = rsAll("shipinshu")
xlWorkBook.Worksheets(1).Cells(2+j,13).Value = rsAll("shipinfs")
xlWorkBook.Worksheets(1).Cells(2+j,14).Value = rsAll("zuopinshu")
xlWorkBook.Worksheets(1).Cells(2+j,15).Value = rsAll("zuopinfenshu")
j=j+1
rsALL.movenext
LOOP
xlWorkBook.SaveAs tfile //保存文件
Set xlWorksheet = Nothing //釋放工作表
xlApplication.Quit //釋放對(duì)象
//生成鏈接,供用戶下載
response.write("<center><table width='700' align='center'><tr><td>導(dǎo)出成功!點(diǎn)擊下載:<a href='jftj.xlsx'>jftj.xlsx</a></td></tr></table></center>")
response.Write("<script><alert>Excel導(dǎo)出成功,請(qǐng)到列表下方點(diǎn)擊下載!</alert></script>")
END IF
程序?qū)懲炅,那么要從服?wù)器上運(yùn)行,還需要給服務(wù)器安裝word組件,安裝word的過程不再贅述,安裝上word還要給IIS賦予使用word組件中excel組件的使用權(quán)限,方法是win+R,運(yùn)行框里輸入dcomcnfg,然后選擇“組件服務(wù)”--》計(jì)算機(jī)--》我的電腦--》DCOM配置,找到Microsoft Excel Application,然后右鍵--》屬性--》安全--》啟動(dòng)和激活權(quán)限--》自定義,然后選擇編輯,然后添加Everyone,然后給everyone賦予權(quán)限,然后選擇“標(biāo)識(shí)”選項(xiàng)卡,改成交互式用戶。
上述安裝配置excel組件的原因是,1、如果不配置“啟動(dòng)和激活”權(quán)限,在server.CreateObject( "Excel.Application")時(shí),服務(wù)器會(huì)報(bào)錯(cuò),沒有權(quán)限。2、如果不配置標(biāo)識(shí)這個(gè)玩意兒,在執(zhí)行“Set xlWorkBook=xlApplication.Workbooks.Add '添加工作簿 ”時(shí),會(huì)報(bào)內(nèi)存不足請(qǐng)關(guān)閉相關(guān)進(jìn)行之類的錯(cuò)誤。