DataGridView 列ヘッダー セルのドロップダウン フィルタ一覧を作成する

ExcelのフィルターのようなことをDataGridViewでも同じ操作を実現できたら・・・
とだれしもが思いますよ。


そして、ネットで調べりゃすぐにこんなサイトにたどり着きます。

DataGridView 列ヘッダー セルのドロップダウン フィルタ一覧を作成する



なんだ、簡単にできるんだな。

さっそくダウンロード
Building a Drop-Down Filter List for a DataGridView Column Header Cell Sample Code
https://www.microsoft.com/en-us/download/details.aspx?id=23459


解凍し、DataGridViewAutoFilterを動かしてみます。
DataGridViewAutoFilter\DataGridViewAutoFilter\CS\DataGridViewAutoFilter￥DataGridViewAutoFilter.sin
中に DesignerSetupDemoがあるのでこちらを動かしてみます。







一番左のフィルターを使ってみます。


左から二番目のフィルターを使ってみます。


最後に Show Allのリンクをクリックをするとフィルターが解除されます。


サンプルで使われているデータは解凍したフォルダの
DataGridViewAutoFilter\DataGridViewAutoFilter\TestData.xml
をつかっているので、これを使いながらそれっぽいものを作ってみます。


いきなり、Filterの話をするのではなく、まずはxmlデータをDataGridViewに表示するところを行います。
フォームDataGridViewを配置し親ドッキングします。



今回は、 データセットを作りデータセットのReadXMLでデータをDataGridViewに流し込みます。
ファイルの追加でデータセットを選択

名前はそのままでも問題ないです。


DataSet1.xsdを選択し、データセットを表示しツールボックスのDataTableをドラッグ＆ドロップします



XMLの繰り返し部分であるCustomerOrderDataをDataTable名に
書くタグをプロパティ名として追加します。


↓



あと、DataSetの画面で背景部分のしましまのとこをクリックすると
DataSetのプロパティが表示されます。

Namespaceに http://tempuri.org/DataSet1.xsd
って記載されてるので、削除しておきましょ。


　→　




では、読み込みます

private void Form1_Load(object sender, EventArgs e)
        {
            this.dataSet1.ReadXml("TestData.xml");
        }

データが表示できたのでようやく本題のフィルタの追加をします。
まずは、DataGridViewAutoFilter.dllを追加します。
そして、using DataGridViewAutoFilter; を追加します

今度は、DataGridViewの列の編集画面を開きます
ColumnTypeを選択するとDataGridViewAutoFilterTextBoxColumnってのが選べます
ここでは、すべての列にフィルターを表示したいので選択します。

そして、実行。


できた。


ついでに、DEMOのアプリみたくクリアボタンを追加してみます。
statusStripを追加。
その上に、linkLabelを追加。

private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            DataGridViewAutoFilterColumnHeaderCell.RemoveFilter(this.dataGridView1);
        }

DataGridViewAutoFilterColumnHeaderCellクラスのクラスメソッド(RemoveFilter)の
引数にDataGridViewAutoFilterを使っているDataGridViewのインスタンスを渡せばよいってことですね。


ちなみに、Filterを使用していないのに最初からAllFilterClearのリンクがでてるのもあれです。
そこで、DataGridViewを何かしらの操作を行い、その結果をBindして完了したタイミングで
Filterの状態を調べFilterを使用していなければ linkLabelの表示を消してしまうってのが考えられます。

private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            string filterStatus = DataGridViewAutoFilterColumnHeaderCell.GetFilterStatus(this.dataGridView1);

            if (string.IsNullOrEmpty(filterStatus))
            {
                this.linkLabel1.Visible = false;
            }
            else
            {
                this.linkLabel1.Visible = true;
            }
        }

またもや、DataGridViewAutoFilterColumnHeaderCellクラスのクラスメソッドを利用します。
今度は、GetFilterStatusというメソッドです。引数はもちろん、DataGridViewのインスタンス。

これでステータスがとれます。
フィルターが未選択だと空文字が返ってきます。

選択した後だと"12 of 2155 records found"のような文字列が返ってきます

SQLiteでDataTableを使ってDataGridViewに表示する

■準備

System.Data.SQLite.dllを追加します。




usingを追加します。

using System.Data.SQLite;

適当なデータを用意します。
今回は、都道府県と県庁データとしときます。
テーブルはこんな感じ。

CREATE TABLE [sample] (
  [pref] TEXT,
  [capital] TEXT,
  PRIMARY KEY(pref)
);

フォームにDataGridViewを配置します。
データを読み込みます。

