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

[weixin_38058939]

背景描述 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 Library和Microsoft 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) 在WPF的XAML代码里，通过<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月，全公司的Excel从2010升级到了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) 最先出现问题的语句为ExcelView类的OpenFile()方法里的如下语句：
         
workbook =
application.Workbooks.Open(filename,
isReadonly);
   
当打开Excel表后，ExcelView对象里的application属性的ActiveWindow、ActiveWorkbook、ActiveSheet、ActiveCell、Selection等字段不应该为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);
        }

    }
}