プログラミングのメモ

プログラミングの学び直し備忘録

トップ > C# > C#：Excel：COM

C#：Excel：COM

C#

設定

using System.Runtime.InteropServices;

// Microsoft Excel 16.0 Object Library
using Excel = Microsoft.Office.Interop.Excel;

ひな形

フォーム

開いているブック名リストと最初のブックのシートリスト取得

using System.Runtime.InteropServices;
// [COM]
using Excel = Microsoft.Office.Interop.Excel;

namespace Excel_COM
{
    public partial class frnmMain : Form
    {
        //************************************************************
        /// <summary>
        /// [Excel]開いているブック・シート名
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        //************************************************************
        private void btnWb_Names_Click(object sender, EventArgs e)
        {
            var btn = (Button)sender;

            //==============================
            /* 初期化 */
            //==============================
            cboWbs.Text = "";
            cboWbs.Items.Clear();

            lstWss.Text = "";
            lstWss.Items.Clear();

            //=========================================
            /* 処理 */
            //=========================================
            var Xl = new myXl_Proc.clsXl_Proc();

            /* 起動しているExcel ブック名 */
            var slstWbNames = Xl.mGet_wbNames();

            if (slstWbNames.Count < 1)
                return;
            slstWbNames.ForEach(c => cboWbs.Items.Add(c));
            cboWbs.SelectedIndex = 0;

            /* 指定ブックのシート名 */
            var slstWsNames = Xl.mGet_wsNames(cboWbs.Text);
            slstWsNames.ForEach(c => lstWss.Items.Add(c));

        }

処理Main

メンバ変数

using System.Runtime.InteropServices;
// [COM]
using Excel = Microsoft.Office.Interop.Excel;

namespace Excel_COM.myXl_Proc
{
    public partial class clsXl_Proc
    {

        Excel.Application xlApp;

        Excel.Windows xlWindows;
        Excel.Window xlWindow;

        Excel.Workbooks xlBooks;
        Excel.Workbook xlBook;

        Excel.Sheets xlSheets;
        Excel.Worksheets xlWSheets;
        Excel.Worksheet xlWSheet;

        Excel.Range xlRng;

[Excel]起動しているブック名

        //************************************************************
        /// <summary>
        /// [Excel]起動しているブック名
        /// </summary>
        /// <returns></returns>
        //************************************************************
        public List<string> mGet_wbNames()
        {

            //==============================
            /* 初期化 */
            //==============================
            var slstWbNames = new List<string>();

            //=========================================
            /* Excelプロセス確認 */
            //=========================================
            if (!mChk_ExcelProcessIsOne())
                return slstWbNames;

            //=========================================
            /* 処理 */
            //=========================================
            /* Excel Obj */
            xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
            xlBooks = xlApp.Workbooks;

            /* 起動しているExcel */
            slstWbNames = myXlWb.clsXlWb.mGet_WbNames(xlBooks);

            //=========================================
            /* 修了処理 */
            //=========================================
            mPrc_EndProc();

            //=========================================
            /* 結果 */
            //=========================================
            return slstWbNames;
        }
    }
}

[Excel]ブックのシート名リスト

