使用VB+Excel制作工程报表1.doc

VB操作Excel制作工程报表在工控项目中，报表的制作一直是困扰大部分现场工程师的一个难题，灵活多变的用户需求，复杂的数据表现格式，使得数据的组织十分麻烦。近日，笔者制作的一个项目用户需要的报表十分繁琐，呈现形式、打印方式等要求较高，这写都要求较高的编程技术，而Excel是处理数据报表的十分方便而强大的工具，如果我们将数据全部提交到Excel中去，在Excel中按照用户的意愿进行报表的设计和打印，则是十分轻松的工作。因此，笔者考虑使用VB将数据提交到Excel中，并操作它完成灵活的报表工作。首先，从数据库中查询需要的数据。本文以Access数据库为例，使用VB编程，通过ADO编程接口访问Access数据库，提取需要的数据后填充预定义的Excel表格。1，建立Access数据库。本文假设一个数据库SamDb，建立一个表Table，该表的字段设置如下图示：2，打开VB，在窗体上画上如下控件，在工程引用中引入ADO2.8库。为了示例的需要，加入插入数据一项，用来向数据表中添加示例数据。将Text控件的text属性设置为空字符串。添加Dbcon公共过程。Public Sub Dbcon(ByVal FilePaht As String, ByVal CmdSelect As Boolean, ByVal CmdStr As String)Con.Open provider = microsoft.jet.oledb.4.0;data source = + FilePahtIf CmdSelect ThenRst = Cmd.Execute(CmdStr)ElseCmd.Execute (CmdStr)End IfEnd Sub该模块接受Sql命令字符串通过ADO执行命令，根据命令有没有返回值分别执行。在按钮“插入数据”的点击事件中中加入以下驱动代码：On Error GoTo Err1Dim cmdstr As Stringcmdstr = insert into DataTable(温度,压力,流量,液位,pH,溶氧) values( & Text1.Text & , & Text2.Text & , & Text3.Text & , & Text4.Text & , & Text5.Text & , & Text6.Text & )Call Dbcon(E:创意创作实例SamDb.mdb, False, cmdstr)Con.CloseSet Con = NothingSet Cmd = NothingMsgBox 成功插入数据！Exit SubErr1:MsgBox 插入数据异常！运行程序，在文本框中输入数据，点击插入数据按钮，我们测试一下插入数据：成功插入数据，返回操作提示。同样的方法，添加几行数据到数据库中。3，在工程菜单的引用中加入microsoft Excel Objcet Library2.8的引用。在“关闭”按钮中添加一行代码：END表示程序结束执行。在“打开报表”按钮中添加如下代码：Label7.Visible = True 表示显示该标签，提示用户Label7.Caption = 正在生成报表，这可能花费几分钟时间定义Excel对象Dim app As New Excel.ApplicationDim workbook As New Excel.workbookDim worksheet As New Excel.worksheetDim chart As New Excel.chart连接对象引用app.Caption = 报表系统Set workbook = app.Workbooks.Addworkbook.FullName = 工控报表系统workbook.Worksheets(1).Deleteworkbook.Worksheets(1).DeleteSet worksheet = workbook.Worksheets(1)worksheet.Name = 数据报表Set chart = workbook.Charts.Add(after:=app.Sheets(1)chart.Name = 分析图表app.Sheets(1).Move after:=app.Sheets(2)将数据库中的数据读入到Excel中去Call Dbcon(E:创意创作实例SamDb.mdb, True, select * from datatable)Dim n As Integern = 4worksheet.Activateworksheet.Range(Cells(1, 1), Cells(2, Rst.Fields.Count - 1).SelectSelection.MergeWith worksheet.Cells(1, 1) .Value = 工控系统报表 .Font.Bold = True .Font.Name = 隶书 .Font.Size = 22End With worksheet.Cells(3, 1).Value = 温度 worksheet.Cells(3, 2).Value = 压力 worksheet.Cells(3, 3).Value = 流量 worksheet.Cells(3, 4).Value = 液位 worksheet.Cells(3, 5).Value = pH worksheet.Cells(3, 6).Value = 溶氧 worksheet.Range(Cells(3, 1), Cells(3, 6).Select With Selection.Interior .ColorIndex = 35 .Pattern = xlSolid End WithDo Until Rst.EOF worksheet.Cells(n, 1).Value = CStr(Rst(温度) worksheet.Cells(n, 2).Value = CStr(Rst(压力) worksheet.Cells(n, 3).Value = CStr(Rst(流量) worksheet.Cells(n, 4).Value = CStr(Rst(液位) worksheet.Cells(n, 5).Value = CStr(Rst(pH) worksheet.Cells(n, 6).Value = CStr(Rst(溶氧) Rst.MoveNext n = n + 1Loop显示Excel对象worksheet.Range(Cells(1, 1), Cells(n - 1, Rst.Fields.Count - 1).SelectWith Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContextEnd With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .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 Dim Str As String Str = :$ & Chr(Rst.Fields.Count - 1 + 64) & $ & CStr(n - 1) worksheet.PageSetup.PrintArea = $A$1 & Str With worksheet.PageSetup .LeftHeader = 报表：&A 日期：&D .CenterHeader = .RightHeader = .LeftFooter = .CenterFooter = .RightFooter = 第 &P 页，共 &N 页 .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.984251968503937) .BottomMargin = Application.InchesToPoints(0.984251968503937) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 300 .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDispla