SQL Server中的数据如何使用Excle来打印

先使用rs得到你想打印的数据从sql中然后引用 microsoft excel对象进行对excel的控制,进行打印
example:Dim exl As New Excel.Application
Dim book As Excel.Workbook
Dim sheet As Excel.Worksheet
'先使用excel制作一个模板
Set book = exl.Workbooks.Open("c:\temp1.xls")
Set sheet = book.Worksheets(1)
book.Application.DisplayAlerts = False
'在excel的格子中写入你想打印的内容
With sheet

    '.Cells(Row, col) = ""


    '这里把rs中的数据写入excel中    .cells(1,1)=rs!id     '这里使用

End With
sheet.PrintOut'进行打印book.Save
SendKeys "{enter}"
book.Close
Set book = Nothing
exl.Quit
Set exl = Nothing
可以先用SQL2000的Export向导生成Excel文件，
最后保存DTS Package为VB的模块文件（bas文件），
加入这个文件到项目中，
以后就可以在你的程序里面调用运行了，试试吧。^_^下面是个例子：
'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\DavidZuo\Desktop\New Package.bas
'Package Name: New Package
'Package Description: DTS package description
'Generated Date: 2004-4-27
'Generated Time: 14:01:31
'****************************************************************Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld goPackage.Name = "New Package"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
Dim oConnProperty As DTS.OleDBProperty'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------Dim oConnection as DTS.Connection2'------------- a new connection defined below.
'For security purposes, the password is never scriptedSet oConnection = goPackage.Connections.New("SQLOLEDB") oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("User ID") = "test"
oConnection.ConnectionProperties("Initial Catalog") = "test_data"
oConnection.ConnectionProperties("Data Source") = "10.194.65.1"
oConnection.ConnectionProperties("Application Name") = "DTS  Import/Export Wizard"

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "10.194.65.1"
oConnection.UserID = "smt"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "smt_data"
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"goPackage.Connections.Add oConnection
Set oConnection = Nothing'------------- a new connection defined below.
'For security purposes, the password is never scriptedSet oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0") oConnection.ConnectionProperties("Data Source") = "C:\Documents and Settings\DavidZuo\Desktop\test"
oConnection.ConnectionProperties("Extended Properties") = "Excel 8.0;HDR=YES;"

oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\Documents and Settings\DavidZuo\Desktop\test"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"goPackage.Connections.Add oConnection
Set oConnection = Nothing'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint'------------- a new step defined belowSet oStep = goPackage.Steps.New oStep.Name = "Create Table tbMachine Step"
oStep.Description = "Create Table tbMachine Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Create Table tbMachine Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing'------------- a new step defined belowSet oStep = goPackage.Steps.New oStep.Name = "Copy Data from tbMachine to tbMachine Step"
oStep.Description = "Copy Data from tbMachine to tbMachine Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from tbMachine to tbMachine Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = True
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing'------------- a precedence constraint for steps defined belowSet oStep = goPackage.Steps("Copy Data from tbMachine to tbMachine Step")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("Create Table tbMachine Step")
oPrecConstraint.StepName = "Create Table tbMachine Step"
oPrecConstraint.PrecedenceBasis = 0
oPrecConstraint.Value = 4

oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------'------------- call Task_Sub1 for task Create Table tbMachine Task (Create Table tbMachine Task)
Call Task_Sub1( goPackage )'------------- call Task_Sub2 for task Copy Data from tbMachine to tbMachine Task (Copy Data from tbMachine to tbMachine Task)
Call Task_Sub2( goPackage )'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
tracePackageError goPackage
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
set goPackage = Nothingset goPackageOld = NothingEnd Sub
'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'-----------------------------------------------------------------------------
Public Sub tracePackageError(oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Long
Dim ErrorIDofInterfaceWithError As String
Dim i As Integer For i = 1 To oPackage.Steps.Count
If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
End If
Next iEnd Sub

调试易

SQL Server中的数据如何使用Excle来打印

解决方案 »