private void Form1_Load(object sender, EventArgs e)
        {
            string dbPath = Application.StartupPath + @"\Data.db";
            using (SQLiteConnection con = new SQLiteConnection("Data Source=" + dbPath))
            {
                //空のテーブルを作ります。
                //この時点では、DataGridViewと紐づいていません。
                DataTable dataTable = new DataTable();

                //DataTableに読み込むデータをSQLで指定します。
                //今回はDataTableを指定していないので、SELECTで表示する列名が
                //のちのち紐づけを行った際のDataGridViewの列名になります。
                SQLiteDataAdapter adapter = new SQLiteDataAdapter("SELECT * FROM sample;", con);
                adapter.Fill(dataTable);

                //データテーブルをDataGridViewに紐づけます。
                this.dataGridView1.DataSource = dataTable;
            }
        }

■表示をクリアする
DataTableとDataGridViewをbindingしていると this.DataGridView1.Rows.Clear() ではクリアできません。
DataGridViewはあくまでもDataTableの内容をただ表示しているだけなので、DataTable自身をクリアしないといけません。

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

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        private DataTable datatable { get; set; }

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string dbPath = Application.StartupPath + @"\Data.db";
            using (SQLiteConnection con = new SQLiteConnection("Data Source=" + dbPath))
            {
                con.Open();
                
                //空のテーブルを作ります。
                //この時点では、DataGridViewと紐づいていません。
                this.datatable = new DataTable();

                //DataTableに読み込むデータをSQLで指定します。
                //今回はDataTableを指定していないので、SELECTで表示する列名が
                //のちのち紐づけを行った際のDataGridViewの列名になります。
                SQLiteDataAdapter adapter = new SQLiteDataAdapter("SELECT * FROM sample;", con);
                adapter.Fill(this.datatable);

                //データテーブルをDataGridViewに紐づけます。
                this.dataGridView1.DataSource = this.datatable;
            }
        }

        private void BtnClear_Click(object sender, EventArgs e)
        {
            this.datatable.Clear();
        }
    }
}

表示を消したなら、再表示したいとこですね。
Readボタンを追加。

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

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        private DataTable datatable { get; set; }
        private SQLiteConnection con { get; set; }
        private SQLiteDataAdapter adapter { get; set; }

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string dbPath = Application.StartupPath + @"\Data.db";
            this.con = new SQLiteConnection("Data Source=" + dbPath);
            this.con.Open();
            
            //空のテーブルを作ります。
            //この時点では、DataGridViewと紐づいていません。
            this.datatable = new DataTable();

            //データテーブルをDataGridViewに紐づけます。
            this.dataGridView1.DataSource = this.datatable;

            //データを読み込みます
            this.DataRead();
        }

        private void BtnClear_Click(object sender, EventArgs e)
        {
            this.datatable.Clear();
        }

        private void BtnRead_Click(object sender, EventArgs e)
        {
            this.DataRead();
        }

        private void DataRead()
        {
            //DataGridViewにどんどん追加されるので、一度初期化してから追加する
            this.datatable.Clear();
            adapter = new SQLiteDataAdapter("SELECT * FROM sample;", con);
            adapter.Fill(this.datatable);
        }
    }
}

最後にDataGridViewの内容を変更した結果をDBに反映します。

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

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        private DataTable datatable { get; set; }
        private SQLiteConnection con { get; set; }
        private SQLiteDataAdapter adapter { get; set; }

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string dbPath = Application.StartupPath + @"\Data.db";
            this.con = new SQLiteConnection("Data Source=" + dbPath);
            this.con.Open();
            
            //空のテーブルを作ります。
            //この時点では、DataGridViewと紐づいていません。
            this.datatable = new DataTable();

            //データテーブルをDataGridViewに紐づけます。
            this.dataGridView1.DataSource = this.datatable;

            //データを読み込みます
            this.DataRead();
        }

        private void BtnClear_Click(object sender, EventArgs e)
        {
            this.datatable.Clear();
        }

        private void BtnRead_Click(object sender, EventArgs e)
        {
            this.DataRead();
        }

        private void DataRead()
        {
            //DataGridViewにどんどん追加されるので、一度初期化してから追加する
            this.datatable.Clear();
            adapter = new SQLiteDataAdapter("SELECT * FROM sample;", con);
            adapter.Fill(this.datatable);
        }

        private void BtnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                using(SQLiteTransaction trans = this.con.BeginTransaction())
                {
                    SQLiteCommandBuilder builder = new SQLiteCommandBuilder(this.adapter);
                    builder.SetAllValues = false;
                    builder.ConflictOption = ConflictOption.OverwriteChanges;

                    this.adapter.Update(this.datatable);
                    trans.Commit();
                }

                MessageBox.Show("Update完了");
            }
            catch (Exception ex) 
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}

SQLiteCommandBuilderがDataTableの列名や主キーなどの情報と
変更内容から適宜、SQLを組み立てて実行してくれます。