        //************************************************************
        /// <summary>
        /// [Excel]ブックのシート名リスト
        /// </summary>
        /// <returns></returns>
        //************************************************************
        public List<string> mGet_wsNames(string sWbName)
        {

            //==============================
            /* 初期化 */
            //==============================
            var slstWsNames = new List<string>();

            //=========================================
            /* Excelプロセス確認 */
            //=========================================
            if (!mChk_ExcelProcessIsOne())
                return slstWsNames;

            //=========================================
            /* 処理 */
            //=========================================
            /* Excel Obj */
            xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
            xlBooks = xlApp.Workbooks;
            xlBook = xlBooks[sWbName];
            xlSheets = xlBook.Sheets;
            //xlWSheets = xlBook.Worksheets;

            /* 起動しているExcel */
            //slstWsNames = myXlWs.clsXlWs.mGet_WsNames(xlWSheets);
            slstWsNames = myXlWs.clsXlWs.mGet_WsNames(xlSheets);

            //=========================================
            /* 修了処理 */
            //=========================================
            mPrc_EndProc();

            //=========================================
            /* 結果 */
            //=========================================
            return slstWsNames;
        }

共通処理

using System.Windows.Forms;

using System.Runtime.InteropServices;
// [COM]
using Excel = Microsoft.Office.Interop.Excel;

namespace Excel_COM.myXl_Proc
{
    public partial class clsXl_Proc
    {
        //*************************************************************
        /// <summary>
        /// [Excel]修了処理
        /// </summary>
        //*************************************************************
        public void mPrc_EndProc()
        {
            //※※ 修了処理 ※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
            if (xlRng != null) { Marshal.ReleaseComObject(xlRng); xlRng = null; }
            if (xlWSheet != null) { Marshal.ReleaseComObject(xlWSheet); xlWSheet = null; }
            if (xlWSheets != null) { Marshal.ReleaseComObject(xlWSheets); xlWSheets = null; }
            if (xlSheets != null) { Marshal.ReleaseComObject(xlSheets); xlSheets = null; }
            if (xlBook != null) { Marshal.ReleaseComObject(xlBook); xlBook = null; }
            if (xlBooks != null) { Marshal.ReleaseComObject(xlBooks); xlBooks = null; }
            if (xlWindow != null) { Marshal.ReleaseComObject(xlWindow); xlWindow = null; }
            if (xlWindows != null) { Marshal.ReleaseComObject(xlWindows); xlWindows = null; }
            GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();

            if (xlApp != null) { Marshal.ReleaseComObject(xlApp); xlApp = null; }
            GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();
            //※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

        }

        //***************************************************
        /// <summary>
        /// [Excel]プロセス確認
        /// </summary>
        /// <returns></returns>
        //***************************************************
        public bool mChk_ExcelProcessIsOne()
        {
            string sProcName = "Excelプロセス確認";
            bool bRes = true;
            string sMsg = string.Empty;

            //======================================
            /* Excelプロセス */
            //======================================
            /* バックグラウンドExcel Kill */
            (new myProcess.clsProcess()).mPrc_BackgroundKill("EXCEL");

            /* Excelプロセス確認 */
            int xlProcessCnt = System.Diagnostics.Process.GetProcessesByName("EXCEL").Count();
            if (xlProcessCnt != 1)
            {
                sMsg = (xlProcessCnt > 1) ?
                        "Excelプロセスが２つ以上起動しています。" :
                        "Excelプロセスが起動していません。";
                MessageBox.Show(sMsg, sProcName, MessageBoxButtons.OK, icon: MessageBoxIcon.Exclamation);
                bRes = false;

            }

            return bRes;

        }

    }
}

処理

[ブック]開いているブック名

using System.Runtime.InteropServices;
// [COM]
using Excel = Microsoft.Office.Interop.Excel;

namespace Excel_COM_10.myXlWb
{
    class clsXlWb
    {
        //*********************************************************
        /// <summary>
        /// [ブック]開いているブック名
        /// </summary>
        /// <param name="wbs"></param>
        /// <returns></returns>
        //*********************************************************
        public static List<string> mGet_WbNames(Excel.Workbooks wbs)
        {
            var slstWbNames = new List<string>();

            foreach (Excel.Workbook wb in wbs)
                slstWbNames.Add(wb.Name);

            return slstWbNames;
        }
    }
}

[Excel]ブックのシート名リスト

        //************************************************************
        /// <summary>
        /// [シート]シートリスト
        /// </summary>
        /// <param name="wss"></param>
        /// <returns></returns>
        //************************************************************
        public static List<string> mGet_WsNames(Excel.Sheets wss)
        {
            var slstWsNames = new List<string>();

            foreach (Excel.Worksheet ws in wss)
                slstWsNames.Add(ws.Name);

            return slstWsNames;
        }

バックグラウンドプロセスKILL

(new myProcess.clsProcess()).mPrc_BackgroundKill("EXCEL");

        //*************************************************
        /// <summary>
        /// バックグラインドプロセス　KILL
        /// </summary>
        /// <param name="sName"></param>
        //*************************************************
        public void mPrc_BackgroundKill(string sName)
        {
            foreach (var p in System.Diagnostics.Process.GetProcessesByName(sName))
            {
                if (p.MainWindowTitle == "")
                {
                    p.Kill();
                }
            }

            System.Threading.Thread.Sleep(100);

        }

COM解放

Office オートメーションで割り当てたオブジェクトを解放する - Part1 | Microsoft Docs"

ブック

[ブック]新規作成

