{"id":1048,"date":"2017-03-06T22:47:46","date_gmt":"2017-03-06T13:47:46","guid":{"rendered":"http:\/\/okamurax.com\/?p=1048"},"modified":"2017-03-06T22:47:46","modified_gmt":"2017-03-06T13:47:46","slug":"c-vba-%e5%9c%a8%e5%ba%ab%e7%ae%a1%e7%90%86","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=1048","title":{"rendered":"C# VBA \u5728\u5eab\u7ba1\u7406"},"content":{"rendered":"<p>\u4ee5\u524d\u4f5c\u3063\u305f\u30d7\u30ed\u30b0\u30e9\u30e0\u304a\u8535\u5165\u308a\u7248<\/p>\n<p>Form1.cs<\/p>\n<pre class=\"lang:c# decode:true \">using System;\r\nusing System.Collections.Generic;\r\nusing System.ComponentModel;\r\nusing System.Data;\r\nusing System.Drawing;\r\nusing System.Linq;\r\nusing System.Text;\r\nusing System.Threading.Tasks;\r\nusing System.Windows.Forms;\r\n\r\nusing System.Data.OleDb;\r\n\r\nnamespace \u5168\u5728\u5eab\u7ba1\u7406\r\n{\r\n    public partial class Form1 : Form\r\n    {\r\n        DataTable dt;\r\n\r\n        public Form1()\r\n        {\r\n            InitializeComponent();\r\n\r\n            dataGridView1.MultiSelect = false;\r\n            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;\r\n            dataGridView1.AllowUserToAddRows = false;\r\n            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;\r\n\r\n            comboBox2.Enabled = false;\r\n\r\n            textBox1.TextChanged += new EventHandler(init_view);\r\n            textBox2.TextChanged += new EventHandler(init_view);\r\n            textBox3.TextChanged += new EventHandler(init_view);\r\n            textBox5.TextChanged += new EventHandler(init_view);\r\n\r\n            contextMenuStrip1.Items.Add(\"\u66f4\u65b0\");\r\n            contextMenuStrip1.Items.Add(\"\u51fa\u5eab\");\r\n            contextMenuStrip1.Items.Add(\"\u8907\u5199\");\r\n            contextMenuStrip1.Items.Add(\"\u524a\u9664\");\r\n\r\n            init();\r\n        }\r\n\r\n         ~Form1()\r\n        {\r\n            dt.Dispose();\r\n        }\r\n\r\n        private void init()\r\n        {\r\n            string q = @\"select * from tbl where \u51fa\u5eab\u65e5 is null order by ID\";\r\n            if (checkBox3.Checked) q = @\"select * from tbl where \u51fa\u5eab\u65e5 is not null order by ID\";\r\n            if (checkBox4.Checked) q = @\"select * from tbl where \u5165\u5eab\u65e5 = #\" + DateTime.Now.ToString(\"yyyy\/MM\/dd\") + \"# order by ID\";\r\n            if (checkBox5.Checked) q = @\"select * from tbl where \u51fa\u5eab\u65e5 = #\" + DateTime.Now.ToString(\"yyyy\/MM\/dd\") + \"# order by ID\";\r\n\r\n            dt = Util.getDt(q);\r\n            list_add();\r\n        }\r\n\r\n        public void list_add()\r\n        {\r\n            comboBox1.Items.Clear();\r\n            comboBox2.Items.Clear();\r\n            using (DataView dv = new DataView(dt))\r\n            {\r\n                using (DataTable tmp = dv.ToTable(true, new string[] { \"\u4ed5\u5165\u6708\" }))\r\n                {\r\n                    DataRow[] dr = tmp.Select(\"\u4ed5\u5165\u6708 is not null\");\r\n                    for (int i = 0; i &lt; dr.Length; i++)\r\n                    {\r\n                        comboBox1.Items.Add(DateTime.Parse(dr[i][\"\u4ed5\u5165\u6708\"].ToString()).ToString(\"yyyy\u5e74MM\u6708\"));\r\n                        comboBox2.Items.Add(DateTime.Parse(dr[i][\"\u4ed5\u5165\u6708\"].ToString()).ToString(\"yyyy\u5e74MM\u6708\"));\r\n                    }\r\n                }\r\n            }\r\n        }\r\n\r\n        private void Form1_Load(object sender, EventArgs e)\r\n        {\r\n            init_view(sender, e);\r\n        }\r\n\r\n        public void init_view(object sender, EventArgs e)\r\n        {\r\n            dataGridView1.Rows.Clear();\r\n            string tmp = \"\";\r\n            bool chk = false;\r\n\r\n            if (textBox2.Text != \"\")\r\n            {\r\n                tmp = \"\u5de5\u756a like '%\" + textBox2.Text + \"%'\";\r\n                chk = true;\r\n            }\r\n\r\n            if (textBox1.Text != \"\")\r\n            {\r\n                if (chk) tmp = tmp + \" and \";\r\n                tmp = tmp + \"\u88fd\u54c1\u540d\u79f0 like '%\" + textBox1.Text + \"%'\";\r\n                chk = true;\r\n            }\r\n\r\n            if (textBox3.Text != \"\")\r\n            {\r\n                if (chk) tmp = tmp + \" and \";\r\n                tmp = tmp + \" \u4ed5\u5165\u5148 like '%\" + textBox3.Text + \"%'\";\r\n                chk = true;\r\n            }\r\n\r\n            if (textBox5.Text != \"\")\r\n            {\r\n                if (chk) tmp = tmp + \" and \";\r\n                tmp = tmp + \" \u533a\u5206 like '%\" + textBox5.Text + \"%'\";\r\n                chk = true;\r\n            }\r\n\r\n            if (comboBox1.Text != \"\" &amp;&amp; comboBox2.Enabled == false)\r\n            {\r\n                if (chk) tmp = tmp + \" and \";\r\n                tmp = tmp + \" \u4ed5\u5165\u6708 = #\" + DateTime.Parse(comboBox1.Text).ToString(\"yyyy\/MM\/01\") + \"#\";\r\n            }\r\n            else if (comboBox1.Text != \"\" &amp;&amp; comboBox2.Enabled == true &amp;&amp; comboBox2.Text != \"\")\r\n            {\r\n                if (chk) tmp = tmp + \" and \";\r\n                tmp = tmp + \" \u4ed5\u5165\u6708 &gt;= #\" + DateTime.Parse(comboBox1.Text).ToString(\"yyyy\/MM\/01\")\r\n                    + \"# and \u4ed5\u5165\u6708 &lt;= #\" + DateTime.Parse(comboBox2.Text).ToString(\"yyyy\/MM\/01\") + \"#\";\r\n            }\r\n            else if (comboBox1.Text != \"\" &amp;&amp; comboBox2.Enabled == true &amp;&amp; comboBox2.Text == \"\")\r\n            {\r\n                if (chk) tmp = tmp + \" and \";\r\n                tmp = tmp + \" \u4ed5\u5165\u6708 &gt;= #\" + DateTime.Parse(comboBox1.Text).ToString(\"yyyy\/MM\/01\") + \"#\";\r\n            }\r\n            else if (comboBox1.Text == \"\" &amp;&amp; comboBox2.Enabled == true &amp;&amp; comboBox2.Text != \"\")\r\n            {\r\n                if (chk) tmp = tmp + \" and \";\r\n                tmp = tmp + \" \u4ed5\u5165\u6708 &lt;= #\" + DateTime.Parse(comboBox2.Text).ToString(\"yyyy\/MM\/01\") + \"#\";\r\n            }\r\n\r\n            System.Diagnostics.Debug.Print(tmp.ToString());\r\n\r\n            DataRow[] dr = dt.Select(tmp, \"ID\");\r\n            decimal[,] rc = new decimal[6, 4];\r\n\r\n            for (int i = 0; i &lt; dr.Length; i++)\r\n            {\r\n                decimal res00;\r\n                decimal.TryParse(dr[i][\"\u6570\u91cf\"].ToString(), out res00);\r\n                decimal res01;\r\n                decimal.TryParse(dr[i][\"\u8a08\u4e0a\u5358\u4fa1\"].ToString(), out res01);\r\n                DateTime res02;\r\n                if (!DateTime.TryParse(dr[i][\"\u4ed5\u5165\u6708\"].ToString(), out res02)) res02 = new DateTime(1970, 1, 1);\r\n\r\n                dataGridView1.Rows.Add(\r\n                    dr[i][\"ID\"].ToString(),\r\n                    dr[i][\"\u5de5\u756a\"].ToString(),\r\n                    dr[i][\"\u88fd\u54c1\u540d\u79f0\"].ToString(),\r\n                    dr[i][\"\u4ed5\u5165\u5148\"].ToString(),\r\n                    res00.ToString(),\r\n                    string.Format(\"{0:#,0}\", res01),\r\n                    string.Format(\"{0:#,0}\", (res00 * res01)),\r\n                    dr[i][\"\u533a\u5206\"].ToString(),\r\n                    res02.ToString(\"yyyy\u5e74MM\u6708\"),\r\n                    dr[i][\"\u5099\u8003\"].ToString()\r\n                    );\r\n\r\n                switch (dr[i][\"\u533a\u5206\"].ToString())\r\n                {\r\n                    case \"\u5185\u4f5c\u6750\u6599\u2460\":\r\n\r\n                        if ((res00 * res01) &gt;= 50000)\r\n                        {\r\n                            rc[1, 2] = rc[1, 2] + (res00 * res01);\r\n                        }\r\n                        else\r\n                        {\r\n                            rc[1, 3] = rc[1, 3] + (res00 * res01);\r\n                        }\r\n                        rc[1, 1] = rc[1, 1] + (res00 * res01);\r\n                        break;\r\n\r\n                    case \"\u5916\u4f5c\u6750\u6599\u2460\":\r\n\r\n                        if ((res00 * res01) &gt;= 50000)\r\n                        {\r\n                            rc[2, 2] = rc[2, 2] + (res00 * res01);\r\n                        }\r\n                        else\r\n                        {\r\n                            rc[2, 3] = rc[2, 3] + (res00 * res01);\r\n                        }\r\n                        rc[2, 1] = rc[2, 1] + (res00 * res01);\r\n                        break;\r\n\r\n                    case \"\u5185\u4f5c\u88fd\u54c1\":\r\n\r\n                        if ((res00 * res01) &gt;= 50000)\r\n                        {\r\n                            rc[3, 2] = rc[3, 2] + (res00 * res01);\r\n                        }\r\n                        else\r\n                        {\r\n                            rc[3, 3] = rc[3, 3] + (res00 * res01);\r\n                        }\r\n                        rc[3, 1] = rc[3, 1] + (res00 * res01);\r\n                        break;\r\n\r\n                    case \"\u5916\u4f5c\u88fd\u54c1\":\r\n\r\n                        if ((res00 * res01) &gt;= 50000)\r\n                        {\r\n                            rc[4, 2] = rc[4, 2] + (res00 * res01);\r\n                        }\r\n                        else\r\n                        {\r\n                            rc[4, 3] = rc[4, 3] + (res00 * res01);\r\n                        }\r\n                        rc[4, 1] = rc[4, 1] + (res00 * res01);\r\n                        break;\r\n\r\n                    default:\r\n\r\n                        if ((res00 * res01) &gt;= 50000)\r\n                        {\r\n                            rc[5, 2] = rc[5, 2] + (res00 * res01);\r\n                        }\r\n                        else\r\n                        {\r\n                            rc[5, 3] = rc[5, 3] + (res00 * res01);\r\n                        }\r\n                        rc[5, 1] = rc[5, 1] + (res00 * res01);\r\n                        break;\r\n                }\r\n            }\r\n            textBox4.Text = string.Format(\"{0:#,0}\", rc[1, 1]);\r\n            textBox8.Text = string.Format(\"{0:#,0}\", rc[2, 1]);\r\n            textBox11.Text = string.Format(\"{0:#,0}\", rc[3, 1]);\r\n            textBox14.Text = string.Format(\"{0:#,0}\", rc[4, 1]);\r\n            textBox17.Text = string.Format(\"{0:#,0}\", rc[5, 1]);\r\n            textBox20.Text = string.Format(\"{0:#,0}\", rc[1, 1] + rc[2, 1] + rc[3, 1] + rc[4, 1] + rc[5, 1]);\r\n            textBox6.Text = string.Format(\"{0:#,0}\", rc[1, 2]);\r\n            textBox9.Text = string.Format(\"{0:#,0}\", rc[2, 2]);\r\n            textBox12.Text = string.Format(\"{0:#,0}\", rc[3, 2]);\r\n            textBox15.Text = string.Format(\"{0:#,0}\", rc[4, 2]);\r\n            textBox18.Text = string.Format(\"{0:#,0}\", rc[5, 2]);\r\n            textBox21.Text = string.Format(\"{0:#,0}\", rc[1, 2] + rc[2, 2] + rc[3, 2] + rc[4, 2] + rc[5, 2]);\r\n            textBox7.Text = string.Format(\"{0:#,0}\", rc[1, 3]);\r\n            textBox10.Text = string.Format(\"{0:#,0}\", rc[2, 3]);\r\n            textBox13.Text = string.Format(\"{0:#,0}\", rc[3, 3]);\r\n            textBox16.Text = string.Format(\"{0:#,0}\", rc[4, 3]);\r\n            textBox19.Text = string.Format(\"{0:#,0}\", rc[5, 3]);\r\n            textBox22.Text = string.Format(\"{0:#,0}\", rc[1, 3] + rc[2, 3] + rc[3, 3] + rc[4, 3] + rc[5, 3]);\r\n            label16.Text = dr.Length.ToString() + \"\u4ef6\u53d6\u5f97\u3057\u307e\u3057\u305f\";\r\n        }\r\n\r\n        private void button1_Click(object sender, EventArgs e)\r\n        {\r\n            textBox1.Text = \"\";\r\n            textBox2.Text = \"\";\r\n            textBox3.Text = \"\";\r\n            textBox5.Text = \"\";\r\n            comboBox1.Text = \"\";\r\n            comboBox2.Text = \"\";\r\n            checkBox1.Checked = false;\r\n            comboBox2.Enabled = false;\r\n            textBox23.Text = \"\";\r\n            textBox24.Text = \"\";\r\n            textBox25.Text = \"\";\r\n            textBox26.Text = \"\";\r\n            textBox27.Text = \"\";\r\n            textBox28.Text = \"\";\r\n            textBox29.Text = \"\";\r\n            textBox30.Text = \"\";\r\n            init_view(sender, e);\r\n        }\r\n\r\n        private void dataGridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)\r\n        {\r\n            if (e.Button == MouseButtons.Right)\r\n            {\r\n                dataGridView1.ClearSelection();\r\n\r\n                if (e.RowIndex &gt;= 0 &amp;&amp; e.ColumnIndex &gt;= 0) dataGridView1.Rows[e.RowIndex].Selected = true;\r\n\r\n                foreach (ToolStripMenuItem tsmi in contextMenuStrip1.Items)\r\n                {\r\n                    if (tsmi.Text == \"\u51fa\u5eab\u53d6\u6d88\")\r\n                    {\r\n                        contextMenuStrip1.Items.Remove(tsmi);\r\n                        break;\r\n                    }\r\n                }\r\n\r\n                if (checkBox3.Checked || checkBox5.Checked) contextMenuStrip1.Items.Add(\"\u51fa\u5eab\u53d6\u6d88\");\r\n            }\r\n        }\r\n\r\n        private string[] parse_num(string[] tmp)\r\n        {\r\n            string[] res = new string[3];\r\n\r\n            decimal out001;\r\n            if (tmp[0] == \"\" || !decimal.TryParse(tmp[0], out out001))\r\n            {\r\n                res[0] = \"0\";\r\n            }\r\n            else\r\n            {\r\n                res[0] = out001.ToString();\r\n            }\r\n\r\n            decimal out002;\r\n            if (tmp[1] == \"\" || !decimal.TryParse(tmp[1], out out002))\r\n            {\r\n                res[1] = \"0\";\r\n            }\r\n            else\r\n            {\r\n                res[1] = out002.ToString();\r\n            }\r\n\r\n            DateTime out003;\r\n\r\n            if (tmp[2].Length == 6 &amp;&amp; tmp[2].Substring(4, 1) != \"\/\")\r\n            {\r\n                tmp[2] = tmp[2].Substring(0, 4) + \"\/\" + tmp[2].Substring(4, 2) + \"\/1\";\r\n            }\r\n            else if (tmp[2].Length == 6 &amp;&amp; tmp[2].Substring(4, 1) == \"\/\")\r\n            {\r\n                tmp[2] = tmp[2].Substring(0, 4) + \"\/\" + tmp[2].Substring(5, 1) + \"\/1\";\r\n            }\r\n            else if (tmp[2].Length == 7 &amp;&amp; tmp[2].Substring(4, 1) == \"\/\")\r\n            {\r\n                tmp[2] = tmp[2].Substring(0, 4) + \"\/\" + tmp[2].Substring(5, 2) + \"\/1\";\r\n            }\r\n\r\n            if (tmp[2] == \"\" || !DateTime.TryParse(tmp[2], out out003))\r\n            {\r\n                res[2] = \"1970\/01\/01\";\r\n            }\r\n            else\r\n            {\r\n                res[2] = out003.ToString(\"yyyy\/MM\/01\");\r\n            }\r\n\r\n            return res;\r\n        }\r\n\r\n        private void contextMenuStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)\r\n        {\r\n            if (dataGridView1.Rows.Count == 0) return;\r\n            set_selection(dataGridView1.SelectedRows[0].Index);\r\n\r\n            string q = \"\";\r\n\r\n            switch (e.ClickedItem.Text.ToString())\r\n            {\r\n                case \"\u66f4\u65b0\":\r\n\r\n                    if (dgvr.Cells[4].Value == null || dgvr.Cells[5].Value == null || dgvr.Cells[8].Value == null)\r\n                    {\r\n                        MessageBox.Show(\"\u6570\u91cf\u3001\u8a08\u4e0a\u5358\u4fa1\u3001\u4ed5\u5165\u6708\u304c\u7a7a\u767d\u3067\u306f\u51e6\u7406\u3067\u304d\u307e\u305b\u3093\u3002\");\r\n                        return;\r\n                    }\r\n\r\n                    string[] res = parse_num(new string[] {\r\n                        dgvr.Cells[4].Value.ToString(),\r\n                        dgvr.Cells[5].Value.ToString(),\r\n                        dgvr.Cells[8].Value.ToString()\r\n                    } );\r\n\r\n                    q = @\"update tbl set \" +\r\n                        \"\u5de5\u756a='\" + dgvr.Cells[1].Value.ToString() + \"',\" +\r\n                        \"\u88fd\u54c1\u540d\u79f0='\" + dgvr.Cells[2].Value.ToString() + \"',\" +\r\n                        \"\u4ed5\u5165\u5148='\" + dgvr.Cells[3].Value.ToString() + \"',\" +\r\n                        \"\u6570\u91cf=\" + res[0] + \",\" +\r\n                        \"\u8a08\u4e0a\u5358\u4fa1=\" + res[1] + \",\" +\r\n                        \"\u533a\u5206='\" + dgvr.Cells[7].Value.ToString() + \"',\" +\r\n                        \"\u4ed5\u5165\u6708=#\" + res[2] + \"#,\" +\r\n                        \"\u5099\u8003='\" + dgvr.Cells[9].Value.ToString() + \"' \" +\r\n                        \"where ID = \" + dgvr.Cells[0].Value.ToString() + \"\";\r\n\r\n                    break;\r\n\r\n                case \"\u51fa\u5eab\":\r\n\r\n                    q = @\"update tbl set \" +\r\n                        \"\u51fa\u5eab\u65e5=#\" + DateTime.Now.ToString(\"yyyy\/MM\/dd\") + \"# \" +\r\n                        \"where ID = \" + dgvr.Cells[0].Value.ToString() + \"\";\r\n\r\n                    break;\r\n\r\n                case \"\u51fa\u5eab\u53d6\u6d88\":\r\n\r\n                    q = @\"update tbl set \" +\r\n                    \"\u51fa\u5eab\u65e5=NULL \" +\r\n                    \"where ID = \" + dgvr.Cells[0].Value.ToString() + \"\";\r\n\r\n                    break;\r\n\r\n                case \"\u8907\u5199\":\r\n\r\n                    q = @\"insert into tbl select \u5de5\u756a,\u88fd\u54c1\u540d\u79f0,\u4ed5\u5165\u5148,\u6570\u91cf,\u8a08\u4e0a\u5358\u4fa1,\u533a\u5206,\u4ed5\u5165\u6708,\u5099\u8003,\u51fa\u5eab\u65e5,\u5165\u5eab\u65e5 from tbl \" + \r\n                        \"where ID = \" + dgvr.Cells[0].Value.ToString() + \"\";\r\n\r\n                    break;\r\n\r\n                case \"\u524a\u9664\":\r\n\r\n                    q = @\"delete from tbl \" +\r\n                        \"where ID = \" + dgvr.Cells[0].Value.ToString() + \"\";\r\n                    break;\r\n            }\r\n\r\n            Util.exeQry(q);\r\n\r\n            init();\r\n            init_view(sender, e);\r\n            init_selection(e.ClickedItem.Text.ToString());\r\n            MessageBox.Show(e.ClickedItem.Text.ToString() + \"\u3057\u307e\u3057\u305f\u3002\");\r\n        }\r\n\r\n        private void init_selection(string tmp)\r\n        {\r\n            if (dataGridView1.Rows.Count == 0) return;\r\n\r\n            switch (tmp)\r\n            {\r\n                case \"\u66f4\u65b0\":\r\n                case \"\u51fa\u5eab\":\r\n                case \"\u524a\u9664\":\r\n\r\n                    if (dgv_rows_count == dataGridView1.Rows.Count)\r\n                    {\r\n                        dataGridView1.Rows[dgv_selected].Selected = true;\r\n                        dataGridView1.FirstDisplayedScrollingRowIndex = dgv_view_index;\r\n                    }\r\n                    else\r\n                    {\r\n                        if (dgv_selected &gt;= 1) dataGridView1.Rows[dgv_selected - 1].Selected = true;\r\n                        dataGridView1.FirstDisplayedScrollingRowIndex = dgv_view_index;\r\n                    }\r\n                    break;\r\n\r\n                case \"\u8907\u5199\":\r\n                case \"\u65b0\u898f\":\r\n\r\n                    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Selected = true;\r\n                    dataGridView1.FirstDisplayedScrollingRowIndex = dataGridView1.Rows.Count - 1;\r\n                    break;\r\n            }\r\n        }\r\n\r\n        private DataGridViewRow dgvr;\r\n        private int dgv_view_index;\r\n        private int dgv_selected;\r\n        private int dgv_rows_count;\r\n\r\n        private void set_selection(int rows_index)\r\n        {\r\n            dgvr = dataGridView1.SelectedRows[0];\r\n            dgv_view_index = dataGridView1.FirstDisplayedScrollingRowIndex;\r\n            dgv_selected = rows_index;\r\n            dgv_rows_count = dataGridView1.Rows.Count;\r\n        }\r\n\r\n        private void comboBox1_TextChanged(object sender, EventArgs e)\r\n        {\r\n            DateTime out001;\r\n            if (!DateTime.TryParse(comboBox1.Text, out out001)) comboBox1.Text = \"\";\r\n            init_view(sender, e);\r\n        }\r\n\r\n        private void comboBox2_TextChanged(object sender, EventArgs e)\r\n        {\r\n            DateTime out001;\r\n            if (!DateTime.TryParse(comboBox2.Text, out out001)) comboBox2.Text = \"\";\r\n            init_view(sender, e);\r\n        }\r\n\r\n        private void checkBox1_CheckedChanged(object sender, EventArgs e)\r\n        {\r\n            if(checkBox1.Checked)\r\n            {\r\n                comboBox2.Enabled = true;\r\n            }\r\n            else\r\n            {\r\n                comboBox2.Text = \"\";\r\n                comboBox2.Enabled = false;\r\n            }\r\n            init_view(sender, e);\r\n        }\r\n\r\n        private void button2_Click(object sender, EventArgs e)\r\n        {\r\n            checkBox3.Checked = false;\r\n\r\n            string[] res = parse_num(new string[] {\r\n                        textBox26.Text,\r\n                        textBox27.Text,\r\n                        textBox29.Text\r\n                    });\r\n\r\n            string q = @\"insert into tbl (\u5de5\u756a,\u88fd\u54c1\u540d\u79f0,\u4ed5\u5165\u5148,\u6570\u91cf,\u8a08\u4e0a\u5358\u4fa1,\u533a\u5206,\u4ed5\u5165\u6708,\u5099\u8003,\u5165\u5eab\u65e5) values \" +\r\n                \"('\" + textBox23.Text + \"','\" + textBox24.Text + \"','\" + textBox25.Text + \"'\" +\r\n                \",\" + res[0] + \",\" + res[1] + \",'\" + textBox28.Text + \"',#\" + res[2] + \"#,'\" + textBox30.Text + \"',#\" + DateTime.Now.ToString(\"yyyy\/MM\/dd\") + \"#)\";\r\n\r\n            Util.exeQry(q);\r\n\r\n            init();\r\n            init_view(sender, e);\r\n            init_selection(\"\u65b0\u898f\");\r\n            button1.PerformClick();\r\n            MessageBox.Show(\"\u65b0\u898f\u767b\u9332\u3057\u307e\u3057\u305f\u3002\");\r\n        }\r\n\r\n        private void btn5678_click(object sender, EventArgs e)\r\n        {\r\n            switch (((Button)sender).Text)\r\n            {\r\n                case \"\u66f4\u65b0\":\r\n                    contextMenuStrip1.Items[0].PerformClick();\r\n                    break;\r\n                case \"\u51fa\u5eab\":\r\n                    contextMenuStrip1.Items[1].PerformClick();\r\n                    break;\r\n                case \"\u8907\u5199\":\r\n                    contextMenuStrip1.Items[2].PerformClick();\r\n                    break;\r\n                case \"\u524a\u9664\":\r\n                    contextMenuStrip1.Items[3].PerformClick();\r\n                    break;\r\n            }\r\n        }\r\n\r\n        private void btn34_click(object sender, EventArgs e)\r\n        {\r\n            DialogResult res = MessageBox.Show(\"\u8868\u793a\u3055\u308c\u3066\u3044\u308b\u884c\u304c\" + ((Button)sender).Text + \"\u3055\u308c\u307e\u3059\u3002\u5b9f\u884c\u3057\u307e\u3059\u304b\uff1f\",\"\",MessageBoxButtons.OKCancel);\r\n            if (res == DialogResult.Cancel) return;\r\n\r\n            for (int r = 0; r &lt; dataGridView1.Rows.Count; r++)\r\n            {\r\n                string q = \"\";\r\n\r\n                if (((Button)sender).Text == \"\u5168\u3066\u51fa\u5eab\")\r\n                {\r\n                    q = @\"update tbl set \" +\r\n                        \"\u51fa\u5eab\u65e5=#\" + DateTime.Now.ToString(\"yyyy\/MM\/dd\") + \"# \" +\r\n                        \"where ID = \" + dataGridView1.Rows[r].Cells[0].Value.ToString() + \"\";\r\n                }\r\n                else\r\n                {\r\n                    q = @\"delete from tbl \" +\r\n                        \"where ID = \" + dataGridView1.Rows[r].Cells[0].Value.ToString() + \"\";\r\n                }\r\n\r\n                Text = r + \"\u4ef6\u76ee\u51e6\u7406\u4e2d...\";\r\n                Util.exeQry(q);\r\n\r\n            }\r\n            \r\n            Text = \"\u5168\u5728\u5eab\u7ba1\u7406 v1.0\";\r\n\r\n            init();\r\n            init_view(sender, e);\r\n            button1.PerformClick();\r\n            MessageBox.Show(((Button)sender).Text + \"\u3057\u307e\u3057\u305f\u3002\");\r\n        }\r\n\r\n        private void checkBox3_CheckedChanged(object sender, EventArgs e)\r\n        {\r\n            if (checkBox3.Checked)\r\n            {\r\n                checkBox4.Checked = false;\r\n                checkBox5.Checked = false;\r\n            }\r\n            init();\r\n            init_view(sender, e);\r\n            button1.PerformClick();\r\n        }\r\n\r\n        private void checkBox4_CheckedChanged(object sender, EventArgs e)\r\n        {\r\n            if (checkBox4.Checked)\r\n            {\r\n                checkBox3.Checked = false;\r\n                checkBox5.Checked = false;\r\n            }\r\n            init();\r\n            init_view(sender, e);\r\n            button1.PerformClick();\r\n        }\r\n\r\n        private void checkBox5_CheckedChanged(object sender, EventArgs e)\r\n        {\r\n            if (checkBox5.Checked)\r\n            {\r\n                checkBox3.Checked = false;\r\n                checkBox4.Checked = false;\r\n            }\r\n\r\n            init();\r\n            init_view(sender, e);\r\n            button1.PerformClick();\r\n        }\r\n    }\r\n}\r\n<\/pre>\n<p>Util.cs<\/p>\n<pre class=\"lang:c# decode:true\">using System;\r\nusing System.Collections.Generic;\r\nusing System.Linq;\r\nusing System.Text;\r\nusing System.Threading.Tasks;\r\nusing System.Data;\r\nusing System.Data.OleDb;\r\nusing System.Windows.Forms;\r\n\r\nnamespace \u5168\u5728\u5eab\u7ba1\u7406\r\n{\r\n    class Util\r\n    {\r\n        public static void exeQry(string q)\r\n        {\r\n            using (OleDbConnection con = new OleDbConnection(@\"provider=microsoft.jet.oledb.4.0;data source=\" + Application.StartupPath + @\"\\zen_zaiko.mdb\"))\r\n            {\r\n                try\r\n                {\r\n                    con.Open();\r\n                    OleDbCommand cmd = new OleDbCommand(q, con);\r\n                    cmd.ExecuteNonQuery();\r\n                }\r\n                finally\r\n                {\r\n                    con.Close();\r\n                }\r\n            }\r\n        }\r\n\r\n        public static DataTable getDt(string q)\r\n        {\r\n            DataTable dt;\r\n\r\n            using (OleDbConnection con = new OleDbConnection(@\"provider=microsoft.jet.oledb.4.0;data source=\" + Application.StartupPath + @\"\\zen_zaiko.mdb\"))\r\n            {\r\n                try\r\n                {\r\n                    con.Open();\r\n                    OleDbCommand cmd = new OleDbCommand(q, con);\r\n                    using (OleDbDataReader dr = cmd.ExecuteReader())\r\n                    {\r\n                        dt = new DataTable();\r\n                        dt.Load(dr);\r\n                        dr.Close();\r\n                    }\r\n                }\r\n                finally\r\n                {\r\n                    con.Close();\r\n                }\r\n\r\n            }\r\n            return dt;\r\n        }\r\n    }\r\n}\r\n<\/pre>\n<p>\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9VBA<\/p>\n<pre class=\"lang:vb decode:true \">Public frm_type As String\r\nPublic frm_date As String\r\nPublic frm_book As String\r\nPublic frm_chk As Boolean\r\n\r\nSub upload_zaiko()\r\n\r\nOn Error GoTo ex\r\n\r\nfrm_chk = False\r\n\r\nUserForm1.Show\r\n\r\nIf frm_chk = False Then Exit Sub\r\n\r\nIf frm_type = \"\" Then\r\n\r\n  MsgBox \"\u533a\u5206\u3092\u9078\u629e\u3057\u3066\u304f\u3060\u3055\u3044\u3002\u7d42\u4e86\u3057\u307e\u3059\u3002\"\r\n  Exit Sub\r\n\r\nEnd If\r\n\r\nIf frm_date = \"\" Then\r\n\r\n  MsgBox \"\u4ed5\u5165\u6708\u3092\u9078\u629e\u3057\u3066\u304f\u3060\u3055\u3044\u3002\u7d42\u4e86\u3057\u307e\u3059\u3002\"\r\n  Exit Sub\r\n\r\nEnd If\r\n\r\nIf frm_book = \"\" Then\r\n\r\n  MsgBox \"\u5bfe\u8c61\u30d6\u30c3\u30af\u3092\u9078\u629e\u3057\u3066\u304f\u3060\u3055\u3044\u3002\u7d42\u4e86\u3057\u307e\u3059\u3002\"\r\n  Exit Sub\r\n\r\nEnd If\r\n\r\nApplication.ScreenUpdating = False\r\nApplication.DisplayAlerts = False\r\n\r\np = ThisWorkbook.Path &amp; \"\\zen_zaiko.mdb\"\r\n\r\nt = Selection(1).Row\r\nb = Selection(Selection.Count).Row\r\n\r\nSet con = CreateObject(\"adodb.connection\")\r\nSet res = CreateObject(\"adodb.recordset\")\r\n\r\ncon.Open \"provider=microsoft.jet.oledb.4.0;data source=\" &amp; p\r\n\r\nDim buf(9) As String\r\n\r\nFor r = t To b\r\n\r\n  If frm_book = \"\u4ed5\u5165\u53f0\u5e33\" Then\r\n  \r\n    buf(1) = h(Cells(r, 10).Text, \"str\") '\u5de5\u756a\r\n    buf(2) = h(Cells(r, 5).Text &amp; \" \" &amp; Cells(r, 6).Text &amp; \" \" &amp; Cells(r, 7).Text, \"str\") &amp; \"(\" &amp; h(Cells(r, 12).Text, \"str\") &amp; \")\" '\u88fd\u54c1\u540d\u79f0\/\u578b\u5f0f\r\n    buf(3) = h(Cells(r, 3).Text, \"str\") '\u4ed5\u5165\u5148\r\n    buf(4) = h(Cells(r, 15).Text, \"int\") '\u6570\u91cf\r\n    buf(5) = h(Cells(r, 18).Text, \"cur\") '\u8a08\u4e0a\u5358\u4fa1\r\n    buf(6) = frm_type '\u533a\u5206\r\n    buf(7) = Format(CDate(frm_date), \"yyyy\/MM\/01\") '\u4ed5\u5165\u6708\r\n    buf(8) = \"\" '\u5099\u8003\r\n    buf(9) = Format(Year(Date) &amp; \"\/\" &amp; Month(Date) &amp; \"\/\" &amp; Day(Date), \"yyyy\/MM\/dd\") '\u5165\u5eab\u65e5\r\n    \r\n  ElseIf frm_book = \"\u5de5\u7a0b\u8868\" Then\r\n  \r\n    buf(1) = h(Cells(r, 3).Text, \"str\") '\u5de5\u756a\r\n    buf(2) = h(Cells(r, 2).Text, \"str\") &amp; \"(\" &amp; h(Cells(r, 102).Text, \"str\") &amp; \")\" '\u88fd\u54c1\u540d\u79f0\/\u5ba2\u5148\r\n    buf(3) = h(ActiveSheet.Name, \"str\") '\u73ed\r\n    buf(4) = h(Cells(r, 5).Text, \"int\") '\u6570\u91cf\r\n    buf(5) = h(Cells(r, 104).Text, \"cur\") '\u8a08\u4e0a\u5358\u4fa1\r\n    buf(6) = frm_type '\u533a\u5206\r\n    buf(7) = Format(CDate(frm_date), \"yyyy\/MM\/01\") '\u4ed5\u5165\u6708\r\n    buf(8) = \"\" '\u5099\u8003\r\n    buf(9) = Format(Year(Date) &amp; \"\/\" &amp; Month(Date) &amp; \"\/\" &amp; Day(Date), \"yyyy\/MM\/dd\") '\u5165\u5eab\u65e5\r\n    \r\n  End If\r\n  \r\n  q = \"insert into tbl (\u5de5\u756a,\u88fd\u54c1\u540d\u79f0,\u4ed5\u5165\u5148,\u6570\u91cf,\u8a08\u4e0a\u5358\u4fa1,\u533a\u5206,\u4ed5\u5165\u6708,\u5099\u8003,\u5165\u5eab\u65e5) values (\" &amp; _\r\n  \"'\" &amp; buf(1) &amp; \"',\" &amp; _\r\n  \"'\" &amp; buf(2) &amp; \"',\" &amp; _\r\n  \"'\" &amp; buf(3) &amp; \"',\" &amp; _\r\n  buf(4) &amp; \",\" &amp; _\r\n  buf(5) &amp; \",\" &amp; _\r\n  \"'\" &amp; buf(6) &amp; \"',\" &amp; _\r\n  \"'\" &amp; buf(7) &amp; \"',\" &amp; _\r\n  \"'\" &amp; buf(8) &amp; \"',\" &amp; _\r\n  \"'\" &amp; buf(9) &amp; \"')\"\r\n  \r\n  con.Execute (q)\r\n\r\nNext r\r\n\r\nIf con.State = 1 Then con.Close\r\nSet con = Nothing\r\n\r\nApplication.ScreenUpdating = True\r\nApplication.DisplayAlerts = True\r\n\r\nMsgBox \"\u7d42\u4e86\u3057\u307e\u3057\u305f\u3002\"\r\n\r\nExit Sub\r\n\r\nex:\r\n\r\n  If con.State = 1 Then con.Close\r\n  Set con = Nothing\r\n\r\n  Application.ScreenUpdating = True\r\n  Application.DisplayAlerts = True\r\n\r\n  MsgBox \"\u30a8\u30e9\u30fc\u304c\u767a\u751f\u3057\u307e\u3057\u305f\u3002\"\r\n\r\nEnd Sub\r\n\r\nFunction h(str, chk)\r\n\r\nOn Error GoTo ex\r\n\r\nIf (chk = \"int\" Or chk = \"cur\") And str = \"\" Then\r\n\r\n  h = 0\r\n  Exit Function\r\n\r\nEnd If\r\n\r\nIf chk = \"int\" Then\r\n\r\n  If IsNumeric(str) Then\r\n  \r\n    h = CInt(str)\r\n    Exit Function\r\n  \r\n  Else\r\n  \r\n    h = 0\r\n    Exit Function\r\n  \r\n  End If\r\n\r\nEnd If\r\n\r\nIf chk = \"cur\" Then\r\n\r\n  If IsNumeric(str) Then\r\n  \r\n    h = CCur(str)\r\n    Exit Function\r\n  \r\n  Else\r\n  \r\n    h = 0\r\n    Exit Function\r\n  \r\n  End If\r\n\r\nEnd If\r\n\r\nIf chk = \"str\" And str &lt;&gt; \"\" Then\r\n\r\n  str = Replace(str, \",\", \"\uff0c\")\r\n  str = Replace(str, \"'\", \"\u2019\")\r\n  '\u5168\u89d2\u7a7a\u767d\r\n  str = Replace(str, \"\u3000\", \" \")\r\n  '\u534a\u89d2\u7a7a\u767d\r\n  str = Replace(str, \"  \", \" \")\r\n  str = Replace(str, \"  \", \" \")\r\n  str = Replace(str, \"  \", \" \")\r\n  \r\n  h = str\r\n  \r\n  Exit Function\r\n\r\nElseIf chk = \"str\" And str = \"\" Then\r\n\r\n  h = \"\"\r\n  \r\n  Exit Function\r\n\r\nEnd If\r\n\r\nExit Function\r\n\r\nex:\r\n\r\n  MsgBox \"\u6587\u5b57\u5217\u306e\u5909\u63db\u30a8\u30e9\u30fc\u304c\u767a\u751f\u3057\u307e\u3057\u305f\u3002\"\r\n  h = 0\r\n\r\nEnd Function\r\n\r\nSub auto_open()\r\n\r\nOn Error Resume Next\r\n\r\nApplication.CommandBars(\"cell\").Controls(\"\u5168\u5728\u5eab\u3078\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9\").Delete\r\n\r\nWith Application.CommandBars(\"cell\").Controls.Add\r\n\r\n  .Caption = \"\u5168\u5728\u5eab\u3078\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9\"\r\n  .OnAction = \"upload_zaiko\"\r\n\r\nEnd With\r\n\r\nEnd Sub\r\n\r\nSub auto_close()\r\n\r\nOn Error Resume Next\r\n\r\nApplication.CommandBars(\"cell\").Controls(\"\u5168\u5728\u5eab\u3078\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9\").Delete\r\nApplication.CommandBars(\"cell\").Controls(\"\u5168\u5728\u5eab\u3078\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9\").Delete\r\n\r\nEnd Sub\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u4ee5\u524d\u4f5c\u3063\u305f\u30d7\u30ed\u30b0\u30e9\u30e0\u304a\u8535\u5165\u308a\u7248 Form1.cs using System; using System.Collections.Generic; using System.ComponentModel; using Sy &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/appbay.org\/?p=1048\" class=\"more-link\"><span class=\"screen-reader-text\">&#8220;C# VBA \u5728\u5eab\u7ba1\u7406&#8221; \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[9,5],"class_list":["post-1048","post","type-post","status-publish","format-standard","hentry","category-1","tag-c-net","tag-vba"],"_links":{"self":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1048","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1048"}],"version-history":[{"count":1,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1048\/revisions"}],"predecessor-version":[{"id":1049,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/1048\/revisions\/1049"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1048"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1048"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1048"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}