VB.NET：複数テーブルを結合して各列の合計列を新たに作成

Question

前提・実現したいこと

複数のテーブルを結合して各列の数値の合計列を新たに作成したいです。

http://hanatyan.sakura.ne.jp/patio/read.cgi?no=174
を参考にしてテストしています。

実際は何万ものデータのCSVを読み込んで運用します。

発生している問題・エラーメッセージ

該当データがない場合のセルが空白となり、一部の列が計算されません。

空白セルを0として計算させたいのですが方法がわかりません。

現在の結果
HIN CODE1 CODE2 CODEME1 CODEME2 total
100 101 102
110 111 112
300 121 142 221 242 726
310 131 152 231 252 766
200 201 202
210 211 212

希望する結果
HIN　　CODE1 CODE2 CODEME1 CODEME2 total
100 101 102 0 0 203
110 111 112 0 0 223
300 121 142 221 242 726
310 131 152 231 252 766
200 0 0 201 202 403
210 0 0 211 212 423

該当のソースコード

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim table1 As New DataTable()
        table1.Columns.Add("HIN")
        table1.Columns.Add("CODE1", GetType(Decimal))
        table1.Columns.Add("CODE2", GetType(Decimal))
        table1.Rows.Add("100", "101", "102")
        table1.Rows.Add("110", "111", "112")
        table1.Rows.Add("300", "121", "142")
        table1.Rows.Add("310", "131", "152")
        table1.PrimaryKey = New DataColumn() {table1.Columns("HIN")}
        table1.AcceptChanges()

        Dim table2 As New DataTable()
        table2.Columns.Add("HIN")
        table2.Columns.Add("CODEME1", GetType(Decimal))
        table2.Columns.Add("CODEME2", GetType(Decimal))
        table2.Rows.Add("200", "201", "202")
        table2.Rows.Add("210", "211", "212")
        table2.Rows.Add("300", "221", "242")
        table2.Rows.Add("310", "231", "252")
        table2.PrimaryKey = New DataColumn() {table2.Columns("HIN")}
        table2.AcceptChanges()

        '★ Merge メソッドで結合 ★
        Dim table0 As New DataTable()
        table0.Merge(table1)
        table0.Merge(table2)
        table0.Columns.Add("total", GetType(Decimal), "CODE1 + CODE2 + CODEME1 + CODEME2")

        DataGridView1.DataSource = table0
    End Sub
End Class

試したこと

DataGridView1.DefaultCellStyle.NullValue = "0"

とすると空白セルが0になりますが、合計も0になってしまいます。

HIN　　CODE1 CODE2 CODEME1 CODEME2 total
100 101 102 0 0 0
110 111 112 0 0 0
300 121 142 221 242 726
310 131 152 231 252 766
200 0 0 201 202 0
210 0 0 211 212 0

補足情報（FW/ツールのバージョンなど）

OS Windows10
VB.NET

Microsoft Visual Studio Community 2017
Version 15.6.7
VisualStudio.15.Release/15.6.7+27428.2043
Microsoft .NET Framework
Version 4.8.04084

Comments

[退会済みユーザー]

> 実際は何万ものデータのCSVを読み込んで運用します。

それは今回の話とどういう関係があるのですか？

何万件もあるから、DataTable に取得して Merge するのに無理があるとか言う話になったりするのでしょうか？

[Kouta2020]

SurferOnwww様コメントありがとうございます。

プログラムの方法によってデータ数が多いと処理時間がとてもかかってしまい実用に耐えられないと思ったので、そのような記載に至りました。

提示したソースコードで何万ものデータの処理は許容範囲の時間で処理できるのですが、
合計を計算する処理の方法によってはとても時間がかかってしまうので、現在処理時間の短い方法を模索しています。

質問後に試したことですが、
提示したソースコード中の
table0.Columns.Add("total", GetType(Decimal), "CODE1 + CODE2 + CODEME1 + CODEME2")
を
table0.Columns.Add("total", GetType(Decimal))
に変更して

Dim CODE11 As Decimal
Dim CODE22 As Decimal
Dim CODEME11 As Decimal
Dim CODEME22 As Decimal

For i As Integer = 0 To table0.Rows.Count - 1
If IsNumeric(DataGridView1.Rows(i).Cells(1).Value) = True Then
CODE11 = DataGridView1.Rows(i).Cells(1).Value
Else
CODE11 = 0
End If

