C#：ADO：Excel - プログラミングのメモ

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

//++++++++++++++++++++++++++++++++++++++++++++++++++++++++
using System.Data;  //DataSet
using System.Data.OleDb; //OleDbConnection
using System.Data.SqlClient; //SqlConnection,SqlCommand
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++

namespace ADO.ExcelADO
{
    public partial class ExcelADO_frm : Form
    {
        string _sProvider = @"Microsoft.ACE.OLEDB.12.0";
        //string _sDataSorce = @"D:\_Prog\CS\ADO\Northwind.accdb";
        string _sDataSorce = @"D:\_Prog\CS\ADO\ExcelADO.xlsx";
        //string _sPass = @"pass";

        public ExcelADO_frm()
        {
            InitializeComponent();
        }

        private void ExcelADO_frm_Load(object sender, EventArgs e)
        {

        }

        //#############################################################
        /// <summary>
        /// [***] 範囲指定_ヘッダあり
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        //#############################################################
        private void btn_範囲指定_ヘッダあり_Click(object sender, EventArgs e)
        {

            DataView dv = mth_範囲指定_HDR_YES();
            this.dgv.DataSource = dv;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private DataView mth_範囲指定_HDR_YES()
        {
            DataTable dt = new DataTable();


            //---------------------------------------------------------------
            var constr = new OleDbConnectionStringBuilder
            {
                ["Provider"] = "Microsoft.ACE.OLEDB.12.0",
                ["Data Source"] = _sDataSorce,
                ["Extended Properties"] = "Excel 12.0 Xml;HDR=YES;IMEX=1;"
            }.ToString();

            OleDbConnection cn = new OleDbConnection(constr);

            cn.Open();

            //---------------------------------------------------------------
            // 
            //---------------------------------------------------------------
            string sSQL = "";
            //sSQL += "SELECT * FROM 商品";
            sSQL += "SELECT * FROM [Sheet1$B2:D9]";

            OleDbDataAdapter ad = new OleDbDataAdapter(sSQL, cn);
            ad.Fill(dt);

            //++++++++++++++++++++++++++++++++++
            // Get
            //++++++++++++++++++++++++++++++++++
            DataView view = new DataView(dt, "部門コード = 'C-001'", "", DataViewRowState.CurrentRows);

            //cn.Close();

            //---------------------------------------------------------------
            return view;
        }

        //#############################################################
        /// <summary>
        /// [***] 範囲指定_ヘッダなし
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        //#############################################################
        private void btn_範囲指定_ヘッダなし_Click(object sender, EventArgs e)
        {

            DataView dv = mth_範囲指定_HDR_NO();
            this.dgv.DataSource = dv;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private DataView mth_範囲指定_HDR_NO()
        {
            DataTable dt = new DataTable();


            //---------------------------------------------------------------
            var constr = new OleDbConnectionStringBuilder
            {
                ["Provider"] = "Microsoft.ACE.OLEDB.12.0",
                ["Data Source"] = _sDataSorce,
                ["Extended Properties"] = "Excel 12.0 Xml;HDR=NO;IMEX=1;"
            }.ToString();

            OleDbConnection cn = new OleDbConnection(constr);

            cn.Open();

            //---------------------------------------------------------------
            // 
            //---------------------------------------------------------------
            string sSQL = "";
            //sSQL += "SELECT * FROM 商品";
            sSQL += "SELECT * FROM [Sheet1$B3:D9]";

            OleDbDataAdapter ad = new OleDbDataAdapter(sSQL, cn);
            ad.Fill(dt);

            //++++++++++++++++++++++++++++++++++
            // Get
            //++++++++++++++++++++++++++++++++++
            DataView view = new DataView(dt, "F3 = 'C-001'", "", DataViewRowState.CurrentRows);

            //cn.Close();

            //---------------------------------------------------------------
            return view;
        }

        //#############################################################
        /// <summary>
        /// [***] 名前付き範囲
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        //#############################################################
        private void btn_名前付き範囲_Click(object sender, EventArgs e)
        {
            DataView dv = mth_名前付き範囲();
            this.dgv.DataSource = dv;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private DataView mth_名前付き範囲()
        {
            DataTable dt = new DataTable();


            //---------------------------------------------------------------
            var constr = new OleDbConnectionStringBuilder
            {
                ["Provider"] = "Microsoft.ACE.OLEDB.12.0",
                ["Data Source"] = _sDataSorce,
                ["Extended Properties"] = "Excel 12.0 Xml;HDR=YES;IMEX=1;"
            }.ToString();

            OleDbConnection cn = new OleDbConnection(constr);

            cn.Open();

            //---------------------------------------------------------------
            // 
            //---------------------------------------------------------------
            string sSQL = "";
            //sSQL += "SELECT * FROM 商品";
            sSQL += "SELECT * FROM [lst_社員]";
            //sSQL += " WHERE 社員番号 = '100020'";

            OleDbDataAdapter ad = new OleDbDataAdapter(sSQL, cn);
            ad.Fill(dt);

            //++++++++++++++++++++++++++++++++++
            // Get
            //++++++++++++++++++++++++++++++++++
            // arg：データテーブル, 条件, 並び替え, DataViewRowState.CurrentRows
            DataView view = new DataView(dt, "社員番号 = '100020'", "", DataViewRowState.CurrentRows);

            //cn.Close();

            //---------------------------------------------------------------
            return view;
        }

        //#############################################################
        /// <summary>
        /// [***] シート指定
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        //#############################################################
        private void btn_シート指定_Click(object sender, EventArgs e)
        {
            DataView dv = シート指定();
            this.dgv.DataSource = dv;

        }
        private DataView シート指定()
        {
            DataTable dt = new DataTable();


            //---------------------------------------------------------------
            var constr = new OleDbConnectionStringBuilder
            {
                ["Provider"] = "Microsoft.ACE.OLEDB.12.0",
                ["Data Source"] = _sDataSorce,
                ["Extended Properties"] = "Excel 12.0 Xml;HDR=YES;IMEX=1;"
            }.ToString();

            OleDbConnection cn = new OleDbConnection(constr);

            cn.Open();

            //---------------------------------------------------------------
            // 
            //---------------------------------------------------------------
            string sSQL = "";
            //sSQL += "SELECT * FROM 商品";
            sSQL += "SELECT * FROM [Sheet1$]";
            //sSQL += " WHERE 社員番号 = '100020'";

            OleDbDataAdapter ad = new OleDbDataAdapter(sSQL, cn);
            ad.Fill(dt);

            //++++++++++++++++++++++++++++++++++
            // Get
            //++++++++++++++++++++++++++++++++++
            // arg：データテーブル, 条件, 並び替え, DataViewRowState.CurrentRows
            DataView view = new DataView(dt, "社員番号 = '100020'", "", DataViewRowState.CurrentRows);

            //cn.Close();

            //---------------------------------------------------------------
            return view;
        }

        //#############################################################
        /// <summary>
        /// [***] 名前付き範囲_COUNT
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        //#############################################################
        private void btn_名前付き範囲_COUNT_Click(object sender, EventArgs e)
        {
            int num = mth_名前付き範囲_COUNT();
            Console.WriteLine(num);
        }
        private int mth_名前付き範囲_COUNT()
        {
            DataTable dt = new DataTable();


            //---------------------------------------------------------------
            var constr = new OleDbConnectionStringBuilder
            {
                ["Provider"] = "Microsoft.ACE.OLEDB.12.0",
                ["Data Source"] = _sDataSorce,
                ["Extended Properties"] = "Excel 12.0 Xml;HDR=YES;IMEX=1;"
            }.ToString();

            OleDbConnection cn = new OleDbConnection(constr);

            cn.Open();

            //---------------------------------------------------------------
            // 
            //---------------------------------------------------------------
            string sSQL = "";
            //sSQL += "SELECT * FROM 商品";
            sSQL += "SELECT COUNT(*) FROM [lst_社員]";
            //sSQL += " WHERE 社員番号 = '100020'";

            OleDbCommand cmd = new OleDbCommand(sSQL, cn);
            int num = (int)cmd.ExecuteScalar();


            cn.Close();

            //---------------------------------------------------------------
            return num;
        }
    }
}