locked
Embed Excel to winform RRS feed

  • Question

  •  I am try embed Excel to WinForm with following code:

    Imports System.Collections
    Imports System.ComponentModel
    Imports System.Drawing
    Imports System.Data
    Imports System.Windows.Forms
    Imports System.Runtime.InteropServices
    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Interop.Excel

    Public Class Form1
        <DllImport("User32.dll", EntryPoint:="GetWindowLong")> _
        Private Shared Function GetWindowLong(ByVal HWND As IntPtr, ByVal Index As Integer) As Int32
        End Function

        <DllImport("User32.dll", EntryPoint:="SetWindowLong")> _
        Private Shared Function SetWindowLong(ByVal HWND As IntPtr, ByVal Index As Integer, dwNewLong As Int32) As Int32
        End Function

        <DllImport("user32.dll", EntryPoint:="SetWindowPos")> _
        Private Shared Function SetWindowPos(ByVal hWnd As Integer, ByVal hWndInsertAfter As Integer, ByVal X As Integer, ByVal Y As Integer, ByVal cx As Integer, ByVal cy As Integer, _
       ByVal uFlags As UInteger) As Boolean
            ' window-positioning options
        End Function

        <DllImport("user32.dll", EntryPoint:="MoveWindow")> _
        Private Shared Function MoveWindow(ByVal hWnd As Integer, ByVal X As Integer, ByVal Y As Integer, ByVal nWidth As Integer, ByVal nHeight As Integer, ByVal bRepaint As Boolean) As Boolean
        End Function

        <DllImport("user32.dll")> _
        Private Shared Function SetParent(ByVal hWndChild As Integer, ByVal hWndNewParent As Integer) As Integer
        End Function


        Public Excel As Excel.Application = Nothing
        Public Workbook As Microsoft.Office.Interop.Excel.Workbook

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Excel = New Excel.Application
            Dim ExcelWnd As Integer = Excel.Hwnd

            Workbook = Excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
            Workbook.Activate()
            'Const GWL_STYLE As Integer = -16
            'Const WS_POPUP As Int32 = &H80000000
            'Const WS_CHILD As Int32 = &H40000000
            'Const WS_CAPTION As Int32 = &HC00000
            'Const WS_SIZEBOX As Int32 = &H40000

            'Dim Style As Int32 = GetWindowLong(HWND, GWL_STYLE)
            'Style = (Style And Not (WS_POPUP)) Or WS_CHILD
     
            SetParent(ExcelWnd, Me.Handle.ToInt32())
            Excel.Visible = True

             ResizeExcel(ExcelWnd)
        End Sub

        Private borderWidth As Integer = SystemInformation.Border3DSize.Width
        Private borderHeight As Integer = SystemInformation.Border3DSize.Height
        Private captionHeight As Integer = SystemInformation.CaptionHeight
        Private statusHeight As Integer = SystemInformation.ToolWindowCaptionHeight
        Public Sub ResizeExcel(ExcelWnd As Integer)
            Try
                MoveWindow(ExcelWnd, -3 * borderWidth, -4 * borderHeight - captionHeight, Me.Bounds.Width - 20, Me.Bounds.Height - 20, True)

            Catch ex As Exception

            End Try

        End Sub

        Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
            Excel.Quit()
            Excel = Nothing
            Workbook = Nothing

        End Sub

        Private Sub Form1_Resize(sender As Object, e As EventArgs) Handles Me.Resize
            ResizeExcel(Excel.Hwnd)
        End Sub

    End Class

    after SetParent function excel  set up in ReadOnly mode and i can'to edit content. Could anybody help my to fix this problem.

    Me not interesting ether hosting in webbrowser control nor DSOFramer solutions 


    alex

    • Moved by Mike Feng Thursday, November 8, 2012 2:47 PM (From:.NET Base Class Library)
    • Moved by Mike Feng Friday, November 23, 2012 11:23 AM (From:Visual Basic General)
    Wednesday, November 7, 2012 2:27 PM

Answers

