user excel application . example: dim xlsApp as Excel.Application dim xlsWorkSheet as Excel.WorkSheet xlsApp= ....read help of VBA of Excel . and u will know it . good luck .
Dim exlApp As New Excel.Application Dim exlBook As Excel.Workbook Dim exlSheet As Excel.Worksheet Dim conn As ADODB.Connection Dim rs As ADODB.RecordsetPrivate Sub Command1_Click() Set exlBook = exlApp.Workbooks().Open(App.Path & "\税收.xlt") Set exlSheet = exlBook.Worksheets("sheet1")Set conn = New ADODB.Connection conn.Open "Main", "sa", "" Set rs = New ADODB.Recordset rs.Open "select * from 数据临时表", conn, adOpenStatic, adLockOptimistic If rs.RecordCount > 0 Then exlSheet.Range("b1").Value = rs.Fields("注册类型") exlSheet.Range("e1").Value = Left(rs.Fields("交税时间"), 4) exlSheet.Range("g1").Value = Month(CDate(rs.Fields("交税时间"))) exlSheet.Range("i1").Value = Day(CDate(rs.Fields("交税时间"))) exlSheet.Range("l1").Value = rs.Fields("征收机关") exlSheet.Range("b2").Value = rs.Fields("纳税人代码") exlSheet.Range("h2").Value = rs.Fields("地址") exlSheet.Range("b3").Value = rs.Fields("车牌号码") exlSheet.Range("d3").Value = rs.Fields("车主姓名") exlSheet.Range("j3").Value = rs.Fields("税款所属时间") exlSheet.Range("a5").Value = rs.Fields("税种") exlSheet.Range("c5").Value = rs.Fields("品目名称") exlSheet.Range("d5").Value = rs.Fields("科税数量") exlSheet.Range("f5").Value = rs.Fields("记税金额") exlSheet.Range("i5").Value = rs.Fields("税率") exlSheet.Range("k5").Value = rs.Fields("扣除额") exlSheet.Range("l5").Value = rs.Fields("实交金额") exlSheet.Range("f10").Value = rs.Fields("添票人") exlSheet.Range("c9").Value = rs.Fields("金额合计大写") exlSheet.Range("l9").Value = rs.Fields("实交金额")
exlBook.Close '用 Quit 方法关闭 Microsoft Excel exlApp.Quit '释放对象 rs.Close conn.Close Set rs = Nothing Set conn = Nothing Set exlApp = Nothing Set exlBook = Nothing Set exlSheet = Nothing End If End Sub
with exlSheet do for i = 1 to max rowNum for j = 1 to max colNum .cell(i,j).value = ??? next j next i
请参考: '引用excel9.0 Dim tempxlApp As New Excel.Application Dim tempxlWorkbook As New Excel.Workbook Dim tempxlSheet As New Excel.WorksheetDim tempRange As String Dim strRangeValue As String '打开自己作好的报表模板templet.xlt Set tempxlWorkbook = tempxlApp.Workbooks.Open(App.Path & "\templet.xlt") tempxlApp.Visible = True tempxlApp.DisplayAlerts = False tempxlWorkbook.SaveAs "report.xls" Set tempxlSheet = tempxlWorkbook.Worksheets("sheet1") tempxlSheet.Select'单个单元格写入数据 tempxlSheet.Range("A1").Value = "test" '一次性写入tempRs数据记录集中的数据 tempxlSheet.Range("A1").CopyFromRecordset tempRS '保存 tempxlApp.save '释放对象 Set tempxlSheet = Nothing Set tempxlWorkbook = Nothing '关闭excel tempxlApp.Quit Set tempxlApp = Nothing祝你好运!
Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim xlRange As Excel.Range '将有关的数据源转成一个数组asTempArray Set xlApp = Excel.Application Set xlBook = xlApp.Workbooks().Open(App.path & "\Templet\Book1.xlt") Set xlSheet = xlBook.Worksheets("sheet1") Set xlRange = xlApp.Range(Cells(1, 1), Cells(UBound(asTempArray, 1) + 1, UBound(asTempArray, 2) + 1))
dim xlsWorkSheet as Excel.WorkSheet xlsApp=
....read help of VBA of Excel . and u will know it . good luck .
Dim exlBook As Excel.Workbook
Dim exlSheet As Excel.Worksheet
Dim conn As ADODB.Connection
Dim rs As ADODB.RecordsetPrivate Sub Command1_Click()
Set exlBook = exlApp.Workbooks().Open(App.Path & "\税收.xlt")
Set exlSheet = exlBook.Worksheets("sheet1")Set conn = New ADODB.Connection
conn.Open "Main", "sa", ""
Set rs = New ADODB.Recordset
rs.Open "select * from 数据临时表", conn, adOpenStatic, adLockOptimistic
If rs.RecordCount > 0 Then
exlSheet.Range("b1").Value = rs.Fields("注册类型")
exlSheet.Range("e1").Value = Left(rs.Fields("交税时间"), 4)
exlSheet.Range("g1").Value = Month(CDate(rs.Fields("交税时间")))
exlSheet.Range("i1").Value = Day(CDate(rs.Fields("交税时间")))
exlSheet.Range("l1").Value = rs.Fields("征收机关")
exlSheet.Range("b2").Value = rs.Fields("纳税人代码")
exlSheet.Range("h2").Value = rs.Fields("地址")
exlSheet.Range("b3").Value = rs.Fields("车牌号码")
exlSheet.Range("d3").Value = rs.Fields("车主姓名")
exlSheet.Range("j3").Value = rs.Fields("税款所属时间")
exlSheet.Range("a5").Value = rs.Fields("税种")
exlSheet.Range("c5").Value = rs.Fields("品目名称")
exlSheet.Range("d5").Value = rs.Fields("科税数量")
exlSheet.Range("f5").Value = rs.Fields("记税金额")
exlSheet.Range("i5").Value = rs.Fields("税率")
exlSheet.Range("k5").Value = rs.Fields("扣除额")
exlSheet.Range("l5").Value = rs.Fields("实交金额")
exlSheet.Range("f10").Value = rs.Fields("添票人")
exlSheet.Range("c9").Value = rs.Fields("金额合计大写")
exlSheet.Range("l9").Value = rs.Fields("实交金额")
exlSheet.PrintOut
exlApp.Application.DisplayAlerts = False
exlBook.Close
'用 Quit 方法关闭 Microsoft Excel
exlApp.Quit
'释放对象
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
Set exlApp = Nothing
Set exlBook = Nothing
Set exlSheet = Nothing
End If
End Sub
for i = 1 to max rowNum
for j = 1 to max colNum
.cell(i,j).value = ???
next j
next i
'引用excel9.0
Dim tempxlApp As New Excel.Application
Dim tempxlWorkbook As New Excel.Workbook
Dim tempxlSheet As New Excel.WorksheetDim tempRange As String
Dim strRangeValue As String
'打开自己作好的报表模板templet.xlt
Set tempxlWorkbook = tempxlApp.Workbooks.Open(App.Path & "\templet.xlt")
tempxlApp.Visible = True
tempxlApp.DisplayAlerts = False
tempxlWorkbook.SaveAs "report.xls"
Set tempxlSheet = tempxlWorkbook.Worksheets("sheet1")
tempxlSheet.Select'单个单元格写入数据
tempxlSheet.Range("A1").Value = "test"
'一次性写入tempRs数据记录集中的数据
tempxlSheet.Range("A1").CopyFromRecordset tempRS
'保存
tempxlApp.save '释放对象
Set tempxlSheet = Nothing
Set tempxlWorkbook = Nothing
'关闭excel
tempxlApp.Quit
Set tempxlApp = Nothing祝你好运!
不用那么复杂吧,呵呵。
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
'将有关的数据源转成一个数组asTempArray
Set xlApp = Excel.Application
Set xlBook = xlApp.Workbooks().Open(App.path & "\Templet\Book1.xlt")
Set xlSheet = xlBook.Worksheets("sheet1")
Set xlRange = xlApp.Range(Cells(1, 1), Cells(UBound(asTempArray, 1) + 1, UBound(asTempArray, 2) + 1))
xlRange.Value = asTempArray
xlRange.Borders.LineStyle = 1
xlRange.Borders.Weight = 1
xlApp.Visible = True ' "显示表格
xlSheet.PrintOut
xlApp.Application.DisplayAlerts = False
xlBook.Close
xlApp.Quit
Set xlApp = Nothing ' "交还控制给Excel