VSFlexGrid控件属性和常用方法二

一、增加记录使用for来循环表格行。
     for i=1 to grid1.rows-1
           with rs
                  .addnew
                  .fileds(o)=grid1.textmariy(i,0)
                  .fileds(1)=grid1.textmariy(i,1)
                  .fileds(2)=grid1.textmariy(i,2)
                  .fileds(3)=grid1.textmariy(i,3)
                  .update
            end with
       next
二、添加行
　　grid1.additem row
三、删除当前行
　　with grid1
             i=.row
             .removeitem i
       end with
四、要显示下拉框，可以使用vsflexgrid中列绑定功能
　　grid1.colcombolist(1)＝grid.buildcombolist(rs,"商品名称")
跟楼上的相比,仅仅是datamode不一样(2-flexDMBoundBatch)
但这样做的优势是非常明显的:可以撤销包括新增删除在内的所有操作,按保存键才写入数据库
Private Sub CmdDel_Click()
If fg.Row <> 0 Then fg.RemoveItem (fg.Row)
fg.Refresh
End Sub
Private Sub CmdAdd_Click()
On Error Resume Next
Adodc1.Recordset.AddNew
If Err.Number <> 0 Then MsgBox Err.Description
End Sub
Private Sub CmdUpdate()
Adodc1.Recordset.UpdateBatch adAffectAllChapters
End Sub
Private Sub CmdCancel_Click()
     Adodc1.Recordset.CancelBatch
     fg.DataRefresh
End Sub
Private Sub Form_Load()
Adodc1.ConnectionString = "FILE NAME=" & App.Path & "/conn.dsn"
Adodc1.LockType = adLockBatchOptimistic
Adodc1.RecordSource = "Your_Tablename"
Set fg.DataSource = Adodc1
End Sub
 
1、打印vsflexgrid可以使用vsprinter打印控件。跟vsflexgrid配套使用效果不错。
2、导出EXECL，可以使用grid.savegrid的方法。
    用savegrid的方法，在导出execl时，如果碰到类似于银行帐号的列如：“６４６５４５６６６５”，导到ＥＸＥＣＬ中就不这样显示了，这个问题还不知道怎么解决？？
    另外也可以写代码（这个方法比较实用，但慢一些）：
    Dim excelApp As Excel.Application
     Set excelApp = New Excel.Application
     On Error Resume Next
     If excelApp Is Nothing Then
        Set excelApp = CreateObject("Excel.application")
        If excelApp Is Nothing Then
           Exit Sub
        End If
     End If
     excelApp.Visible = True
     Me.MousePointer = vbHourglass
     excelApp.Workbooks.Add
     With excelApp.ActiveSheet
         Dim i As Integer, j As Integer
         For i = 1 To Grid1.rows
             For j = 1 To Grid1.Cols
                   .Cells(i, j).value ="'"& Grid1.TextMatrix((i - 1), (j - 1))'加上“'”号则可以解决上面savegrid中银行帐号的导出问题。
             Next j
             DoEvents
         Next i
     End With
     Me.MousePointer = vbDefault
     Set excelApp = Nothing
End Sub
   EXCEL同Vsflexgrid通过
最近很多的朋友,都想知道EXCEL怎样同VSflexgrid交换数据。
实际上，利用“复制”、“粘贴”菜单即可实现。具体如下：
（1）在Vsflexgrid上弹出右键菜单
   Private Sub grid1_MouseDown(Button As Integer, Shift As Integer, X As Single, y    As Single)
     if   Button = 2 Then   PopupMenu mnutccd
   End Sub
（2）设置各菜单的内容
A 复制
     Clipboard.Clear
     Clipboard.SetText grid1.Clip
B 剪切
    Dim rowc As Long
    Dim rowz As Long
    Dim colc As Long
    dim colz As Long
    dim i as long
    dim s as long
    If grid1.Rows = 1 Then Exit Sub
    Clipboard.Clear
   Clipboard.SetText grid1.Clip
    If grid1.RowSel > grid1.row Then
        rowc = grid1.row
        rowz = grid1.RowSel
    Else
        rowc = grid1.RowSel
       rowz = grid1.row
    End If
    If grid1.ColSel > grid1.Col Then
       colc = grid1.Col
       colz = grid1.ColSel
    Else
       colc = grid1.ColSel
       colz = grid1.Col
     End If
     For i = rowc To rowz
        For s = colc To colz
            grid1.TextMatrix(i, s) = ""
       Next
     Next
