I am trying to embed the Excel into the WPF application. The reason is, the user of mine must open a large (5000+ lines, 100+ columns) readonly excel, filter it, and then I have to extract the lines as shown and do alot of postprocessing. I am trying to use interop for this. Taking the tutorial here https://www.cnblogs.com/redalert2c/articles/2760352.html I now came to the following result - Excel is shown in my window as embedded, I can open a file by clicking a button in my GUI, I also can interact with menus in excel (switch ribbon bar stuff, select dropdowns, open dialogs...). But, I cant interact with the sheet itself! Selected cell is just A1 in the first tab, and I can neither select other tab, nor scroll around nor filter. What could be a problem?
My code:
public partial class ExcelView : UserControl
{
/**
* Solution based upon https://www.cnblogs.com/redalert2c/articles/2760352.html
* */
public ExcelView()
{
InitializeComponent();
Init();
// setup the Handler to ExcelView_Resize
this.Resize += new EventHandler(ExcelViewer_Resize);
}
#region Fields
private Application application;
private Process process;
public IntPtr excelHandle;
private bool initialized = false;
private Workbooks workbooks;
private Workbook workbook;
private CultureInfo threadCulture;
#endregion Fields
#region Hosting Excel Properties
public bool Saved { get { return workbook.Saved; } }
public Microsoft.Office.Interop.Excel.Application Application { get { return application; } }
public Workbook Workbook { get { return workbook; } }
#endregion Hosting Excel Properties
#region Proxy Method
public void Init()
{
threadCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
application = new Application();
application.WindowState = XlWindowState.xlNormal;
application.Visible = true;
application.DisplayFormulaBar = true;
excelHandle = new IntPtr(application.Hwnd); // get the excelHandle, it is created in a separate process, through the use of
// Hwnd, we can get to the handle to the main Wnd
SetParent(excelHandle, this.Handle); // set the current Host as the Parent 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);
initialized = true; // do not initalize a second tieme
}
public void OpenFile(string fileName, bool isExclusive = true, bool isReadonly = true)
{
if (!initialized)
Init();
if (isExclusive && application.ActiveWorkbook != null) // close existing apps
application.ActiveWorkbook.Close(false); // close without saving
workbooks = application.Workbooks;
workbook = workbooks.Open(fileName, isReadonly); // Open mode - readonly
}
public void CloseExcel()
{
InternalCloseExcel();
}
private void InternalCloseExcel()
{
try
{
if (workbook != null)
{
workbook.Close(false);
Marshal.ReleaseComObject(workbooks); // take care of the ref counting?
Marshal.ReleaseComObject(workbook);
if (application != null)
{
application.Quit();
Marshal.ReleaseComObject(application);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
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()
{
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);
}
#region Internal Handlers
// once the host is resized, the content should shall resize according to the new host
private void ExcelViewer_Resize(object sender, EventArgs args)
{
if (excelHandle != IntPtr.Zero)
{
SetWindowPos(excelHandle, new IntPtr(0), 0, 0, this.Width, this.Height, SWP_NOACTIVATE);
}
}
#endregion Internal Handlers
#endregion Proxy Method
#region Override
protected override void OnHandleDestroyed(EventArgs e)
{
CloseExcel();
base.OnHandleDestroyed(e);
}
#endregion Override
#region P/Invoke
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);
#endregion P/Invoke
}
And it is embedded like this:
<WindowsFormsHost Grid.Row="0" IsManipulationEnabled="True">
<excel:ExcelView x:Name="excel"/>
</WindowsFormsHost>
EDIT: I tried to remove integration into my WPF just starting the excel in separate window per interop (commented out the this.Resize line and the Init method after excelHandle= line) and then loading the document - in separate window I can easily interact and do what I want inside the worksheet.... Just not pretty enough! Looks like the excel worksheet is not getting any mouse or keyboard events or so... just the
