============
7.
下面的代码你可以参考一下Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
Select Case Button.Index
Case 1
' SSPanel2.Visible = True
' probar.Value = 0
' Dim myexcel As New Excel.Application, I, J, K As Integer, col As String
' With myexcel
' On Error GoTo excle
' .Application.Visible = False
' .Workbooks.Add
' '***********画字段************
' J = 0
' 'example: b2 ----g2 ’列 本程序从b列,和第2行开始
' For I = 66 To (66 + Rs_temp.Fields.Count - 1) '从rs中头一个字段到最后一个
' col = Chr(I) & "2" 'chr(66)就是b
' Range(col).Select
' ActiveCell.FormulaR1C1 = Rs_temp.Fields(J).Name '
' J = J + 1
' Next I
' '*************以先横后竖顺序画表***************
' K = 0
' Rs_temp.MoveFirst
' DoEvents
' For J = 3 To 3 + Rs_temp.RecordCount '本程序从b3开始，所以用3
' K = 0
' For I = 66 To (66 + Rs_temp.Fields.Count - 1)
' col = Chr(I) & CStr(J) '得到目标表格的值如 c3
' Range(col).Select
' ActiveCell.FormulaR1C1 = Rs_temp.Fields(K)
' K = K + 1
' Next I
' On Error Resume Next
' probar.Value = probar.Value + 1
' Rs_temp.MoveNext
' If Rs_temp.EOF = True Then
' SSPanel2.Visible = False
' .Application.Visible = True
' End If
' Next J
' End With
'excle:
' MsgBox ("您没有安装excle2000，请先安装excel2000")
Dim Irow, Icol As Integer
Dim Irowcount, Icolcount As Integer
Dim Fieldlen1 As Integer
'存字段长度值
Dim Fieldlen()
'Dim xlApp As Excel.Application
'Dim xlBook As Excel.Workbook
'Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1) SSPanel2.Visible = True
probar.Value = 0 'On Error GoTo excle
With Rs_temp
.MoveLast If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Sub
End If '记录总数
Irowcount = .RecordCount
'字段总数
Icolcount = .Fields.Count ReDim Fieldlen(Icolcount)
.MoveFirst For Irow = 1 To Irowcount + 1
For Icol = 1 To Icolcount
Select Case Irow
'在Excel中的第一行加标题
Case 1
xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1).Name
'将数组FIELDLEN()存为第一条记录的字段长
Case 2
If IsNull(.Fields(Icol - 1)) = True Then
Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name)
'如果字段值为NULL,则将数组Filelen(Icol)的值设为标题名的宽度
Else
Fieldlen(Icol) = LenB(.Fields(Icol - 1))
End If xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
'Excel列宽等于字段长
xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1)
'向Excel的CellS中写入字段值
Case Else
If IsNull(.Fields(Icol - 1)) Then
Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name)
Else
Fieldlen1 = LenB(.Fields(Icol - 1))
End If If Fieldlen(Icol) < Fieldlen1 Then
xlSheet.Columns(Icol).ColumnWidth = IIf(Fieldlen1 > 255, 255, Fieldlen1)
'表格列宽等于较长字段长
Fieldlen(Icol) = IIf(Fieldlen1 > 255, 255, Fieldlen1)
'数组Fieldlen(Icol)中存放最大字段长度值
Else
xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
End If xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1)
End Select
Next If Irow > 2 Then
If Not .EOF Then .MoveNext
End If
If Not .EOF Then
If Irow < Irowcount Then
probar.Value = probar.Value + 1
End If
End If
Next '网格线
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Name = "黑体"
'设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(Irow, Icol - 1)).Borders.LineStyle = xlContinuous
'设表格边框样式
End With '*!* 页眉、填报单位、报表时间、单位
With xlSheet.PageSetup
.LeftHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10公司名称：" ' & Gsmc
.CenterHeader = "&""楷体_GB2312,常规""业务数据综合查询表&""宋体,常规""" & Chr(10) & "&""楷体_GB2312,常规""&10日 期："
.RightHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10单位："
.LeftFooter = "&""楷体_GB2312,常规""&10制表人："
.CenterFooter = "&""楷体_GB2312,常规""&10制表日期："
.RightFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页"
End With '显示表格
Dim ExclFileName As String
ExclFileName = App.path & "\业务数据综合查询表.xls"
If Dir(ExclFileName) <> "" Then
Kill ExclFileName
End If
xlSheet.SaveAs (ExclFileName)
SSPanel2.Visible = False
xlApp.Application.Visible = True
'交还控制给Excel
'xlSheet.PrintPreview
'xlApp.Quit
End With
'excle:
' MsgBox ("您没有安装 Excle2000，请先安装 Excel2000 ！")
Case 2
Unload Me
End Select
End Sub
Dim wkbObj As Workbook '定义工作空间
Dim wksObj As Worksheet '定义表单
Dim StoreProc As String
Dim i As Integer Set wkbObj = Workbooks.Open(App.Path & "\Report\DocHistory.xls") '打开模版
wkbObj.NewWindow
Set wksObj = wkbObj.Worksheets(1) '打开表单模版 '判断其查询条件
If TxtName.Text = "" Then
StoreProc = " HFDocumentInOutTableFind '" & TxtNumID.Text & "','" & "'"
Set Rs = New ADODB.Recordset: Rs.Open StoreProc, CnnSql, adOpenKeyset, adLockOptimistic
Else
StoreProc = " HFDocumentInOutTableFindT '" & TxtName.Text & "','" & "'"
Set Rs = New ADODB.Recordset: Rs.Open StoreProc, CnnSql, adOpenKeyset, adLockOptimistic
End If
If Rs.EOF = True Then
MsgBox "没有找到记录，打印内容为空！", , "提示"
labtip.Visible = False
Rs.Close: Set Rs = Nothing
Exit Sub
End If
wksObj.Range("B2").Value = PUserUnitName
wksObj.Range("H2").Value = POperatorId
'循环
i = 4
Do Until Rs.EOF
'设表格线
Call wksObj.Range("A" + Format(i), "a" + Format(i)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
Call wksObj.Range("B" + Format(i), "b" + Format(i)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
Call wksObj.Range("C" + Format(i), "c" + Format(i)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
Call wksObj.Range("D" + Format(i), "d" + Format(i)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
Call wksObj.Range("E" + Format(i), "e" + Format(i)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
Call wksObj.Range("F" + Format(i), "f" + Format(i)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
Call wksObj.Range("G" + Format(i), "g" + Format(i)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
Call wksObj.Range("H" + Format(i), "h" + Format(i)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("A" + Format(i)).Value = Trim(Rs!DDocumentID)
wksObj.Range("B" + Format(i)).Value = Format(Trim(Rs!DDocumentLendDate), "yyyy-mm-dd")
wksObj.Range("C" + Format(i)).Value = Trim(Rs!DDocumentLendUserID)
wksObj.Range("D" + Format(i)).Value = Trim(Rs!DDocumentLendOperatorID)
wksObj.Range("E" + Format(i)).Value = Format(Trim(Rs!DDocumentStillUserDate), "yyyy-mm-dd")
wksObj.Range("F" + Format(i)).Value = Trim(Rs!DDocumentStillUserID)
wksObj.Range("G" + Format(i)).Value = Trim(Rs!DDocumentStillOperatorID)
wksObj.Range("H" + Format(i)).Value = Trim(Rs!DDocumentJournalMem)
i = i + 1
Rs.MoveNext
Loop
Rs.Close: Set Rs = Nothing
'可视
wkbObj.Application.Visible = True
'打印预兰
wksObj.PrintPreview
'关闭
wkbObj.Close SaveChanges:=False
labtip.Visible = False
End Sub
Dim a, i, j As Integer
Dim b As StringPrivate Sub Command1_Click()
Dim ex As Object
Dim exbook As Object
Dim exsheet As Object
Set ex = CreateObject("Excel.Application")
Set exbook = ex.Workbooks().Add
Set exsheet = exbook.Worksheets("sheet1")
'按控件的内容赋值
'11
exsheet.Cells(1, 1).Value = Text1.Text
'为同行的几个格赋值
Range("C3").Select
ActiveCell.FormulaR1C1 = "表格"
' ex.Range("c3").Value = "表 格"
ex.Range("d3").Value = " 春 天 "
ex.Range("e3").Value = " 夏 天 "
ex.Range("f3").Value = " 秋 天 "
ex.Range("g3").Value = " 冬 天 "
'大片赋值
ex.Range("c4:g7").Value = x
'按变量赋值
a = 8
b = "c" & Trim(Str(a))
ex.Range(b).Value = "下雪"
'另外一种大片赋值
For i = 9 To 12
For j = 4 To 7
exsheet.Cells(i, j).Value = i * j
Next j
Next i
'计算赋值
exsheet.Cells(13, 1).Formula = "=R9C4 + R9C5"
'设置字体
Dim exRange As Object
Set exRange = exsheet.Cells(13, 1)
exRange.Font.Bold = True'设置一行为18号字体加黑
Rows("3:3").Select
Selection.Font.Bold = True
With Selection.Font
.Name = "宋体"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
'设置斜体
Range("E2").Select
Selection.Font.Italic = True
'设置下划线
Range("E3").Select
Selection.Font.Underline = xlUnderlineStyleSingle'设置列宽为15
Selection.ColumnWidth = 15'设置一片数据居中
Range("C4:G7").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
'设置某区域的小数位数
Range("F4:F7").Select
Selection.NumberFormatLocal = "0.00"
'求和
Range("G9:G13").Select
Range("G13").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
'某列自动缩放宽度
Columns("C:C").EntireColumn.AutoFit
'画表格
Range("C4:G7").Select
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
'加黑框
Range("C9:G13").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'设置某单元格格式为文本
Range("E11").Select
Selection.NumberFormatLocal = "@"
'设置单元格格式为数值
Range("F10").Select
Selection.NumberFormatLocal = "0.000_);(0.000)"
'设置单元格格式为时间
Range("F11").Select
Selection.NumberFormatLocal = "h:mm AM/PM"'取消选择
Range("C10").Select
'设置横向打印，A4纸张
' With ActiveSheet.PageSetup
' .PrintTitleRows = ""
' .PrintTitleColumns = ""
' End With
' ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
' .CenterHeader = ""
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 300
' .CenterHorizontally = False
' .CenterVertically = False
.Orientation = xlLandscape
' .Draft = False
.PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
End With
'跨列居中
Range("A1:G1").Select
With Selection
.HorizontalAlignment = xlCenter
' .VerticalAlignment = xlBottom
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .ShrinkToFit = False
.MergeCells = True
End With
Selection.Merge'打印表格
ActiveWindow.SelectedSheets.PrintOut Copies:=1'取值
Text1.Text = exsheet.Cells(13, 1)
'保存
ChDir "C:\WINDOWS\Desktop"
ActiveWorkbook.SaveAs filename:="C:\WINDOWS\Desktop\aaa.xls", FileFormat:=xlNormal, Password:="123", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
' 关闭工作表。
exbook.Close
'用 Quit 方法关闭 Microsoft Excel
ex.Quit
'释放对象
Set ex = Nothing
Set exbook = Nothing
Set exsheet = Nothing
End Sub