C 粘贴（精华部分)
   Dim i As Long
   Dim s As Long
   Dim m As Long
   Dim t As Long
    If grid1.Rows = 1 Then Exit Sub
    t = Len(Clipboard.GetText)
    If t = 0 Then Exit Sub
    For i = 1 To t
       If Mid(Clipboard.GetText, i, 1) = Chr(9) Then s = s + 1
       If Mid(Clipboard.GetText, i, 1) = Chr(13) Then m = m + 1
    Next
    If s / (m + 1) + grid1.Col > grid1.Cols - 1 Then
        grid1.ColSel = grid1.Cols - 1
    Else
       grid1.ColSel = s / (m + 1) + grid1.Col
    End If
    If grid1.row + m > grid1.Rows - 1 Then
        grid1.RowSel = grid1.Rows - 1
    Else
        grid1.RowSel = grid1.row + m
    End If
    grid1.Clip = Clipboard.GetText
 
VSFlexGrid 常用属性或方法：
.FixedRows = 1                             '固定几行
.FixedCols = 1                             '固定几列
.Editable = True                           '允许修改
.AllowUserResizing = flexResizeBoth        '可调整行/列
.FocusRect = flexFocusNone                 '无虚框
.SelectionMode = flexSelectionListBox      '焦点选中样式
.BackColor = RGB(255, 255, 255)            '单元背景色
.BackColorSel = vbBlue                     '单元选择色
.BackColorFixed = RGB(208, 192, 160)       '固定单元色
.BackColorAlternate = RGB(255, 250, 230)   '间隔行背景色
.GridColor = RGB(245, 240, 210)            '单元线条色
.ForeColor = RGB(0, 0, 0)                  '单元前景色(字符色)
.RowHeightMin = 260                        '最小行高
.RowHeightMax = 800                        '最大行高
.ColHeightMin = 50                         '最小列宽
.ColHeightMax = 3000                       '最大列宽
.ColWidth(Col) = 1000                      '指定列宽
.RowHeight(Row) = 260                      '指定行高
.TextMatrix(Row,Col) = "Text"              '指定单元字符
.Text = "Text"                             '选定单元字符
.MergeCol(Col) = True                      '允许合并列
.MergeRow(Row) = True                      '允许合并行
.MergeCells = 0|1|2|3|4|5|6                '合并选项
.Cell(选项准则, Row1, Col1, Row2, Col2)    '选择部分的相应准则值
.EditCell                                  '当移动到当前单元时自动选择
.EditSelStart                              '移动到单元时的光标位置
.MousePointer                              '设置对象的鼠标指针样式 O.A = 0 到 15|99
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
几个特殊的属性方法的使用：
FormatString 属性：管道符格式化字符串示例：
下面定义对齐方式同字意，列宽窄同距离
VSG1.FormatString = "^   中 |<      左     |>    右   |>    右   |^   中   "
+++++++++++++++++++++++++
搜索(查找)表格中符合条件的行：
FindRow 属性：该属性返回一个行值
MsgBox VSG1.FindRow(关键词,[指定行],[指定列],[敏感],[精度])
关键词：String，表示要搜索的字符串
指定行/指定列：Long，表示只在指定的行或列中找
敏感：Boolean，
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
'限制只能在指定列输入(这里默认倒数第2列)
Private Sub VSG1_BeforeRowColChange(ByVal OldRow As Long, _
        ByVal OldCol As Long, ByVal NewRow As Long, _
        ByVal NewCol As Long, Cancel As Boolean)
   VSG1.Editable = flexEDKbd
   If VSG1.Redraw <> flexRDNone And NewCol <> VSG1.Cols - 2 Then
      Cancel = True
      VSG1.Select NewRow, VSG1.Cols - 2
   End If