ちなみに、下記のようにBtnUpdate_Clickを書き換えると・・・

private void BtnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                using(SQLiteTransaction trans = this.con.BeginTransaction())
                {
                    SQLiteCommandBuilder builder = new SQLiteCommandBuilder(this.adapter);
                    builder.SetAllValues = false;
                    builder.ConflictOption = ConflictOption.OverwriteChanges;

                    MessageBox.Show(builder.GetUpdateCommand().CommandText);
                    MessageBox.Show(builder.GetInsertCommand().CommandText);
                    MessageBox.Show(builder.GetDeleteCommand().CommandText);

                    int ret = this.adapter.Update(this.datatable);
                    trans.Commit();

                    string message = string.Format("Update完了\n{0}件変更", ret);
                    MessageBox.Show(message);
                }
            }
            catch (Exception ex) 
            {
                MessageBox.Show(ex.Message);
            }
        }

SQLiteCommandBuilderは、自動的に状況に応じてSQLを発行してくれるのでとても便利ではあるのですが
当然ながら万能なものではないので、あらゆるものがUpdateしたりDeleteしたりできるわけではないです。

そのため、主キーを利用したものに限られます。

あと、this.adapter.Update(this.datatable)で変更のレコード数を返します。
これは、Readするときに使っている
adapter.Fill(this.datatable);
も戻り値としてintの変数で受けるとSelectした行数が取得できます。

DataGridViewにデータを入れる②

今度はバインドさせてみます。


データは、下記に掲載されているxmlデータとしみましょう。
https://msdn.microsoft.com/ja-jp/library/bb387012.aspx

PurchaseOrder.xml

<?xml version="1.0"?>
<PurchaseOrder PurchaseOrderNumber="99503" OrderDate="1999-10-20">
  <Address Type="Shipping">
    <Name>Ellen Adams</Name>
    <Street>123 Maple Street</Street>
    <City>Mill Valley</City>
    <State>CA</State>
    <Zip>10999</Zip>
    <Country>USA</Country>
  </Address>
  <Address Type="Billing">
    <Name>Tai Yee</Name>
    <Street>8 Oak Avenue</Street>
    <City>Old Town</City>
    <State>PA</State>
    <Zip>95819</Zip>
    <Country>USA</Country>
  </Address>
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
  <Items>
    <Item PartNumber="872-AA">
      <ProductName>Lawnmower</ProductName>
      <Quantity>1</Quantity>
      <USPrice>148.95</USPrice>
      <Comment>Confirm this is electric</Comment>
    </Item>
    <Item PartNumber="926-AA">
      <ProductName>Baby Monitor</ProductName>
      <Quantity>2</Quantity>
      <USPrice>39.98</USPrice>
      <ShipDate>1999-05-21</ShipDate>
    </Item>
  </Items>
</PurchaseOrder>

新しい項目の追加　から データセットを追加します。


追加したデータセットに対し、格納する列の定義をします。
ツールボックスからDataTableをDrag & Dropで追加します。




DataGridViewに表示したいXMLのデータは、１行単位で考えるとAddressタグが１行データに該当します。
じゃ、DataTable名にでもしましょうか。

で、各タグ名を追加します


ついでに、DataSet自信をクリックして、プロパティにある Namespaceを空にします。


今度は、フォームを開いてDataGridViewを追加して、今作ったDataSetをDataGridViewにバインドします。




追加した結果、dataSet12, bindingSource2 として追加されました。


後は、Loadイベントにでも追加すれば表示できます。

using System;
using System.Windows.Forms;

namespace DataGridView
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load_1(object sender, EventArgs e)
        {
            this.dataSet12.ReadXml("PurchaseOrder.xml");
        }
    }
}

セルの値をまとめて取得する

Excelのデータは、１つずつアクセスするとすごい遅いので
２次元配列でごそっととってそれぞれをアクセスするほうが圧倒的に効率的です。


では、サンプルを書いておきます。




今回は、行列の数がわかっている設定としておきます。

