もともと複数のエクセルに分割されたデータで、都度ファイルを開いて検索＋合計の計算を繰り返していたようなので、テキストファイル(CSV)にまとめ、インクリメンタルサーチで行の絞り込み＋選択した行の合計を求める機能を作成した。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Threading; using System.Windows.Forms; namespace 手入力工数検索 { public partial class Form1 : Form { string CsvPath = Application.StartupPath + @"\dat.csv"; List<string> RawItems = new List<string>(); int VisibleCount = 5000; string SearchWords = ""; object LockObject = new object(); bool CanExit = true; public Form1() { InitializeComponent(); RawItems = File.ReadAllLines(CsvPath).ToList(); var delayExecute = new DelayExecute(); delayExecute.Execute += (s, e) => UpdateDatasource(); textBox1.TextChanged += (s, e) => { lock (LockObject) { CanExit = false; } SearchWords = textBox1.Text; delayExecute.ReserveExecute(); }; listBox1.SelectedValueChanged += (s, e) => { decimal total = 0; foreach(var item in listBox1.SelectedItems) { var cols = item.ToString().Split(','); decimal.TryParse(cols[5], out decimal parseResult); total += parseResult; } label2.Text = "作業時間合計(選択行)：" + total.ToString("0.00"); }; this.FormClosing += (s, e) => { lock (LockObject) { if (!CanExit) e.Cancel = true; } }; } private void UpdateDatasource() { IEnumerable<string> items = new List<string>(); if (SearchWords == "") { items = RawItems.Take(VisibleCount); } else { List<string> tmpList = new List<string>(RawItems); foreach (string tmpString in SearchWords.Split('|')) { tmpList = tmpList.AsParallel().Where(x => x.ToLower().Contains(tmpString.ToLower())).ToList(); } items = tmpList.Take(VisibleCount); } Invoke(new Action(() => { listBox1.DataSource = items.ToList(); })); lock (LockObject) { CanExit = true; } } } class DelayExecute { public event EventHandler Execute; private int DelayTime = 500; System.Threading.Timer Timer; public DelayExecute() { Timer = new System.Threading.Timer(x => { Execute(this, EventArgs.Empty); }); } public void ReserveExecute() { Timer.Change(DelayTime, Timeout.Infinite); } } } |
DataGridView版
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 |
using System; using System.Collections.Generic; using System.Data; using System.Drawing; using System.IO; using System.Linq; using System.Threading; using System.Windows.Forms; namespace 手入力工数検索 { public partial class Form1 : Form { string CsvPath = Application.StartupPath + @"\dat.csv"; List<string> RawItems = new List<string>(); int VisibleCount = 1000; string SearchWords = ""; object LockObject = new object(); bool CanExit = true; public Form1() { InitializeComponent(); RawItems = File.ReadAllLines(CsvPath).ToList(); var delayExecute = new DelayExecute(); delayExecute.Execute += (s, e) => UpdateDatasource(); textBox1.TextChanged += (s, e) => { lock (LockObject) { CanExit = false; } SearchWords = textBox1.Text; delayExecute.ReserveExecute(); }; dataGridView1.SelectionChanged += (s, e) => { decimal total = 0; foreach (DataGridViewRow row in dataGridView1.SelectedRows) { decimal.TryParse(row.Cells["作業時間"].Value.ToString(), out decimal parseResult); total += parseResult; } label2.Text = "作業時間合計(選択行)：" + total.ToString("0.00"); }; this.FormClosing += (s, e) => { lock (LockObject) { if (!CanExit) e.Cancel = true; } }; SetDgv(); } private void SetDgv() { dataGridView1.RowHeadersWidth = 50; dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; dataGridView1.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False; dataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing; dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None; dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None; dataGridView1.Font = new Font("メイリオ", 10); dataGridView1.RowTemplate.Height = 22; dataGridView1.AllowUserToAddRows = false; dataGridView1.ReadOnly = true; typeof(DataGridView). GetProperty("DoubleBuffered", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic). SetValue(dataGridView1, true, null); } private void UpdateDatasource() { IEnumerable<string> items = new List<string>(); if (SearchWords == "") { items = RawItems.Take(VisibleCount); } else { List<string> tmpList = new List<string>(RawItems); foreach (string tmpString in SearchWords.Split('|')) { tmpList = tmpList.AsParallel().Where(x => x.ToLower().Contains(tmpString.ToLower())).ToList(); } items = tmpList.Take(VisibleCount); } DataSet ds = new DataSet(); DataTable dt = new DataTable("tbl"); ds.Tables.Add(dt); string[] cols = new string[] { "作業者","型式","数量","工番","作業日","作業時間","備考","登録日時" }; foreach (string c in cols) { dt.Columns.Add(c); } foreach(string item in items) { DataRow row = dt.NewRow(); string[] vals = item.Split(','); row["作業者"] = vals[0]; row["型式"] = vals[1]; row["数量"] = vals[2]; row["工番"] = vals[3]; row["作業日"] = vals[4]; row["作業時間"] = vals[5]; row["備考"] = vals[6]; row["登録日時"] = vals[7]; ds.Tables["tbl"].Rows.Add(row); } Invoke(new Action(() => { dataGridView1.DataSource = ds.Tables["tbl"]; dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells); })); lock (LockObject) { CanExit = true; } } } class DelayExecute { public event EventHandler Execute; private int DelayTime = 800; System.Threading.Timer Timer; public DelayExecute() { Timer = new System.Threading.Timer(x => { Execute(this, EventArgs.Empty); }); } public void ReserveExecute() { Timer.Change(DelayTime, Timeout.Infinite); } } } |