        //*********************************************************
        /// <summary>
        /// [ブック]新規作成
        /// </summary>
        //*********************************************************
        public void mPrc_WbsAdd()
        {
            /* バックグラウンドプロセス Kill */
            (new catProcess.clsProcess()).mPrc_BackgroundKill("EXCEL");

            /**/
            int xlProcessCnt = System.Diagnostics.Process.GetProcessesByName("EXCEL").Count();
            if (xlProcessCnt == 1)
            {
                /* 起動しているExcelで新規作成 */
                xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
                xlBooks = xlApp.Workbooks;
                xlBook = xlBooks.Add();
            }
            else if (xlProcessCnt == 0)
            {
                /* Excelを起動 */
                var p = new System.Diagnostics.Process();
                p.StartInfo.FileName = "excel.exe";

                // ↓スプラッシュウィンドウから遷移しなかった
                //p.StartInfo.Arguments = @" /p " + @"""d:\""";
                //p.StartInfo.Arguments = @" /s ";

                p.Start();
                bool res = p.WaitForInputIdle();   //起動待ち→すぐに抜けちゃう

                /* Excel起動待ち */
                do
                {
                    try
                    {
                        xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
                        break;
                    }
                    catch (Exception)
                    {
                        //throw;
                    }

                } while (true);

                ///* 起動しているExcelで新規作成 */
                xlBooks = xlApp.Workbooks;
                xlBook = xlBooks.Add();
            }
            else
            {
                System.Windows.Forms.MessageBox.Show("Excelプロセスが２つ以上起動しています。");
            }

            //※※ 修了処理 ※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
            if (xlRng != null) { Marshal.ReleaseComObject(xlRng); xlRng = null; }
            if (xlWSheet != null) { Marshal.ReleaseComObject(xlWSheet); xlWSheet = null; }
            if (xlSheets != null) { Marshal.ReleaseComObject(xlSheets); xlSheets = null; }
            if (xlBook != null) { Marshal.ReleaseComObject(xlBook); xlBook = null; }
            if (xlBooks != null) { Marshal.ReleaseComObject(xlBooks); xlBooks = null; }
            if (xlRng != null) { Marshal.ReleaseComObject(xlRng); xlRng = null; }
            GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();

            if (xlApp != null) { Marshal.ReleaseComObject(xlApp); xlApp = null; }
            GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();
            //※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

        }

[ブック]開いているブック

    class clsXlWb
    {
        //*********************************************************
        /// <summary>
        /// [ブック]開いているブック名
        /// </summary>
        /// <param name="wbs"></param>
        /// <returns></returns>
        //*********************************************************
        public static List<string> mGet_WbNames(Excel.Workbooks wbs)
        {
            var slstWbNames = new List<string>();

            foreach (Excel.Workbook wb in wbs)
                slstWbNames.Add(wb.Name);

            return slstWbNames;
        }

[ブック]開いているかどうか

    class clsXlWb
    {
        //*********************************************************
        /// <summary>
        /// [ブック]開いているかどうか
        /// </summary>
        /// <param name="wbs"></param>
        /// <param name="sWbName"></param>
        /// <returns></returns>
        //*********************************************************
        public static bool mChk_IsOpen(Excel.Workbooks wbs, string sWbName)
        {
            bool bExist = false;

            foreach (Excel.Workbook wb in wbs)
            {
                if (wb.Name== sWbName)
                {
                    bExist = true;
                    break;
                }

            }

            return bExist;
        }

[ブック]ブックの選択（Activate）

        //*********************************************************
        /// <summary>
        /// [ブック]ブックの選択（Activate）
        /// </summary>
        /// <param name="wb"></param>
        //*********************************************************
        public void mPrc_Activate(Excel.Workbook wb)
        {
            wb.Activate();
        }

[ブック]表示　NOMAL、MAX、MIN