All replies

  • Hi Alex,

    Welcome to the MSDN Forum.

    I tried your code, it works well on my side.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, November 8, 2012 2:47 PM
  • Hi Mike,
    Thank you for your spent time. Unfortunately my code didn't work properly till I changed lines order. 

    Lines order:

           SetParent(ExcelWnd, Me.Handle.ToInt32())
            Excel.Visible = True

    I change to:

                  Excel.Visible = True

                  SetParent(ExcelWnd, Me.Handle.ToInt32())

    and now all work well .

    Unpleasantly what now I must hide Excel putting it to invisible screen area until SetParent. 


    alex

    Friday, November 9, 2012 1:52 PM
  • Hi Alex,

    What version OS do you use?

    I test your code in Win7 64bit, and I didn't change anything, just built a winform project and paste your whole code in the default form, save it, press F5, and then I got the above picture.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, November 9, 2012 2:22 PM
  • Hi Mike,

    I used Win8 64bit,

    But I tested on win7 64 bit and WinXP too. Problem still exist.

    Because my app more complex then my sample, I have still that problem so far.
    And now I found out where that problem appear.
    In my app i need periodically check workbook property SAVED, so after first check Excel at once switch to not editable mode.  This is true only for Excel2013, oldest versions work without discribed  problem. May be you now how to fix that problem.
    For get this problem enough to put one line after SetParent function
     
      SetParent(ExcelWnd, Me.Handle.ToInt32())   
      Dim boolSaved As Boolean = Excel.Workbook.Saved


    alex

    Friday, November 9, 2012 3:18 PM
  • Hi Alex,

    If so, I suggest you to open a new thread on Excel forum:  http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads 

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Just Karl Wednesday, April 15, 2015 7:23 PM
    Monday, November 12, 2012 6:08 AM
  • Hi Alex & Mike,

    Your code works until I did a right click on the embedded excel. A pop up menu opens and then the excel is back to read only i.e not responding to mouse clicks or keyboard types.

    Interestingly the formula text box is the only thing that is editable.

    Do you have the same problem?

    Regards,

    Ary

    • Proposed as answer by freejy Wednesday, March 18, 2015 11:56 AM
    • Unproposed as answer by freejy Wednesday, March 18, 2015 12:11 PM
    Thursday, February 21, 2013 2:30 AM
  • Hi all

    I've been having similar issues - if interested see thread http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/3a928d6d-8d58-4463-9772-8800fcdd5147 for my symptons

    Regards

    Thursday, February 28, 2013 3:09 PM
  • Please try to call below user32.ddl method before SetParent method.

    EnableWindow(hwndExcelHandler, true);

    hwndExcelHandler is Excel Handler


    Uday

    Wednesday, September 18, 2013 7:35 PM
  • could you please how you were able to hide the File bar in Excel?


    Uday

    Wednesday, September 18, 2013 7:42 PM
  • We are experiencing the same problem. In our implementation we are starting the excel process and then we are embedding the excel window in our application. We are forced to do that because the user wants to have all the add-ins enabled. Web browser and dsoframer does not load all the com add-ins. So the only solution was that and it worked perfectly in all the other office versions.

    What I have observed is if all add-ins are disabled everything works fine. By enabling any add-in in the excel, the worksheet becomes somehow readonly.

    I tried the EnableWindow but without any results. The excel worksheet remains in a readonly state.

    Is there any fix for this issue?

    Wednesday, October 30, 2013 8:52 PM
  • I think I found the problem : when the embedded excel app losts focus, either if you click somewhere in the host app, or in another window, and you click back to the embedded excel sheet, it does not catch the focus back. The focus is set to 'idle'.

    I tried this, which is working well for me :

    Declare some API

        <DllImport("user32.dll", SetLastError:=True)> _

        Private Shared Function GetWindowThreadProcessId(ByVal hwnd As IntPtr, _
                                  ByRef lpdwProcessId As Integer) As Integer
        End Function
        <DllImport("user32.dll")> _
        Private Shared Function SetForegroundWindow(ByVal hWnd As IntPtr) As <MarshalAs(UnmanagedType.Bool)> Boolean
        End Function
        <DllImport("user32.dll")> _
        Private Shared Function GetForegroundWindow() As IntPtr
        End Function

    then put this code into a timer :

            Dim iProcessID As IntPtr
            GetWindowThreadProcessId(GetForegroundWindow(), iProcessID)
            Dim myProcess As Process = Process.GetProcessById(iProcessID)
            If myProcess.ProcessName = "Idle" Then
                SetForegroundWindow(ExcelWnd)
            End If

    of course you need to catch the handler or the embedded Excel app

            Dim myProcesses As Process() = Process.GetProcessesByName("EXCEL")
            Dim myProcess As Process, processExists As Boolean = False
            For Each myProcess In myProcesses
                For p = 1 To NbXL
                    If myProcess.Id = XLProcesses(p) Then
                        processExists = True
                        Exit For
                    End If
                Next
                If Not processExists Then
                    NbXL += 1
                    ReDim Preserve XLProcesses(NbXL)
                    XLProcesses(NbXL) = myProcess.Id
                End If
            Next myProcess

    Execute this code once (in case Excel was already loaded outside of your application)

    then create the embedded Excel, and execute this again. the topmost XLProcesses(NbXL) is the right handler

    • Proposed as answer by freejy Wednesday, March 18, 2015 12:12 PM
    Wednesday, March 18, 2015 12:10 PM