End Sub
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
限制不能编辑某些列：(这里限制第1列和第3列)
Private Sub VSG1_RowColChange()
    If VSG1.Col = 1 or VSG1.Col = 3 Then
       VSG1.FocusRect = flexFocusNone
       VSG1.Editable = flexEDNone
      'SendKeys "{TAB}"
    Else
       VSG1.Editable = flexEDKbd
       SendKeys "{ENTER}"
    End If
End Sub
或：
Private Sub VSG1_RowColChange()
If VSG1.Col = 1 or VSG1.Col = 3 Then
    SendKeys "{RIGHT}"
Else
    SendKeys "{ENTER}"
End If
End Sub
或：
Private Sub VSG1_RowColChange()
If VSG1.Col = 1 or VSG1.Col = 3 Then
    VSG1.Editable = flexEDNone
Else
    VSG1.Editable = flexEDKbd
    VSG1.EditCell   '自动选择单元内容
    VSG1.EditSelStart = 0[选到最前]|1[选到指定]|Len(VSG1.Text)[选到最后]
End If
End Sub
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
'对齐方式(-1标示所有)
.ColAlignment(-1) = flexAlignLeftCenter|flexAlignCenterCenter|flexAlignRightCenter
示例1：(最后一行的第3列靠右对齐)
VSG1.Select VSG1.Rows - 1, 2
VSG1.CellAlignment = flexAlignRightCenter
示例2：
VSG1.Row = VSG1.Rows - 1: VSG1.Col = 1
VSG1.CellAlignment = flexAlignRightCenter
示例3：
VSG1.Cell(flexcpAlignment, VSG1.Rows - 1, 1, VSG1.Rows - 1, 3) = flexAlignRightCenter
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
本对象拖放：
Private Sub VSG1_MouseDown(Button As Integer, _
         Shift As Integer, X As Single, Y As Single)
VSG1.Drag
VSG1.DragIcon = LoadPicture("D:/Icon.ico")
VSG1.DragRow VSG1.RowSel
End Sub
或从其它对象拖：
Private Sub VSG2_MouseDown(Button As Integer, _
         Shift As Integer, X As Single, Y As Single)
VSG2.OLEDrag
VSG1.OLEDropMode = flexOLEDropAutomatic
End Sub
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Public Sub VSGridCount(Optional SelRow As Long, Optional SelCol As Long)
Dim X As Long, i As Long
Dim Hj1, Hj2, Hj3 As String
Const A1 = -922337203685477#, A2 = 922337203685477#
On Error GoTo ErrTransact
With frmFG.VSG1
    X = .Rows - 1
    .MergeCells = flexMergeFree
    .MergeRow(X) = True
    .Cell(flexcpText, X, 0, X, 1) = "合 计"
    If .Rows = 3 Then
       .TextMatrix(X, 0) = 0
       .Cell(flexcpText, X, 2, X, 14) = " "
       .Cell(flexcpText, X, 16, X, 17) = "￥0.00"
       Exit Sub
    End If
    'Hj1 = Val(.Aggregate(flexSTSum, 2, 2, X - 1, 2))
    'Hj2 = Val(.Aggregate(flexSTSum, 2, 16, X - 1, 16))
    'Hj3 = CurrencyToStr(Hj2)
    For i = 2 To X - 2
        Hj1 = Hj1 + Val(.TextMatrix(i, 15))
        If Val(.TextMatrix(i, 16)) > 0 Then
           Hj2 = Hj2 + Val(.TextMatrix(i, 15)) * Val(.TextMatrix(i, 16))
        End If
    Next i
    If Hj2 <= A1 or Hj2 >= A2 Then
      GoTo ErrTransact
    End If
    Hj3 = CurrencyToStr(Hj2)
    .TextMatrix(X, 2) = Hj1
    .Cell(flexcpText, X, 3, X, 15) = IIf(Hj3 = "", " ", Hj3)
    .Cell(flexcpText, X, 16, X, 17) = Format(Hj2, "￥0.00")
    .Cell(flexcpAlignment, X, 2, X, 14) = flexAlignLeftCenter
    '.Select X, 3
    '.CellAlignment = flexAlignLeftCenter
    If SelRow > 1 And SelCol > 0 Then .Select SelRow, SelCol
End With
Exit Sub
ErrTransact:
      MsgBox "你输入的数字过大无法计算！请修改！！！"
End Sub