Posted on 2008-09-19 13:01
疯狂水车 阅读(1021)
评论(0) 编辑 收藏 引用
VB Script 使用Excel template (自定义模板) 导出数据
先建立一个你要Excel template 例如下面的CI.xlt
定义一些Excel的参数跟数据的单元格位置,然后调用Excel.Application将数据写入模板中.
<%
on error resume Next
'-----------------------------------------------------------
'Excel defined variables
' can get the variable value from http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.constants.aspx
Const xlEdgeBottom = 9
Const xlEdgeTop = 8
Const xlContinuous = 1
Const xlRight = -4152
Const xlCenter = -4108
Const xlLeft = -4131
'-----------------------------------------------------------
' Start of Excel generation for commerical invoice
'-----------------------------------------------------------
' Cells variable position for templates
Const currentRowNo_addrx = 10 ' row number for the details
' S/No
Const SNo_addry = 1
' Part No
Const PartNo_addry = 2
' compaq Part description
Const PartDesc_addry = 3
' end of cells variable
'-----------------------------------------------------------
rowsInserted = 0 ' keep track of the total numbers of rows inserted
colsInserted = 0 ' keep track of the total numbers of columns inserted after partDesc
' Create an instance of Excel and add a workbook
Set xlApp = Server.CreateObject("Excel.Application")
xlApp.DisplayAlerts = False ' set to no user prompt
Set xlWb = xlApp.Workbooks.Open(Server.MapPath("template\CI.xlt"))
Set xlWs = xlWb.Worksheets("Invoice")
xlWs.name = "1 " &PickingHdr_CI_Title &" " &containerNo
'xlWb.copyWorkSheet xlWs.name, xlWb.Worksheets(1).name ' copy a new worksheet to start of workbook
xlWs.copy(xlWb.Worksheets(1))
xlWb.worksheets(1).name = "Invoice"
' Display Excel and give user control of Excel's lifetime
xlApp.Visible = false
xlApp.UserControl = false
for i=0 To CIRows
xlWs.Rows(currentRowNo_addrx+rowsInserted ).Insert
xlWs.Cells(currentRowNo_addrx+rowsInserted , SNo_addry) = i+1
xlWs.Cells(currentRowNo_addrx+rowsInserted , SNo_addry).HorizontalAlignment = xlCenter
xlWs.Cells(currentRowNo_addrx +rowsInserted, PartNo_addry) = "'" & ucase(trim(aryCI(0,i)))
xlWs.Cells(currentRowNo_addrx +rowsInserted, PartNo_addry).HorizontalAlignment = xlCenter
if trim(aryCI(1,i))<>"" then
xlWs.Cells(currentRowNo_addrx +rowsInserted, PartDesc_addry) = ucase(trim(aryCI(1,i)))
xlWs.Cells(currentRowNo_addrx +rowsInserted, PartDesc_addry).HorizontalAlignment = xlLeft
End If
rowsInserted = rowsInserted + 1
Next
xlWs.columns(SNo_addry).entirecolumn.autofit()
xlWs.columns(PartNo_addry).entirecolumn.autofit()
Set xlWs = xlWb.Worksheets("Invoice")
xlWs.delete
xlWb.SaveAs strFile
xlApp.quit
Set xlWs = Nothing
Set xlWb = Nothing
%>