查阅相关的VBA的帮助文档即可。在以前VB中,俺直接这么写: '打印导出项目数据到Excel '=================================================== Public Function ExportSingleProject(ByVal id As Long)
'Dim xlApp As Excel.Application 'Dim xlBook As Excel.Workbook 'Dim xlSheet As Excel.Worksheet
Dim xlApp As Object Dim xlBook As Object Dim xlSheet As Object
'Dim rsProject As ADODB.Recordset Dim rsProject As Object
Dim iIndex As Integer Dim TxtSQL As String
'创建EXCEL对象 Set xlApp = CreateObject("Excel.Application") 'Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open(FileName:=sReportPath & "\Project.xls")
Set xlSheet = xlBook.Worksheets(1)
'删除原有的旧打印文件 If UCase(Dir(sCachePath & "\_Project.xls")) = UCase("_Project.xls") Then Kill sCachePath & "\_Project.xls"
'从数据库查询得到项目数据 TxtSQL = "select A.*, B.Name as AreaName from Projects A left join AreaCodes B on A.AreaCode = B.Code where A.ID=" & id
Set DbCn = CreateObject("ADODB.Connection")
DbCn.ConnectionString = sDbConnectionString
DbCn.Open
Set rsProject = DbCn.Execute(TxtSQL)
'If rsProject.RecordCount < 1 Then If rsProject.EOF Then
Set xlSheet = Nothing: Set xlBook = Nothing: Set xlApp = Nothing Err.Raise rsProject.RecordCount, Me, "数据库发生了错误,无法取得项目库项目数据" Exit Function
'打印导出项目数据到Excel
'===================================================
Public Function ExportSingleProject(ByVal id As Long)
'Dim xlApp As Excel.Application
'Dim xlBook As Excel.Workbook
'Dim xlSheet As Excel.Worksheet
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
'Dim rsProject As ADODB.Recordset
Dim rsProject As Object
Dim iIndex As Integer
Dim TxtSQL As String
'创建EXCEL对象
Set xlApp = CreateObject("Excel.Application")
'Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open(FileName:=sReportPath & "\Project.xls")
Set xlSheet = xlBook.Worksheets(1)
'删除原有的旧打印文件
If UCase(Dir(sCachePath & "\_Project.xls")) = UCase("_Project.xls") Then Kill sCachePath & "\_Project.xls"
'从数据库查询得到项目数据
TxtSQL = "select A.*, B.Name as AreaName from Projects A left join AreaCodes B on A.AreaCode = B.Code where A.ID=" & id
Set DbCn = CreateObject("ADODB.Connection")
DbCn.ConnectionString = sDbConnectionString
DbCn.Open
Set rsProject = DbCn.Execute(TxtSQL)
'If rsProject.RecordCount < 1 Then
If rsProject.EOF Then
Set xlSheet = Nothing: Set xlBook = Nothing: Set xlApp = Nothing
Err.Raise rsProject.RecordCount, Me, "数据库发生了错误,无法取得项目库项目数据"
Exit Function
End If
'填充打印数据
With rsProject
xlSheet.Cells(2, 1).Value = "地区:" & .Fields("AreaName") & " " + "日期:" & Now
xlSheet.Cells(3, 2).Value = .Fields("Name")
xlSheet.Cells(4, 2).Value = .Fields("Kind")
xlSheet.Cells(5, 2).Value = .Fields("Owner")
xlSheet.Cells(6, 2).Value = .Fields("Size")
xlSheet.Cells(7, 2).Value = "总投资" & .Fields("Invest") & "万元,其中固定资产投资" & .Fields("Fixup") & "万元,流动资金" & .Fields("Dynamic") & "万元 "
xlSheet.Cells(8, 2).Value = "自筹" & .Fields("Financing") & "万元,贷款" & .Fields("Provide") & "万元,引进" & .Fields("Indraught") & "万元,其他" & .Fields("Other") & "万元 "
xlSheet.Cells(9, 2).Value = "产值" & .Fields("Production") & "万元,利税" & .Fields("Revenue") & "万元"
xlSheet.Cells(10, 2).Value = .Fields("CooperateMode")
xlSheet.Cells(11, 2).Value = .Fields("BuildCondition")
xlSheet.Cells(12, 2).Value = .Fields("Address")
xlSheet.Cells(13, 2).Value = .Fields("Linkman")
xlSheet.Cells(14, 2).Value = .Fields("PostCode")
xlSheet.Cells(15, 2).Value = .Fields("Tel")
xlSheet.Cells(16, 2).Value = .Fields("Email")
xlSheet.Cells(17, 2).Value = .Fields("Fax")
End With
Set rsProject = Nothing
Set DbCn = Nothing
'另存EXCEL打印对象
If xlBook.Saved = False Then xlBook.Saved = True
xlBook.SaveAs (sCachePath & "\_Project.xls")
xlApp.Visible = True
'(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)
xlApp.ActiveSheet.PrintOut , , 1, True '预览打印
'xlApp.Quit
Set xlSheet = Nothing: Set xlBook = Nothing: Set xlApp = Nothing
End Function
foreach(DataRow dr in this.dataSet11.Tables["ST_RIVER_R"].Rows)
{
for (j=1;j<=10;j++)
{
excel.Cells[i,j] = dr[j-1];
}
i++;
}
workbook.SaveAs(temp[0],Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlSaveAsAccessMode.xlNoChange,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
workbook.Close(false,Type.Missing,Type.Missing);