从Excel文档中的代码隐藏到达ActiveX或Form对象（文本框） [英] Reach ActiveX or Form objects (textbox) from codebehind in an Excel document

问题描述

excel文件中有几个文本框作为ActiveX对象，我想从代码隐藏中访问它们。

There are several textboxes in an excel file as ActiveX objects and I want to reach them from codebehind.

我正在对其他字段使用ClosedXML，但是我很开放其他建议。

I am using ClosedXML for other fields, but I am open for other suggestions.

推荐答案

要从C＃访问OLE对象，请添加对Microsoft Forms 2.0对象库的引用。您可以遍历控件中所需的复选框和文本框。使用Excel = Microsoft.Office.Interop.Excel享受！

For accessing OLE objects from C#, add reference to Microsoft Forms 2.0 object library. You can iterate through the controls for your desired checkbox and textbox. Enjoy !

using Excel = Microsoft.Office.Interop.Excel;
using VBE = Microsoft.Vbe.Interop.Forms;

private static void ExcelOperation(string xlFileName)
        {
            var xlApp = new Excel.Application();
            var xlWorkbook = xlApp.Workbooks.Open(xlFileName);
            var xlSheet = xlWorkbook.Worksheets["your_sheet_Name"] as Excel.Worksheet;

            try
            {
                Excel.OLEObjects oleObjects = xlSheet.OLEObjects() as Excel.OLEObjects;
                foreach (Excel.OLEObject item in oleObjects)
                {                   
                    if (item.progID == "Forms.TextBox.1")
                    {
                        VBE.TextBox xlTB = item.Object as VBE.TextBox;
                        Console.WriteLine("Name: " + item.Name);
                        Console.WriteLine("Text: " + xlTB.Text);
                        Console.WriteLine("Value: " + xlTB.get_Value());
                        Marshal.ReleaseComObject(xlTB); xlTB = null;
                    }
                    else if (item.progID == "Forms.CheckBox.1")
                    {
                        VBE.CheckBox xlCB = item.Object as VBE.CheckBox;
                        Console.WriteLine("checkbox: " + item.Name);
                        Console.WriteLine("Value: " + xlCB.get_Value());
                        Marshal.ReleaseComObject(xlCB); xlCB = null;
                    }                    

                }

                Marshal.ReleaseComObject(oleObjects); oleObjects = null;
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            Marshal.ReleaseComObject(xlSheet); xlSheet = null;
            xlWorkbook.Close();
            Marshal.ReleaseComObject(xlWorkbook); xlWorkbook = null;
            Marshal.ReleaseComObject(xlApp); xlApp = null;
        }

这篇关于从Excel文档中的代码隐藏到达ActiveX或Form对象（文本框）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！