none
Excel从2010升级到2013后,通过Excel Object Library无法获取Application的ActiveWorkbook等字段:can't fetch ActiveWorkbook field after upgraded Excel from 2010 to 2013 RRS feed

  • 问题

  • 背景描述 Background description:

    (1)输入输出:需要将许多新版 (客户的)外部BOM表与旧版(公司的)内部BOM表进行对比,以便更新内部BOM及生成变更清单,然后产生零件购买清单;

           Input/Output: need to compare a lot of (customer’s) outside BOM tables with (our) inside BOM table,

           to update inside BOM table and create changes list, and create buy list;

    (2) 实现方法:由于输入输出数据均为Excel表格,且输入数据可能存在问题,程序运行中有时需要停下来,

         由用户修正问题后再继续运行,因此采用将Excel集成到C#/WPF的方式,开发BOM Tool程序;

         Implementation: as input/output data is Excel table, and if the input data has mistake, we need to

         suspend BOM Tool application till user corrected the input data, so I developed the BOM Tool by

         C#/WPF, and host several Excel windows inside of BOM Tool application.


    (3) 实现描述:project description

       (I) 添加Microsoft Office Object LibraryMicrosoft Excel Object Library这两个库;

           Added reference libraries: Microsoft Office Object Library and Microsoft Excel Object Library;

       (II) 定义ExcelView这个WinForm自定义控件,该控件里嵌入了Excel窗口;代码见如下附档;

           Defined a WinForm user control named ‘ExcelView’, embedded Excel window in this control;

           The code of ExcelView control please look at below attachments;

       (III) WPFXAML代码里,通过<WindowsFormsHost>元素载入ExcelView控件.

           In WPF XAML code: load ExcelView control by <WindowsFormsHost> element.

    Main Issue:

    (1) 该程序是以Excel2010为基础开发出来的,因此当系统里安装的是Excel2010时,运行正常无错误;

        This BOM Tool application was developed base on Excel2010; before, our system installed Excel2010, the BOM Tool run correctly, no exception.

    (2) 今年6月,全公司的Excel2010升级到了2013因此我将Office & Excel库从v14.0(支持Office2010)级到v15.0(支持到Office2013)但升级后程序无法运行!环境的差异详见下表

        Our Excel upgraded from 2010 to 2013 in whole company on June, so I upgraded the Office & Excel Object Library from v14.0 to v15.0; But the application can’t run after upgrading!

     Environment difference: Before After
    System :  Windows 7 (32bit) Windows 7 (32bit)
    .NET Framework Version :  4 4
    Visual Studio Version :  VS 2012 Express for Desktop VS 2012 Express for Desktop
    Office Version :  Office 2010, Excel2010 Office 2013, Excel2013
    Microsoft Office Object Library : v14.0 v15.0
    Microsoft Excel Object Library : v14.0 v15.0


    Detailed Issue:

    (1) 无法运行的主要现象为:从ExcelView”application”属性的许多字段值为null或异常;

        Main issue is: some fields of the ‘application’ attribute in ExcelView object are null or exception;

    (2) 现问题的语句ExcelViewOpenFile()方法里的如下语句:

              workbook = application.Workbooks.Open(filename, isReadonly);

        打开Excel表后,ExcelView象里的application性的ActiveWindowActiveWorkbookActiveSheetActiveCellSelection等字段不应该为null

        The main issue comes from below code in OpenFile() method of ExcelView class:

             workbook = application.Workbooks.Open(filename, isReadonly);

        after opened an excel book, the ActiveWindow,ActiveWorkbook,ActiveSheet,ActiveCell,Selection and some other fields of ‘application’ attribute in ExcelView object should not be null;

    (3) 但是,以前使用Excel2010时,打开Excel表后,上述字段是正确的,不null

        But in the past using Excel2010, after opened an excel book, these fields are correct(not null);

    Detailed Issue:

    (4) 主要的代码如下:ExcelView类的构造方法里,创建内嵌的Excel,并将它赋值给”application属性然后,定OpenFile()方法用于打Excel表格

        Code is: in constructor of ExcelView class, we assign excel application to ‘application’ attribute when creating embedded excel, then defined OpenFile() method for opening excel book;

    using Excel = Microsoft.Office.Interop.Excel;

    public partial class ExcelView : UserControl {

        private Excel.Application application;

        ……

        public ExcelView() { InitializeComponent(); Init(); }

        public void Init() { application = new Excel.Application(); …… }

        public void OpenFile(string fileName, bool isExclusive = true, bool isReadonly = true) {

            ……

            workbook = application.Workbooks.Open(fileName, isReadonly);}

    }

    (5) ExcelView.application的如下字段不应该为null

        Issue: Below fields in ExcelView.application attribute should not be null:

    ActiveWindow

    ActiveWorkbook

    ActiveSheet

    ActiveCell

    Selection

    (6) 获取属性ExcelView.application的如下字段不应该出现异常:

        Issue: Below fields in ExcelView.application attribute should not be exception:

    Assistant  Assistance    CalculateBeforeSave

    Cells  Calculation  Charts

    Columns  COMAddIns    CommandBars

    Rows  DialogSheets    Excel4IntlMacroSheets

    Iteration  MaxChange    MaxIterations

    Names  NewWorkbook  Excel4MacroSheets 

    Sheets  SmartArtColors  SmartArtLayouts

    Worksheets  SmartArtQuickStyles

    问题 Problem:

    (1) 是否将Office/Excel Object Library升级至15.0版后,需要通过其他方式才能获取这些字段的值?如何获取?

        After upgraded Office/Excel Object Library to v15.0, whether need to fetch these fields(which is null/exception) by other ways? How to fetch these fields?

    附注 Additional:

    (1) 如下是 ExcelView.cs的代码:Below is the code of ExcelView.cs

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Drawing;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Runtime.InteropServices;
    using System.Diagnostics;
    using System.Globalization;
    using System.Threading;
    using System.Windows;
    using System.Windows.Interop;
    using Excel = Microsoft.Office.Interop.Excel;


    namespace BomUpdater.LmtControlTmp
    {
        public partial class ExcelView : UserControl
        {
            private Excel.Application application;
            private Process process;
            public IntPtr excelHandle;
            private bool initialized = false;
            private Excel.Workbooks workbooks;
            private Excel.Workbook workbook;
            private CultureInfo threadCulture;

            public ExcelView()
            {
                InitializeComponent();
                // init to the Excel.Application
                Init();
            }
            public void Init()
            {
                threadCulture = Thread.CurrentThread.CurrentCulture;
                Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

                application = new Excel.Application();
                application.WindowState = Excel.XlWindowState.xlNormal;

                application.Visible = true;
                application.DisplayFormulaBar = true;


                // get the excelHandle, it is created in a separate process, through the use of
                excelHandle = new IntPtr(application.Hwnd);
                // Hwnd, we can get to the handle to the main Wnd
                SetParent(excelHandle, this.Handle);       // set the current Host as the _Father of the Excel Hwnd
                int lngStyle = GetWindowLong(excelHandle, GWL_STYLE);
                lngStyle = lngStyle ^ WS_CAPTION;
                lngStyle = lngStyle ^ WS_SIZEBOX;

                SetWindowLong(excelHandle, GWL_STYLE, lngStyle); // apply the new style
                SetWindowPos(excelHandle, new IntPtr(0), 0, 0, this.Width, this.Height, SWP_FRAMECHANGED);

                int pid = 0;
                GetWindowThreadProcessId(excelHandle, out pid);
                process = Process.GetProcessById(pid);

                // setup the Handler to ExcelView_Resize
                this.Resize += new EventHandler(ExcelViewer_Resize);

                // do not initalize a second tieme
                initialized = true;
            }

            public Excel.Application Application
            {
                get { return application; }
            }
            public Excel.Workbook Workbook
            {
                get { return workbook; }
            }
            public bool Saved
            {
                get { return workbook.Saved; }
            }

            public Excel.Workbook CreateNewBook()
            {
                if (!initialized) Init();
                // close existing apps : without saving
                if (application.ActiveWorkbook != null)
                {
                    application.ActiveWorkbook.Close(false);
                }
                // create new workbook : customize method
                workbook = application.Workbooks.Add();
                workbooks = application.Workbooks;
                return workbook;
            }
            public void OpenFile(string fileName, bool isExclusive = true, bool isReadonly = true)
            {
                if (!initialized) Init();
                // close existing apps : without saving
                if (isExclusive && application.ActiveWorkbook != null)
                {
                    application.ActiveWorkbook.Close(false);
                }
                // open target file
                workbook = application.Workbooks.Open(fileName, isReadonly);
                workbooks = application.Workbooks;
            }
            public void CloseExcel()
            {
                InternalCloseExcel();
            }
            private void InternalCloseExcel()
            {
                try
                {
                    //// old code: if (workbook != null)
                    if (workbook != null && application.ActiveWorkbook != null)
                    {
                        workbook.Close(false);
                        // take care of the ref counting?
                        Marshal.ReleaseComObject(workbooks);
                        Marshal.ReleaseComObject(workbook);
                        if (application != null)
                        {
                            application.Quit();
                            Marshal.ReleaseComObject(application);
                        }
                    }
                }
                catch (Exception ex)
                {
                    // when closing main window, if this excel view's workbook has been closed,
                    // then will display this error
                    System.Windows.MessageBox.Show(ex.Message, "Error");
                }
                finally
                {
                    workbooks = null;
                    workbook = null;
                    application = null;
                    if (process != null && !process.HasExited)
                    {
                        process.Kill();
                    }
                    Thread.CurrentThread.CurrentCulture = threadCulture;
                    initialized = false;
                }
            }
            public void KillExcel()
            {
                if (process != null && !process.HasExited)
                {
                    process.Kill();
                }
            }
            public void SaveActiveWorkbook()
            {
                if (application.ActiveWorkbook != null) workbook.Save();
            }
            public void SaveActiveBookAs(string filename)
            {
                Debug.Assert(!string.IsNullOrEmpty(filename));
                workbook.SaveAs(filename);
            }
            public void SaveCopyOfActiveBookAs(string filename)
            {
                Debug.Assert(!string.IsNullOrEmpty(filename));
                workbook.SaveCopyAs(filename);
            }

            private void ExcelViewer_Resize(object sender, EventArgs args)
            {
                // once the host is resized, the content should shall resize according to the new host
                if (excelHandle != IntPtr.Zero)
                {
                    SetWindowPos(excelHandle, new IntPtr(0), 0, 0, this.Width, this.Height, SWP_NOACTIVATE);
                }
            }
            protected override void OnHandleDestroyed(EventArgs e)
            {
                CloseExcel();
                base.OnHandleDestroyed(e);
            }

            private const int SWP_FRAMECHANGED = 0x0020;
            private const int SWP_DRAWFRAME = 0x20;
            private const int SWP_NOMOVE = 0x2;
            private const int SWP_NOSIZE = 0x1;
            private const int SWP_NOZORDER = 0x4;
            private const int GWL_STYLE = (-16);
            private const int WS_CAPTION = 0xC00000;
            private const int WS_THICKFRAME = 0x40000;
            private const int WS_SIZEBOX = WS_THICKFRAME;
            private const int SWP_NOACTIVATE = 0x0010;

            [DllImport("user32.dll", SetLastError = true)]
            private static extern IntPtr SetParent(IntPtr hWndChild, IntPtr hWndNewParent);
            [DllImport("user32.dll", SetLastError = true)]
            public static extern int GetWindowLong(IntPtr hWnd, int nIndex);
            [DllImport("user32.dll")]
            public static extern int SetWindowLong(IntPtr hWnd, int nIndex, int dwNewLong);
            [DllImport("User32", SetLastError = true)]
            public static extern bool SetWindowPos(IntPtr hWnd, IntPtr hWndInsertAfter, int X, int Y, int cx, int cy, int uFlags);
            [DllImport("user32.dll", SetLastError = true)]
            public static extern uint GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
            
        }
    }

    (2) 如下是 ExcelView.Designer.cs的代码:Below is the code of ExcelView.Designer.cs

    namespace BomUpdater.LmtControlTmp
    {
        // this is automatic code generated by VS
        partial class ExcelView
        {
            /// <summary> Required designer variable. </summary>
            private System.ComponentModel.IContainer components = null;


            /// <summary> Required method for Designer support - do not modify the contents of this method with the code editor. </summary>
            private void InitializeComponent()
            {
                components = new System.ComponentModel.Container();
                this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            }

            /// <summary> Clean up any resources being used. </summary>
            /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
            protected override void Dispose(bool disposing)
            {
                if (disposing && (components != null)) components.Dispose();
                base.Dispose(disposing);
            }

        }
    }

    2017年8月9日 14:24

全部回复

  • Hi LimitLin,

    感谢你在MSDN论坛发帖。

    因为你的问题涉及到excel,我建议你去下面专门处理excel论坛去发帖。 我将会把这个帖子移到一般性讨论论坛。

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    Best Regards,

    Hart


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2017年8月10日 2:01