        //*********************************************************
        /// <summary>
        /// [ブック]表示　NOMAL、MAX、MIN
        /// </summary>
        /// <param name="wbWin"></param>
        /// <param name="sStat"></param>
        //*********************************************************
        public static void mPrc_WindowsState(Excel.Window wbWin, string sStat)
        {
            var ews = Excel.XlWindowState.xlNormal;

            switch (sStat)
            {
                case "NOMAL": ews = Excel.XlWindowState.xlNormal;    break;
                case "MAX":   ews = Excel.XlWindowState.xlMaximized; break;
                case "MIN":   ews = Excel.XlWindowState.xlMinimized; break;
                default:
                    break;
            }
        }

[ブック]表示／非表示

            xlApp.Windows["TEST.xlsx"].Visible = false;
            xlApp.Windows["TEST.xlsx"].Visible = true;

シート

［シート］シートリスト

        //*********************************************************
        /// <summary>
        /// [シート]シートリスト
        /// </summary>
        /// <param name="xlBook"></param>
        /// <returns></returns>
        //*********************************************************
        public List<string> mGet_WsName(Excel.Workbook xlBook)
        {
            List<string> slstWsNames = new List<string>();

            foreach (Excel.Worksheet item in xlBook.Sheets)
            {
                slstWsNames.Add(item.Name);
            }

            return slstWsNames;

        }

［シート］シートの選択(Activate)

        //*********************************************************
        /// <summary>
        /// [シート]シートの選択(Activate)
        /// </summary>
        /// <param name="ws"></param>
        //*********************************************************
        public void mPrc_Activate(Excel.Worksheet ws)
        {
            ws.Activate();
        }

その他

ブックを選択して、シートを選択

【開いているブック・選択】

        //*********************************************************
        /// <summary>
        /// [ブック]開いているブック
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        //*********************************************************
        private void btnWbs_pName_Click(object sender, EventArgs e)
        {
            //====================
            /* 初期化 */
            //====================
            cboWbs_pName.Text = "";
            cboWbs_pName.Items.Clear();

            lstWwokSheet.Text = "";
            lstWwokSheet.Items.Clear();


            List<string> slstWbNames = null;

            //=========================================
            /* バックグラウンドプロセス Kill */
            //=========================================
            (new myProcess.clsProcess()).mPrc_BackgroundKill("EXCEL");

            //=========================================
            /* 処理 */
            //=========================================
            int xlProcessCnt = System.Diagnostics.Process.GetProcessesByName("EXCEL").Count();
            if (xlProcessCnt == 1)
            {
                /* 起動しているExcel */
                xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
                xlBooks = xlApp.Workbooks;
                slstWbNames = (new myExcelCOM.clsWorkBook()).mGet_WbsName(xlBooks);
            }
            else if (xlProcessCnt > 1)
            {
                System.Windows.Forms.MessageBox.Show("Excelプロセスが２つ以上起動しています。");
                return;
            }

            //==============================
            /* 結果 */
            //==============================
            if (slstWbNames != null)
            {
                slstWbNames.ForEach(c => cboWbs_pName.Items.Add(c));
                cboWbs_pName.SelectedIndex = 0;
            }

            //※※ 修了処理 ※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
            if (xlRng != null) { Marshal.ReleaseComObject(xlRng); xlRng = null; }
            if (xlWSheet != null) { Marshal.ReleaseComObject(xlWSheet); xlWSheet = null; }
            if (xlSheets != null) { Marshal.ReleaseComObject(xlSheets); xlSheets = null; }
            if (xlBook != null) { Marshal.ReleaseComObject(xlBook); xlBook = null; }
            if (xlBooks != null) { Marshal.ReleaseComObject(xlBooks); xlBooks = null; }
            if (xlRng != null) { Marshal.ReleaseComObject(xlRng); xlRng = null; }
            GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();

            if (xlApp != null) { Marshal.ReleaseComObject(xlApp); xlApp = null; }
            GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();
            //※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
        }

【ブック→シートリスト】

