我将测试程序中读取文件,设置格式,复制格式,数据绑定这部分代码集中处理了一下放在下面。
- With fpsp
- ' load file
- .LoadFromFile("SampleSheet.ss7")
- .BlockMode = True
- .MaxCols = 113
- .MaxRows = 2996
- .Col = 1
- .Row = 2996
- .Col2 = 113
- .Row2 = 2996
- ' VB6InterFace是项目中针对VB6的旧版Spread制作的接口。 属性名与旧版Spread一致,内容通过Spread7实现。
- .CellType = VB6InterFace.CellTypeConstants.CellTypeNumber `是列举型,根据 Typeof Cell.CellType is 新版CellType做内部分离
- .TypeTextOrient = VB6InterFace.TypeTextOrientConstants.TypeTextOrientHorizontal
- .TypeNumberShowSep = True
- .TypeNegRed = True
- .TypeNumberDecPlaces = 0
- .TypeNumberSeparator = ","
- For i As Integer = 1 To 113
- .Col = i
- .Row = 2996
- .Col2 = i
- .Row2 = 2996
-
- Select Case i
- Case 1, 2, 3, 4, 5, 6, 7
- .CellType = VB6InterFace.CellTypeConstants.CellTypeEdit
- .TypeTextOrient = VB6InterFace.TypeTextOrientConstants.TypeTextOrientHorizontal
- .TypeNegRed = True
- .TypePercentDecPlaces = 1
- Case 11, 16, 21, 25, 29, 33, 37, 41, 45, 50, 55, 60, 65, 70, 75
- .CellType = VB6InterFace.CellTypeConstants.CellTypePercent
- .TypeTextOrient = VB6InterFace.TypeTextOrientConstants.TypeTextOrientHorizontal
- .TypeNegRed = True
- .TypePercentDecPlaces = 1
- End Select
- Next i
- .BlockMode = False
- .Row = 1
- .Col = 1
- ` 将定义好的类型复制到第一行开始的位置。数量为取得Data的件数
- For i As Integer = 1 To 81
- .CopyRowRange(2996, 2996, i)
- Next
- 'End If
- .ActiveSheet.DataAutoCellTypes = False
- Dim strBf1 As String = GetSqlStr()
- Dim cn As New xxxAdodc.Connection
- cn.Open("Provider=SQLOLEDB.1;Data Source=database1;Persist Security Info=True;Password=1234;User ID=5678;Initial Catalog=testDb")
- `取得数据并保存在Adodc1中
- Dim da1 As OleDb.OleDbDataAdapter
- Dim ds1 As DataSet = New DataSet("Bs1")
- da1 = New OleDb.OleDbDataAdapter(strBf1, cn.conObj)
- da1.Fill(ds1, "Bs1")
- Me.Adodc1.DataSource = ds1
- Me.Adodc1.DataMember = "Bs1"
- `区域绑定
- Dim data As FarPoint.Win.Spread.Data.SpreadDataBindingAdapter = New FarPoint.Win.Spread.Data.SpreadDataBindingAdapter()
- Dim ds As DataSet = Adodc1.DataSource
- data.DataSource = ds.Tables(0)
- data.Spread = fpsp
- data.SheetName = fpsp.ActiveSheet.SheetName
- data.DataAutoCellTypes = False
- data.MapperInfo = New FarPoint.Win.Spread.Data.MapperInfo(0, 0, ds.Tables(0).Rows.Count - 1, ds.Tables(0).Columns.Count - 1)
- data.FillSpreadDataByDataSource() ' 执行相对很慢,大约5~10秒, 在实际项目中,这个部分则是55秒
- End With
复制代码 |