sqlserver导出报表的几种方法.doc

SQL Server数据表在编程中实现导出EXCEL几种方法 -快速将SQL Server 数据库表（或网格控件）数据导出EXCEL关键字: SQL Server2000、Select高级查询、MsFlexGrid、MsHFlexGrid、ListView、EXCEL和VB6前言： 目前，随着电脑技术知识的广泛普及，掌握微软Office办公软件的技术人员（一般办公人员）队伍逐渐加速扩大；促使多数商用软件如：MIS、ERP、MRP等系统在解决数据输出方面，（除数据显示和打印功能外）都多了一项任务，即将数据导出EXCEL文件的功能。这个“转换”技术并不难，主要是将数据库编程技术与Office Excel文件的建立、存储技术有机地结合就会把这个问题解决。以下是我们在设计开发软件中，解决数据导出EXCEL实例，方法及相关的事宜。具体说明如下：一、采用SQL Server数据库导出EXCEL文件原由和方法在SQL Server数据库系统中已经提供了非常丰富的实现表导入、导出的方法，而我们又重复此举的必要性何在呢？回答：有必要。只要对用户实际工作有利有益，我们就要做，还要做好，让其到位。.其原由：用户需要数据的结果集不是大量静态表的数据，而是经过Select高级查询（多条件；排序；筛选，或汇总后）结果集数据，是有保存价值（有再次使用的空间）。. 三种导出EXCEL的方法： EXCEL;1.Table (采用Select高级查询后的结果集) 2.Table EXCEL;控件(显示后)(采用Select高级查询后的结果集)3.Table 打印（后，再导出Crystal Report 9中文版）(采用Select高级查询后的结果集) EXCEL；.熟练掌握以下编程技术;1.能够熟练地掌握用编程语言，书写数据库：SQL 语句（Select高级查询）的使用方法；2.正确地使用MsFlexGrid、MsHFlexGrid、ListView控件，装入Select高级查询后的结果集方法。3.正确设计水晶报表，解决输出Select高级查询后的结果。.用VB过程调用来解决导出EXCEL的方法。二、实例：请见图示1（由于篇幅所限，仅举图示1中的两种方法）.在模块中必须的”引用”#. Microsoft Excel 11.0 Object Library#. Microsoft ActiveX Data Object 2.6 Library 以下两个实例均采用过程调用得以实现。 模块中的定义代码： Option ExplicitDim RS2 As New ADODB.Recordset 定义数据集对象Dim RS4 As New ADODB.Recordset 定义数据集对象Dim Ssql1, Ssql2, Ssql3 As String Select高级查询字符串变量Dim My_Path As String 定义路径及文件名Dim DT1, DT2 As Variant 定义日期变量Dim Q As Integer 定义数据整型变量 具体步骤：.进入界面：首先，选择（HireDate）租用日期，自从2001-01-03截至2003-08-27。.用鼠标点击【显示数据】按钮；符合条件的数据显示到网格中。.【显示数据】按钮下的代码：Private Sub Command1_Click() 显示数据DT1 = Format(Trim(D1.Value), yyyy-mm-dd)DT2 = Format(Trim(D2.Value), yyyy-mm-dd)Ssql1 = : Ssql2 = : Ssql3 = 1.全部。2.表名。3.条件。Ssql1 = Select * From Ssql2 = EmployeesSsql3 = where hiredate= & DT1 & And hiredate= & DT2 & Ssql1 = Ssql1 & Ssql2 & Ssql3MSFlexGrid1_Click 运行Select高级查询将结果集装入网格控件;其过程略。Label1.Caption = Label1.ForeColor = QBColor(9) Label1.Caption = 目前运行表名: & Ssql1 显示图示上Select高级查询字符串Command3.Enabled = True 激活命令按钮Command5.Enabled = True 激活命令按钮End Sub.将MsFlexGrid控件所显示的数据导出EXCEL1.【MSFlexGrid导出至Excel】按钮下的过程代码：Private Sub Command3_Click() MSFlexGrid导出EXCEL/My_Path = App.Path & & Ssql2 & _G.xlsPrintTableToExcel1 Me.MSFlexGrid1, 表名: & Ssql2, Select_高级查询： & Ssql1, My_Path, Me.ProgressBar1 有参数过程调用MsgBox Chr(13) + 数据已经导出到文件: + Chr(13) + Chr(13) + My_Path + , vbInformationCommand3.Enabled = FalseIf Command5.Enabled = False And Command3.Enabled = False Then Clea_xy1 符合条件清空网格End Sub2. 生成EXCEL文件的代码Public Sub PrintTableToExcel1(ByRef myGrid As MSFlexGrid, ByVal strHeader As String, ByVal strInfo As String, ByVal strFileName As String, ByRef proBar As ProgressBar) 参数：网格、标题、Select_高级查询字符串、导出路径和文件名、进度条控件 On Error Resume Next Dim ExcelApp As Excel.Application 定义EXCEL变量 Dim R As Long, C As Long 定义行、列变量 Set ExcelApp = GetObject(, Excel.Application) If myGrid.Rows = 1 Then Exit Sub 导入文件执行的条件 If Err.Number 0 Then Err.Clear Set ExcelApp = CreateObject(Excel.application) End If Dim wsBook As Workbook 定义EXCEL的BOOK变量 Dim wsSheet As Worksheet 定义EXCEL的SHEET变量 With ExcelApp.AutoCorrect.Application .WindowState = xlMinimized .SheetsInNewWorkbook = 1 .Visible = False .Workbooks.Add Set wsBook = .ActiveWorkbook Set wsSheet = .ActiveSheet End With -进度 proBar.Min = 0 proBar.Max = myGrid.Rows proBar.Value = 0 proBar.Visible = True - With wsSheet .Cells.Font.Name = System .Cells.Font.Size = 12 .Name = 导出的表格 Range(Cells(1, 1), Cells(1, myGrid.Cols).Select 第一行表名 Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Merge 合并居中 .Cells(1, 1) = strHeader Range(Cells(2, 1), Cells(2, myGrid.Cols).Select 第二行Select查询字符串 Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Merge 合并居中 .Cells(2, 1) = strInfo For R = 0 To myGrid.Rows - 1 For C = 0 To myGrid.Cols - 1 .Cells(R + 3, C + 1) = myGrid.TextMatrix(R, C) Next On Error Resume Next -进度 proBar.Value = R + 1 - If Err.Number 0 Then Err.Clear Next 第三行开始存放MSFlexGrid控件的数据 Range(.Cells(3, 1), .Cells(myGrid.Rows + 2, myGrid.Cols).Select With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlWide .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlWide .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlWide .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlWide .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 With -进度 proBar.Visible = False - ExcelApp.AutoCorrect.Application.DisplayAlerts = True Call ExcelApp.AutoCorrect.Application.ActiveWorkbook.SaveAs(strFileName) 路径及文件名 ExcelApp.QuitEnd Sub .将Select高级查询的结果集导出EXCEL 1.【Select高级查询-EXCEL】按钮下的过程代码： Private Sub Command5_Click() Table导出EXCEL #.注释：过程调用参数：1.Select 高级查询字符串。2.默认当前路径及文件名。3.表的名称。My_Path = App.Path & & Ssql2 & _T.xlsExEcToExcel Ssql1, My_Path, Ssql2 有参数过程调用MsgBox Chr(13) + 数据表已经导出到文件: + Chr(13) + Chr(13) + My_Path + , vbInformationCommand5.Enabled = FalseIf Command3.Enabled = False And Command5.Enabled = False Then Clea_xy1 符合条件清空网格End Sub 2. 生成EXCEL文件的代码Public Function ExEcToExcel(ByVal StrOpen As String, ByVal strFileName As String, ByVal S_TabName As String)参数: Select_高级查询字符串、导出文件路径及文件名、表名称 Dim iRowCount As Integer 记录总数变量 Dim iColCount As Integer 字段总数变量 Dim xlApp As New Excel.Application 定义EXCEL变量 Dim xlbook As Excel.Workbook 定义EXCEL的BOOK变量 Dim xlsheet As Excel.Worksheet 定义EXCEL的SHEET变量 Dim xlQuery As Excel.QueryTable 定义 EXCEL的查询变量 With RS4 执行数据集的操作 If .State = adStateOpen Then .Close End If .ActiveConnection = Mydb Cn .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockReadOnly .Source = StrOpen .Open End With With RS4 If .RecordCount 1 Then MsgBox 没有可导出的记录!, vbInformation + vbOKOnly, 提示 : Exit Function End If 记录总数 iRowCount = .RecordCount 字段总数 iColCount = .Fields.Count End With Set xlApp = CreateObject(Excel.Application) Set xlbook = Nothing Set xlsheet = Nothing Set xlbook = xlApp.Workbooks().Add Set xlsheet = xlbook.Worksheets(sheet1) xlApp.Visible = True With xlsheet .Cells.Font.Name = 宋体 设置字体，加粗，字号 .Cells.Font.Bold = True .Cells.Font.Size = 12 .Name = 导出Select查询结果集 Range(Cells(1, 1), Cells(1, iColCount).Select Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Merge 合并居中 .Cells(1, 1) = 表名： & S_TabName 表名称 Range(Cells(2, 1), Cells(2, iColCount).Select Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Merge 合并居中 .Cells(2, 1) = Select 高级查询: & StrOpen 查询字符串 End With 添加查询语句，导入EXCEL数据 Set xlQuery = xlsheet.QueryTables.Add(RS4, xlsheet.Range(a3) 从a3行开始 With xlQuery .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True End With xlQuery.FieldNames = True 显示字段名 xlQuery.Refresh With xlsheet 设置表格的样式;从3行开始 .Range(.Cells(3, 1), .Cells(iRowCount + 3, iColCount).Borders.LineStyle = xlContinuous End With xlApp.Application.Visible = True -将导出数据存放在默认的路径的文件夹中 xlApp.AutoCorrect.Application.DisplayAlerts = True Call xlApp.AutoCorrect.Application.ActiveWorkbook.SaveAs(strFileName) 存盘的文件名称 xlApp.Quit Set xlApp = Nothing 交还控制于Excel Set xlbook = Nothing Set xlsheet = NothingEnd Function三、程序设计要点和“联系实际”问题：1. 程序设计要点要熟练掌握编程中过程调用（重点为：带参数）方法；熟知数据库，要熟练掌握SQL的基本语法；掌握EXCEL的一些使用编辑过程。补充说明：.文中提到控件MsHFlexGrid、ListView的数据导出EXCEL，由于篇幅的问题，可向栏目责编索取。.Table 打印（后，再导出Crystal Report 9中文版）(采用Select高级查询后的结果集) EXCEL.,水晶报表打印软件，提供了丰富的导出格式文件，无须编程；可导出16种格式文件：如PDF；EXCEL等。详见图示2 .定义数据库连接与打开的语句：Public Mydb As New ADODB.Connection 定义数据库连接Mydb.OpenProvider=sqloledb;DataSource=NEDTWO-56958BFB;UserID=sa;pwd=