用javascript操作excel的模板，直接生成xlt文件和生成xls文件后改文件名，两者还是有区别。
琢磨了好多天的一个问题，用javascript直接从datagrid里读取数据，并在格式中套用excel里面的宏，生成一个excel报表文件。
由于我的报表中的数据有些需要合并，但是合并的格数却不固定，从速度和操作角度来说，考虑用excel的宏来制作。
我的机器是windows2003,asp.net+vb.net，首先做了一个excel的宏，然后另存为.xlt文件，在我机器上，报表实现我需要的功能，但是，在客户机（windows2000)上操作,每次都报告找不到宏。
想了好多天，打电话给了一个软件公司的朋友，他告诉我要用xls文件制作完成之后，该后缀名为xlt即可。试着做了一下，果然问题解决。但是直接作xlt和改xls的具体区别还是不清楚。
贴出我的程序吧，也许有朋友需要用得到：
Private Sub printExlS005(ByVal DataInfo As ACC101, ByVal DataResult As DataSet)
'打印营业店店业绩统计表－按时间
'报表格式内容确定。无论当月该店是否发生了该笔费用。都进行打印。
'生成Excel实例
Dim iStartRow As Integer
Dim iRow As Integer = 0
Dim iInput As Integer = 0
Dim sSubunitCD As String
Dim dItemMny As Double
Dim dAllMny As Double
Dim sAllMny As String
'表格的外框（临时存放开始行和结束行），当一个项目结束后，立即更换。
Dim iTmpStartRow As String
Dim iTmpEndRow As String
Dim iItem As Integer
With Me.Response
.Write("<SCRIPT Language='JavaScript'>")
.Write(" var AppExcel ;") '定义要用的EXCEL模板对象
.Write(" var OLOldBook,DataSheet,OLNewBook,OLCurSheet;") '定义要用的EXCEL模板对象
.Write(" var Row ; ") '定义打印数据起始行
.Write(" AppExcel= new ActiveXObject('Excel.Application'); ") '创建EXCEL对象
.Write(" Row=2 ; ") '起始行
.Write(" OLOldBook = AppExcel.WorkBooks.Open( '" & MCO004.PRINT_TEMPLETE_PATH & MCO004.PAGEID_PRINT_S005 & "');")
.Write(" DataSheet=OLOldBook.Sheets(1); ")
.Write(" AppExcel.Workbooks.add(); ")
.Write(" OLNewBook = AppExcel.ActiveWorkbook ; ")
For iRow = 0 To DataResult.Tables("MONTHTARIFF").Rows.Count - 1
'开始行号
iStartRow = 6
dAllMny = 0
'当前的营业店Subunitcd
sSubunitCD = Trim(DataResult.Tables("MONTHTARIFF").Rows(iRow).Item("SUBUNITCD"))
'统计当前营业店费用总额
dItemMny = 0
.Write(" DataSheet.Copy(OLNewBook.Sheets(1)); ")
.Write(" OLCurSheet = OLNewBook.ActiveSheet; ")
'当前SHEET的Name属性。
.Write(" OLCurSheet.Name = '" & DataResult.Tables("MONTHTARIFF").Rows(iRow).Item("SUBUNITNM") & "' ; ")
Dim sSEX As String
If MCO001.DBNULL2Str(DataResult.Tables("MONTHTARIFF").Rows(iRow).Item("SEX")) = "0" Then
sSEX = "先生"
Else
sSEX = "小姐"
End If
'抬头内容
'=============================================================================================================================================================================================================================
.Write(" OLCurSheet.Cells(2, 1) = ' 您好！现将" & MCO001.getYear(MCO001.Date2YMDChar(Me.txtCOMMYMDS.Text)) & "年" & MCO001.getMonth(MCO001.Date2YMDChar(Me.txtCOMMYMDS.Text)) & "月您在总部领取收费物品与签约中心收费项目对帐单寄到您处。' ; ")
'报表内容开始
'季会费用
'=============================================================================================================================================================================================================================
iStartRow = iStartRow + 1
iTmpStartRow = "A" & iStartRow
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!MergeRows','A" & iStartRow & "','F" & iStartRow & "','True','xlCenter') ; ")
.Write(" OLCurSheet.Cells(" & iStartRow & ", 1) = '动员季会费用' ; ")
iStartRow = iStartRow + 1
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!MergeRows','A" & iStartRow & "','C" & iStartRow & "','True','xlCenter') ; ")
.Write(" OLCurSheet.Cells(" & iStartRow & ", 1) = '事由' ; ")
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!MergeRows','D" & iStartRow & "','F" & iStartRow & "','True','xlCenter') ; ")
.Write(" OLCurSheet.Cells(" & iStartRow & ", 4) = '金额' ; ")
'初始化当前的项目的费用为0。
'结束时检查当前的项目的费用，若为0表示当前项目所有数据行未找到当前营业店信息，报表增加一条，金额为0。
'数据库为空，新店均适用。
dItemMny = 0
For iInput = 0 To DataResult.Tables("SEASON").Rows.Count - 1
If sSubunitCD = Trim(MCO001.DBNULL2Str(DataResult.Tables("SEASON").Rows(iInput).Item("SUBUNITCD"))) Then
If MCO001.DBNULL2Decimal(DataResult.Tables("SEASON").Rows(iInput).Item("SEASONMNY")) = 0 Then
Exit For
End If
iStartRow = iStartRow + 1
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!MergeRows','A" & iStartRow & "','C" & iStartRow & "','False','xlCenter') ; ")
.Write(" OLCurSheet.Cells(" & iStartRow & ", 1) = '动员季会' ; ")
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!MergeRows','D" & iStartRow & "','F" & iStartRow & "','False','xlRight') ; ")
.Write(" OLCurSheet.Cells(" & iStartRow & ", 4) = '" & MCO001.DBNULL2Decimal(DataResult.Tables("SEASON").Rows(iInput).Item("SEASONMNY")) & "' ; ")
dItemMny = dItemMny + MCO001.DBNULL2Decimal(DataResult.Tables("SEASON").Rows(iInput).Item("SEASONMNY"))
End If
Next
If dItemMny = 0 Then
iStartRow = iStartRow + 1
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!MergeRows','A" & iStartRow & "','C" & iStartRow & "','False','xlCenter') ; ")
.Write(" OLCurSheet.Cells(" & iStartRow & ", 1) = '动员季会' ; ")
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!MergeRows','D" & iStartRow & "','F" & iStartRow & "','False','xlCenter') ; ")
.Write(" OLCurSheet.Cells(" & iStartRow & ", 4) = '0' ; ")
End If
iTmpEndRow = "A" & iStartRow
'累计总额
dAllMny = dAllMny + dItemMny
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!DrawBorders','" & iTmpStartRow & "','" & iTmpEndRow & "') ; ")
'大小写转换
'=============================================================================================================================================================================================================================
iStartRow = iStartRow + 2
iTmpStartRow = "A" & iStartRow
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!MergeRows','A" & iStartRow & "','F" & iStartRow & "','True','xlCenter') ; ")
.Write(" OLCurSheet.Cells(" & iStartRow & ", 1) = '本月费用总额' ; ")
iStartRow = iStartRow + 1
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!MergeRows','A" & iStartRow & "','D" & iStartRow & "','True','xlLeft') ; ")
sAllMny = MCO003.ChineseFormat(CStr(dAllMny))
.Write(" OLCurSheet.Cells(" & iStartRow & ", 1) = '合计：（大写） " & sAllMny & "' ; ")
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!MergeRows','E" & iStartRow & "','F" & iStartRow & "','True','xlRight') ; ")
.Write(" OLCurSheet.Cells(" & iStartRow & ", 5) = '￥" & MCO003.Format2Style(dAllMny) & "' ; ")
iTmpEndRow = "A" & iStartRow
.Write(" AppExcel.run('" & MCO004.PAGEID_PRINT_S005 & "!DrawBorders','" & iTmpStartRow & "','" & iTmpEndRow & "') ; ")
Next
.Write(" AppExcel.visible=true; ")
.Write(" OLOldBook.Saved = true ; ")
.Write(" OLOldBook.Close() ; ")
.Write(" OLNewBook.Saved = true ; ")
.Write(" DataSheet = null ; ")
.Write(" OLCurSheet = null ; ")
.Write(" OLNewBook = null ; ")
.Write("</SCRIPT>")
End With
以下是excel的宏：
'指定行上合并行
Sub MergeRows(sStartRow As String, sEndRow As String, sBold As String, sAlign As String)
Range(CStr(sStartRow) & ":" & CStr(sEndRow)).Merge
If sBold = "True" Then
Range(CStr(sStartRow) & ":" & CStr(sEndRow)).Font.Bold = True
ElseIf sBold = "False" Then
Range(CStr(sStartRow) & ":" & CStr(sEndRow)).Font.Bold = False
End If
If sAlign = "xlCenter" Then
Range(CStr(sStartRow) & ":" & CStr(sEndRow)).HorizontalAlignment = xlCenter
ElseIf sAlign = "xlRight" Then
Range(CStr(sStartRow) & ":" & CStr(sEndRow)).HorizontalAlignment = xlRight
ElseIf sAlign = "xlLeft" Then
Range(CStr(sStartRow) & ":" & CStr(sEndRow)).HorizontalAlignment = xlLeft
End If
End Sub
Sub DrawBorders(sStartRow As String, sEndRow As String)
Range(CStr(sStartRow) & ":" & CStr(sEndRow)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.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
浙公网安备 33010602011771号