前几天一朋友问我C1FlexGrid中如何实现Excel一样计算的功能,想了一下,觉得应该可以实现,于是做了个小例子。
运行结果基本上还算可以,只是中间有些地方还需要完善,也有一些没有解决掉的问题。如果有什么更好的办法,请大家指教。
程序打包如下: FlexGridSample.rar
需要环境: Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600)
.NET版本:Microsoft .NET Framework Version 2.0.50727
C1FlexGrid版本:Version 2.6.20071.324
包括一下几个类:
Calculate.vb 运算的分类,加减乘除什么的,需要其他运算的话自行追加
' '' 运算分类
' '' </summary>
' '' <remarks></remarks>
Public Enum Calculate Enum Calculate As Integer
ADDITION = 1
SUBTRACTION = 2
MULTIPLICATION = 3
DIVISION = 4
End Enum
CalculateType.vb 运算的类型,区分根据单元格加上固定值还是单元格加上单元格之类的分类,例子中只是简单实现了单元格与固定值的运算,单元格以及行列之间的加减需要自行添加
' '' 计算类型
' '' </summary>
' '' <remarks></remarks>
Public Enum CalculateType Enum CalculateType As Integer
CELL_BY_VALUE = 1
ROW_BY_VALUE = 2
COLUMN_BY_VALUE = 3
CELL_BY_CELL = 4
SUM_CELLS = 5
OTHER = 6
''此处需要追加
End Enum
CalculateInfo.vb 运算规则信息,包括运算的分类,类型,以及所运算需要的行列值和固定值,当前只是实现了简单的加减运算,复杂的运算应该在该类中追加一个ArrayList来记录所需要的单元格信息
' '' 计算规则信息
' '' </summary>
' '' <remarks></remarks>
Public Class CalculateInfo Class CalculateInfo
Private _calType As CalculateType
Private _x As Integer
Private _y As Integer
Private _xRef As Integer
Private _yRef As Integer
Private _cal As Calculate
Private _value As Integer
Property calType()Property calType() As CalculateType
Get
Return _calType
End Get
Set(ByVal value As CalculateType)
_calType = value
End Set
End Property
Property x()Property x() As Integer
Get
Return _x
End Get
Set(ByVal value As Integer)
_x = value
End Set
End Property
Property y()Property y() As Integer
Get
Return _y
End Get
Set(ByVal value As Integer)
_y = value
End Set
End Property
Property xRef()Property xRef() As Integer
Get
Return _xRef
End Get
Set(ByVal value As Integer)
_xRef = value
End Set
End Property
Property yRef()Property yRef() As Integer
Get
Return _yRef
End Get
Set(ByVal value As Integer)
_yRef = value
End Set
End Property
Property cal()Property cal() As Calculate
Get
Return _cal
End Get
Set(ByVal value As Calculate)
_cal = value
End Set
End Property
Property value()Property value() As Integer
Get
Return _value
End Get
Set(ByVal value As Integer)
_value = value
End Set
End Property
End Class
CalculateUtil.vb 根据运算分类计算结果,当前只实现了Integer型的加减乘除,需要完善
Public Shared Function Compute()Function Compute(ByVal cellValue As Integer, ByVal value As Integer, ByVal cal As Calculate) As Integer
Select Case cal
Case Calculate.ADDITION
Return cellValue + value
Case Calculate.SUBTRACTION
Return cellValue - value
Case Calculate.MULTIPLICATION
Return cellValue * value
Case Calculate.DIVISION
If value = 0 Then
Return 0
Else
Return cellValue value
End If
End Select
End Function
End Class
FlexGridAsExcel.vb 主程序,随机生成数据,添加计算规则
开始用的是CellChanged事件,结果发现如果行列计算有交叉的时候会出现死循环,因为行列变化之后再次调用了CellChanged事件,现在用AfterEdit事件。
计算规则的添加顺序表示计算的优先级,这也引出了一个问题,在通过其他单元格计算过来的单元格不能改变值,因为现在的计算方法是按照计算规则的顺序进行计算的,比如说添加计算规则
(1)第二行第二列的值 = 第一行第二列的值 +10
(2)第三行第二列的值 = 第二行第二列的值 +10
这样的话在手动改变第二行第二列值的时候,程序还是会根据计算规则的顺序先把(1)执行了,这样的话修改后的值就无法保存上,必须修改第一行第二列的值才能反映到第二行第二列上。现在的想法是判断修改的是哪一个单元格,再根据行列值将修改过得值设置在C1FlexGrid上,但是在什么地方修改不好判断,因为计算规则不是固定的,不知道应该在哪一个规则之后添加才好。
Imports C1.Win.C1FlexGrid
Imports System.Drawing
Public Class FlexGridAsExcel Class FlexGridAsExcel
Private dt As DataTable = Nothing ''全部DataTable
Private list As ArrayList = New ArrayList ''计算规则集合
Private Sub Button1_Click()Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
dt = GetData()
Me.C1FlexGrid1.DataSource = dt
Me.C1FlexGrid1.AllowResizing = C1.Win.C1FlexGrid.AllowResizingEnum.None
C1FlexGrid1.Rows(0).Height = 30 ''
C1FlexGrid1.Rows(C1FlexGrid1.Rows.Count - 1).AllowEditing = False
For i As Int32 = 1 To C1FlexGrid1.Rows.Count - 1
''行号
C1FlexGrid1(i, 0) = i.ToString
Next
' 添加计算规则
SetCalculateRules()
Me.C1FlexGrid1.Enabled = True
End Sub
Public Function GetData()Function GetData() As DataTable
Dim dt As DataTable
Dim dr As DataRow
dt = New DataTable("TEST")
dt.Columns.Add(New DataColumn("号码", GetType(String)))
dt.Columns.Add(New DataColumn("数量1", GetType(Integer)))
dt.Columns.Add(New DataColumn("数量2", GetType(Integer)))
dt.Columns.Add(New DataColumn("数量3", GetType(Integer)))
dt.Columns.Add(New DataColumn("数量4", GetType(Integer)))
dt.Columns.Add(New DataColumn("数量5", GetType(Integer)))
Dim rdm As Random = New Random
For i As Integer = 10 To 20
dr = dt.NewRow()
dr(0) = "00" & i.ToString
For j As Integer = 1 To dt.Columns.Count - 1
dr(j) = rdm.Next(10, 20)
Next
dt.Rows.Add(dr)
Next
Return dt
End Function
Private Sub Button3_Click()Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.Close()
End Sub
Private Sub Button2_Click()Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If dt Is Nothing Then
MessageBox.Show("没有数据能导出。", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
Me.C1FlexGrid1.SaveExcel("D: emp.xls", "Data", FileFlags.IncludeFixedCells)
MessageBox.Show("Excel导出成功。", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub
''' <summary>
''' 单元格编辑后根据计算规则计算
''' 需要考虑传的值是否合法以及计算行列中所有的数据类型,此例中全为Integer型
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub C1FlexGrid1_AfterEdit()Sub C1FlexGrid1_AfterEdit(ByVal sender As Object, ByVal e As C1.Win.C1FlexGrid.RowColEventArgs) Handles C1FlexGrid1.AfterEdit
Try
''循环计算规则
For Each calInfo As CalculateInfo In list
Select Case calInfo.calType
Case CalculateType.CELL_BY_VALUE
Debug.WriteLine("CELL_BY_VALUE")
Me.C1FlexGrid1.Rows(calInfo.x).Item(calInfo.y) = CalculateUtil.Compute(Me.C1FlexGrid1.Rows(calInfo.xRef).Item(calInfo.yRef), calInfo.value, calInfo.cal)
Case CalculateType.COLUMN_BY_VALUE
Debug.WriteLine("COLUMN_BY_VALUE")
For i As Integer = 1 To Me.C1FlexGrid1.Rows.Count - 1
''计算列和参照列不是号码列
If Me.C1FlexGrid1.Cols(calInfo.yRef).Caption <> "号码" And Me.C1FlexGrid1.Cols(calInfo.y).Caption <> "号码" Then
Me.C1FlexGrid1.Rows(i).Item(calInfo.y) = CalculateUtil.Compute(Me.C1FlexGrid1.Rows(i).Item(calInfo.yRef), calInfo.value, calInfo.cal)
End If
Next
Case CalculateType.ROW_BY_VALUE
Debug.WriteLine("ROW_BY_VALUE")
For i As Integer = 1 To Me.C1FlexGrid1.Cols.Count - 1
''计算列和参照列不是号码列
If Me.C1FlexGrid1.Cols(i).Caption <> "号码" Then
Me.C1FlexGrid1.Rows(calInfo.x).Item(i) = CalculateUtil.Compute(Me.C1FlexGrid1.Rows(calInfo.xRef).Item(i), calInfo.value, calInfo.cal)
End If
Next
Case CalculateType.SUM_CELLS
Debug.WriteLine("SUM_CELLS")
''TODO 追加
Case CalculateType.OTHER
Debug.WriteLine("OTHER")
End Select
Next
Catch ex As Exception
End Try
End Sub
''' <summary>
''' 计算规则
''' </summary>
''' <remarks></remarks>
Private Sub SetCalculateRules()Sub SetCalculateRules()
''第二行第二个单元格值 = 第一行第二个单元格 + 5
SubCalculate(2, 2, 1, 2, Calculate.ADDITION, CalculateType.CELL_BY_VALUE, 5)
''第三行的值 = 对应第一行的值 + 10(整行计算的时候列值不需要,设为任意值都可以)
SubCalculate(3, 0, 1, 0, Calculate.ADDITION, CalculateType.ROW_BY_VALUE, 10)
''第三列的值 = 对应第二列的值 * 10(整列计算的时候行值不需要,设为任意值都可以)
SubCalculate(0, 3, 0, 2, Calculate.MULTIPLICATION, CalculateType.COLUMN_BY_VALUE, 10)
End Sub
''' <summary>
'''
''' 计算内容
''' </summary>
''' <param name="x">计算单元格行号</param>
''' <param name="y">计算单元格列号</param>
''' <param name="xObj">参照单元格行号</param>
''' <param name="yObj">参照单元格列号</param>
''' <param name="cal">计算类型(加减乘除)</param>
''' <param name="calType">计算分类(根据行列单元格)</param>
''' <param name="value">计算固定值</param>
''' <remarks></remarks>
Private Sub SubCalculate()Sub SubCalculate(ByVal x As Integer, ByVal y As Integer, ByVal xObj As Integer, ByVal yObj As Integer, ByVal cal As Calculate, ByVal calType As CalculateType, ByVal value As Integer)
Dim calInfo As New CalculateInfo
calInfo.x = x
calInfo.y = y
calInfo.xRef = xObj
calInfo.yRef = yObj
calInfo.cal = cal
calInfo.calType = calType
calInfo.value = value
list.Add(calInfo)
End Sub
End Class