fpspread 葵花宝典.doc

fpvaspread 详解With vaspread .DeleteRows 1, 1 -删除第一列第一行数据 .Col = -1: .Row = -1: .Lock = False -设置vaspread为解锁状态 .AllowUserFormulas = False 不允许格式化 .MaxCols = 67 58 设置最大列数 .MaxRows = 300 设置显示行的最大数 .ColHeaderRows = 1 2 将头换成1行 .UserColAction = UserColActionSort 表头是否排序 .AddCellSpan 1, SpreadHeader, 17, 1 16 change 17 设置显示标题栏格式 .Col = 1: .Row = SpreadHeader: .Text = 基本信息 设置列名 .Col = COL_ID: .Row = SpreadHeader: .Text = 序号 SpreadHeader + 1 控制显示的行 SpreadHeader + 1 DateField 为绑定的数据表字段 colhidden是否为隐藏 Colwidth 设置显示的列的宽度 .Col = COL_FID: .Row = SpreadHeader: .DataField = FID: .Text = 表体唯一号: .ColHidden = True ：。ColWidth（1）=12 SpreadHeader + 1 格式化列表 .CellType = CellTypeDate 日期.Col = FhwBoolIn(2, FStartDate): .CellType = CellTypeDate: .TypeDateFormat = TypeDateFormatYYMMDD 设置数字 .CellType = CellTypeNumber .Col = FhwBoolIn(2, FThickness): .CellType = CellTypeNumber: .TypeNumberDecPlaces = 2: .TypeNumberShowSep = True: .TypeHAlign = TypeHAlignRight 设置文本型.CellType = CellTypeEdit: .Col = FhwBoolIn(2, FDescriptionCust): .TypeEditMultiLine = True: .CellType = CellTypeEdit: .AutoSize = True自动换行换列Private Sub EntrerCtrlCol(ByVal KeyCode As Long) Enter 换行 With vaspread If KeyCode = 13 Then 获取回车事件 If .MaxCols .ActiveCol Then 。如果当前列不等于最大列，则换行否则换列 .SetActiveCell .ActiveCol + 1, .ActiveRow .SetFocus Else .SetActiveCell 1, .ActiveRow + 1 .SetFocus End If End If End WithEnd Sub获取值Dim pvalue as variantvaspread.GetText vaspread.ActiveCol, vaspread.ActiveRow,pvalue pvalue 为获取的值vaspread.SetText vaspread.ActiveCol, vaspread.ActiveRow,pvalue pvalue需要给赋予的值设置行底色一级边框颜色 第一行设置背景颜色，第二行设置边框颜色With vasperad.Col = -1: .Row = Row: .BackColor = &HC0C0C0: .BackColorStyle = BackColorStyleUnderGrid.Col = -1: .Row = Row: .BorderStyle = BorderStyleFixedSingle: .BackColor = &H80000018End with复制行With vaspread .InsertRows vaspread.ActiveRow + 1, 1 For i = 0 To .MaxCols .Col = i .GetText i, .ActiveRow, Temp If i = FhwBoolIn(2, FDescriptionYard) Or i = FhwBoolIn(2, FDescriptionCust) Then Temp = Temp & .SetText i, .ActiveRow + 1, Temp vaspread.ColWidth(FhwBoolIn(2, FDescriptionCust) = 20 vaspread.ColWidth(FhwBoolIn(2, FDescriptionYard) = 20 iHeight = Len(Temp) / 13 If Right(FormatNumber(iHeight), 1) 5 Then iHeightTemp = Round(iHeight, 0) Else iHeightTemp = Round(iHeight, 0) + 1 End If vaspread.RowHeight(.ActiveRow + 1) = iHeightTemp * 12 Else .SetText i, .ActiveRow + 1, Temp End If Next i End With插入行删除行 With vaspread If i = 1 Then .InsertRows .ActiveRow + 1, 1 ElseIf i = 2 Then .GetText COL_FID, .ActiveRow, fid .DeleteRows .ActiveRow, 1 If fid Then DateMoving (fid) End If End If End With1设置滚动条有时候需要设置滚动条不可见就可以通过这里进行设置。设置序号15.修改数据 这儿需要定义两个全局变量Dim dctFront as KFO.DictionaryDim vctFront as KFO. VectorIf fid Then Set dctFront = New KFO.Dictionary dctFront(fid) = fid dctFront(filename) = GetFieldName(vaspread.ActiveCol) If .ActiveCol = FhwBoolIn(2, FWhetherAssessment) Or .ActiveCol = FhwBoolIn(2, FBusinessConfirmState) Or .ActiveRow = FhwBoolIn(2, FSettlementState) Or .ActiveCol = FhwBoolIn(2, FSignTheBillState) Then If Trim(Temp) = 是 Then dctFront(value) = 1 ElseIf Trim(Temp) = Or Trim(Temp) 是 Then dctFront(value) = 0 End If Else dctFront(value) = Temp End If vctFront.Add dctFront Set dctFront = Nothing定义需要拿到的字段名称 Private Function GetFieldName(ByVal i As Integer) As String If i = SpreadCol.COL_ID Then GetFieldName = Fid ElseIf i = SpreadCol.FhwBoolIn(2, FMaterialsType)FIndex Then GetFieldName = FIndex ElseIf i = FhwBoolIn(2, FSubcontractorName) Then GetFieldName = FSubcontractorNameendif End subI 为需要修改的列数16.进行修改数据Set dctFront = New KFO.Dictionary If vctFront.UBound 0 Then For i = vctFront.LBound To vctFront.UBound Set dctFront = vctFront.Item(i) If dctFront(filename) Then Ssql = update t_quota_entry_table set Ssql = Ssql & vbCrLf & dctFront(filename) & = & Replace(dctFront(value), , ) & Ssql = Ssql & vbCrLf & where fid = & dctFront(fid) & Set obill = CreateObject(BillDataAccess.GetData) obill.ExecuteSQL DEMMTS.PropsString, Ssql ModifyFlag = True End If Next i Set dctFront = Nothing Set vctFront