複数テーブルの範囲データを統合・分割する方法
下記のようなデータパターンを統合する方法
pattern1(start is same value)
S1■■■■■■■■■■E1
S2■■■■■■■■■■□□□□□□□□□□□E2
pattern2
S1■■■■■■■■■■■■■■■■■■E1
□□□□□□□S2■■■■■■■■■■■■■■■■■■■■■■■E2
pattern3(END is same value)
S1■■■■■■■■■■■■■■■■■■E1
□□□□□□□S2■■■■■■■■■■■E2
pattern4(all same)
S1■■■■■■■■■■■■■■■■■■E1
S2■■■■■■■■■■■■■■■■■■E2
・データのソートを実施することでデータのパターンは4パターンに絞られます。
'【クラス】 Public Class ClsDataMerge ''' <summary> ''' データマージ処理 ''' </summary> ''' <param name="dtTable"></param> Public Function ExecuteDataMerge(ByRef dtTable As DataTable) As Double Dim dblResult As Double = 0 Try Dim idx As Double Dim idxMax As Double = dtTable.Rows.Count - 2 'DELETE DeleteData(dtTable) 'ChangeAccept dtTable.AcceptChanges() 'SORT SortTBL(dtTable) For idx = 0 To idxMax Dim S1 As String Dim E1 As String Dim S2 As String Dim E2 As String Dim S3 As String Dim E3 As String '現在行を取得 S1 = dtTable.Rows(idx).Item("S").ToString() E1 = dtTable.Rows(idx).Item("E").ToString() S2 = dtTable.Rows(idx + 1).Item("S").ToString() E2 = dtTable.Rows(idx + 1).Item("E").ToString() S3 = "" E3 = "" If (S1 <= S2) And (S2 <= E1) Then dblResult = dblResult + 1 ' まったく同じ場合は一個下を削除するのみで良い If S1 = S2 And E1 = E2 Then dtTable.Rows(idx + 1).Item("DEL") = 1 Continue For End If S3 = S2 E3 = E1 E1 = (S2 - 1).ToString("000000") S2 = (E1 + 1).ToString("000000") dtTable.Rows(idx).Item("E") = E1 dtTable.Rows(idx + 1).Item("S") = S2 '新規Row作成 Dim rwNEW As DataRow rwNEW = dtTable.NewRow() rwNEW.Item("S") = S3 rwNEW.Item("E") = E3 ' データカラムがある場合(DATA1,DATA2が影響を受けない) Dim sNewData As String = Convert.ToString(dtTable.Rows(idx).Item("DATA")) Dim sAddData() As String = Convert.ToString(dtTable.Rows(idx + 1).Item("DATA")).Replace(",,", ",").Split(",") If sAddData.Length > 0 Then ' 既に含まれているデータは追加しない For i As Integer = 0 To UBound(sAddData) If String.IsNullOrEmpty(sAddData(i).Trim) Then Continue For If sNewData.Contains(sAddData(i)) = False Then sNewData = sNewData & "," & sAddData(i) End If Next End If rwNEW.Item("DATA") = sNewData 'データの整合性チェック CheckDataValue(dtTable.Rows(idx)) CheckDataValue(dtTable.Rows(idx + 1)) CheckDataValue(rwNEW) 'とりあえず追加してしまう dtTable.Rows.Add(rwNEW) End If Next Catch ex As Exception End Try Return dblResult End Function ''' <summary> ''' 削除フラグを設定 ''' </summary> ''' <param name="rwTarget"></param> Private Sub CheckDataValue(ByRef rwTarget As DataRow) If rwTarget.Item("S").ToString() > rwTarget.Item("E").ToString() Then rwTarget.Item("DEL") = 1 End If End Sub ''' <summary> ''' 実際の削除処理 ''' </summary> ''' <param name="dtTable"></param> Private Sub DeleteData(ByRef dtTable As DataTable) Try For Each rwDEL As DataRow In dtTable.Rows ' 実際に削除 If rwDEL.Item("DEL").ToString() = "1" Then rwDEL.Delete() End If Next dtTable.AcceptChanges() Catch ex As Exception End Try End Sub ''' <summary> ''' 最終処理 ''' </summary> ''' <param name="dtTable"></param> Public Sub DataFinish(ByRef dtTable As DataTable) Try For Each rwItem As DataRow In dtTable.Rows CheckDataValue(rwItem) Next 'DELETE DeleteData(dtTable) 'ChangeAccept dtTable.AcceptChanges() 'SORT SortTBL(dtTable) Catch ex As Exception End Try End Sub ''' <summary> ''' ソート ''' </summary> ''' <param name="dtTable"></param> Private Sub SortTBL(ByRef dtTable As DataTable) Try 'DataTable.Select()を使いソート(第二引数にソート条件を書く) Dim rows As DataRow() = dtTable.Select(Nothing, "S ASC , E ASC").Clone() 'ソート後の DataTable を用意 Dim dtWort As DataTable = dtTable.Clone 'ソートされてる DataRow 配列をソート後の DataTable に追加 For Each row As DataRow In rows dtWort.ImportRow(row) Next dtWort.AcceptChanges() dtTable = dtWort Catch ex As Exception End Try End Sub ''' <summary> ''' データテーブル作成（データ無しテンプレート） ''' </summary> ''' <returns></returns> Public Function CreateTemplateDataTable() As DataTable Dim dtTBL As New DataTable Try dtTBL.Columns.Add("S") dtTBL.Columns.Add("E") dtTBL.Columns.Add("DATA") dtTBL.Columns.Add("DEL") dtTBL.AcceptChanges() Catch ex As Exception End Try Return dtTBL End Function End Class
‘ フォームから呼び出します Public Class Form1 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim cMG As New ClsDataMerge() Try ' テーブル初期化 Dim tblDataTable As New DataTable tblDataTable = cMG.CreateTemplateDataTable() ' データ格納 For Each s As String In Me.RichTextBox1.Lines Dim sItem() As String sItem = s.Split(",") Dim rwNew As DataRow = tblDataTable.NewRow rwNew.Item("S") = sItem(0) rwNew.Item("E") = sItem(1) If sItem.Length > 2 Then For i As Integer = 2 To UBound(sItem) If String.IsNullOrEmpty(sItem(i)) Then Continue For rwNew.Item("DATA") = Convert.ToString(rwNew.Item("DATA")) & "," & sItem(i) Next End If rwNew.Item("DEL") = "0" tblDataTable.Rows.Add(rwNew) Next tblDataTable.AcceptChanges() ' マージ処理 Dim result As Double = 1 While (result = 1) result = cMG.ExecuteDataMerge(tblDataTable) End While ' 最終整理 cMG.DataFinish(tblDataTable) Me.DataGridView1.DataSource = tblDataTable Catch ex As Exception End Try End Sub End Class