        //*********************************************************
        /// <summary>
        /// [シート]シートリスト
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        //*********************************************************
        private void cboWbs_pName_SelectedIndexChanged(object sender, EventArgs e)
        {
            var cbo = (ComboBox)sender;

            //====================
            /* 初期化 */
            //====================
            lstWwokSheet.Text = "";
            lstWwokSheet.Items.Clear();

            List<string> slstWsNames = null;

            //=========================================
            /* バックグラウンドプロセス Kill */
            //=========================================
            (new myProcess.clsProcess()).mPrc_BackgroundKill("EXCEL");

            //=========================================
            /* 処理 */
            //=========================================
            int xlProcessCnt = System.Diagnostics.Process.GetProcessesByName("EXCEL").Count();
            if (xlProcessCnt == 1)
            {
                /* 起動しているExcel */
                xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
                xlBooks = xlApp.Workbooks;
                xlBook = xlBooks[cbo.Text];
                slstWsNames = (new myExcelCOM.clsWorkSheets()).mGet_WsName(xlBook);
            }
            else if (xlProcessCnt > 1)
            {
                System.Windows.Forms.MessageBox.Show("Excelプロセスが２つ以上起動しています。");
                return;
            }

            //==============================
            /* 結果 */
            //==============================
            if (slstWsNames != null)
            {
                slstWsNames.ForEach(c => lstWwokSheet.Items.Add(c));
                lstWwokSheet.SelectedIndex = 0;
            }

            //※※ 修了処理 ※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
            if (xlRng != null) { Marshal.ReleaseComObject(xlRng); xlRng = null; }
            if (xlWSheet != null) { Marshal.ReleaseComObject(xlWSheet); xlWSheet = null; }
            if (xlSheets != null) { Marshal.ReleaseComObject(xlSheets); xlSheets = null; }
            if (xlBook != null) { Marshal.ReleaseComObject(xlBook); xlBook = null; }
            if (xlBooks != null) { Marshal.ReleaseComObject(xlBooks); xlBooks = null; }
            if (xlRng != null) { Marshal.ReleaseComObject(xlRng); xlRng = null; }
            GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();

            if (xlApp != null) { Marshal.ReleaseComObject(xlApp); xlApp = null; }
            GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();
            //※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
        }

【シート選択(Activate)】

        //***********************************************************
        /// <summary>
        /// [シート]シートの選択(Activate)
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        //***********************************************************
        private void lstWwokSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            var cbo = (ComboBox)cboWbs_pName;
            var lst = (ListBox)sender;

            //====================
            /* 初期化 */
            //====================

            //=========================================
            /* バックグラウンドプロセス Kill */
            //=========================================
            (new myProcess.clsProcess()).mPrc_BackgroundKill("EXCEL");

            //=========================================
            /* 処理 */
            //=========================================
            int xlProcessCnt = System.Diagnostics.Process.GetProcessesByName("EXCEL").Count();
            if (xlProcessCnt == 1)
            {
                /* 起動しているExcel */
                xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
                xlBooks = xlApp.Workbooks;
                if (!(new myExcelCOM.clsWorkBook()).mChk_IsOpen(xlBooks, cbo.Text))
                    return;
                xlBook = xlBooks[cbo.Text];
                xlWSheet = xlBook.Sheets[lst.SelectedItem];

                (new myExcelCOM.clsWorkSheets()).mPrc_Activate(xlWSheet);
            }
            else if (xlProcessCnt > 1)
            {
                System.Windows.Forms.MessageBox.Show("Excelプロセスが２つ以上起動しています。");
                return;
            }

            //==============================
            /* 結果 */
            //==============================

            //※※ 修了処理 ※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
            if (xlRng != null) { Marshal.ReleaseComObject(xlRng); xlRng = null; }
            if (xlWSheet != null) { Marshal.ReleaseComObject(xlWSheet); xlWSheet = null; }
            if (xlSheets != null) { Marshal.ReleaseComObject(xlSheets); xlSheets = null; }
            if (xlBook != null) { Marshal.ReleaseComObject(xlBook); xlBook = null; }
            if (xlBooks != null) { Marshal.ReleaseComObject(xlBooks); xlBooks = null; }
            if (xlRng != null) { Marshal.ReleaseComObject(xlRng); xlRng = null; }
            GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();

            if (xlApp != null) { Marshal.ReleaseComObject(xlApp); xlApp = null; }
            GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();
            //※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

        }