using System;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application xlApplication = null;
            Excel.Workbooks xlWorkbooks = null;
            Excel.Workbook xlWorkbook = null;
            Excel.Sheets xlSheets = null;
            Excel.Worksheet xlWorksheet = null;

            try
            {
                string filePath = Application.StartupPath + @"\Data.xlsx";

                xlApplication = new Excel.Application();
                xlApplication.Visible = true;

                xlWorkbooks = xlApplication.Workbooks;
                xlWorkbook = xlWorkbooks.Open(filePath);

                xlSheets = xlWorkbook.Sheets;
                xlWorksheet = xlSheets[1];

                object[,] cellData = new object[10, 5]; //受け取るデータの個数分（行、列セル数を指定）
                cellData = xlWorksheet.get_Range("A1", "E10").Value; //Cellsは激遅なのでRangeを利用します。
                
                xlWorkbooks.Close();
                xlApplication.Quit();

                //データを出力 要素数は1から格納されています
                for (int r = 1; r <= cellData.GetLength(0); r++)
                {
                    for (int c = 1; c <= cellData.GetLength(1); c++)
                    {
                        Console.Write(string.Format(" {0} ", cellData[r, c]));
                    }

                    Console.WriteLine(string.Empty);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (xlSheets != null)
                {
                    Marshal.FinalReleaseComObject(xlSheets);
                }

                if (xlWorkbook != null)
                {
                    Marshal.FinalReleaseComObject(xlWorkbook);
                }

                if (xlWorkbooks != null)
                {
                    Marshal.FinalReleaseComObject(xlWorkbooks);
                }

                if (xlApplication != null)
                {
                    Marshal.FinalReleaseComObject(xlApplication);
                }
            }
        }
    }
}

DataGridViewにデータを入れる①

まずは非バインドモードで入れてみます。

入れるデータは、郵便局のCSVデータでも入れてみますか。
http://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html

北海道あたりのデータでも使ってみましょう。
では、先頭の９列でもデータを取り込んでみましょう。

まずは、取り込む先を確保します。
DataGridViewを配置し、９列作成します。
名称も変更せず適当に・・・。



CSVは、DEBUGフォルダ配下にでもおいておきますか。

using System;
using System.IO;
using System.Text;
using System.Windows.Forms;

namespace DataGridView
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.DataLoad();
        }

        private void DataLoad()
        {
            foreach (string line in File.ReadLines("01HOKKAI.CSV", Encoding.Default))
            {
                string[] csv = line.Split(',');
                string[] data = new string[9];
                Array.Copy(csv, 0, data, 0, 9);
                this.dataGridView1.Rows.Add(data);
            }
        }
    }
}

csvをスプリットして先頭の9データを１行ずつDataGridViewに追加しています。

一方で、効率の面から、Rowデータをまとめていれるというやり方もります

using System;
using System.IO;
using System.Text;
using System.Windows.Forms;

namespace DataGridView
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.DataLoad();
        }

        private void DataLoad()
        {
            string[] csvDataAll = File.ReadAllLines("01HOKKAI.CSV", Encoding.Default);
            
            //データの分配列を用意
            DataGridViewRow[] rows = new DataGridViewRow[csvDataAll.Length];

            for(int i = 0; i < csvDataAll.Length; i++)
            {
                string[] csv = csvDataAll[i].Split(',');
                string[] data = new string[9];
                Array.Copy(csv, 0, data, 0, 9);

                DataGridViewRow row = new DataGridViewRow();
                row.CreateCells(this.dataGridView1);
                row.SetValues(data);
                rows[i] = row;
            }

            this.dataGridView1.Rows.AddRange(rows);

        }
    }
}

DataGridViewに直接何度も放り込むと時間がかかるので、 Rowデータを配列に保持して１度に放り込むと速いという話ですね。 参考までに速度を比較しておきます。 8245レコード９列分のデータを追加する場合だと １レコードずつDataGridViewへ追加

2.4792633
2.5976070
2.4511964
2.9920726
2.8634533
2.3278521
2.7264484
2.5300329
2.3477178
2.4274311

Ave. 2.57秒 １度にDataGridViewへ追加

0.9712624
1.1560973
1.1795451
1.0009344
1.1347776
1.0131825
1.0595981
1.0133348
1.0227687
1.1925171

Ave. 1.07秒

Excel列名の変換（数字⇔文字列）

Rangeによるアクセスの方が大きく速いことを考えると
数字→文字列変換等が必要になるのでメモ。

Range, Cellsのパフォーマンスは以下より

Excel 100×10のデータを取得する方法の比較
Range vs Cells（１セルを取得）
Range vs Cells（範囲を取得）



数字→文字列

public string ToAlphabet(int columnNo)
{
    string alphabet = "ZABCDEFGHIJKLMNOPQRSTUVWXY";
    string columnStr = string.Empty;
    int m = 0;

    do
    {
        m = columnNo % 26;
        columnStr = alphabet[m] + columnStr;
        columnNo = columnNo / 26;
    } while (0 < columnNo && m != 0);

    return columnStr;
}

文字列→数字

public string ToAlphabet(int columnNo)
{
    columnStr = columnStr.ToUpper();

    double result = 0;
    for (int i = 0; i < columnStr.Length; i++)
    {
        int x = columnStr.Length - i - 1;
        int num = Convert.ToChar(columnStr[x]) - 64;

        result += num * Math.Pow(26, i);
    }

    return Convert.ToInt32(result);
}