If IsNumeric(DataGridView1.Rows(i).Cells(2).Value) = True Then
CODE22 = DataGridView1.Rows(i).Cells(2).Value
Else
CODE22 = 0
End If

If IsNumeric(DataGridView1.Rows(i).Cells(3).Value) = True Then
CODEME11 = DataGridView1.Rows(i).Cells(3).Value
Else
CODEME11 = 0
End If

If IsNumeric(DataGridView1.Rows(i).Cells(4).Value) = True Then
CODEME22 = DataGridView1.Rows(i).Cells(4).Value
Else
CODEME22 = 0
End If

DataGridView1.Rows(i).Cells(5).Value = CODE11 + CODE22 + CODEME11 + CODEME22
Next

を追記したところ望み通りの結果が得られました。

しかし実際の何万ものデータで試したところ処理時間が10倍ぐらいに増加してしまい、実用しにくいことになってしまいました。

[退会済みユーザー]

そこがボトルネックになっているのかは分かりませんが、DataGridView から値を取得しているところが変ですね。DataGridView のデータソースが Merge した DataTable なら DataTable から値を取得した方が早そうな気がするのですが(気がするだけで確証はないです)。

とにかく、最初の質問を、上のコメントのことを追加して書き直し、コメントの方法では遅いことを解決するにはどうしたらよいかという質問にしてはいかがですか。その際、ボトルネックがどこにありそうか質問者さんの方で調べて質問に書いてください。ボトルネックを調べることが出来るのは質問者さんだけなのですから。

Answer 1

質問のコメント欄に、

そこがボトルネックになっているのかは分かりませんが、DataGridView から値を取得しているところが変ですね。DataGridView のデータソースが Merge した DataTable なら DataTable から値を取得した方が早そうな気がするのですが(気がするだけで確証はないです)。

と書いたので、Merge した DataTable に total 列を追加し、DataTable から値を取得して合計して結果を total 列に書き込むサンプルを書いておきます。それで早くなるのかどうかは分かりません。質問者さんの方で検証してください。

CSV ファイルのデータを DataTable に取り込む際、ADO.NET + OleDb + JET (ACE も使用可) を使い、CSV ファイルの各列の型を Schema.ini ファイルを使用して指定してやれば、自動的に情報を取得して各列の型が指定された DataTable を作ってくれます。詳しくは以下の記事を見てください。

CSV ファイルを DataGridView に表示
http://surferonwww.info/BlogEngine/post/2020/09/11/show-date-in-csv-file-on-datagridview.aspx

サンプルコード (C# です。C# は分からないということでしたら変換サービスを使ってください）：

using System;
using System.Data;
using System.Windows.Forms;

namespace WinFormsApp1
{
    public partial class Form13 : Form
    {
        public Form13()
        {
            InitializeComponent();

            this.dataGridView1.DataSource = this.bindingSource1;
        }

        private void Form13_Load(object sender, EventArgs e)
        {
            DataTable table = new DataTable();
            table.Merge(CreateDataTable1());
            table.Merge(CreateDataTable2());
            table.Columns.Add("total", typeof(decimal));

            foreach (DataRow row in table.Rows)
            {
                decimal total = 0m;
                foreach (object value in row.ItemArray)
                {
                    if (value is decimal)
                    {
                        total += (decimal)value;
                    }
                }
                row["total"] = total;
            }

            this.bindingSource1.DataSource = table;
        }

        protected DataTable CreateDataTable1()
        {
            DataTable table = new DataTable();
            DataColumn idColumn = new DataColumn("HIN", typeof(string));
            table.Columns.Add(idColumn);
            table.Columns.Add(new DataColumn("CODE1", typeof(decimal)));
            table.Columns.Add(new DataColumn("CODE2", typeof(decimal)));
            table.Rows.Add("100", 101m, 102m);
            table.Rows.Add("110", 111m, 112m);
            table.Rows.Add("300", 121m, 142m);
            table.Rows.Add("310", 131m, 152m);
            table.PrimaryKey = new DataColumn[] { idColumn };
            return table;
        }

        protected DataTable CreateDataTable2()
        {
            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("HIN", typeof(string)));
            table.Columns.Add(new DataColumn("CODEME1", typeof(decimal)));
            table.Columns.Add(new DataColumn("CODEME2", typeof(decimal)));
            table.Rows.Add("200", 201m, 202m);
            table.Rows.Add("210", 211m, 212m);
            table.Rows.Add("300", 221m, 242m);
            table.Rows.Add("310", 231m, 252m);
            return table;
        }        
    }
}

