Hi,
Here's some code I've written which flattens a CellSet into a datatable. I looked for a long time to find similar code but could not find any. Can I please have some thoughts on optimisation/elegance etc... I'm a bit new to .NET so any advice would be great!
Thanks.
Public Function GetFlatData() As DataTable
'If (Me.Cells.Count = 1) Then
' Return New DataTable()
'End If
Dim table As DataTable = New DataTable()
Dim headers As ArrayList = New ArrayList()
For a As Integer = 0 To (Me.Axes.Count - 1)
For i As Integer = 0 To (Me.Axes(a).Set.Hierarchies.Count - 1)
Dim h As Hierarchy = Me.Axes(a).Set.Hierarchies(i)
If (Not h.Name.Contains("Measure")) Then
table.Columns.Add(h.Name)
End If
Next i
Next a
Dim items As ArrayList = New ArrayList()
For x As Int32 = 0 To (Me.Axes(0).Set.Tuples.Count - 1)
Dim tx As Tuple = Me.Axes(0).Set.Tuples(x)
Dim itemBase As CellItem = New CellItem()
For Each mx As Member In tx.Members
If (mx.LevelName.Contains("Measure")) Then
If table.Columns.IndexOf(mx.Caption) = -1 Then
table.Columns.Add(New DataColumn(mx.Caption, GetType(Double)))
End If
itemBase.AddMeasure(mx)
Else
itemBase.AddAttribute(mx)
End If
Next
If (Me.Axes.Count > 1) Then
For y As Int32 = 0 To (Me.Axes(1).Set.Tuples.Count - 1)
Dim item As CellItem = itemBase.Clone(True)
Dim ty As Tuple = Me.Axes(1).Set.Tuples(y)
Dim memy As Member
For Each memy In ty.Members
If (memy.LevelName.Contains("Measure")) Then
item.AddMeasure(memy)
Else
item.AddAttribute(memy)
End If
Next
item.ValueCell = Me.Cells.Item((y * Me.Axes(0).Set.Tuples.Count) + x)
'item.ValueCell = Me.CellGrid(x, y)
items.Add(item)
Next
Else
' for 1 axis cellsets
itemBase.ValueCell = Me.Cells.Item(x)
items.Add(itemBase)
End If
Next
'' might have no measures need to cater
For i As Integer = 0 To (items.Count - 1)
Dim row As DataRow = table.NewRow()
Dim attributes As ArrayList = (CType(items(i), CellItem)).Attributes
For j As Integer = 0 To (attributes.Count - 1)
Dim member As Member = CType(attributes(j), Member)
row(j) = member.Caption
Next
Dim measures As ArrayList = (CType(items(i), CellItem)).Measures
If Not measures Is Nothing Then
' each CellItem should only have one measure
For j As Integer = 0 To (measures.Count - 1)
Dim member As Member = CType(measures(j), Member)
Dim value As Cell = (CType(items(i), CellItem)).ValueCell
If Not (String.IsNullOrEmpty(value.Value)) Then
'need to set appropriate type accroding to cell
'table.Columns[member.Caption].DataType = value.CellProperties.
row(member.Caption) = value.Value
Else
row(member.Caption) = DBNull.Value
End If
Next
End If
table.Rows.Add(row)
Next
Return table
End Function
#End Region
#Region " Private Classes "
Private Class CellItem
Private _measures As ArrayList
Private _attributes As ArrayList
Private _valueCell As Cell
Public Property Attributes() As ArrayList
Get
Return _attributes
End Get
Set(ByVal value As ArrayList)
Me._attributes = value
End Set
End Property
Public Property Measures() As ArrayList
Get
Return _measures
End Get
Set(ByVal value As ArrayList)
Me._measures = value
End Set
End Property
Public Property ValueCell() As Cell
Get
Return _valueCell
End Get
Set(ByVal Value As Cell)
_valueCell = Value
End Set
End Property
Public Sub New()
_attributes = New ArrayList()
_measures = New ArrayList()
End Sub
Public Sub New(ByVal attributes As ArrayList, ByVal measures As ArrayList, ByVal valueCell As Cell)
Me.Attributes = attributes
Me.Measures = measures
Me.ValueCell = valueCell
End Sub
Public Sub AddAttribute(ByVal o As Object)
_attributes.Add(o)
End Sub
Public Sub AddMeasure(ByVal o As Object)
_measures.Add(o)
End Sub
Public Function Clone(ByVal isDeep As Boolean) As Object
If (isDeep) Then
Dim item As CellItem = New CellItem(Me.Attributes.Clone(), Me.Measures.Clone(), Me.ValueCell)
Return item
Else
Return New CellItem(Me.Attributes, Me.Measures, Me.ValueCell)
End If
End Function