結果：

【訂正＆追記】

最初にアップしてたコード中の row["total"] = total; の位置が不適切だったので直しました。結果は同じですが余計な回数代入を行ってました。

基本的に質問者さんの「該当のソースコード」と違うのは、そこにあった、

table0.Columns.Add("total", GetType(Decimal), "CODE1 + CODE2 + CODEME1 + CODEME2")

を、

table.Columns.Add("total", typeof(decimal));

foreach (DataRow row in table.Rows)
{
    decimal total = 0m;
    foreach (object value in row.ItemArray)
    {
        if (value is decimal)
        {
            total += (decimal)value;
        }                   
    }
    row["total"] = total;
}

にしたという点のみです。「該当のソースコード」で許容時間で完了できるなら、私のコードでも許容時間内に入るのではないかと思いますが、試してみていかがだったでしょう？

Comments

[Kouta2020]

SurferOnWww様たくさんのアドバイスありがとうございます。

CSV ファイルのデータを DataTable に取り込む方法も紹介して頂き、とても参考になりました。
まだ試せていないのですが、今後取り入れたいと思います。

本題のプログラムですが、
table0.Columns.Add("total", GetType(Decimal), "CODE1 + CODE2 + CODEME1 + CODEME2")
の部分を下記コードに書き換えて再現できました。

table.Columns.Add("total", GetType(Decimal))

For Each row As DataRow In table.Rows
Dim total As Decimal = 0D

For Each value As Object In row.ItemArray

If TypeOf value Is Decimal Then
total += CDec(value)
End If
Next

row("total") = total
Next

実際の何万ものデータでも許容時間でした。

参考までにお伺いしたいのですが
サンプルデータではtotal列の合計式を”CODE1 + CODE2 + CODEME1 + CODEME2”としましたが
もしtotal列の合計式を”CODE1 + CODEME1"としたい場合は
CODE2列とCODEME2列をString型にするしかないでしょうか？

[退会済みユーザー]

> もしtotal列の合計式を”CODE1 + CODEME1"としたい場合は
CODE2列とCODEME2列をString型にするしかないでしょうか？

そんな必要はありません。row("total") = total の左辺で使っている ("total") は何か考えて。

[Kouta2020]

SurferOnWww様、参考までの質問に回答頂きありがとうございます。

If TypeOf value Is Decimal Then
total += CDec(value)
End If

上記プログラムでデータテーブルの値がDecimal型なら数値を足していくので、除外する列はDecimal以外の型にするのかと思った次第です。

でも新たに別の列だけを足したいときに支障がでますね。
例えばCODE1とCODEME1の合計、CODE2とCODEME2の合計を別々に計算したい場合です。
イメージとしては
table0.Columns.Add("total1", GetType(Decimal), "CODE1 + CODEME1")
table0.Columns.Add("total2", GetType(Decimal), "CODE2 + CODEME2")
のような感じです。

("total")はデータテーブルのtotal列を指定しているという認識で合っていますでしょうか。

[退会済みユーザー]

> ("total")はデータテーブルのtotal列を指定しているという認識で合っていますでしょうか。

DataRow.Item プロパティを調べてください。それを使って任意の列のデータを取得できます。

[Kouta2020]

試しに
MsgBox(table.Rows(2).Item("CODE1") + table.Rows(2).Item("CODEME1"))
としたところ、3行目のCODE1+CODEME1の合計が表示されました。

これをどのように応用したらよいかで止まっております。

[退会済みユーザー]

質問があるなら新たにスレッドを立てて質問してください。

[Kouta2020]

新たにスレッド立てさせていただきます。ありがとうございました。

Answer 2

DataGridViewに設定する前に、DataTableのDBNullを事前に0に置換しておいたらどうでしょう？

    For Each row As DataRow In table0.Rows
        For Each col As DataColumn In table0.Columns
            If col.DataType = GetType(Decimal) AndAlso IsDBNull(row(col.Ordinal)) Then
                row(col.Ordinal) = 0
            End If
        Next
    Next

Comments

[Kouta2020]

radian様回答ありがとうございます。

ご提示いただいたプログラムを
table0.Columns.Add("total", GetType(Decimal), "CODE1 + CODE2 + CODEME1 + CODEME2")
の前に記述することで希望する結果となりました。
何万ものデータでも許容時間でした。
大変助かりました。